Bagikan melalui

Mengotomatiskan replikasi perubahan skema di Azure SQL Data Sync

  • Artikel

Berlaku untuk:Azure SQL Database

SQL Data Sync memungkinkan pengguna menyinkronkan data antara database di Azure SQL Database dan instans SQL Server dalam satu arah atau kedua arah. Salah satu keterbatasan SQL Data Sync saat ini adalah kurangnya dukungan untuk replikasi perubahan skema. Setiap kali Anda mengubah skema tabel, Anda perlu menerapkan perubahan secara manual di semua titik akhir, termasuk hub dan semua anggota, lalu memperbarui skema sinkronisasi.

Artikel ini menawarkan solusi untuk mereplikasi perubahan skema secara otomatis ke semua titik akhir SQL Data Sync.

  1. Solusi ini menggunakan pemicu DDL untuk melacak perubahan skema.
  2. Pemicu menyisipkan perintah perubahan skema dalam tabel pelacakan.
  3. Tabel pelacakan ini disinkronkan ke semua titik akhir menggunakan layanan Data Sync.
  4. Pemicu DML setelah penyisipan digunakan untuk menerapkan perubahan skema pada titik akhir lainnya.

Artikel ini menggunakan ALTER TABLE sebagai contoh perubahan skema, tetapi solusi ini juga dapat diterapkan untuk jenis perubahan skema lainnya.

Penting

Sebaiknya Anda membaca artikel ini dengan cermat, terutama bagian Pemecahan Masalah dan Pertimbangan lainnya, sebelum mulai menerapkan replikasi perubahan skema otomatis di lingkungan sinkronisasi Anda. Sebaiknya Anda juga membaca Sinkronisasi data di beberapa database cloud dan lokal dengan SQL Data Sync. Beberapa operasi database dapat membuat solusi yang dijelaskan dalam artikel ini tidak efektif. Pengetahuan domain tambahan tentang SQL Server dan Transact-SQL mungkin diperlukan untuk memecahkan masalah tersebut.

Automating the replication of schema changes

Siapkan replikasi perubahan skema otomatis

Buat tabel untuk melacak perubahan skema

Buat tabel untuk melacak perubahan skema di semua database dalam grup sinkronisasi:

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

Tabel ini memiliki kolom identitas untuk melacak urutan perubahan skema. Anda bisa menambahkan lebih banyak bidang untuk mencatat informasi lainnya jika diperlukan.

Buat tabel untuk melacak riwayat perubahan skema

Di semua titik akhir, buat tabel untuk melacak ID dari perintah perubahan skema yang terakhir diterapkan.

CREATE TABLE SchemaChangeHistory (
LastAppliedId bigint PRIMARY KEY
)
GO

INSERT INTO SchemaChangeHistory VALUES (0)

Membuat pemicu ALTER TABLE DDL di database yang mengalami perubahan skema

Membuat pemicu DDL untuk operasi ALTER TABLE. Anda hanya perlu membuat pemicu ini di database yang mengalami perubahan skema. Untuk menghindari konflik, hanya perbolehkan perubahan skema dalam satu database dalam grup sinkronisasi.

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')

Pemicu menyisipkan rekaman dalam tabel pelacakan perubahan skema untuk setiap perintah ALTER TABLE. Contoh ini menambahkan filter untuk menghindari replikasi perubahan skema yang dibuat pada skema DataSync, karena perubahan ini kemungkinan besar diakibatkan oleh layanan Sinkronisasi Data. Tambahkan lebih banyak filter jika Anda hanya ingin mereplikasi tipe perubahan skema tertentu.

Anda juga dapat menambahkan lebih banyak pemicu untuk mereplikasi jenis perubahan skema lainnya. Misalnya, buat CREATE_PROCEDURE, ALTER_PROCEDURE, dan DROP_PROCEDURE untuk mereplikasi perubahan pada prosedur yang disimpan.

Buat pemicu pada titik akhir lain untuk menerapkan perubahan skema selama penyisipan

Pemicu ini menjalankan perintah perubahan skema ketika disinkronkan ke titik akhir lainnya. Anda perlu membuat pemicu ini di semua titik akhir, kecuali di titik di mana dilakukan perubahan skema (yaitu, dalam database di mana pemicu DDL AlterTableDDLTrigger dibuat pada langkah sebelumnya).

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

Pemicu ini berjalan setelah dilakukan penyisipan dan memeriksa apakah perintah saat ini akan berjalan berikutnya. Logika kode memastikan bahwa tidak ada pernyataan perubahan skema yang dilewati, dan semua perubahan diterapkan meskipun penyisipan rusak.

Sinkronkan tabel pelacakan perubahan skema ke semua titik akhir

Anda bisa menyinkronkan tabel pelacakan perubahan skema ke semua titik akhir menggunakan grup sinkronisasi yang sudah ada atau grup sinkronisasi baru. Pastikan perubahan dalam tabel pelacakan dapat disinkronkan ke semua titik akhir, terutama saat Anda menggunakan sinkronisasi satu arah.

Jangan sinkronkan tabel riwayat perubahan skema, karena tabel tersebut mempertahankan status berbeda untuk setiap titik akhir yang berbeda.

Menerapkan perubahan skema dalam grup sinkronisasi

Replikasi dilakukan hanya untuk perubahan skema yang dibuat dalam database tempat pemicu DDL dibentuk. Perubahan skema yang dilakukan di database lain tidak direplikasi.

Setelah perubahan skema direplikasi ke semua titik akhir, Anda juga perlu mengambil langkah tambahan untuk memperbarui skema sinkronisasi untuk memulai atau berhenti menyinkronkan kolom baru.

Tambahkan kolom baru

  1. Ubah skema.

  2. Jangan lakukan perbahan pada data yang disinkronkan dengan kolom baru hingga Anda menyelesaikan langkah pembuatan pemicu.

  3. Tunggu hingga perubahan skema diterapkan ke semua titik akhir.

  4. Refresh skema database dan tambahkan kolom baru ke skema sinkronisasi.

  5. Data di kolom baru disinkronkan selama operasi sinkronisasi berikutnya.

Hapus kolom

  1. Menghapus kolom dari skema sinkronisasi. Data Sync berhenti menyinkronkan data dalam kolom ini.

  2. Ubah skema.

  3. Refresh skema database.

Perbarui jenis data

  1. Ubah skema.

  2. Tunggu hingga perubahan skema diterapkan ke semua titik akhir.

  3. Refresh skema database.

  4. Jika tipe data baru dan lama tidak sepenuhnya kompatibel - misalnya, jika Anda mengubah dari int ke bigint - sinkronisasi mungkin gagal sebelum langkah pembuatan pemicu selesai. Sinkronisasi berhasil setelah mencoba melakukannya kembali.

Mengganti nama kolom atau tabel

Mengganti nama kolom atau tabel membuat Data Sync berhenti berfungsi. Buat tabel atau kolom baru, isi ulang data, lalu hapus tabel atau kolom lama alih-alih mengganti nama.

Tipe perubahan skema lainnya

Untuk jenis perubahan skema lainnya - misalnya, membuat prosedur yang disimpan atau menghapus indeks - tidak perlu memperbarui skema sinkronisasi.

Memecahkan masalah replikasi perubahan skema otomatis

Logika replikasi yang dijelaskan dalam artikel ini berhenti berfungsi dalam beberapa situasi- misalnya, jika Anda membuat perubahan skema dalam database lokal yang tidak didukung di Azure SQL Database. Dalam hal ini, sinkronisasi tabel pelacakan perubahan skema gagal dilakukan. Anda perlu mengatasi masalah ini secara manual:

  1. Nonaktifkan pemicu DDL dan hindari perubahan skema lebih lanjut sampai masalah diperbaiki.

  2. Di database titik akhir tempat masalah terjadi, nonaktifkan pemicu AFTER INSERT pada titik akhir di mana perubahan skema tidak dapat dilakukan. Tindakan ini memungkinkan perintah perubahan skema untuk disinkronkan.

  3. Sinkronisasi pemicu untuk menyinkronkan tabel pelacakan perubahan skema.

  4. Di database titik akhir tempat masalah terjadi, lakukan kueri pada tabel riwayat perubahan skema untuk mendapatkan ID perintah perubahan skema yang terakhir diterapkan.

  5. Lakukan kueri pada tabel pelacakan perubahan skema untuk mencantumkan semua perintah dengan ID yang lebih besar dari nilai ID yang Anda ambil di langkah sebelumnya.

    a. Abaikan perintah yang tidak dapat dijalankan di database titik akhir. Anda harus mengatasi inkonsistensi skema. Kembalikan perubahan skema asli jika inkonsistensi ini berdampak pada aplikasi Anda.

    b. Terapkan secara manual perintah-perintah yang harus diterapkan.

  6. Perbarui tabel riwayat perubahan skema dan atur ID terakhir yang diterapkan ke nilai yang benar.

  7. Periksa kembali apakah skema sudah diperbarui.

  8. Aktifkan kembali pemicu AFTER INSERT yang dinonaktifkan di langkah kedua.

  9. Aktifkan kembali pemicu DDL yang dinonaktifkan pada langkah pertama.

Jika Anda ingin membersihkan catatan dalam tabel pelacakan perubahan skema, gunakan DELETE, bukan TRUNCATE. Jangan pernah melakukan reseeding pada kolom identitas dalam tabel pelacakan perubahan skema dengan menggunakan DBCC CHECKIDENT. Anda dapat membuat tabel pelacakan perubahan skema baru dan memperbarui nama tabel di pemicu DDL jika reseeding diperlukan.

Pertimbangan Lainnya

  • Pengguna database yang mengonfigurasi hub dan database anggota harus memiliki izin yang cukup untuk menjalankan perintah perubahan skema.

  • Anda dapat menambahkan lebih banyak filter di pemicu DDL agar hanya mereplikasi perubahan skema dalam tabel atau operasi yang dipilih.

  • Anda hanya bisa membuat perubahan skema di database tempat pemicu DDL dibuat.

  • Jika Anda membuat perubahan dalam database SQL Server, pastikan perubahan skema didukung di Azure SQL Database.

  • Jika perubahan skema dibuat dalam database selain database tempat pemicu DDL dibuat, perubahan tidak direplikasi. Untuk menghindari masalah ini, Anda dapat membuat pemicu DDL untuk memblokir perubahan pada titik akhir lainnya.

  • Jika Anda perlu mengubah skema tabel pelacakan perubahan skema, nonaktifkan pemicu DDL sebelum Anda melakukan perubahan, lalu terapkan perubahan secara manual ke semua titik akhir. Memperbarui skema dalam pemicu AFTER INSERT pada tabel yang sama tidak berfungsi.

  • Jangan pernah melakukan reseeding pada kolom identitas dengan menggunakan DBCC CHECKIDENT.

  • Jangan gunakan TRUNCATE untuk membersihkan data dalam tabel pelacakan perubahan skema.

Langkah berikutnya

Untuk informasi selengkapnya tentang SQL Data Sync, lihat: