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

مقدمه

قبل از اینکه دیر شود از اطلاعات خود 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 به چهارده عدد رسیده است. شما با توجه به سخت افزار سرور و همچنین نوع هاردهای خود می توانید این عدد را ارتقاء دهید تا بهترین حالت را بدست آورید.

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

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

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

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

3. پارامتر 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 را عدد ۴۱۹۴۳۰۴ بایت (معادل 4MB) و مقدار 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