راه‌های افزایش سرعت بکاپ و ریستور | بخش چهارم

راه‌های افزایش سرعت بکاپ و ریستور | بخش چهارم

نوشته شده توسط: حسین عبادی
۲۱ اردیبهشت ۱۴۰۱
زمان مطالعه: 11 دقیقه
۵
(۲)

 

مقدمه

قبل از اینکه دیر شود از اطلاعات خود Backup یا نسخه پشتیبان تهیه نمایید. به جرات می توان گفت یکی از مهمترین و مهمترین وظیفه یک DBA تهیه منظم Backup طی یک سناریو منظم می باشد. شاید به کرات شنیده باشید که داده های فلان سازمان هک شده و سازمان با خطر نابودی مواجه شده است.

روش چهارم جهت افزایش سرعت Backup و Restore استفاده از تکنیک Data Transfer می باشد. در این روش اگر شما سه پارامتر به نام های BufferCount و MaxTransferSize و BlockSize به درستی تنظیم کنید در این صورت سرعت Backup و Restore به شدت بالا خواهد رفت.

این تنظیمات را شما می توانید هم به صورت Command ای انجام دهید و هم می توانید به صورت Wizard ای انجام دهید(لازم به ذکر است که در نسخه های قدیم این تنظیمات را فقط می توانستید به صورت Command ای انجام دهید).

آشنایی با بعضی از اصطلاحات مربوط به حوزه Data Transfer

تعریف Buffer: در این قسمت می خواهیم ببینیم که SQL Server چگونه از Buffer جهت Backup و Restore استفاده می کند. همانطور که همه شما دوستان عزیز می دانید Buffer محل قرارگرفتن داده ها در حافظه می باشد.

