آموزش Microsoft SQL Server - # جلسه سوم – تهیه پشتیبان

در این جلسه در مورد مباحث Log انواع backup و انواع Crash در بانک های اطلاعاتی و در پایان ایجاد یک دیتابیس جدید و جزییات مربوط به فایل های فیزیکی در دیتابیس صحبت خواهد شد.

Log File:

در کنار فایل اصلی اطلاعاتی همیشه یک فایل با پسوند *.Log وجود دارد که در پشت پرده نقش مهمی در نگه داری بانک اطلاعاتی ایفا می کند. اطلاعات کلیه تراکنش های اجرا شده و تغییراتی که روی بانک انجام شده توسط هر تراکنش در فایل Log ذخیره می شود. این فایل Log برای جلوگیری از رشد بیش از حد نیاز به نگه داری و کاهش حجم منظم دارد. وجود فایل Log که کلیه عملیات انجام شده را روی بانک ثبت و ضبط می کند در مواقع وجود یک Failure و یا Crash بسیار حیاتی است. این اطلاعات می تواند بانک را به یک نقطه سازگار و صحیح قبل از وقوع یک خطا در بانک اطلاعاتی است ببرد. ممکن است این خطا یک خطای فیزیکی در دیسک و یا نقص نرم افزاری باشد در بعضی مواقع خطا ممکن است اجرای یک دستور اشتباه توسط مدیر بانک اطلاعاتی نیز باشد.

فواید وجود Log File:

1-ره گیری تراکنش Transaction Tracking

2-بازیابی پایگاه داده Data Base Recovery

3-تهیه پشتیبان از عملیات ثبت نشده در دیسک Transaction Log Backup

Transaction Log Backup:

چون کلیه اطلاعات تراکنش ها در Log ذخیره می شود هنگامی که ترافیک سنگینی از دستکاری اطلاعات اتفاق می افتد حجم این فایل به سرعت رشد می کند، برای بزرگ نشدن فایل Log بایستی به صورت مرتب از آن Backup گرفت. تا فایل اصلی همیشه کم حجم بماند.

روش اول: گرفتن Backup دستی

در RDBMS روی فایل بانک راست کلیک کنید و در منوی Tasks زیر منوی Backup را انتخاب کنید

در فرم نمایش داده شده پارامتر Type را روی Transaction Log Backup قرار دهید

روش دوم: نوشتن یک Job

سناریو : سر هر ساعت job اجرا می شود و از فایل Log یک Transaction Log Backup می گیرد، ساعت 12 فایل Log پر میشود، فایل Backup با فرمت DBName_Date_Time.Trn ذخیره می شود.

در ساعت 7 صبح فایل Log خالی است

در ساعت 8 صبح اولین Log Backup انجام شده است

در ساعت 10 صبح سومین Log Backup انجام شده است

در ساعت 11 با انجام Log Backup چهارم فایل Log پر می شود.

اگر از این فایل Log، هیچ Backup گرفته نشده بود با رسیدن به انتهای فایل Log اندازه فایل Log افزایش می یافت. این اندازه با توجه به مقداری که در Auto Growth که برای فایل Log تعیین شده رشد می کند.

برای اصلاح Auto Growth روی بانک راست کلیک کرده و از منوی باز شده آیتم Properties را انتخاب کنید در فرم باز شده به قسمت Files بروید در اینجا به ازای هر کدام از فایل های فیزیکی بانک می توان پارامتر Auto Growth را تعیین کرد. این اندازه می تواند بر حسب درصد و یا یک مقدار معین بر حسب واحد سایز (KB,MB)  باشد.

اندازه پیش فرض برای فایل Log، Auto growth = 1 MB, Unrestricted growth می باشد.

اگر برای Transaction Log Backup یک Job تعریف کرده باشیم زمان پر شدن Log File نوشتن ادامه اطلاعات Log روی بخش هایی از فایل که از آن backup گرفته شده به صورت خودکار انجام می گردد.

علی رقم اینکه برای یک دیتابیس که دارای Auto growth = 20 MB می باشد و Transaction Backup Job نیز دارد بازهم افزایش اندازه Log File اتفاق می افتد. چرا؟

            اندازه سابقه یک تراکنش ممکن است بزرگتر از فضای خالی Log File به علاوه بخشی که از آن backup گرفته شده باشد، در نتیجه با افزایش اندازه Log File فضای کافی برای ذخیره شدن سابقه این تراکنش در Log File ایجاد می گردد.

