أتمتة النسخ المتماثل تغييرات المخطط في مزامنة بيانات Azure SQL

ينطبق على: قاعدة بيانات Azure SQL

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

تقدم هذه المقالة حلا للنسخ المتماثل تلقائياً تغييرات المخطط إلى جميع نقاط النهاية مزامنة البيانات SQL.

1. يستخدم هذا الحل مشغل DDL لتعقب تغييرات المخطط.
2. يقوم المشغل بإدراج أوامر تغيير المخطط في جدول تعقب.
3. تتم مزامنة جدول التتبع هذا مع جميع نقاط النهاية باستخدام خدمة مزامنة البيانات.
4. يتم استخدام مشغلات DML بعد الإدراج لتطبيق تغييرات المخطط على نقاط النهاية الأخرى.

يستخدم هذا المقال ALTER TABLE كمثال تغيير مخطط ولكن يعمل هذا الحل أيضاً لأنواع أخرى من تغييرات المخطط.

هام

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

إعداد النسخ المتماثل لتغيير المخطط التلقائية

إنشاء جدول لتعقب تغييرات المخطط

إنشاء جدول لتعقب تغييرات المخطط في جميع قواعد البيانات في مجموعة المزامنة:

CREATE TABLE SchemaChanges (
ID bigint IDENTITY(1,1) PRIMARY KEY,
SqlStmt nvarchar(max),
[Description] nvarchar(max)
)

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

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

على جميع نقاط النهاية، قم بإنشاء جدول لتعقب معرف الأمر تغيير المخطط الذي تم تطبيقه مؤخرا.

CREATE TABLE SchemaChangeHistory (
LastAppliedId bigint PRIMARY KEY
)
GO

INSERT INTO SchemaChangeHistory VALUES (0)

إنشاء مشغل ALTER TABLE DDL في قاعدة البيانات حيث يتم إجراء تغييرات المخطط

إنشاء مشغل DDL لعمليات ALTER TABLE. تحتاج فقط إلى إنشاء هذا المشغل في قاعدة البيانات حيث يتم إجراء تغييرات المخطط. لتجنب التعارضات، يسمح فقط تغييرات المخطط في قاعدة بيانات واحدة في مجموعة مزامنة.

CREATE TRIGGER AlterTableDDLTrigger
ON DATABASE
FOR ALTER_TABLE
AS

-- You can add your own logic to filter ALTER TABLE commands instead of replicating all of them.