چه اتفاقی در طی پروسه بکاپ رخ می دهد(What happens during a backup

پروسه بکاپ گیری SQL Server به این صورت است که، به محض اجرای دستور بکاپ، SQL Server مطابق شکل زیر به کمک یکسری Thread به جان Page های Data File ها افتاده (مبداء) (مرحله اول) و آنها را به Memory Buffers (واقع در حافظه RAM) منتقل می کند(مرحله دوم). سپس از این Memory Buffer اطلاعات خوانده شده و به دیسک یا مدیا شما منتقل (مقصد) می شوند(مرحله سوم). شما می توانید به کمک پارامترهای مربوط به Data Transfer مقدار قرارگیری داده در Memory Buffers را می توانید تغییر دهید، تا سرعت بکاپ گیری یا ریستور شما بالاتر رود.

معرفی پارامترهای Data Transfer

  1. BufferCount: در واقع تعداد کل IO Buffer رو برای بکاپ و ریستور مشخص می کند. در شکل فوق تعداد BufferCount ها ، چهار عدد می باشد.به عبارتی تعداد وانت ها می باشد.

در نسخه های قدیم تعداد پیش فرض BufferCount ده عدد بود که در نسخه SQL Server 2019 به چهارده عدد رسیده است. شما با توجه به سخت افزار سرور و همچنین نوع هاردهای خود می توانید این عدد را ارتقاء دهید تا بهترین حالت را بدست آورید.

  1. پارامتر MaxTransferSize: این پارامتر در واقع مشخص کننده میزان ظرفیت هر کدام از Memory Buffers ها را مشخص می کند.(به عبارت دیگر مقدار انتقال اطلاعات به مدیا را مشخص می کند).(به عبارت دیگر واحدهایی که SQL Server می خواد ببره روی RAM ، چقدر چقدر باشه)(به عبارتی ابعاد وانت ها می باشد این عدد باید بر اساس بایت مشخص شود.همچنین این عدد مضربی از ۶۴KB می باشد. مقدار پیش فرض آن یک مگابایت می باشد. نهایتا شما می توانید سایز این پارامتر را به چهار مگابایت تغییر دهید، که البته باید به بایت نهایتا تغییر یابد.

کافیست عدد ۴ را دو بار در عدد ۱۰۲۴ ضرب کنید تا مقدار بایت آن را بدست آورید (که عدد ۴۱۹۴۳۰۴ خواهد شد).

تذکر: اگر شما BufferCount را در MaxTransferSize ضرب کنید ، اندازه مربوط به کل Buffer شما بدست می آید(در واقع مقدار حافظه مربوط به Buffer سرور شما بدست می آید.).

تذکر: فرض کنید که می خواهید وسایل خانه خود را با تعدادی وانت و یا نیسان و یا خاور جا به جا کنید. در این صورت تعداد آن وانت هایی (و یا نیسان و یا خاور) که انتخاب می کنید معادل BufferCount خواهند بود و ظرفیت جابجایی که می خواهید انتخاب کنید، که آیا می خواهید جهت جابجایی اسباب خانه از وانت استفاده کنید و یا ظرفیت بالاتر می خواهید از نیسان استفاده کنید و یا باز هم می خواهید ظرفیت را بالاتر برده و از خاور جهت جابجایی وسایل خانه استفاده کنید که در حقیقت این ظرفیت، معادل MaxTransferSize می باشد.

  1. پارامتر BlockSize: این پارامتر در واقع مشخص کنند Physical Block Size می باشد، که قرار است روی مدیای شما نوشته شود. ما در مباحث مربوط به دیسک ، یک مبحثی به نام Block Size داریم که در حقیقت مقدار دیتای نوشته شده بر روی دیسک را مشخص می کند(در واقع زمانی که شما Update ای را در حافظه انجام میدهد و می خواهید با همان قالبی (Extent) که اطلاعات به حافظه رفته بودند با همان قالب، Dirty Page ها به دیسک منتقل شوند (مثلا در اثر زدن Checkpoint و یا گرفتن بکاپ ، Page های کثیف به دیسک منتقل می شوند) باید قبلا فرمت دیسک به صورت ۶۴ KB انجام گرفته شده باشد. بهترین گزینه برای پارامتر BlockSize عدد ۶۴ KB می باشد که دقت کنید این عدد را نیز باید به بایت تبدیل و سپس استفاده نمایید. نکته بسیار مهم این است، زمانی که شما سرور را تحویل می گیرید و می خواهید راه اندازی کنید ،حتما درایوهای مربوط به Data File ها و Log File ها و TempDB را با عدد ۶۴ KB فرمت کنید. (به عبارتی برای اینکه بهترین پرفورمنس را بگیرید بهتر است که درایو مربوط به Data Fileها و درایو مربوط به Log File و درایو مربوط به دیتابیس سیستمی TempDB را جدا گرفته و همان ابتدا فرمت ۶۴ KB انجام دهید). نکته دیگر این که شما باید عدد ۶۴ KB را باید در عدد ۱۰۲۴ ضرب کنید تا به بایت تبدیل شود و سپس استفاده کنید.

تذکر: نکته بسیار مهم دیگری که شما باید در همان ابتدا رعایت کنید این است که برای Data File ها بهتر است از Raid، پنج و برای Log File بهترین گزینه استفاده از Raid ده می باشد. همچنین برای دیتابیس سیستمی TempDB بهترین گزینه استفاده از Raid صفر می باشد. البته این مباحث مربوط به سخت افزار و شبکه می شود. در ضمن برای گرفتن بهترین کارایی اگر می توانید دیسک را نیز SSD انتخاب کنید.

دوستان دقت کنید که برای اطلاعات بیشتر راجع به Raid ها و مطالب فوق در رابطه با Raid ها می توانید به سایت Red-gate مراجعه کرده و در تب مربوط به Learning & Community در انتهای صفحه بر روی کلمه Book کلیک کنید و سپس کتاب Troubleshooting SQL Server را پیدا کرده و pdf آنرا که free هم هست را دانلود کنید. در این کتاب از صفحه ۴۶ می توانید اطلاعاتی را راجع به Raid کسب کنید.

تذکر: شما می توانید در مبداء، چند Data File داشته باشید و در مقصد (مدیا) هم فایل بکاپ خود را به چند فایل تکه تکه کنید. به شکل زیر دقت کنید.

پیاده سازی Data Transfer به صورت Commandای

در این جا پارامترهای Data Transfer استفاده شده است که شما با توجه به سخت افزار سرور خود می توانید با سعی و خطا به Best Practice مورد نظر برسید.

تذکر: اگر شما BufferCount را در MaxTransferSize ضرب کنید ، اندازه مربوط به کل Buffer شما بدست می آید(در واقع مقدار حافظه مربوط به Buffer سرور شما بدست می آید.).لذا اگر عدد مربوط به حاصلضرب BufferCount در MaxTransferSize از Memory Buffer شما بالاتر شود در این صورت با خطای حافظه (مطابق شکل زیر) مواجه خواهید شد.

<!-- wp:enlighter/codeblock {"language":"sql"} -->
<pre class="EnlighterJSRAW" data-enlighter-language="sql" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">BACKUP DATABASE AdventureWorks2019 
TO DISK='D:\Backup\AwBackup1.bak',
DISK='D:\Backup\AwBackup2.bak'
WITH STATS=1,COMPRESSION
,BUFFERCOUNT = 250
,MAXTRANSFERSIZE=4194304 
,BLOCKSIZE = 65536</pre>
<!-- /wp:enlighter/codeblock -->

تذکر: معمولا مقدار MaxTransferSize را عدد ۴۱۹۴۳۰۴ بایت (معادل ۴MB) و مقدار Blocksize را عدد ۶۵۵۳۶ بایت انتخاب می کنند (به شرطی که فرمت درایوهای مربوط به Data File و Log File عدد ۶۴ کیلوبایت باشد). حال شما با تغییر مقدار BufferCount می توانید بهترین خروجی را بدست آورید.

تنظیمات پارامترهای مربوط به Data Transfer به صورت Wizardای

در این حالت در قسمت Management، بر روی Maintenance Plan کلیک راست نموده و بر روی گزینه New Maintenance Plan مطابق شکل زیر کلیک می کنیم.

در این صورت پنجره ایی مطابق شکل زیر نمایان می شود. در این پنجره یک نام مناسب وارد نموده و سپس مطابق شکل زیر بر روی دکمه OK کلیک می نماییم.

در این صورت محیط زیر نمایان می شود.

حال از پنجره Toolbox، مطابق شکل زیر، بر روی ابزار Backup Database Task دابل کلیک نموده تا این ابزار به محیط Maintenance Plan قرار بگیرد.

حال جهت تنظیمات بکاپ، بر روی ابزار Back Up Database Task دابل کلیک می نماییم و در پنجره ظاهر شده مطابق شکل زیر ابتدا در تب General نام دیتابیس و یا دیتابیس های مورد را علامت می زنیم. سپس در تب Destination و در قسمت Folder مسیر بکاپ را تنظیم می نماییم و در تب Options می توانیم تنظیمات مربوط به Compress backup را انجام دهیم و همچنین تنظیمات مربوط به Data Transfer (که در این جا می توانید آیتم های مربوط Block size و Max transfer size را انجام دهیم) را انجام دهیم.

تذکر : برای دانلود کتاب های مورد نظر خود می توانید به سایت Libgen.is مراجعه کنید. شما در این سایت هم می توانید کتاب ها را به صورت Epub دریافت نمایید که با نرم افزارهای Epub Reader آنها را باز کنید و هم می توانید با فرمت pdf کتاب ها را دانلود کنید. یکی از بهترین کتاب ها در حوزه مدیریت دیتابیس مربوط به انتشارات Apress بوده، که نام آن SQL Server 2019 Administration می باشد که راجع به این کتاب به طور مفصل در سایت نیک آموز صحبت شده است.

تذکر: شما می توانید تکنیک های مربوط به افزایش سرعت بکاپ و ریستور را همزمان استفاده نمایید به مثال زیر دقت کنید، در این مثال هم از تکنیک Media family استفاده شده است، هم از تکنیک Compression استفاده شده است و هم از تکنیک Data Transfer استفاده شده است.

BACKUP DATABASE AdventureWorks2019 
TO DISK='D:\Backup\AwBackup1.bak',
DISK='D:\Backup\AwBackup2.bak'
WITH STATS=1,COMPRESSION
,BUFFERCOUNT = 250
,MAXTRANSFERSIZE=4194304 
,BLOCKSIZE = 65536

چه رتبه ای می‌دهید؟

میانگین ۵ / ۵. از مجموع ۲

اولین نفر باش

title sign
دانلود مقاله
راه‌های افزایش سرعت بکاپ و ریستور | بخش چهارم
فرمت PDF
9 صفحه
حجم 1 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
غلامحسین عبادی
مقالات
13 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
غلامحسین عبادی

غلامحسین عبادی هستم، سوابق حرفه‌ای من به شرح ذیل است: ۱- ۸ سال سابقه کار به عنوان برنامه نویس در شرکت خودرو سازی سایپا، ۲- ۱۱ سال سابقه کاری در شرکت ایران خودرو به عنوان رئیس برنامه‌های کاربردی و ۴ سال آخر مسئول دیتابیس، ۳- ۴ سال سابقه DBA در شرکت داده ورزی سداد، ۴- دارای مدرک MCSD مایکروسافت از کشور امارات، ۵- دارای مقالات متعدد در ماهنامه تجارات الکترونیک ۶- بیش از یک سال سابقه DBA در شرکت خدمات رایانه‌ای امید، ۷- مدرسی SQL Server در برخی سازمان‌های دولتی ۸- ۱۶ سال سابقه تدریس در دانشگاه‌ها و سازمان‌های مختلف ۹- مولف کتاب‌های متعدد در حوزه برنامه نویسی و SQL Server

پروفایل نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    • سلام دوست عزیز :
      کاملا صحیح می فرمایید. به خاطر همین است که در هنگام تنظیم پارامترهای BufferCount و Maxtransfersize و BlockSize خیلی باید با احتیاط عمل نمود. تا بهترین شرایط را فراهم نمود. اگر بخوام یک نمونه مشابه را مثال بزنم بحث مربوط به Fill Factor هست. یکی از راههای رفع Fragmentation تنظیم آپشن مربوط به Fill Factor (میزان فضای آزاد یک Page) می باشد. معمولا ابتدا مقدار این آپشن را با عدد ۹۵ شروع کنیم و بعد مانیتور کنیم اگه بازم نیاز بود اون رو کاهش داده و به ۹۰ تغییر میدهیم و الی آخر. نکته خیلی خیلی مهم اینکه بر روی فیلدی که کلاستر ایندکس است به هیچ عنوان نباید Fill Factor تنظیم نمود.

    • سلام. ممنون از مقالات خوب شما
      همیشه برای من جای سوال بود که چرا در Maintenance Plan پارامتر BufferCount برای تنظیم وجود نداره و باید در اسکریپت اضافه نمود. با توجه به تجربه دیروز این حدس رو میزنم (سناریو من) :
      درحال تهیه بک آپ فول از سرور آرشیو با تنظیمات Data Transfer بودم و پارامتر BufferCount هم ۲۵۰ بود. لاگ بک آپ سرور اصلی هم بر روی همین سرور آرشیو ذخیره می شود. در حین گرفتن فول بک آپ، لاگ بک آپ چند کیلوبایتی با سرعت بسیار بسیار پایین در حال انجام بود (لاگ بک آپ ۲۰۰کیلوبایتی که قبلا چند صدم ثانیه طول می کشید اینبار حدود ۱۰ دقیقه طول کشید). چرا؟ چون من تمام بافر سرور رو مشغول به گرفتن فول بک آپ کردم و بافری بسیار محدودی برای تهیه لاگ بک آپ تخصیص می گرفت.
      نتیجه : انتخاب BufferCount درست بسیار حساس بوده و استفاده کورکورانه از آن در هنگام گرفتن چند بک آپ همزمان باعث کندی بسیار شدید سرور می شود. شاید به همین خاطر تنظیم BufferCount در Maintenance Plan دیده نمی شود تا با تنظیمات پیش فرض عملیات بک آپ صورت پذیرد و از اختلال در سیستم ها جلوگیری شود.

هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
جشنواره عیدآموز نیک آموز، سال جدید رو با قدرت شروع کن
مشاهده تخفیف ها
close-image