معمولا Log Backup ها فقط برای یک هفته نگه داری می شوند، سپس حذف می شوند چون نگهداری آنها فضای زیادی اشغال می کند. می توان برای نگه داری و حذف این فایل های تولید شده نیز Job تعریف کرد.

برای اینکه اندازه یک Log File را کاهش دهیم بایستی از دیتابیس Full Backup گرفته و سپس Log را Shrink کنیم.

تعریف Job :

یک job به یک مجموعه کار زمان بندی شده در پایگاه داده گفته می شود که عملیات نگهداری ایندکس ها، تهیه backup حذف این فایل ها و ... غیره را در تایم های مشخص به صورت مرتب انجام می دهد. هم چنین یک Job می تواند به صورت دستی یا اتوماتیک فراخونی شود، هم چنین ماژول های T-sql مانند پروسیجر یا فانکشن را نیز از این طریق می توان اجرا کرد. به طور کلی هر عملیات قابل انجام در بانک را می توان از طریق یک job زمان بند وارد یک حلقه اجرا کرد.

در منوی Management منو آیتم  Management Plan Wizard را انتخاب کنید. می توان Job هایی برای گرفتن backup و نگه داری آنها و حذف Backup های قدیمی را اینجا تعریف کرد.

فایده اصلی Log Backup:

اول: Restore کردن یک دیتابیس به هر لحظه دلخواه، حتی به لحظه قبل از Crash

دوم: جلوگیری از افزایش اندازه Log File

انواع Crash :

1-Physical Crash فایل های دیتابیس صدمه دیده اند

2-Logical خطای منطقی مثل اجرای یک دستور اشتباه روی بانک

به علت Raid شدن هار سرور احتمال Logical Crash از Physical Crash بیشتر است، Raid کردن هارد سرور علاوه بر سرعت بیشتر تحمل خطا fault Tolerance را بالا می برد. برای اطلاعات بیشتر  به اینجا و اینجا مراجعه کنید.

بازیابی اطلاعات هنگام وقوع خطا یا Crash Recovery :

برای بررسی راه کارهای جبران Crash های احتمالی در سرور ابتدا انواع backup را مورد بررسی قرار می دهیم:

انواع backup در SQL Server

1-Full data base Backup

2-Differential Backup

3-Transaction Log Backup

اگر Recovery Model روی Simple باشد دیگر نمی توانیم از دیتابیس Transaction Log backup تهیه کنیم.

Full Backup : یک کپی کامل از همه اطلاعات یک Data Base شامل همه فایل های آن (*.MDF, *.LDF, *.NDF) معمولا روزی یک باز قبل از شروع وقت کاری این نوع backup انجام می گیرد.

Differential Backup : در SQL Server اطلاعات به صورت صفحه صفحه ذخیره می شود که Data Page نام دارد و هر Data Page دارای حجم خاصی عموما 8KB می باشد، خواندن و نوشتن اطلاعات به صورت واکشی و ثبت یک page است. Referential Backup به صورت یک کپی از صفحات دستکاری شده ی دیتابیس بعد از آخرین Full Backup می باشد و استفاده خاص دارد. معمولا چند روز یک بار و زمانی که دیتابیس Log Backup ندارد استفاده می شود.

Transaction Log Backup : کپی از آخرین اطلاعات موجود در فایل Log که backup گرفته نشده است، معمولا بازه زمانی آن ساعتی یک بار است.

یک سناریوی Physical Crash

حالت اول: فایل Log سالم مانده است و فایل Data از بین رفته است:

ابتدا Full Backup قبل از Crash را Restore کرده و سپس تمام Log Backup ها را به ترتیب از تاریخ Full Backup روی این دیتابیس Restore می کنیم و در پایان backup گرفته شده از دم Log را نیز تا زمان قبل از Crash روی دیتابیس Restore می کنیم. برای این کار روی دیتابیس داست کلیک کرده و آیتم tasks را انتخاب می کنیم و سپس Restore و به تناسب عملیات مورد نظر یکی از آیتم های Restore کردن که شامل (Data Base, Files Group, Log) را انتخاب و لیست آخرین Full Backup به همراه تمام Log Backup های موجود نمایش داده می شود که می توانیم به صورت انتخابی آنها را Restore کنیم.

اطلاعات مربوط به این Backup  ها در یک System Data Base به نام msdb نگه داری می شود، معمولا ماهی یک بار این سوابق بایستی پاک شوند، این کار را می توان با Maintenance Plan انجام داد تا به صورت خودکار ماهی یک بار پاک سازی انجام شود.

حالت دوم : Data  سالم است و فایل Log از بین رفته است

چون Log از بین رفته است البته Log جاری تا آخرین Log Backup از دست رفته است، بردن دیتابیس به حالت Emergency :

دستور تغییر به حالت Emergency

Alter Database DB_Name Set Emergency

چون دیتابیس Integrity ندارد با این کار موقتا آن را به حالت Read Only می بریم.

این فایل دیتا قابل اعتماد نیست چون ممکن است تراکنش های ذخیره شده باشند که Commit نشده اند پس نباید ذخیره می شدند و یا تراکنش هایی هستند که در رم ذخیره شده اند ولی در دیتابیس نشده اند در نتیجه از دست رفته اند، در SQL 2000 می توان از این دیتا استفاده کرد اما از SQL 2005 به بعد این گونه فایل های Data که بدون Log شده اند توسط SQL پذیرفته نمی شوند.

در نتیجه بخشی از اطلاعات از لحظه Crash تا زمان آخرین Log Backup از بین رفته است.

منطقی ترین راه برای به دست آوردن تغییرات از دست رفته : DB موجود را با DB حاصل از آخرین Full Backup و تمام Log های موجود مقایسه کنید. برای اینکار ابزار Red gate SQL Data Compare ابزار مناسبی است.

سناریوی Logical Crash :

اولین کاری که انجام می دهیم از دم لاگ Tail Log یک backup تهیه کنید.

اجتناب از Restore Data Base to a Point Time = Most Recent Possible بلکه بایستی تا قبل از تاریخی که مثلا دستور اشتباه رخ داده Restore کنیم. سپس با مقایسه Data خراب شده و Backup تا قبل از خرابی تغییرات را به دست آورده و اطلاعات را اصلاح می کنیم. در این حالت نیز از خرابی به بعد هم ممکن است تغییرات را از دست بدهیم. برای مقایسه بایستی Restore کردن را مستقل از دیتابیس اصلی انجام داد تا بتوان دو دیتابیس را مقایسه کرد.

روشی برای نگرفتن Log Backup و بزرگ نشدن Log :

روی بانک مورد نظر راست کلیک کرده و آیتم Properties را انتخاب کنید و در قسمت option پارامتر Recovery Model را که تا به حال روی Full بوده است را روی Simple قرار می دهیم.

Simple Recovery Model :

در این حالت نمی توان از Log، backup  گرفت در این حالت به محض اینکه یک تراکنش خاتمه می یابد و روی دیسک ذخیره می شود، سابقه آن در Log قابل رونویسی (Overwrite)  می شود، بدون اینکه از آن Log Backup گرفته شود.

Simple Recovery Model برای دیتابیس های بزرگ چون فقط می توان از آن Full Backup گرفت در نتیجه زمان هایی که صرف عملیات backup گیری می شود اغلب بسیار زیاد است.

تعریف Differential Backup

برای دیتابیس هایی که Recovery Model = Simple می باشد و اندازه دیتابیس بزرگ است، می توانند روزی یک بار Full Backup و هر چند ساعت یک Differential backup بگیرند.

می توان Recovery Model را از Simple به Full باز گرداند، البته بایستی Job Log Backup را نیز تعریف کنیم. برای ایجاد Job برای هر نوعی از backup می توان از Maintenance Plan استفاده کرد که یکی Wizard برای تعریف job دارد.

می توان به جای چندین Log Backup از یک Differential backup که در همان بازه معادل استفاده کرد این کار فرآیند Restore را سریع تر می کند سرعت به این دلیل اهمیت دارد چون در طول عملیات تهیه backup سرور از دسترس خارج می شود. هم چنین برگرداندن Differential Backup در واقع انجام عملیات کپی کردن است در حالی که برگرداندن Log Backup انجام تراکنش های انجام گرفته است.

در یک سرور واقعی هیچ گاه backup ها روی همان هارد دیسکی که دیتا قرار دارد نیست، هم چنین اگر دیتابیس شامل چندین فایل باشد که در جلوتر به آن می پردازیم برای سرعت بیشتر بایستی این فایل ها روی هارد های مختلف باشند.

ایجاد یک دیتابیس جدید

هر دیتابیس جداقل یک و حداکثر n  عدد Data File دارد، هم چنین دارای یک یا چندین فایل Log است. می توان فایل های دیتابیس را به File Group ها دسته بندی کرد. پس دسته بندی فایل های دیتابیس  File Group نام دارد. نوع File Group برای MDF اصلی همان primary است و هر فایل یک Logical Name دارد که برای MDF هم نام با نام دیتابیس است. بقیه فایل های NDF نیز بایستی دارای Logical Name باشند، همچنین می توان آنها را در File Group های مختلف یا همسان قرار داد، در آیتم <new filegroup> می توان File Group های جدید نیز تعریف کرد.

اگر یک دستور SQL بایستی روی یک فایل خاص انجام شود بایستی از نام Logical Name را ذکر کنیم. مثلا استفاده از دستورات Shrink یا دستور backup را اگر بخواهیم روی یک فایل اجرا کنیم، دستور Shrink را برای حذف فضای مرده فایل های دیتابیس استفاده می کنیم. File Group می تواند شامل چندین فایل نیز باشد.

فواید File Group

اگر یک File Group از چندین فایل ایجاد شده و آن فایل ها هر کدام روی یک دیسک مستقل باشند آنگاه اطلاعات آن فایل گروپ بسیار سریع تر خوانده و نوشته خواهد شد، زیرا اطلاعات به صورت موازی روی این فایل ها نوشته و خوانده می شود.

می توان از File Group های یک Data base جدا از هم Backup گرفت و Restore نمود. در بانک های حجیم عملیات Backup و Restore برای کل بانک بسیار وقت گیر است و سبب از دسترس خارج شدن سرور مادامی که در حال تهیه Backup است می شود.به همین خاطر به صرفه تر است که عملیات را به File Group ها محدود کنیم.

اطلاعات حجیم (BLOB : Binary large Object) یک جدول می تواند درون یک File Group غیر از همان File Group مربوط به اطلاعات معمولی آن قرار گیرد. مثلا یک File Group برای Text/Image، در این روش هر جدول دارای دو File Group است اولی برای اطلاعات معمولی و دیگری برای اطلاعات حجیم.

Schema  یک دسته بندی منطقی اما File Group یک دسته بندی فیزیکی در پایگاه داده است

در Database Properties می توان Regular File Group را تعیین کرد.

هارد دیسک بدون raid در سرور وجود ندارد raid به ما Fault Tolerance می دهد در واقع در صورتی که یک از هارد دیسک های فیزیکی موجود به علت خرابی از چرخه خارج شود سیستم raid این بروز خرابی و خطا را بدون از دست دادن اطلاعات مدیریت می کند.

Raid معادل سخت افزاری File Group در پایگاه داده است.

ارزانترین و راحت ترین راه raid 1 یا Mirror می باشد که استفاده از دو هارد هم زمان و موازی است.

اطلاعاتی که بایستی در بانک ثبت شود بلا فاصله انجام نمی گیرد بلکه در رم نگه داری شده و این اطلاعات در بازه های مشخصی به تشخیص RDBMS روی هارد نوشته می شود به این نقاط که اطلاعات موجود در رم روی هارد نوشته می شود Check Point گفته می شود.

File Stream

تا قبل از SQL 2008 برای نگه داری اطلاعات حجیم دو روش وجود داشت:

1-مسیر فایل ها در دیتابیس نگه داری می شد و خود فایل ها در یک فولدر Share قرار داشتند

2-داخل فیلدی از نوع Varbinary max که تا 2 گیگابایت اطلاعات حجیم را نگه داری می کند  Image نام داشت.

روش اول

مزایا : بانک سبک است دیتابیس اصلی کوچک است. چون کلیه فایل ها خارج از بانک نگه داری می شود

مشکلات: backup تهیه شده Sync نیست، امنیت سرور پایین است. فایل backup تهیه شده از بانک فایل ها را در بر ندارد و بایستی از فایل ها نیز به صورت موازی کپی تهیه شود

روش دوم

مزایا : backup آن sync می باشد، امن است. فایل backup فایل ها را نیز در خود دارد

مشکلات: حجیم است، نیازمند دانش نگه داری است.

در SQL 2008 این دو روش با هم ادغام گردیده و روش File Stream استخراج شده است. در این روش در ظاهر دیتا در بانک ذخیره می شود اما در پشت پرده این اطلاعات حجیم در یک فایل که فقط خود SQL Server به آن دسترسی دارد، که همه مزایا رو بدست می آورد.