2017/03/29

اتساق قواعد البياانات والامر DBCC CHECKDB بالعربي

مقدمة

في هذا المقال سوف نتعرف على مشاكل اتساق Consistency قواعد بيانات SQL Server وكيف تحدث وكيفية معالجتها وايضا كيفية استخدام الامر DBCC CHECKDB وما هو ال Emergency Mode.



ما معنى الاتساق Consistency 

تخيل معي ان لديك جدولين يرتبطان ببعضهما عن طريق Foreign Key Constraint المفترض ان الجدول الابن لا يمكن باي حال من الاحوال ان يحتوي على قيمة في عمود ال Foreign Key غير متواجدة في الجدول الاب , اليس كذلك؟
هذا هو معنى ال Consistency وهو ان كل البيانات المتواجدة في قاعدة البيانات متسقة مع بعضها وتحترم كل ال Rules و ال Constrains المتواجدة داخل قاعدة البيانات.

لكن في بعض الاحيان يحدث خلل داخل قاعدة البيانات يؤدي الى Consistency Error اي بمعنى اخر وفقا للمثال السابق قد تجد قيم ما في الجدول الابن غير متواجدة في الجدول الاب ولكن لماذا؟
طبعا نعرف جميعا ان كل عمليات الكتابة داخل قاعدة البيانات في نهاية الامر يتم كتابتها داخل ملفات على ال Disk بمعنى ادق IO Operations فقد يحدث في بعض الاحيان خلل اثناء عملية الكتابة قد يكون ناتج عن مشاكل Hardware او مشاكل متعلقة بنظام التشغيل نفسه وقد واجهت مثل هذه المشاكل مع احد عملائي حيث كان يحدث عنده Consistency Error باستمرار بسبب انقطاع التيار الكهربائي في Server قاعدة البيانات ولم يكن مجهز ب UPS.
فمثلا تخيل انك قمت بحذف سجل من الجدول الاب المفترض ان يقوم بمسح كل السجلات المرتبطة به في الجدول الابن ولكن نتيجة خلل اثناء الكتابة داخل ملفات قاعدة البيانات تم مسح الاب ولم يتم مسح الابناء اذا سيوجد بيانات غير صحيحة.

وسائل اكتشاف ال Consistency Errors

Page Verify option

الوسيلة الاولى هي عن طريق ال Page Verify option وهو عبارة عن Option داخل قاعدة البيانات له 3 اختيارات 
None وهذا الاختيار معناه ان هذه الخاصية غير مفعلة
Checksum
TORN_PAGE_DETECTION وهذا الاختيار ستقوم مايكروسوفت بالغاءه في الاصدارات القادمة ولذلك لن نتكلم عنه

الاختيارChecksum

لفهم ما يفعله هذا الاختيار داخل ال SQL Server لابد من فهم ما هو ال Checksum عموما 
ال Checksum هو في الاصل وسيلة للتأكد من سلامة البيانات فتخيل معي ان لديك ملف Text وانت ترسله من جهازك الى جهاز اخر عبر الانترنت مثلا وتريد ان تتأكد من ان محتويات الملف الذي قمت بارساله لم تتغير خلال عملية النقل وان المستلم تلقى نفس الملف بدون تعديل
احد وسائل التأكد هو ال Checksum او بمصطلح اخر ال Hashing ولكن كيف يتم؟
عند ارسالك للملف فانت ترسل الملف + قيمة ال Checksum الخاصة به وهذه القيمة يتم انشائها عن طريق Function معينة اذا تم اعطائها الملف تعطي قيمة معينة ومن المستحيل (المفترض ان يكون مستحيل 😃 ) ان تعطي نفس القيمة الا مع نفس الملف
وفي نفس الوقت يقوم المستلم باستلام الملف وقيمة ال Checksum الخاصة به وهنا يقوم بتشغيل نفس ال Function على الملف للتأكد ان القيمة التي ستنتج هي نفس قيمة ال Checksum المرسلة.

اي بيان داخل ال SQL Server يتم كتابته في صورة وحدات تسمى Pages داخل الملفات او بمعنى اخر تستطيع ان تعتبر ال Page هي اقل وحدة قياس للبيانات داخل الملفات و في حالة اختيار هذا ال Option يقوم ال SQL Server عند كتابة اي Page بكتابة ايضا ال Checksum الخاص بها وعند قراءة هذه ال Page يقوم ايضا بفحص اتساقها مع ال Checksumالمكتوب عليها فاذا وجد انهم غير متسقين يقوم باصدار ERROR يوضح انه يوجد خلل في ال Checksum وبذلك يستطيع مدير قاعدة البيانات التعامل مع هذا الخطأ.
كما انه يوجد Option عند عمل Backup لقاعدة البيانات يمكنك من فحص ال Checksum لكل ال Pages التي لها Checksum اثناء عملية ال Backup.

ملاحظة هامة:
اذا كانت قاعدة البيانات على الوضع None وقمت بتفعيل ال Checksum هذا معناه انه عند كتابة او تعديل اي page سيتم انشاء ال Checksum الخاص بها بينما الpages التي لم يتم تعديلها ستبقى كما هي بدون checksum.

ولتفعيل هذا الاختيار يمكن عن طريق ال SQL Server Management Studio عن طريق كليك يمين على قاعدة البيانات ثم اختيار Properties ثم اختيار Options

جدول dbo.suspect_pages 

اي Page يكتشف ال SQL Server ان بها Consistency Error يقوم بتخزين بيانات عنها في جدول dbo.suspect_pages داخل قاعدة بيانات ال MSDB ويمكن الاستعلام عنه باسخدام الQuery التالية
SELECT  DB_NAME(sp.database_id) [Database] ,
        mf.name ,
        sp.page_id ,
        CASE sp.event_type
          WHEN 1 THEN '823 or 824 or Torn Page'
          WHEN 2 THEN 'Bad Checksum'
          WHEN 3 THEN 'Torn Page'
          WHEN 4 THEN 'Restored'
          WHEN 5 THEN 'Repaired (DBCC)'
          WHEN 7 THEN 'Deallocated (DBCC)'
        END AS [Event] ,
        sp.error_count ,
        sp.last_update_date
FROM    msdb.dbo.suspect_pages sp
        INNER JOIN sys.master_files mf ON sp.database_id = mf.database_id
                                          AND sp.file_id = mf.file_id;


اكتشاف الاخطاء بالامر DBCC CHECKDB

في الحقيقة هذا الامر مهم جدا لاكتشاف وايضا علاج اخطاء ولاكتشاف الاخطاء يمكنك عمل ال Query التالية

DBCC CHECKDB('DatabaseName')

وبهذا الامر يقوم ال SQL Server بفحص كل Page داخل قاعدة البيانات والتأكد من عدم وجود Consistency Error بها فاذا وجد خطأ يظهره باللون الاحمر واذا لم يجد خطأ يكتب معلومات عن ال Page التي يتم فحصها.

مما سبق يتضح ان هذه العملية تكون مكلفة جدا حيث انها تفحص كل Page داخل قاعدة البيانات وبالتالي تسحب كل ال Resources الخاصة بال Server لذلك ينصح ان يتم عملها اثناء الفترات التي لا يوجد تعامل كثيف على قاعدة البيانات في ايام العطلات او ليلا وفي بعض الاحيان يقوم مديروا قواعد البيانات باخذ نسخة احتياطية واسترجاعها على Server اخر وعمل CheckDB لها على الServer الاخر بعيدا عن ال Production Server.

كما لابد من ملاحظة ان هذا الامر لكي يعمل (في الوضع الافتراضي) يقوم بالتالي اولا يعمل Snapshot لقاعدة البيانات المراد فحصها ثم يقوم بالفحص على هذه ال Snapshot ولكن لماذا؟
لسببين الاول ان يتم الفحص على اللحظة الزمنية التي تم تشغيل الامر فيها وبالتالي لا يتأثر باي ادخالات حالية والسبب الثاني حتى يخفف من تعارض ال Transactions للجداول التي يتم فحصها ولا يعمل لها Locking.

ايضا يجب معرفة ان هذا الامر له Parameters كثيرة لتغير الوضع الافتراضي له فمثلا يوجد Parameter ياسم NO_INFOMSGS يقوم باخفاء كل رسائل المعلومات الصادرة من الامر وبالتالي يتم اظهار رسائل الاخطاء فقط وممكن معرفة كل ال Parameters المتاحة من هنا.

معالجة ال Consistency Errors

خط الدفاع الاول لهذه الاخطاء هو استرجاع البيانات من نسخة احتياطية سابقة ولذلك لابد من اخذ نسخ احتياطية من قواعد البيانات بصفة دورية لتجنب التعرض لفقدان البيانات.

باستخدام DBCC CHECKDB

في الحقيقة يمكن اصلاح Consistency Error بدون فقد اي بيانات عن طريق الامر التالي 

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (DatabaseName, REPAIR_REBUILD) ;
GO
ALTER DATABASE DatabaseName SET MULTI_USER ;
GO
حيث ان الكود السابق يقوم بقطع اي اتصال مع قاعدة البيانات ثم عمل Repair لها ثم فتح امكانية الاتصال مع قاعدة البيانات مرة اخرى.
ولكن للاسف ليست دائما الحياة وردية ويمكن اصلاح الاخطاء بدون فقد بيانات فاذا رجعت للمثال السابق في اول المقال الخاص بحذف بيانات من الجدول الاب ولم يتم حذف ابنائه في الجدول الابن فكيف يمكن لل SQL Server حل هذه المشكلة بدون اعادة حذف البيانات في جدول الابن التي بها مشكلة؟

وبالتالي يتم استبدال الكود السابقة بالكود التالي

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE  ;
GO
DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS) ;
GO
ALTER DATABASE DatabaseName SET MULTI_USER ;
GO

ولكن قد ما هي المشاكل التي يمكن حلها بدون الحاجة الى فقدان البيانات ؟
مثلا لو مشاكل خاصة بال Log File او خاصة ب Non-Clustered Index يمكن اعادة بنائه من جديد وهكذا.

ملاحظة هامة
عند تشغيل امر CheckDB لفحص الاخطاء يقوم باعطائك معلومة عن الامكانية المتاحة لحل الاخطاء التي وجدها سواء بدون فقد بيانات او بفقد بيانات في اخر الرسائل التي يكتبها

Emergency Mode

في بعض الاحيان يكون ال Consistency Error المتواجد ادى الى تعطل عمل ملف او اكثر من قاعدة البيانات بالكامل مما ادى الى دخول قاعدة البيانات في حالة لا يمكن الولوج اليها وهذه مشكلة كبيرة.
ولحل هذه المشكلة خط الدفاع الاول هو استرجاع البيانات من نسخة احتياطية سابقة كما ذكرت من قبل ولكن اذا لم يكن هناك نسخة احتياطية لابد من وضع قاعدة البيانات على وضع ال Emergency Mode وعمل REPAIR_ALLOW_DATA_LOSS وهذا هو الحل الوحيد حيث لا يوجد لديك اي نسخة احتياطية.

ولتجربة ذلك على اي قاعدة بيانات لديك قم بايقاف ال SQL Server Service ثم قم بحذف ملف ال Log File ثم اعد تشغيل ال SQL Server وهنا ستجد قاعدة البيانات موجودة ولكن لا يمكن التعامل معها بعد ذلك قم بعمل الامر التالي لاصلاحها واعادة بناء ال Log File

ALTER DATABASE DatabaseName SET EMERGENCY ;
GO
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE  ;
GO
DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS) ;
GO
ALTER DATABASE DatabaseName SET MULTI_USER ;
GO


مراجع

كتاب Pro SQL Server Administration

========================
تم بحمد الله


هناك 5 تعليقات:

علي منصور يقول...

بارك الله فيك اخي المهندس .. ماشاء الله لم أجد شروحات وافية بمثل شروحاتك .
جزاك الله كل خير .. ومن المتابعين لمدونتكم الأكثر من رائعة .

Mohamed Galal يقول...

جزاك الله خير
شكرا لحضرتك على التعليق الجميل

سوق دولفين يقول...

بارك الله فيك و زادك علما و كل عام و حضرتك بالف خير

سوق دولفين يقول...
أزال المؤلف هذا التعليق.
سوق دولفين يقول...

عزيزي محمد بيه لو انا احتاجت مساعدة حضرتك فى حل مشكلة واجهتني اثناء تثبيت احد المشاريع php with sql فهل دا ممكن يا فندم. و شكرا لحضرتك فى كل الاحوال

مقدمة عن الDevOps

في هذا المقال سوف اتحدث عن مقدمة بسيطة عن ال DevOps ولماذا لابد ان تهتم فرق تطوير البرمجيات به وما هي النتائج المترتبة عن استخدام اساليبه ...