在 Azure SQL 資料同步中自動複寫結構描述的變更內容

發行項
09/30/2024

重要

SQL 資料同步將於 2027 年 9 月 30 日淘汰。請考慮移轉至替代資料複寫/同步處理解決方案。

SQL 資料同步可讓使用者以單向或雙向同步處理 Azure SQL 資料庫和 SQL Server 執行個體中的資料庫之間的資料。 SQL 資料同步目前存在的其中一個限制是，缺乏對結構描述變更複寫的支援。每次變更資料表結構描述時，都必須在所有端點，包括中樞和所有成員上手動套用變更，然後更新同步結構描述。

本文介紹一個解決方案，可自動將結構描述變更複寫至所有 SQL 資料同步端點。

此解決方案會使用 DDL 觸發程序來追蹤結構描述變更。
觸發程序會在追蹤資料表中插入結構描述變更命令。
此追蹤資料表會使用資料同步服務同步至所有端點。
插入之後的 DML 觸發程序會用來在其他端點上套用結構描述變更。

本文使用 ALTER TABLE 作為結構描述變更的範例，但此解決方案也適用於其他類型的結構描述變更。

請仔細閱讀本文，特別是有關疑難排解和其他考量的章節，再開始在同步環境中實作自動化結構描述變更複寫。部分資料庫作業可能會中斷本文所述的解決方案。可能需要更多 SQL Server 和 Transact-SQL 的領域知識，才能針對這些問題進行疑難排解。

結構描述變更複寫的圖表。

設定自動化結構描述變更複寫

建立資料表以追蹤結構描述變更

建立資料表來追蹤同步群組中所有資料庫的結構描述變更：

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

此資料表具有識別欄位，以追蹤結構描述變更的順序。您可以視需要新增更多欄位來記錄更多資訊。

建立資料表以追蹤結構描述變更的歷程記錄

在所有端點上，建立資料表來追蹤最近套用的結構描述變更命令的 ID。

CREATE TABLE SchemaChangeHistory (
LastAppliedId bigint PRIMARY KEY
)
GO

INSERT INTO SchemaChangeHistory VALUES (0)

在結構描述變更所在的資料庫中建立 ALTER TABLE DDL 觸發程序

建立 ALTER TABLE 作業的 DDL 觸發程序。您只需要在結構描述變更所在的資料庫中建立此觸發程序。為了避免衝突，同步群組只允許一個資料庫中發生結構描述變更。

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 觸發程序之資料庫中所做的結構描述變更。不會複寫其他資料庫中所做的結構描述變更。

將結構描述變更複寫到所有端點之後，您也需要採取額外的步驟來更新同步結構描述，以啟動或停止同步新的資料行。

加入新資料行

進行結構描述變更。
避免任何涉及新資料行的資料變更，直到您完成建立觸發程序的步驟為止。
等到結構描述變更套用至所有端點為止。
重新整理資料庫結構描述，並將新資料行新增至同步結構描述。
新資料行中的資料會在下次同步作業期間進行同步。

移除欄

從同步結構描述中移除資料行。資料同步會停止同步這些資料行中的資料。
進行結構描述變更。
重新整理資料庫結構描述。

更新資料類型

進行結構描述變更。
等到結構描述變更套用至所有端點為止。
重新整理資料庫結構描述。
如果新的和舊的資料類型無法完全相容，例如，如果您從 int 變更為 bigint - 同步可能會在建立觸發程序的步驟完成之前失敗。重試之後，同步會成功。

重新命名資料行或資料表

重新命名資料行或資料表會讓資料同步停止運作。建立新的資料表或資料行、回填資料，然後刪除舊的資料表或資料行，而不是重新命名。

其他類型的結構描述變更

針對其他類型的結構描述變更 (例如，不需要建立預存程序或卸除索引)，而不需要更新同步結構描述。

針對自動化結構描述變更複寫進行疑難排解

本文所述的複寫邏輯在某些情況下會停止運作，例如，如果您在 Azure SQL 資料庫不支援的內部部署資料庫中進行結構描述變更。在此情況下，同步結構描述變更追蹤資料表會失敗。您必須手動修正此問題：

停用 DDL 觸發程序，並避免任何進一步的結構描述變更，直到問題得到修正為止。
在發生問題的端點資料庫中，停用無法進行結構描述變更的端點上的 AFTER INSERT 觸發程序。此動作可讓結構描述變更命令同步。
觸發同步，以同步結構描述變更追蹤資料表。
在發生問題的端點資料庫中，查詢結構描述變更歷程記錄資料表，以取得上次套用之結構描述變更命令的 ID。
查詢結構描述變更追蹤資料表，列出 ID 大於您在上一個步驟中擷取的 ID 值的所有命令。

a. 忽略無法在端點資料庫中執行的命令。您必須處理結構描述不一致的問題。如果不一致影響您的應用程式，請還原原始結構描述變更。

b. 手動套用應套用的命令。
更新結構描述變更歷程記錄資料表，並將最後套用的 ID 設定為正確的值。
仔細檢查結構描述是否為最新狀態。
在第二個步驟中，重新啟用停用的 AFTER INSERT 觸發程序。
在第一個步驟中重新啟用停用的 DDL 觸發程序。

如果您想要清理結構描述變更追蹤資料表中的記錄，請使用 DELETE，而不是 TRUNCATE。永不使用 DBCC CHECKIDENT 來重新植入結構描述變更追蹤資料表中的識別欄位。如果需要重新植入，您可以建立新的結構描述變更追蹤資料表，並更新 DDL 觸發程序中的資料表名稱。

其他考量

設定中樞和成員資料庫的資料庫用戶必須有足夠的權限來執行結構描述變更命令。
您可以在 DDL 觸發程序中新增更多篩選，只複寫所選資料表或作業中的結構描述變更。
您只能在建立 DDL 觸發程序的資料庫中進行結構描述變更。
如果您要在 SQL Server 資料庫中進行變更，請確定 Azure SQL 資料庫支援結構描述變更。
如果在建立 DDL 觸發程序的資料庫以外的資料庫中進行結構描述變更，則不會複寫這些變更。若要避免此問題，您可以建立 DDL 觸發程序來封鎖其他端點上的變更。
如果您需要變更結構描述變更追蹤資料表的結構描述，請先停用 DDL 觸發程序，再進行變更，然後手動將變更套用至所有端點。在相同資料表上更新 AFTER INSERT 觸發程序中的結構描述無法運作。
請勿使用 DBCC CHECKIDENT 重新植入識別欄位。
請勿使用 TRUNCATE 來清理結構描述變更追蹤資料表中的資料。

共用方式為

在 Azure SQL 資料同步中自動複寫結構描述的變更內容

設定自動化結構描述變更複寫

建立資料表以追蹤結構描述變更

建立資料表以追蹤結構描述變更的歷程記錄

在結構描述變更所在的資料庫中建立 ALTER TABLE DDL 觸發程序

在其他端點上建立觸發程序，以在插入期間套用結構描述變更

將結構描述變更追蹤資料表同步至所有端點

在同步群組中套用結構描述變更

加入新資料行

移除欄

更新資料類型

重新命名資料行或資料表

其他類型的結構描述變更

針對自動化結構描述變更複寫進行疑難排解

其他考量

意見反應

其他資源

共用方式為

在 Azure SQL 資料同步中自動複寫結構描述的變更內容

設定自動化結構描述變更複寫

建立資料表以追蹤結構描述變更

建立資料表以追蹤結構描述變更的歷程記錄

在結構描述變更所在的資料庫中建立 ALTER TABLE DDL 觸發程序

在其他端點上建立觸發程序，以在插入期間套用結構描述變更

將結構描述變更追蹤資料表同步至所有端點

在同步群組中套用結構描述變更

加入新資料行

移除欄

更新資料類型

重新命名資料行或資料表

其他類型的結構描述變更

針對自動化結構描述變更複寫進行疑難排解

其他考量

相關內容

意見反應

其他資源