在 Azure SQL 数据同步中自动复制架构更改Automate the replication of schema changes in Azure SQL Data Sync

适用于:是Azure SQL 数据库 APPLIES TO: yesAzure SQL Database

SQL 数据同步允许用户在 Azure SQL 数据库和 SQL Server 实例中的数据库之间单向或双向同步数据。SQL Data Sync lets users synchronize data between databases in Azure SQL Database and SQL Server instances in one direction or in both directions. SQL 数据同步的当前限制之一是不支持架构更改的复制。One of the current limitations of SQL Data Sync is a lack of support for the replication of schema changes. 每次更改表架构时,都需要在所有终结点(包括中心和所有成员)上手动应用更改,然后更新同步架构。Every time you change the table schema, you need to apply the changes manually on all endpoints, including the hub and all members, and then update the sync schema.

本文介绍了将架构更改自动复制到所有 SQL 数据同步终结点的解决方法。This article introduces a solution to automatically replicate schema changes to all SQL Data Sync endpoints.

  1. 此解决方法使用 DDL 触发器来跟踪架构更改。This solution uses a DDL trigger to track schema changes.
  2. 此触发器在跟踪表中插入架构更改命令。The trigger inserts the schema change commands in a tracking table.
  3. 使用数据同步服务将此跟踪表同步到所有终结点。This tracking table is synced to all endpoints using the Data Sync service.
  4. 插入后,使用 DML 触发器在其他终结点上应用架构更改。DML triggers after insertion are used to apply the schema changes on the other endpoints.

本文使用 ALTER TABLE 作为架构更改的示例,但此解决方案也适用于其他类型的架构更改。This article uses ALTER TABLE as an example of a schema change, but this solution also works for other types of schema changes.

重要

我们建议仔细阅读本文,尤其是故障排除其他注意事项部分,然后在同步环境中开始实现自动架构更改复制。We recommend that you read this article carefully, especially the sections about Troubleshooting and Other considerations, before you start to implement automated schema change replication in your sync environment. 我们还建议阅读使用 SQL 数据同步跨多个云和本地数据库同步数据。某些数据库操作可能导致本文所述的解决方法不起作用。We also recommend that you read Sync data across multiple cloud and on-premises databases with SQL Data Sync. Some database operations may break the solution described in this article. 可能需要有 SQL Server 和 Transact-SQL 领域的其他知识才能排查这些问题。Additional domain knowledge of SQL Server and Transact-SQL may be required to troubleshoot those issues.

自动复制架构更改

设置自动架构更改复制Set up automated schema change replication

创建用于跟踪架构更改的表Create a table to track schema changes

创建一个表,用于跟踪同步组中所有数据库的架构更改:Create a table to track schema changes in all databases in the sync group:

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

此表包含一个用于跟踪架构更改顺序的标识列。This table has an identity column to track the order of schema changes. 可根据需要添加更多字段来记录详细信息。You can add more fields to log more information if needed.

创建用于跟踪架构更改历史记录的表Create a table to track the history of schema changes

在所有终结点上,创建一个表用于跟踪最近应用的架构更改命令的 ID。On all endpoints, create a table to track the ID of the most recently applied schema change command.

CREATE TABLE SchemaChangeHistory (
LastAppliedId bigint PRIMARY KEY
)
GO

INSERT INTO SchemaChangeHistory VALUES (0)

在发生架构更改的数据库中创建一个 ALTER TABLE DDL 触发器Create an ALTER TABLE DDL trigger in the database where schema changes are made

为 ALTER TABLE 操作创建 DDL 触发器。Create a DDL trigger for ALTER TABLE operations. 只需在发生架构更改的数据库中创建此触发器。You only need to create this trigger in the database where schema changes are made. 为避免冲突,只允许同步组中的一个数据库发生架构更改。To avoid conflicts, only allow schema changes in one database in a sync group.

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 命令插入一条记录。The trigger inserts a record in the schema change tracking table for each ALTER TABLE command. 此示例会添加一个筛选器,以避免复制架构 DataSync 下发生的架构更改,因为这些更改很有可能是数据同步服务做出的。This example adds a filter to avoid replicating schema changes made under schema DataSync, because these are most likely made by the Data Sync service. 如果只想复制特定类型的架构更改,请添加更多的筛选器。Add more filters if you only want to replicate certain types of schema changes.

还可以添加更多触发器来复制其他类型的架构更改。You can also add more triggers to replicate other types of schema changes. 例如,创建 CREATE_PROCEDURE、ALTER_PROCEDURE 和 DROP_PROCEDURE 触发器,将更改复制到存储过程。For example, create CREATE_PROCEDURE, ALTER_PROCEDURE and DROP_PROCEDURE triggers to replicate changes to stored procedures.

在其他终结点上创建用于在插入期间应用架构更改的触发器Create a trigger on other endpoints to apply schema changes during insertion

此触发器在同步到其他终结点后,将执行架构更改命令。This trigger executes the schema change command when it is synced to other endpoints. 需要在所有端点上创建此触发器,但发生架构更改的终结点(即,在上一步骤中创建 DDL 触发器 AlterTableDDLTrigger 的数据库)除外。You need to create this trigger on all the endpoints, except the one where schema changes are made (that is, in the database where the DDL trigger AlterTableDDLTrigger is created in the previous step).

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

插入后,此触发器将会运行,并检查接下来是否应运行当前命令。This trigger runs after the insertion and checks whether the current command should run next. 代码逻辑会确保不会跳过任何架构更改语句,并且即使插入操作失序,也会应用所有更改。The code logic ensures that no schema change statement is skipped, and all changes are applied even if the insertion is out of order.

将架构更改跟踪表同步到所有终结点Sync the schema change tracking table to all endpoints

可以使用现有同步组或新同步组将架构更改跟踪表同步到所有终结点。You can sync the schema change tracking table to all endpoints using the existing sync group or a new sync group. 确保可将跟踪表中的更改同步到所有终结点,尤其是使用单向同步时。Make sure the changes in the tracking table can be synced to all endpoints, especially when you're using one-direction sync.

不要同步架构更改历史记录表,因为该表维护不同终结点上的不同状态。Don't sync the schema change history table, since that table maintains different state on different endpoints.

在同步组中应用架构更改Apply the schema changes in a sync group

只会复制创建了 DDL 触发器的数据库中发生的架构更改。Only schema changes made in the database where the DDL trigger is created are replicated. 不会复制其他数据库中发生的架构更改。Schema changes made in other databases are not replicated.

将架构更改复制到所有终结点后,还需要执行额外的步骤才能更新同步架构,以启动或停止新列同步。After the schema changes are replicated to all endpoints, you also need to take extra steps to update the sync schema to start or stop syncing the new columns.

添加新列Add new columns

  1. 进行架构更改。Make the schema change.

  2. 在完成创建触发器的步骤之前,请避免做出涉及到新列的任何数据更改。Avoid any data change where the new columns are involved until you've completed the step that creates the trigger.

  3. 等到架构更改已应用到所有终结点。Wait until the schema changes are applied to all endpoints.

  4. 刷新数据库架构,并将新列添加到同步架构。Refresh the database schema and add the new column to the sync schema.

  5. 在下次执行同步操作期间,新列中的数据会同步。Data in the new column is synced during next sync operation.

删除列Remove columns

  1. 从同步架构中删除列。Remove the columns from the sync schema. 数据同步会停止同步这些列中的数据。Data Sync stops syncing data in these columns.

  2. 进行架构更改。Make the schema change.

  3. 刷新数据库架构。Refresh the database schema.

更新数据类型Update data types

  1. 进行架构更改。Make the schema change.

  2. 等到架构更改已应用到所有终结点。Wait until the schema changes are applied to all endpoints.

  3. 刷新数据库架构。Refresh the database schema.

  4. 如果新旧数据类型不完全兼容 - 例如,从 int 更改为 bigint - 在完成创建触发器的步骤之前,同步可能会失败。If the new and old data types are not fully compatible - for example, if you change from int to bigint - sync may fail before the steps that create the triggers are completed. 重试后,同步会成功。Sync succeeds after a retry.

重命名列或表Rename columns or tables

重命名列或表会使数据同步停止工作。Renaming columns or tables makes Data Sync stop working. 创建新表或列、回填数据,然后删除旧表或列,而不要重命名。Create a new table or column, backfill the data, and then delete the old table or column instead of renaming.

其他类型的架构更改Other types of schema changes

对于其他类型的架构更改 - 例如,创建存储过程或删除索引 - 不需要更新同步架构。For other types of schema changes - for example, creating stored procedures or dropping an index- updating the sync schema is not required.

排查自动架构更改复制问题Troubleshoot automated schema change replication

在某些情况下,本文所述的复制逻辑会停止工作 - 例如,如果在 Azure SQL 数据库不支持的本地数据库中进行架构更改。The replication logic described in this article stops working in some situations- for example, if you made a schema change in an on-premises database which is not supported in Azure SQL Database. 在这种情况下,同步架构更改跟踪表会失败。In that case, syncing the schema change tracking table fails. 需要手动解决此问题:You need fix this problem manually:

  1. 禁用 DDL 触发器,并在问题得到解决之前避免任何进一步的架构更改。Disable the DDL trigger and avoid any further schema changes until the issue is fixed.

  2. 在发生问题的终结点数据库中,在无法进行架构更改的终结点上禁用 AFTER INSERT 触发器。In the endpoint database where the issue is happening, disable the AFTER INSERT trigger on the endpoint where the schema change can't be made. 此操作可以同步架构更改命令。This action allows the schema change command to be synced.

  3. 触发同步,以同步架构更改跟踪表。Trigger sync to sync the schema change tracking table.

  4. 在发生问题的终结点数据库中,查询架构更改历史记录表,以获取上次应用的架构更改命令的 ID。In the endpoint database where the issue is happening, query the schema change history table to get the ID of last applied schema change command.

  5. 查询架构更改跟踪表,以列出 ID 大于上一步骤中检索到的 ID 的所有命令。Query the schema change tracking table to list all the commands with an ID greater than the ID value you retrieved in the previous step.

    a.a. 忽略无法在终结点数据库中执行的命令。Ignore those commands that can't be executed in the endpoint database. 需要处理架构不一致情况。You need to deal with the schema inconsistency. 如果不一致性影响了应用程序,请还原原始架构更改。Revert the original schema changes if the inconsistency impacts your application.

    b.b. 手动应用这些命令。Manually apply those commands that should be applied.

  6. 更新架构更改历史记录表,并将上次应用的 ID 设置为正确值。Update the schema change history table and set the last applied ID to the correct value.

  7. 仔细检查架构是否是最新的。Double-check whether the schema is up-to-date.

  8. 重新启用在第二个步骤中禁用的 AFTER INSERT 触发器。Re-enable the AFTER INSERT trigger disabled in the second step.

  9. 重新启用在第一个步骤中禁用的 DDL 触发器。Re-enable the DDL trigger disabled in the first step.

如果想要清理架构更改跟踪表中的记录,请使用 DELETE 而不是 TRUNCATE。If you want to clean up the records in the schema change tracking table, use DELETE instead of TRUNCATE. 切勿使用 DBCC CHECKIDENT 在架构更改跟踪表中重新植入标识列。Never reseed the identity column in schema change tracking table by using DBCC CHECKIDENT. 如果需要重新植入,可以创建新的架构更改跟踪表,并更新 DDL 触发器中的表名称。You can create new schema change tracking tables and update the table name in the DDL trigger if reseeding is required.

其他注意事项Other Considerations

  • 配置中心和成员数据库的数据库用户需有足够的权限才能执行架构更改命令。Database users who configure the hub and member databases need to have enough permission to execute the schema change commands.

  • 可在 DDL 触发器中添加更多筛选器,以便只复制所选表或操作中的架构更改。You can add more filters in the DDL trigger to only replicate schema change in selected tables or operations.

  • 只能在创建 DDL 触发器的数据库中进行架构更改。You can only make schema changes in the database where the DDL trigger is created.

  • 如果在 SQL Server 数据库中进行更改,请确保 Azure SQL 数据库支持架构更改。If you are making a change in a SQL Server database, make sure the schema change is supported in Azure SQL Database.

  • 如果在其中进行架构更改的数据库不是创建了 DDL 触发器的数据库,则不会复制更改。If schema changes are made in databases other than the database where the DDL trigger is created, the changes are not replicated. 若要避免此问题,可以创建 DDL 触发器来阻止其他终结点上的更改。To avoid this issue, you can create DDL triggers to block changes on other endpoints.

  • 如果需要更改架构更改跟踪表的架构,请在进行更改之前禁用 DDL 触发器,然后手动将更改应用到所有终结点。If you need to change the schema of the schema change tracking table, disable the DDL trigger before you make the change, and then manually apply the change to all endpoints. 无法在同一个表上更新 AFTER INSERT 触发器中的架构。Updating the schema in an AFTER INSERT trigger on the same table does not work.

  • 不要使用 DBCC CHECKIDENT 重新植入标识列。Don't reseed the identity column by using DBCC CHECKIDENT.

  • 不要使用 TRUNCATE 清理架构更改跟踪表中的数据。Don't use TRUNCATE to clean up data in the schema change tracking table.

后续步骤Next steps

有关 SQL 数据同步的详细信息,请参阅:For more info about SQL Data Sync, see: