دليل مزامنة البيانات في Microsoft SQL Server

أصبحت مشاركة المعلومات ذات الصلة بين الأنظمة المعزولة ذات أهمية متزايدة للمنظمات ، لأنها تتيح لها تحسين جودة البيانات وتوافرها. هناك العديد من المواقف التي يكون فيها من المفيد أن يكون لديك مجموعة بيانات متاحة ومتسقة في أكثر من خادم دليل واحد. هذا هو السبب في أهمية معرفة الطرق الشائعة لإجراء مزامنة بيانات SQL Server.

يمكن تحقيق توافر البيانات واتساقها عن طريق تكرار البيانات وعمليات مزامنة البيانات. نسخ البيانات هو عملية إنشاء نسخة مكررة واحدة أو أكثر من قاعدة البيانات بغرض التسامح مع الخطأ أو تحسين إمكانية الوصول. مزامنة البيانات هي عملية إنشاء اتساق البيانات بين قاعدتي بيانات أو أكثر ، والتحديثات المستمرة اللاحقة للحفاظ على الاتساق المذكور.

في العديد من المؤسسات ، يعد إجراء مزامنة البيانات عبر أنظمة متنوعة أمرًا مرغوبًا فيه وصعبًا. يمكننا العثور على العديد من حالات الاستخدام التي نحتاج فيها إلى إجراء مزامنة البيانات:

• ترحيل قاعدة البيانات
• التزامن المنتظم بين أنظمة المعلومات
• استيراد البيانات من نظام معلومات إلى آخر
• نقل مجموعات البيانات بين المراحل أو البيئات المختلفة
• استيراد البيانات من مصدر غير قاعدة البيانات

لا توجد طريقة فريدة أو طريقة متفق عليها بالإجماع لمزامنة البيانات. تختلف هذه المهمة من حالة إلى أخرى ، وحتى عمليات مزامنة البيانات التي يجب أن تكون بسيطة للوهلة الأولى يمكن أن تكون معقدة ، بسبب تعقيد هياكل البيانات. في السيناريوهات الحقيقية ، تتكون مزامنة البيانات من العديد من المهام المعقدة ، والتي قد تستغرق وقتًا طويلاً لأداءها. عندما يظهر متطلب جديد ، يتعين على متخصصي قواعد البيانات عادةً إعادة تنفيذ عملية المزامنة بالكامل. نظرًا لعدم وجود طرق قياسية للقيام بذلك ، إلى جانب النسخ المتماثل ، نادرًا ما تكون تطبيقات مزامنة البيانات هي الأمثل. هذا يؤدي إلى صعوبة الصيانة وارتفاع النفقات. يعد تنفيذ وصيانة مزامنة البيانات عملية تستغرق وقتًا طويلاً ، ويمكن أن تكون وظيفة بدوام كامل في حد ذاتها.

يمكننا تنفيذ بنية مهام مزامنة البيانات يدويًا ، ربما باستخدام Microsoft Sync Framework ، أو يمكننا الاستفادة من الحلول التي تم إنشاؤها بالفعل ضمن أدوات إدارة Microsoft SQL Server. سنحاول وصف الطرق والأدوات الأكثر شيوعًا التي يمكن استخدامها لحل مزامنة البيانات على قواعد بيانات Microsoft SQL Server ونحاول تقديم بعض التوصيات.

استنادًا إلى هيكل المصدر والوجهة (مثل قواعد البيانات والجداول) يمكننا التمييز بين حالات الاستخدام عندما تكون الهياكل متشابهة أو مختلفة.

المصدر والوجهة لهما هياكل متشابهة للغاية

هذا هو الحال غالبًا عندما نستخدم البيانات في مراحل مختلفة من دورة حياة تطوير البرامج. على سبيل المثال ، بنية البيانات في بيئات الاختبار والإنتاج متشابهة جدًا. المطلب المشترك هو مقارنة البيانات بين قاعدة بيانات الاختبار والإنتاج واستيراد البيانات من الإنتاج إلى قاعدة بيانات الاختبار.

المصدر والوجهة لهما هياكل مختلفة

إذا كانت الهياكل مختلفة ، فإن المزامنة تكون أكثر تعقيدًا. هذه أيضًا مهمة متكررة بشكل متكرر. الحالة الشائعة هي الاستيراد من قاعدة بيانات إلى أخرى. الحالة الأكثر شيوعًا هي عندما يحتاج برنامج ما إلى استيراد بيانات من برنامج آخر تحتفظ به شركة أخرى. عادة ، يجب تشغيل الواردات تلقائيًا على أساس مجدول.

تعتمد الطريقة المستخدمة على التفضيلات الشخصية وتعقيد المشكلة التي تحتاج إلى حلها.

بغض النظر عن مدى تشابه الهياكل ، يمكننا اختيار أربع طرق مختلفة لحل مزامنة البيانات:

• التزامن باستخدام نصوص SQL التي تم إنشاؤها يدويًا
• المزامنة باستخدام طريقة مقارنة البيانات (يمكن استخدامها فقط عندما يكون للمصدر والهدف بنية متشابهة)
• التزامن باستخدام نصوص SQL التي تم إنشاؤها تلقائيًا - تحتاج إلى منتج تجاري

المصدر والوجهة لهما نفس الهياكل أو متشابهة للغاية

استخدام نصوص SQL التي تم إنشاؤها يدويًا

الحل الأكثر مباشرة ومملة هو كتابة نصوص SQL يدويًا من أجل المزامنة.

مزايا

• يمكن تنفيذه بواسطة أدوات مجانية ومفتوحة المصدر (FOSS).
• إذا كان الجدول يحتوي على فهارس ، فهو سريع جدًا.
• يمكن حفظ برنامج SQL النصي في إجراء مخزن ، أو تشغيله بشكل دوري كوظيفة لـ SQL Server.
• يمكن استخدامه كاستيراد تلقائي ، حتى على البيانات المتغيرة باستمرار.

سلبيات

• يعد إنشاء مثل هذا البرنامج النصي لـ SQL مملاً للغاية ، لأن هناك حاجة عادةً إلى ثلاثة نصوص لكل جدول: INSERT و UPDATE و DELETE .
• يمكنك فقط مزامنة البيانات المتوفرة عبر استعلامات SQL ، لذلك لا يمكنك الاستيراد من مصادر مثل ملفات CSV و XML.
• من الصعب الحفاظ عليها - عند تغيير بنية قاعدة البيانات ، من الضروري تعديل نصين أو ثلاثة نصوص ( INSERT ، UPDATE ، وأحيانًا DELETE أيضًا).

مثال

سنقوم بالمزامنة بين Source الجدول ، مع ID الأعمدة Value ، والجدول Target ، بنفس الأعمدة.

إذا كانت الجداول تحتوي على نفس المفتاح الأساسي ، وكان الجدول الهدف لا يحتوي على مفتاح أساسي (هوية) يتزايد تلقائيًا ، يمكنك تنفيذ برنامج المزامنة النصي التالي.

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

باستخدام طريقة مقارنة البيانات

في هذه الطريقة ، يمكننا استخدام أداة للمقارنة بين البيانات المصدر والهدف. تنشئ عملية المقارنة برامج SQL النصية التي تطبق الاختلافات من قاعدة البيانات المصدر في قاعدة البيانات الهدف.

هناك عدد من البرامج لمقارنة البيانات ومزامنتها. تستخدم هذه البرامج في الغالب نفس النهج. يقوم المستخدم بتحديد المصدر وقاعدة البيانات الهدف ، ولكن البدائل الأخرى يمكن أن تكون نسخة احتياطية لقاعدة البيانات ، أو مجلد به نصوص SQL ، أو حتى اتصال بنظام التحكم في المصدر.

فيما يلي الأدوات الأكثر شيوعًا التي تستخدم نهج مقارنة البيانات:

• مقارنة بيانات dbForge لـ SQL Server
• مقارنة بيانات RedGate SQL
• Apex SQL Data Diff

في الخطوة الأولى ، تتم قراءة البيانات أو قراءة مجاميع اختبارية لبيانات أكبر من المصدر ومن الهدف. ثم يتم تنفيذ عملية المقارنة.

توفر هذه الأدوات أيضًا إعدادات إضافية للمزامنة.

نحتاج إلى إعداد خيارات التكوين التالية الضرورية لمزامنة البيانات:

مفتاح المزامنة

بشكل افتراضي ، يتم استخدام المفتاح الأساسي أو قيد UNIQUE . إذا لم يكن هناك مفتاح أساسي ، يمكنك اختيار مجموعة من الأعمدة. يتم استخدام مفتاح المزامنة لإقران صفوف المصدر بصفوف الهدف.

الجدول الاقتران

بشكل افتراضي ، يتم إقران الجداول حسب الاسم. يمكنك تغيير هذا وإقرانهما وفقًا لاحتياجاتك الخاصة. في برنامج dbForge Data Compare ، يمكنك اختيار استعلام SQL ليكون المصدر أو الوجهة.

عملية التزامن

بعد التأكيد ، تقوم الأداة بمقارنة بيانات المصدر والهدف. تتكون العملية برمتها من تنزيل كافة بيانات المصدر والهدف ومقارنتها بناءً على معايير محددة. بشكل افتراضي ، تتم مقارنة القيم من الجداول والأعمدة ذات الأسماء المتساوية. تدعم جميع الأدوات تعيين أسماء الأعمدة والجدول. أيضًا ، هناك إمكانية لاستبعاد أعمدة IDENTITY (autoincrement) أو القيام ببعض التحولات قبل مقارنة القيم (أنواع العائمة المستديرة ، تجاهل حالة الأحرف ، التعامل مع NULL كسلسلة فارغة ، إلخ.) تم تحسين تنزيل البيانات. إذا كان حجم البيانات كبيرًا ، فسيتم تنزيل المجاميع الاختبارية فقط. هذا التحسين مفيد في معظم الحالات ، لكن متطلبات الوقت لأداء العمليات تزداد مع زيادة حجم البيانات.

في الخطوة التالية ، يوجد نص SQL مع عمليات الترحيل التي تم إنشاؤها. يمكن حفظ هذا البرنامج النصي أو تشغيله مباشرة. لكي نكون بأمان ، يمكننا حتى عمل نسخة احتياطية لقاعدة البيانات قبل تشغيل هذا البرنامج النصي. يمكن لأداة ApexSQL Data Diff إنشاء برنامج قابل للتنفيذ يقوم بتشغيل البرنامج النصي على قاعدة بيانات محددة. يحتوي هذا البرنامج النصي على بيانات يجب تغييرها ، وليس منطق كيفية تغييرها. هذا يعني أنه لا يمكن تشغيل البرنامج النصي تلقائيًا لتوفير استيراد متكرر. هذا هو أكبر عيب في هذا النهج.

مزايا

• المعرفة المتقدمة بـ SQL غير مطلوبة ، ويمكن إجراؤها عبر واجهة المستخدم الرسومية.
• لديك القدرة على التحقق بصريًا من الاختلافات بين قواعد البيانات قبل المزامنة.

سلبيات

• إنها ميزة متقدمة للمنتجات التجارية.
• ينخفض ​​الأداء عند نقل كميات هائلة من البيانات.
• يحتوي برنامج SQL النصي الذي تم إنشاؤه على اختلافات فقط ، وبالتالي لا يمكن إعادة استخدامه لمزامنة البيانات المستقبلية تلقائيًا.

أدناه يمكنك رؤية واجهة المستخدم النموذجية لهذه الأدوات.

تزامن مع SQL الذي تم إنشاؤه تلقائيًا

هذه الطريقة مشابهة جدًا لطريقة مقارنة البيانات. الاختلاف الوحيد مقارنة بالطريقة السابقة هو أنه لا توجد مقارنة بيانات ، ولا يحتوي نص SQL الذي تم إنشاؤه على اختلافات في البيانات ، بل على منطق التزامن. يمكن حفظ البرنامج النصي الذي تم إنشاؤه بسهولة في إجراء مخزن ، ويمكن تشغيله بشكل دوري (على سبيل المثال ، كل ليلة). هذه الطريقة مفيدة للواردات التلقائية بين قواعد البيانات. أداء هذه الطريقة أفضل بكثير من طريقة مقارنة البيانات.

يتم توفير التزامن بواسطة SQL الذي تم إنشاؤه تلقائيًا بواسطة SQL Database Studio فقط.

يوفر SQL Database Studio واجهة مشابهة لطريقة مقارنة البيانات. نحتاج إلى تحديد المصدر والهدف (قواعد البيانات أو الجداول). ثم نحتاج إلى إعداد الخيارات (مفاتيح المزامنة ، الاقتران والتعيين). توجد ميزة منشئ الاستعلام الرسومي لإعداد جميع المعلمات.

مزايا

• المعرفة المتقدمة بـ SQL غير مطلوبة.
• يمكنك إعداد كل شيء في واجهة المستخدم الرسومية بسرعة كبيرة.
• يمكن حفظ برنامج SQL النصي الناتج في إجراء مخزن.
• يمكن استخدامه كاستيراد تلقائي - كوظيفة لـ SQL Server.

سلبيات

• إنها ميزة متقدمة للمنتجات التجارية.
• لا يمكن التحقق من الاختلافات يدويًا قبل المزامنة ، لأن العملية برمتها يتم تنفيذها في خطوة واحدة.

معايير الأداء

حالة اختبار

قاعدتا بيانات (أ و ب) تحتوي كل منهما على جدول واحد يحتوي على 2،000،000 صف. الجداول في قاعدتي بيانات مختلفتين على نفس خادم SQL. يغطي هذا الاختبار حالتين متطرفتين: 1) يحتوي الجدول المصدر على جميع الصفوف 2،000،000 والجدول الهدف فارغ. يحتاج التزامن إلى توفير العديد من INSERTS . 2) يحتوي الجدولان المصدر والهدف على 2000000 صف. الفرق في صف واحد فقط. يحتاج التزامن إلى توفير UPDATE واحد فقط.

يحتاج RedGate Data Compare إلى 3 خطوات:

• قارن
• توليد البرنامج النصي
• قم بتشغيل البرنامج النصي على قاعدة البيانات الهدف

يحتاج ApexSQL Data Diff إلى خطوتين:

• قارن
• إنشاء البرنامج النصي وتشغيل البرنامج النصي في خطوة واحدة

يقوم SQL Database Studio بإجراء المزامنة الكاملة في خطوة واحدة. فيما يلي أوقات المزامنة بالثواني. في العمود المسمى "الخطوات الفردية" توجد فترات لخطوات المزامنة المذكورة أعلاه.

الاقل هو الافضل.

نفس الاختبار ، لكن قواعد البيانات موجودة على خوادم SQL مختلفة ، وهي غير متصلة عبر خادم مرتبط.

الاقل هو الافضل.

ملخص

من النتائج ، من الواضح أن RedGate و Apex لا يهتمان إذا كانت قواعد البيانات على نفس خادم SQL ، لأن خوارزمية المزامنة لا تعتمد على SQL Server. يستخدم SQL Database Studio وظائف أصلية لـ SQL Server ؛ لذلك ، تكون النتيجة أفضل عندما تكون قواعد البيانات على نفس الخادم.

المصدر والوجهة لهما هيكل مختلف

هناك أيضًا مواقف يجب فيها مزامنة جدول واحد عريض مع العديد من الجداول الصغيرة ذات الصلة.

يتكون هذا المثال من جدول واحد عريض SourceData الذي يجب مزامنته في جداول صغيرة Continent Country City . المخطط معطى أدناه.

يمكن أن تكون البيانات في SourceData مثل تلك الموجودة في الصورة أدناه.

استخدام نصوص SQL التي تم إنشاؤها يدويًا

البرنامج النصي مزامنة جدول القارة

 INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

البرنامج النصي مزامنة جدول المدينة

 INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

هذا السيناريو أكثر تعقيدًا. ذلك لأن السجلات في جداول Country Continent يجب العثور عليها. يقوم هذا البرنامج النصي بإدراج السجلات المفقودة في City ويملأ ContryId بشكل صحيح.

يمكن أيضًا كتابة البرامج النصية UPDATE و DELETE بنفس الطريقة إذا لزم الأمر.

مزايا

• أنت لا تحتاج إلى أي منتجات تجارية.
• يمكن حفظ البرنامج النصي SQL في الإجراء المخزن أو تشغيله بشكل دوري كوظيفة لـ SQL Server.

سلبيات

• يعد إنشاء مثل هذا البرنامج النصي لـ SQL أمرًا صعبًا ومعقدًا (بالنسبة لكل جدول ، تكون ثلاثة نصوص - INSERT و UPDATE و DELETE - ضرورية عادةً).
• من الصعب جدا الحفاظ عليها.

باستخدام أدوات خارجية

لا يمكن إجراء هذا النوع من المزامنة (الجدول العريض في العديد من الجداول ذات الصلة) باستخدام طريقة مقارنة البيانات ، لأنها تركز على حالات استخدام مختلفة. نظرًا لأن طريقة مقارنة البيانات تنتج برنامج نصي SQL مع إدراج البيانات ، فليس لديها قدرة مباشرة على البحث عن المراجع في الجداول ذات الصلة. لهذا السبب ، لا يمكن استخدام التطبيقات التي تستخدم هذه الطريقة (dbForge Data Compare for SQL Server أو RedGate SQL Data Compare أو Apex SQL Data Diff).

ومع ذلك ، يمكن أن يساعدك SQL Database Studio في إنشاء برامج نصية للمزامنة تلقائيًا. في الصورة أدناه ، يوجد عنصر يسمى محرر لمزامنة البيانات في SQL Database Studio.

يشبه المحرر منشئ الاستعلام المعروف ويعمل بطريقة مشابهة جدًا. يحتاج كل جدول إلى مفتاح مزامنة محدد ، ولكن هناك أيضًا علاقات محددة بين الجداول. في الصورة أعلاه يوجد تعيين للمزامنة أيضًا. في قائمة الأعمدة (الجزء السفلي من الصورة) توجد أعمدة الجدول City (بالنسبة للجداول الأخرى ، فهي متشابهة).

الأعمدة

• المعرف - لم يتم تعيين هذا العمود لأنه المفتاح الأساسي (يتم إنشاؤه تلقائيًا).
• CountryId - يتم تعريف هذا العمود كمرجع للجدول.
• الاسم - يتم ملء هذا العمود من العمود "المدينة" في الجدول المصدر (الجدول العريض).

يتم اختيار الأعمدة CountryId و Name كمفاتيح مزامنة. مفتاح المزامنة عبارة عن مجموعة من الأعمدة التي تحدد بشكل فريد صفًا في الجدول المصدر والهدف. لا يمكنك استخدام Id المفتاح الأساسي كمفتاح مزامنة لأن هذا غير موجود في الجدول المصدر.

بعد المزامنة ، هذا ما تبدو عليه الجداول:

في المثال أعلاه ، كان هناك جدول واحد واسع كمصدر. يوجد أيضًا سيناريو شائع عند تخزين بيانات المصدر في عدة جداول مرتبطة. لم يتم تعريف العلاقات في SQL Database Studio باستخدام مفاتيح خارجية ، ولكن باستخدام أسماء الأعمدة. بهذه الطريقة ، يمكن أيضًا الاستيراد من ملفات CSV أو Excel (يتم تحميل الملف في جدول مؤقت ، ويتم تشغيل المزامنة من هذا الجدول). من الجيد أن يكون لديك أسماء أعمدة فريدة. إذا لم يكن ذلك ممكنًا ، يمكنك تحديد الأسماء المستعارة لتلك الأعمدة.

مزايا

• سهل وسريع في الإنشاء
• سهل الصيانة
• يمكن حفظها في إجراء مخزن (يتم حفظ الإجراء المخزن بالبيانات اللازمة لفتح المزامنة في محرر لاحقًا)

سلبيات

• حل تجاري

مقارنة الحلول

تتكون مزامنة البيانات من تسلسل أوامر INSERT أو UPDATE أو DELETE . توجد طرق متعددة لإنشاء تسلسلات من هذه الأوامر. في هذه المقالة ، نظرنا في ثلاثة خيارات لإنشاء نصوص SQL للمزامنة. الخيار الأول هو إنشاء كل شيء يدويًا. إنه أمر ممكن (ولكنه يستغرق الكثير من الوقت) ، ويتطلب فهمًا معقدًا لـ SQL ، ومن الصعب إنشاؤه وصيانته. الخيار الثاني هو استخدام الأدوات التجارية. نظرنا إلى الأدوات التالية:

• مقارنة بيانات dbForge لـ SQL Server
• مقارنة بيانات RedGate SQL
• Apex SQL Data Diff
• استوديو قاعدة بيانات SQL

تعمل الأدوات الثلاث الأولى بشكل مشابه جدًا. يقارنون البيانات ، ويسمحون للمستخدم بتحليل الاختلافات ، ويمكنهم مزامنة الاختلافات المحددة (حتى تلقائيًا أو من سطر الأوامر). إنها مفيدة لسيناريوهات الاستخدام هذه:

• قواعد البيانات غير متزامنة بسبب أخطاء مختلفة.
• تحتاج إلى تجنب النسخ المتماثل أثناء نقل البيانات بين البيئات.
• هناك حاجة إلى تقارير مقارنة البيانات في Excel أو HTML.

كل أداة محبوبة لسبب أو لآخر: dbForge لديه واجهة مستخدم رائعة والعديد من الخيارات ، ApexSQL يعمل بشكل أفضل من الباقي ، و RedGate هو الأكثر شعبية.

الأداة الرابعة ، SQL Database Studio ، تعمل بشكل مختلف قليلاً. يقوم بإنشاء برامج نصية SQL تحتوي على منطق التزامن ، وليس تغييرات. الأداء رائع أيضًا ، لأن كل العمل يتم مباشرة على خادم قاعدة البيانات ، لذلك لا حاجة إلى نقل البيانات بين خادم قاعدة البيانات وأداة المزامنة. هذه الأداة مفيدة لحالات الاستخدام التالية:

• عمليات الترحيل التلقائية لقاعدة البيانات حيث يكون لقواعد البيانات بنية مختلفة
• الاستيراد في جداول متعددة ذات صلة
• الاستيراد من مصادر خارجية XML ، CSV ، MS Excel