1/13/2024 0 Comments Supersync sync changes![]() Schema changes made in other databases are not replicated.Īfter 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. Only schema changes made in the database where the DDL trigger is created are replicated. Make sure the changes in the tracking table can be synced to all endpoints, especially when you're using one-direction sync.ĭon't sync the schema change history table, since that table maintains different state on different endpoints. You can sync the schema change tracking table to all endpoints using the existing sync group or a new sync group. Sync the schema change tracking table to all endpoints ![]() The code logic ensures that no schema change statement is skipped, and all changes are applied even if the insertion is out of order. IF exists (SELECT id FROM SchemaChanges WHERE ID = SqlStmt FROM SchemaChanges WHERE ID = sp_executesql SchemaChangeHistory SET LastAppliedId = trigger runs after the insertion and checks whether the current command should run next. SELECT TOP 1 = id, = SqlStmt FROM SchemaChanges WHERE id > ORDER BY idĮXEC sp_executesql SchemaChangeHistory SET LastAppliedId = (1 = 1) 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). This trigger executes the schema change command when it is synced to other endpoints. Create a trigger on other endpoints to apply schema changes during insertion For example, create CREATE_PROCEDURE, ALTER_PROCEDURE and DROP_PROCEDURE triggers to replicate changes to stored procedures. You can also add more triggers to replicate other types of schema changes. Add more filters if you only want to replicate certain types of schema changes. 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. The trigger inserts a record in the schema change tracking table for each ALTER TABLE command. VALUES (EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)', 'nvarchar(max)'), 'From DDL trigger') INSERT INTO SchemaChanges (SqlStmt, Description) IF NOT (EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)', 'nvarchar(512)') like 'DataSync') You can add your own logic to filter ALTER TABLE commands instead of replicating all of them. To avoid conflicts, only allow schema changes in one database in a sync group. You only need to create this trigger in the database where schema changes are made. INSERT INTO SchemaChangeHistory VALUES (0)Ĭreate an ALTER TABLE DDL trigger in the database where schema changes are madeĬreate a DDL trigger for ALTER TABLE operations. On all endpoints, create a table to track the ID of the most recently applied schema change command. Create a table to track the history of schema changes You can add more fields to log more information if needed. This table has an identity column to track the order of schema changes. Set up automated schema change replication Create a table to track schema changesĬreate a table to track schema changes in all databases in the sync group: CREATE TABLE SchemaChanges ( Additional domain knowledge of SQL Server and Transact-SQL may be required to troubleshoot those issues. Some database operations may break the solution described in this article. ![]() We also recommend that you read Sync data across multiple cloud and on-premises databases with SQL Data Sync. ![]() 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. This article uses ALTER TABLE as an example of a schema change, but this solution also works for other types of schema changes.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |