در بعضی مواقع ممکن است که در حین کار و با تراکنشهای باز، دیتابیس SQL Server دچار مشکل شود و از دسترس خارج شود و این فرض را هم در نظر بگیرید که بک آپ دیتابیس مربوط به زمانی بیش از حد انتظار است. آیا ممکن است که دیتابیس را با وجود از دست دادن فایل لاگ آن بازگردانی کرد؟
جواب: بله ولی بدون عواقب نیست.
بطور معمول در زمانی که تراکنشهای باز بر روی سرور دیتابیس وجود دارد و بانک کرش میکند، کرش ریکاوری، تراکنشهای باز را رول بک میکند. این امر مانع از اثرات پراکنده از تراکنشهای فعلی در پایگاه داده میباشد.
اگر لاگ در دسترس نباشد هنگامی که کار سرور SQL شروع میشود، پایگاه داده در حالت SUSPECT قرار داده میشود. در این مواقع تنها راه آن لاین نمودن بانک ( البته منظور آماده بکار نمودن بانک نیست) استفاده از حالت قابلیت تعمیر اورژانسی است که از نسخه 2005 افزده شده است که با ساخت یک فایل لاگ جدید و سپس اجرا نمودن DBCC CHECKDB با استفاده از REPAIR_ALLOW_DATA_LOSS انجام میشود.
مشکل اینجاست که اگر شما به همین طریق بخواهید ادامه بدهید و از این قابلیت اورژانسی استفاده کنید، مسلما امکان وجود دستهای از تغییرات در بانک که ممکن است در میانه اعمال بروز رسانی چند رکورد فروش در یک جدول نیمی از تراکنشهای آنها روی بانک اعمال شده و بعد از راه اندازی دوباره با لاگ جدید، امکان برگرداندن آنها وجود داشته باشد، مواجه شوید. به این معنی که در بهترین حالت، پایگاه داده بدست آمده با برنامه هماهنگ نیست و یا اینکه به بک آپی که وجود دارد رضایت بدهید.
حالت تعمیر اورژانسی، زمانیکه همه راهحلهای بازگردانی بانک مغلوب شوند آخرین روش مانده است. این حالت، حالتی بین دو شر، ریکاور کردن بانک به حالتی نا هماهنگ با نرم افزار و یا برگرداندن به زمانی خیلی عقبتر است که در نهایت اقدام به انجام هر دو و هماهنگتر کردن بانک برای کار با برنامه میشوید که بسیار وقت گیر و مشکل ساز است.
ولی باز ممکن است این وضعیت پیش بیاد چون شما از اتفاقی که در زمان کرش در بانک افتاده اطلاعی ندارید. برای جلوگیری از این موارد در آینده سازوکار بک آپ گیری از بانک را باید تغییر بدهید و دفعات بک آپ گیری را افزایش بدهید و نیز از روشهای جدید که قابلیت دسترسی بالا دارند استفاده کنید همانند mirroring و SQL Server 2012 Availability Groups است.
جواب: بله ولی بدون عواقب نیست.
بطور معمول در زمانی که تراکنشهای باز بر روی سرور دیتابیس وجود دارد و بانک کرش میکند، کرش ریکاوری، تراکنشهای باز را رول بک میکند. این امر مانع از اثرات پراکنده از تراکنشهای فعلی در پایگاه داده میباشد.
اگر لاگ در دسترس نباشد هنگامی که کار سرور SQL شروع میشود، پایگاه داده در حالت SUSPECT قرار داده میشود. در این مواقع تنها راه آن لاین نمودن بانک ( البته منظور آماده بکار نمودن بانک نیست) استفاده از حالت قابلیت تعمیر اورژانسی است که از نسخه 2005 افزده شده است که با ساخت یک فایل لاگ جدید و سپس اجرا نمودن DBCC CHECKDB با استفاده از REPAIR_ALLOW_DATA_LOSS انجام میشود.
مشکل اینجاست که اگر شما به همین طریق بخواهید ادامه بدهید و از این قابلیت اورژانسی استفاده کنید، مسلما امکان وجود دستهای از تغییرات در بانک که ممکن است در میانه اعمال بروز رسانی چند رکورد فروش در یک جدول نیمی از تراکنشهای آنها روی بانک اعمال شده و بعد از راه اندازی دوباره با لاگ جدید، امکان برگرداندن آنها وجود داشته باشد، مواجه شوید. به این معنی که در بهترین حالت، پایگاه داده بدست آمده با برنامه هماهنگ نیست و یا اینکه به بک آپی که وجود دارد رضایت بدهید.
حالت تعمیر اورژانسی، زمانیکه همه راهحلهای بازگردانی بانک مغلوب شوند آخرین روش مانده است. این حالت، حالتی بین دو شر، ریکاور کردن بانک به حالتی نا هماهنگ با نرم افزار و یا برگرداندن به زمانی خیلی عقبتر است که در نهایت اقدام به انجام هر دو و هماهنگتر کردن بانک برای کار با برنامه میشوید که بسیار وقت گیر و مشکل ساز است.
ولی باز ممکن است این وضعیت پیش بیاد چون شما از اتفاقی که در زمان کرش در بانک افتاده اطلاعی ندارید. برای جلوگیری از این موارد در آینده سازوکار بک آپ گیری از بانک را باید تغییر بدهید و دفعات بک آپ گیری را افزایش بدهید و نیز از روشهای جدید که قابلیت دسترسی بالا دارند استفاده کنید همانند mirroring و SQL Server 2012 Availability Groups است.
منبع: ^
لینکهای مرتبط:
1. Is the recovery interval guaranteed? ^
2. Scaling and Sharding Deep Freeze ^
3. Why Database Operations Hit the Wall ^
4. Avoiding logging for user operations ^
5. Resolving the Database Performance Problem Blame Game ^
با سلام و احترام؛ همانطور که در متن به عرض رسانده شده:
" از عبارت ON برای مشخص کردن محدوده Trigger در سطح SQL Instance (در این صورت ON All SERVER نوشته میشود) و یا در سطح Database (در این حالت ON DATABASE نوشته میشود) استفاده میشود و از عبارت FOR برای مشخص کردن رویداد یا گروه رویدادی که سبب فراخوانی Trigger میشود، استفاده خواهد شد. "
در خصوص مثالی که اشاره کردید، به نظرم میرسد از Trigger برای این منظور استفاده نمیشود (در حوزهی بکاپ و ریستور)، شاید اگر قصدتان به منظور ثبت log و ... بایست از Auditing استفاده کنید. به این منظور در Auditing با توجه به جدول زیر میتوان اقدام به ثبت موارد نمود:
به طور مختصر Auditing به شرح زیر است:
بررسی SQL Server Audit
بازبینی (Auditing) شامل پیگیری و ثبت رویدادهایی است که در سطح SQL Instance و یا Databaseهای روی یک سیستم اتفاق میافتد. چندین سطح برای Auditing در SQL Server وجود دارد که به صلاحدید و نیازمندیهای نصب شما وابسته است. شما میتوانید گروه اقدامات بازبینی سرویس دهنده (server audit action groups) را به ازای هر SQL Instance و گروه اقدامات بازبینی بانک اطلاعاتی (database audit action groups) را به ازای هر بانک اطلاعاتی ثبت کنید. رویداد Audit هر زمان عملی که مورد رسیدگی قرار گرفته اتفاق افتد، رخ میدهد.
تا پیش از SQL SERVER 2008، شما باید از خصیصههای متعددی برای انجام یک مجموعه کامل بازبینی (Auditing) برای نمونه DDL Trigger، DML Trigger و SQL Trace، بر روی یک SQL Instance استفاده میکردید.
SQL SERVER 2008، همه قابلیتهای Auditing را روی یک audit specification ترکیب میکند. Audit Specification با تعریف یک شی بازبینی (audit object) در سطح سرویس دهنده برای ثبت (logging) یک دنباله بازبینی (audit trial) آغاز میشود. توجه شود که بایست یک شیء بازبینی ایجاد کنید پیش از اینکه یک Server Audit Specification و یا Database Audit Specification ایجاد کنید.
Server Audit Specification، گروه اقدامات در سطح سرویس دهنده را جمع آوری میکند که با رویدادهای وسیعی فعال میشوند، این گروه اقدامات تحت عنوان Server-Level Audit Action Groups تشریح شده اند. شما میتوانید یک Server Audit Specification را به ازای هر Audit ایجاد کنید چرا که هر دو در محدوده یک SQL Instance ایجاد میشوند.
Database Audit Specification، گروه اقدامات در سطح بانک اطلاعاتی را جمع آوری میکند که با رویدادهای وسیعی فعال میشود. این گروه اقدامات تحت عنوانهای Database-Level Audit Action Groups و Database-Level Audit Actions تشریح شده اند.می توانید یک Database Audit Specification را به ازای هر Audit در بانک اطلاعاتی SQL Server ایجاد کنید.
همچنین میتوانید هر گروه اقدامات بازبینی(audit action groups) یا رویدادهای بازبینی(audit events) را به یک Database Audit Specification اضافه کنید. گروه اقدامات بازبینی، گروه اقدامات از پیش تعریف شده ای هستند و رویدادهای بازبینی اقدامات تجزیه ناپذیری هستند که توسط موتور بانک اطلاعاتی مورد رسیدگی قرار میگیرند، هر دو در محدوده بانک اطلاعاتی (Database) هستند. این اقدامات برای Audit فرستاده میشوند تا در Target (که میتواند یک فایل، Windows Security Log و یا Windows Application Log باشد) ذخیره شوند. برای نوشتن در Windows Security Log لازم است که Service Account سرویس دهنده شما به Policy، Generate security audits اضافه شده باشد، به صورت پیش فرض Local System، Local Service و Network Service بخشی از این Policy میباشند. پس از اینکه Audit را ایجاد و فعال کردید، Target ورودیها را دریافت خواهد کرد.
Server-Level Audit Action Groups (گروه اقدامات بازبینی در سطح سرویس دهنده)
این گروه اقدامات به گروه رویداد Security Audit شبیه هستند. به طور خلاصه این گروه اقدامات، اقداماتی را که در یک SQL Instance شامل میشوند، در بر میگیرد. برای مثال اگر گروه اقدام مناسب با Server Audit Specification اضافه شده باشد هر شیء در هر Schema که مورد دستیابی قرار میگیرد، ثبت میشود. اقدامات در سطح سرویس دهنده به شما اجازه نمیدهد که جزئیات اقدامات در سطح بانک اطلاعاتی را فیلتر کنید. یک بازبینی در سطح بانک اطلاعاتی برای انجام به جزئیات دقیق فیلتر کردن نیاز دارد، برای مثال اجرای دستور Select روی جدول Customers برای login هایی که در گروه Employee هستند.
Database-Level Audit Action Groups (گروه اقدامات بازبینی در سطح بانک اطلاعاتی)
این گروه اعمال به کلاسهای رویداد Security Audit شبیه هستند.
Database-Level Audit Actions
اقدامات در سطح بانک اطلاعاتی، اقدامات بازبینی خاصی را به طور مستقیم روی Database، Schema و اشیاء Schema (از قبیل جداول، View ها، رویههای ذخیره شده، توابع و ... ) فراهم میکند. این اقدامات برای فیلدها (Columns) صدق نمیکنند.
Audit-Level Audit Action Groups
شما میتوانید اقداماتی را که در فرآیند Auditing هستند، بازبینی کنید که میتواند در محدوده سرویس دهنده یا بانک اطلاعاتی باشد. در محدوده بانک اطلاعاتی تنها برای database audit specification رخ میدهد.
جهت بررسی بیشتر به این لینک مراجعه شود.
" از عبارت ON برای مشخص کردن محدوده Trigger در سطح SQL Instance (در این صورت ON All SERVER نوشته میشود) و یا در سطح Database (در این حالت ON DATABASE نوشته میشود) استفاده میشود و از عبارت FOR برای مشخص کردن رویداد یا گروه رویدادی که سبب فراخوانی Trigger میشود، استفاده خواهد شد. "
در خصوص مثالی که اشاره کردید، به نظرم میرسد از Trigger برای این منظور استفاده نمیشود (در حوزهی بکاپ و ریستور)، شاید اگر قصدتان به منظور ثبت log و ... بایست از Auditing استفاده کنید. به این منظور در Auditing با توجه به جدول زیر میتوان اقدام به ثبت موارد نمود:
Server-Level Audit Action Groups
Action group name | Event Class | Description |
BACKUP_RESTORE_GROUP | Audit Backup/Restore | یک دستور Backup یا Restore صادر شود |
بررسی SQL Server Audit
بازبینی (Auditing) شامل پیگیری و ثبت رویدادهایی است که در سطح SQL Instance و یا Databaseهای روی یک سیستم اتفاق میافتد. چندین سطح برای Auditing در SQL Server وجود دارد که به صلاحدید و نیازمندیهای نصب شما وابسته است. شما میتوانید گروه اقدامات بازبینی سرویس دهنده (server audit action groups) را به ازای هر SQL Instance و گروه اقدامات بازبینی بانک اطلاعاتی (database audit action groups) را به ازای هر بانک اطلاعاتی ثبت کنید. رویداد Audit هر زمان عملی که مورد رسیدگی قرار گرفته اتفاق افتد، رخ میدهد.
تا پیش از SQL SERVER 2008، شما باید از خصیصههای متعددی برای انجام یک مجموعه کامل بازبینی (Auditing) برای نمونه DDL Trigger، DML Trigger و SQL Trace، بر روی یک SQL Instance استفاده میکردید.
SQL SERVER 2008، همه قابلیتهای Auditing را روی یک audit specification ترکیب میکند. Audit Specification با تعریف یک شی بازبینی (audit object) در سطح سرویس دهنده برای ثبت (logging) یک دنباله بازبینی (audit trial) آغاز میشود. توجه شود که بایست یک شیء بازبینی ایجاد کنید پیش از اینکه یک Server Audit Specification و یا Database Audit Specification ایجاد کنید.
Server Audit Specification، گروه اقدامات در سطح سرویس دهنده را جمع آوری میکند که با رویدادهای وسیعی فعال میشوند، این گروه اقدامات تحت عنوان Server-Level Audit Action Groups تشریح شده اند. شما میتوانید یک Server Audit Specification را به ازای هر Audit ایجاد کنید چرا که هر دو در محدوده یک SQL Instance ایجاد میشوند.
Database Audit Specification، گروه اقدامات در سطح بانک اطلاعاتی را جمع آوری میکند که با رویدادهای وسیعی فعال میشود. این گروه اقدامات تحت عنوانهای Database-Level Audit Action Groups و Database-Level Audit Actions تشریح شده اند.می توانید یک Database Audit Specification را به ازای هر Audit در بانک اطلاعاتی SQL Server ایجاد کنید.
همچنین میتوانید هر گروه اقدامات بازبینی(audit action groups) یا رویدادهای بازبینی(audit events) را به یک Database Audit Specification اضافه کنید. گروه اقدامات بازبینی، گروه اقدامات از پیش تعریف شده ای هستند و رویدادهای بازبینی اقدامات تجزیه ناپذیری هستند که توسط موتور بانک اطلاعاتی مورد رسیدگی قرار میگیرند، هر دو در محدوده بانک اطلاعاتی (Database) هستند. این اقدامات برای Audit فرستاده میشوند تا در Target (که میتواند یک فایل، Windows Security Log و یا Windows Application Log باشد) ذخیره شوند. برای نوشتن در Windows Security Log لازم است که Service Account سرویس دهنده شما به Policy، Generate security audits اضافه شده باشد، به صورت پیش فرض Local System، Local Service و Network Service بخشی از این Policy میباشند. پس از اینکه Audit را ایجاد و فعال کردید، Target ورودیها را دریافت خواهد کرد.
Server-Level Audit Action Groups (گروه اقدامات بازبینی در سطح سرویس دهنده)
این گروه اقدامات به گروه رویداد Security Audit شبیه هستند. به طور خلاصه این گروه اقدامات، اقداماتی را که در یک SQL Instance شامل میشوند، در بر میگیرد. برای مثال اگر گروه اقدام مناسب با Server Audit Specification اضافه شده باشد هر شیء در هر Schema که مورد دستیابی قرار میگیرد، ثبت میشود. اقدامات در سطح سرویس دهنده به شما اجازه نمیدهد که جزئیات اقدامات در سطح بانک اطلاعاتی را فیلتر کنید. یک بازبینی در سطح بانک اطلاعاتی برای انجام به جزئیات دقیق فیلتر کردن نیاز دارد، برای مثال اجرای دستور Select روی جدول Customers برای login هایی که در گروه Employee هستند.
Database-Level Audit Action Groups (گروه اقدامات بازبینی در سطح بانک اطلاعاتی)
این گروه اعمال به کلاسهای رویداد Security Audit شبیه هستند.
Database-Level Audit Actions
اقدامات در سطح بانک اطلاعاتی، اقدامات بازبینی خاصی را به طور مستقیم روی Database، Schema و اشیاء Schema (از قبیل جداول، View ها، رویههای ذخیره شده، توابع و ... ) فراهم میکند. این اقدامات برای فیلدها (Columns) صدق نمیکنند.
Audit-Level Audit Action Groups
شما میتوانید اقداماتی را که در فرآیند Auditing هستند، بازبینی کنید که میتواند در محدوده سرویس دهنده یا بانک اطلاعاتی باشد. در محدوده بانک اطلاعاتی تنها برای database audit specification رخ میدهد.
جهت بررسی بیشتر به این لینک مراجعه شود.
بر اساس رفتار پیش فرض در دیتابیس SQL Server، در زمان انجام دادن یک دستور که منجر به ایجاد تغییرات در اطلاعات موجود در جدول میشود (برای مثال دستور Update)، جدول مربوطه به صورت کامل Lock میشود، ولو آن دستور Update، فقط با یکی از رکوردهای آن جدول کار داشته باشد.
داریم
در سیستمهای با تعداد تراکنش بالا و دارای تعداد زیاد کلاینت، این رفتار پیش فرض موجب ایجاد صفی از تراکنشهای در حال انتظار بر روی جداولی میشود که ویرایشهای زیادی بر روی آنها رخ میدهد.
اگر چه که بنظر این مشکل راه حلهای زیادی دارد، لکن آن راه حلی که همیشه موثر عمل میکند استفاده از SQL Server Table Hints است.
SQL Server Table Hints به تمامی آن دستوراتی گفته میشود که هنگام اجرای دستور اصلی (برای مثال Select و یا Update) رفتار پیش فرض SQL Server را بر اساس Hint ارائه شده تغییر میدهند.
لیست کامل این Hintها را میتوانید در اینجا مشاهده کنید.
Hint ای که در اینجا برای ما مفید است، آن است که به SQL Server بگوییم هنگام اجرای دستور Update، به جای Lock کردن کل جدول، فقط رکورد در حال ویرایش را Lock کند، و این باعث میشود تا باقی تراکنش ها، که ای بسا با سایر رکوردهای آن جدول کار داشته باشند متوقف نشوند، که البته این مسئله کمی به افزایش مصرف حافظه میانجامد، لکن مقدار افزایش بسیار ناچیز است.
این Hint که rowlock نام دارد در تراکنشهای با Isolation Level تنظیم شده بر روی Snapshot باید با یک Table Hint دیگر با نام updlock ترکیب شود.
توضیحات مفصلتر این دو Hint در لینک مربوطه آمده است.
بنابر این، بجای دستور
update products set Name = "Test" Where Id = 1
update products with (nolock,updlock) set Name = "Test" where Id = 1
تا اینجا مشکل خاصی وجود ندارد، آنچه که از اینجا به بعد اهمیت دارد این است که در هنگام کار با Entity Framework، اساسا ما نویسنده دستورات Update نیستیم که به آنها Hint اضافه کنیم یا نه، بلکه دستورات SQL بوسیله Entity Framework ایجاد میشوند.
در Entity Framework، مکانیزمی تعبیه شده است با نام Db Command Interceptor که به شما اجازه میدهد دستورات SQL ساخته شده را Log کنید و یا قبل از اجرا تغییر دهید، که برای اضافه نمودن Table Hintها ما از این روش استفاده میکنیم، برای انجام این کار داریم: (توضیحات در ادامه)
public class UpdateRowLockHintDbCommandInterceptor : IDbCommandInterceptor { public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext) { if (command.CommandType != CommandType.Text) return; // (1) if (!(command is SqlCommand)) return; // (2) SqlCommand sqlCommand = (SqlCommand)command; String commandText = sqlCommand.CommandText; String updateCommandRegularExpression = "(update) "; Boolean isUpdateCommand = Regex.IsMatch(commandText, updateCommandRegularExpression, RegexOptions.IgnoreCase | RegexOptions.Multiline); // You may use better regular expression pattern here. if (isUpdateCommand) { Boolean isSnapshotIsolationTransaction = sqlCommand.Transaction != null && sqlCommand.Transaction.IsolationLevel == IsolationLevel.Snapshot; String tableHintToAdd = isSnapshotIsolationTransaction ? " with (rowlock , updlock) set " : " with (rowlock) set "; commandText = Regex.Replace(commandText, "^(set) ", (match) => { return tableHintToAdd; }, RegexOptions.IgnoreCase | RegexOptions.Multiline); command.CommandText = commandText; } }
این کد در قسمت (1) ابتدا تشخیص میدهد که آیا این یک Command دارای Command Text است یا خیر، برای مثال اگر فراخوانی یک Stored Procedure است، ما با آن کاری نداریم.
در قسمت دوم تشخیص میدهیم که آیا با SQL Server در حال تعامل هستیم، یا برای مثال با Oracle و ...، که ما برای Table Hintها فقط با SQL Server کار داریم.
سپس باید تشخیص دهیم که آیا این یک دستور update است یا خیر ؟ برای این منظور از Regular Expressionها استفاده کرده ایم، که خیلی به بحث آموزش این پست مربوط نیست، به صورت کلی از Regular Expressionها برای یافتن و بررسی و جایگزینی عبارات با قاعده در هنگام کار با رشتهها استفاده میشود.
ممکن است Regular Expression ای که شما مینویسید بسیار بهتر از این نمونه باشد، که در این صورت خوشحال میشوم در قسمت نظرات آنرا قرار دهید.
در نهایت با بررسی Transaction Isolation Level مربوطه که Snapshot است یا خیر، به درج یک یا هر دو Table Hint مربوطه اقدام مینماییم.
اشتراکها
Microsoft.Data.Sqlite 2.1 منتشر شد
اشتراکها
اضافه شدن SHA1 به SQLite 3.17.0
نظرات مطالب
Resource Governor در 2008 SQL Server
در SQL Server 2012 به منظور تضمین عملکرد تعداد پشتیبانی از مخازن منابع از 20 عدد به 64 عدد افزایش یافته است. همچنین در SQL Server 2014 پشتیبانی از I/O نیز اضافه گردید.(تا پیش از ارائه نسخه 2014 محدودیت روی منابع تنها به CPU و حافظه خلاصه میشد)
نظرات مطالب
EF Code First #12
یک ToList به آخر آن اضافه کنید:
public class MyContext : DbContext, IUnitOfWork { // ... public IList<T> GetRows<T>(string sql, params object[] parameters) where T: class { return this.Database.SqlQuery<T>(sql, parameters).ToList(); } }