IF NOT (EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(512)') like 'DataSync')

INSERT INTO SchemaChanges (SqlStmt, Description)
    VALUES (EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'), 'From DDL trigger')

يدرج المشغل سجلاً في مخطط "تغيير تعقب الجدول لكل أمر ALTER TABLE". يضيف هذا المثال عامل تصفية لتجنب النسخ المتماثل تغييرات المخطط التي تم إجراؤها ضمن مخطط DataSync، لأن هذه غالباً ما يتم إجراؤها بواسطة خدمة مزامنة البيانات. إضافة المزيد من عوامل التصفية إذا كنت تريد فقط نسخ أنواع معينة من تغييرات المخطط.

يمكنك أيضاً إضافة المزيد من المشغلات للنسخ المتماثل لأنواع أخرى من تغييرات المخطط. على سبيل المثال، إنشاء CREATE_PROCEDURE ALTER_PROCEDURE ومشغلات DROP_PROCEDURE نسخ التغييرات إلى الإجراءات المخزنة.

إنشاء مشغل على نقاط النهاية الأخرى لتطبيق تغييرات المخطط أثناء الإدراج

ينفذ هذا المشغل الأمر تغيير المخطط عند مزامنته إلى نقاط النهاية الأخرى. تحتاج إلى إنشاء هذا المشغل على جميع نقاط النهاية، باستثناء واحد حيث يتم إجراء تغييرات المخطط (أي، في قاعدة البيانات حيث يتم إنشاء مشغل DDL AlterTableDDLTrigger في الخطوة السابقة).

CREATE TRIGGER SchemaChangesTrigger
ON SchemaChanges
AFTER INSERT
AS
DECLARE @lastAppliedId bigint
DECLARE @id bigint
DECLARE @sqlStmt nvarchar(max)
SELECT TOP 1 @lastAppliedId=LastAppliedId FROM SchemaChangeHistory
SELECT TOP 1 @id = id, @SqlStmt = SqlStmt FROM SchemaChanges WHERE id > @lastAppliedId ORDER BY id
IF (@id = @lastAppliedId + 1)
BEGIN
    EXEC sp_executesql @SqlStmt
        UPDATE SchemaChangeHistory SET LastAppliedId = @id
    WHILE (1 = 1)
    BEGIN
        SET @id = @id + 1
        IF exists (SELECT id FROM SchemaChanges WHERE ID = @id)
            BEGIN
                SELECT @sqlStmt = SqlStmt FROM SchemaChanges WHERE ID = @id
                EXEC sp_executesql @SqlStmt
                UPDATE SchemaChangeHistory SET LastAppliedId = @id
            END
        ELSE
            BREAK;
    END
END

يعمل هذا المشغل بعد الإدراج ويتحقق مما إذا كان يجب تشغيل الأمر الحالي التالي. منطق التعليمات البرمجية يضمن أن يتم تخطي أي عبارة تغيير المخطط ويتم تطبيق جميع التغييرات حتى إذا كان الإدراج خارج الترتيب.

مزامنة جدول تعقب تغيير المخطط إلى جميع نقاط النهاية

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

لا تقم بمزامنة جدول محفوظات تغيير المخطط، حيث يحافظ هذا الجدول على حالة مختلفة على نقاط نهاية مختلفة.

تطبيق تغييرات المخطط في مجموعة مزامنة

يتم نسخ تغييرات المخطط التي تم إجراؤها في قاعدة البيانات حيث يتم إنشاء مشغل DDL فقط. لا يتم نسخ تغييرات المخطط التي تم إجراؤها في قواعد بيانات أخرى.

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

إضافة أعمدة جديدة

1. قم بتغيير المخطط.

2. تجنب أي تغيير في البيانات حيث تتضمن الأعمدة الجديدة حتى تكمل الخطوة التي تنشئ المشغل.

3. انتظر حتى يتم تطبيق تغييرات المخطط على جميع نقاط النهاية.

4. تحديث مخطط قاعدة البيانات وإضافة عمود جديد إلى مخطط المزامنة.

5. تتم مزامنة البيانات في العمود الجديد أثناء عملية المزامنة التالية.

إزالة الأعمدة

1. إزالة الأعمدة من مخطط المزامنة. توقف مزامنة البيانات مزامنة البيانات في هذه الأعمدة.

2. قم بتغيير المخطط.

3. تحديث مخطط قاعدة البيانات.

تحديث أنواع البيانات

1. قم بتغيير المخطط.

2. انتظر حتى يتم تطبيق تغييرات المخطط على جميع نقاط النهاية.

3. تحديث مخطط قاعدة البيانات.

4. إذا لم تكن أنواع البيانات الجديدة والقديمة متوافقة تماماً - على سبيل المثال، إذا قمت بتغيير من int إلى bigint - فقد تفشل المزامنة قبل إكمال الخطوات التي تقوم بإنشاء المشغلات. تنجح المزامنة بعد إعادة المحاولة.

إعادة تسمية الأعمدة أو الجداول

إعادة تسمية الأعمدة أو الجداول يجعل "مزامنة البيانات" تتوقف عن العمل. إنشاء جدول أو عمود جديد، ردم البيانات، ثم حذف الجدول القديم أو العمود بدلاً من إعادة تسمية.

أنواع أخرى من تغييرات المخطط

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

استكشاف أخطاء النسخ المتماثل لتغيير المخطط التلقائي وإصلاحها

منطق النسخ المتماثل الموضح في هذه المقالة يتوقف عن العمل في بعض الحالات-على سبيل المثال، إذا قمت بتغيير مخطط في قاعدة بيانات محلية غير معتمد في قاعدة بيانات SQL Azure. في هذه الحالة، فشل مزامنة جدول تعقب تغيير المخطط. تحتاج إلى إصلاح هذه المشكلة يدويا:

1. تعطيل مشغل DDL وتجنب أي تغييرات مخطط إضافية حتى يتم إصلاح المشكلة.

2. في قاعدة بيانات نقطة النهاية حيث تحدث المشكلة، قم بتعطيل مشغل AFTER INSERT على نقطة النهاية حيث لا يمكن إجراء تغيير المخطط. يسمح هذا الإجراء الأمر تغيير المخطط إلى مزامنة.

3. تشغيل المزامنة لمزامنة جدول تعقب تغيير المخطط.

4. في قاعدة بيانات نقطة النهاية حيث تحدث المشكلة الاستعلام عن جدول محفوظات تغيير المخطط للحصول على معرف الأمر تغيير المخطط تطبيق آخر.

5. الاستعلام عن جدول تعقب تغيير المخطط لسرد جميع الأوامر ذات معرف أكبر من قيمة معرف التي تم استردادها في الخطوة السابقة.

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

   ب. تطبيق تلك الأوامر التي يجب تطبيقها يدويا.

6. تحديث جدول محفوظات تغيير المخطط وتعيين معرف تطبيق آخر إلى القيمة الصحيحة.

7. تحقق مرة أخرى مما إذا كان المخطط محدثاً أم لا.

8. إعادة تمكين المشغل AFTER INSERT معطل في الخطوة الثانية.

9. إعادة تمكين مشغل DDL معطل في الخطوة الأولى.

إذا كنت ترغب في تنظيف السجلات في جدول تعقب تغيير المخطط، فاستخدم DELETE بدلاً من اقتطاع. لم تقم بتغيير عمود الهوية في جدول تعقب تغيير المخطط باستخدام DBCC CHECKIDENT. يمكنك إنشاء جداول تعقب تغيير مخطط جديد وتحديث اسم الجدول في مشغل DDL إذا كان مطلوباً إعادة الاقتناع.

الاعتبارات الأخرى

• يحتاج مستخدمو قاعدة البيانات الذين يقومون بتكوين قاعدة بيانات لوحة الوصل والعضو إلى الحصول على إذن كاف لتنفيذ أوامر تغيير المخطط.

• يمكنك إضافة عوامل تصفية أكثر في مشغل DDL للنسخ المتماثل لتغيير المخطط فقط في الجداول أو العمليات المحددة.

• يمكنك فقط إجراء تغييرات المخطط في قاعدة البيانات حيث يتم إنشاء مشغل DDL.

• إذا كنت تقوم بإجراء تغيير في قاعدة بيانات SQL Server فتأكد من أن تغيير المخطط معتمد في قاعدة بيانات SQL Azure.

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

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

• لا تقم بإعادة إعادة تعيين عمود الهوية باستخدام DBCC CHECKIDENT.

• لا تستخدم اقتطاع لتنظيف البيانات في جدول تعقب تغيير المخطط.

الخطوات التالية

لمزيد من المعلومات حول مزامنة بيانات SQL، راجع:

• نظرة عامة - مزامنة البيانات عبر قواعد بيانات سحابية ومحلية متعددة باستخدام Azure SQL Data Sync
• إعداد Data Sync
  • في المدخل - برنامج تعليمي: إعداد مزامنة بيانات SQL لمزامنة البيانات بين Azure SQL Database وMicrosoft SQL Server
  • باستخدام PowerShell
    • استخدم PowerShell للمزامنة بين قواعد بيانات متعددة في Azure SQL Database
    • استخدام PowerShell للمزامنة بين قاعدة بيانات في قاعدة بيانات Azure SQL وقاعدة بيانات في مثيل SQL Server
• عامل مزامنة البيانات - عامل مزامنة البيانات لمزامنة بيانات Azure SQL
• أفضل الممارسات - أفضل الممارسات لمزامنة بيانات Azure SQL
• المراقبة - مراقبة SQL Data Sync بسجلات Azure Monitor
• استكشاف الأخطاء وإصلاحها - استكشاف مشكلات Azure SQL Data Sync وإصلاحها
• قم بتحديث مخطط المزامنة
  • باستخدام PowerShell - استخدم PowerShell لتحديث مخطط المزامنة في مجموعة مزامنة موجودة