Automatisieren der Replikation von Schemaänderungen in der Azure SQL-DatensynchronisierungAutomate the replication of schema changes in Azure SQL Data Sync

Mit der SQL-Datensynchronisierung können Benutzer Daten zwischen Azure SQL-Datenbank-Instanzen und der lokalen SQL Server-Instanz in eine Richtung oder in beide Richtungen synchronisieren.SQL Data Sync lets users synchronize data between Azure SQL databases and on-premises SQL Server in one direction or in both directions. Eine der aktuellen Einschränkungen der SQL-Datensynchronisierung ist die fehlende Unterstützung für die Replikation von Schemaänderungen.One of the current limitations of SQL Data Sync is a lack of support for the replication of schema changes. Jedes Mal, wenn Sie das Tabellenschema ändern, müssen Sie die Änderungen manuell auf alle Endpunkte, einschließlich des Hubs und aller Mitglieder, anwenden und dann das Synchronisierungsschema aktualisieren.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.

Dieser Artikel stellt eine Lösung zur automatisierten Replikation von Schemaänderungen auf alle Endpunkte der SQL-Datensynchronisierung vor.This article introduces a solution to automatically replicate schema changes to all SQL Data Sync endpoints.

  1. Diese Lösung nutzt einen DDL-Trigger, um Schemaänderungen nachzuverfolgen.This solution uses a DDL trigger to track schema changes.
  2. Durch den Trigger werden die Befehle für die Schemaänderung in eine Nachverfolgungstabelle eingefügt.The trigger inserts the schema change commands in a tracking table.
  3. Diese Nachverfolgungstabelle wird über den Datensynchronisierungsdienst mit allen Endpunkten synchronisiert.This tracking table is synced to all endpoints using the Data Sync service.
  4. DML-Trigger nach dem Einfügen werden verwendet, um die Schemaänderungen auf die anderen Endpunkte anzuwenden.DML triggers after insertion are used to apply the schema changes on the other endpoints.

In diesem Artikel wird ALTER TABLE als Beispiel für eine Schemaänderung verwendet, aber diese Lösung funktioniert auch für andere Arten von Schemaänderungen.This article uses ALTER TABLE as an example of a schema change, but this solution also works for other types of schema changes.

Wichtig

Es wird empfohlen, diesen Artikel sorgfältig zu lesen, insbesondere die Abschnitte über Problembehandlung und Weitere Überlegungen, bevor Sie mit der Implementierung der automatisierten Replikation von Schemaänderungen in Ihrer Synchronisierungsumgebung beginnen.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. Darüber hinaus ist es empfehlenswert, den Artikel Synchronisieren von Daten über mehrere Cloud- und lokale Datenbanken mit SQL-Datensynchronisierung zu lesen. Es besteht die Möglichkeit, dass einige Datenbankvorgänge die in diesem Artikel beschriebene Lösung unterbrechen.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. Daher sind ggf. zusätzliche Domänenkenntnisse von SQL Server und Transact-SQL erforderlich, um derartige Probleme zu beheben.Additional domain knowledge of SQL Server and Transact-SQL may be required to troubleshoot those issues.

Automatisieren der Replikation von Schemaänderungen

Einrichten der automatisierten Replikation von SchemaänderungenSet up automated schema change replication

Erstellen einer Tabelle zum Nachverfolgen von SchemaänderungenCreate a table to track schema changes

Erstellen Sie eine Tabelle zum Nachverfolgen von Schemaänderungen in allen Datenbanken der Synchronisierungsgruppe: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)
)

Diese Tabelle weist eine Identitätsspalte auf, damit Sie die Reihenfolge der Schemaänderungen nachvollziehen können.This table has an identity column to track the order of schema changes. Fügen Sie bei Bedarf zusätzliche Felder hinzu, um weitere Informationen zu protokollieren.You can add more fields to log more information if needed.

Erstellen einer Tabelle zum Nachverfolgen des Verlaufs der SchemaänderungenCreate a table to track the history of schema changes

Erstellen Sie für alle Endpunkte eine Tabelle, um die ID des zuletzt verwendeten Schemaänderungsbefehls zu verfolgen.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)

Erstellen eines ALTER TABLE-DDL-Triggers in der Datenbank, in der Schemaänderungen vorgenommen werdenCreate an ALTER TABLE DDL trigger in the database where schema changes are made

Erstellen Sie einen DDL-Trigger für ALTER TABLE-Vorgänge.Create a DDL trigger for ALTER TABLE operations. Sie müssen diesen Trigger nur in der Datenbank erstellen, in der Schemaänderungen vorgenommen werden.You only need to create this trigger in the database where schema changes are made. Um Konflikte zu vermeiden, lassen Sie nur Schemaänderungen in einer Datenbank einer Synchronisierungsgruppe zu.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')

Für jeden ALTER TABLE-Befehl fügt der Trigger einen Datensatz in die Tabelle der Schemaänderungsnachverfolgung ein.The trigger inserts a record in the schema change tracking table for each ALTER TABLE command. In diesem Beispiel wird ein Filter hinzugefügt, um zu vermeiden, dass Schemaänderungen unter dem Schema DataSync repliziert werden, da diese höchstwahrscheinlich vom Datensynchronisierungsdienst vorgenommen werden.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. Fügen Sie weitere Filter hinzu, wenn Sie nur bestimmte Typen von Schemaänderungen replizieren möchten.Add more filters if you only want to replicate certain types of schema changes.

Außerdem haben Sie auch die Möglichkeit, weitere Trigger hinzuzufügen, um andere Typen von Schemaänderungen zu replizieren.You can also add more triggers to replicate other types of schema changes. Erstellen Sie beispielsweise CREATE_PROCEDURE-, ALTER_PROCEDURE- und DROP_PROCEDURE-Trigger, um Änderungen auf gespeicherte Prozeduren zu replizieren.For example, create CREATE_PROCEDURE, ALTER_PROCEDURE and DROP_PROCEDURE triggers to replicate changes to stored procedures.

Erstellen eines Triggers an anderen Endpunkten zum Anwenden von Schemaänderungen während des EinfügensCreate a trigger on other endpoints to apply schema changes during insertion

Dieser Trigger führt den Befehl für die Schemaänderung aus, wenn er mit anderen Endpunkten synchronisiert wird.This trigger executes the schema change command when it is synced to other endpoints. Sie müssen diesen Trigger an allen Endpunkten erstellen, mit Ausnahme desjenigen, an dem Schemaänderungen vorgenommen werden (d.h. in der Datenbank, in der im vorherigen Schritt der DDL-Trigger AlterTableDDLTrigger erstellt wurde).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

Dieser Trigger wird nach dem Einfügen ausgelöst und prüft, ob der aktuelle Befehl als Nächstes ausgeführt werden soll.This trigger runs after the insertion and checks whether the current command should run next. Durch die Codelogik wird sichergestellt, dass keine Schemaänderungsanweisung übersprungen wird, und alle Änderungen werden übernommen, auch wenn die Reihenfolge beim Einfügen falsch ist.The code logic ensures that no schema change statement is skipped, and all changes are applied even if the insertion is out of order.

Synchronisieren der Verfolgungstabelle für die Schemaänderungen an allen EndpunktenSync the schema change tracking table to all endpoints

Sie können die Verfolgungstabelle für die Schemaänderungen an allen Endpunkten synchronisieren, indem Sie die vorhandene oder eine neue Synchronisierungsgruppe verwenden.You can sync the schema change tracking table to all endpoints using the existing sync group or a new sync group. Stellen Sie sicher, dass die Änderungen in der Nachverfolgungstabelle mit allen Endpunkten synchronisiert werden können, insbesondere wenn Sie die Synchronisierung in eine Richtung in verwenden.Make sure the changes in the tracking table can be synced to all endpoints, especially when you're using one-direction sync.

Synchronisieren Sie die Verlaufstabelle der Schemaänderungen nicht, da diese Tabelle an verschiedenen Endpunkten unterschiedliche Zustände beibehält.Don't sync the schema change history table, since that table maintains different state on different endpoints.

Übernehmen der Schemaänderungen in einer SynchronisierungsgruppeApply the schema changes in a sync group

Es werden nur Schemaänderungen repliziert, die in der Datenbank vorgenommen wurden, in der der DDL-Trigger erstellt wird.Only schema changes made in the database where the DDL trigger is created are replicated. In anderen Datenbanken vorgenommene Schemaänderungen werden nicht repliziert.Schema changes made in other databases are not replicated.

Nachdem die Schemaänderungen an allen Endpunkten repliziert wurden, müssen Sie zusätzliche Schritte unternehmen, um das Synchronisierungsschema zu aktualisieren, sodass die Synchronisierung der neuen Spalten gestartet bzw. beendet wird.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.

Hinzufügen von SpaltenAdd new columns

  1. Nehmen Sie die Schemaänderung vor.Make the schema change.

  2. Vermeiden Sie jede Datenänderung an den neuen Spalten, bis Sie den Schritt zum Erstellen des Triggers abgeschlossen haben.Avoid any data change where the new columns are involved until you've completed the step that creates the trigger.

  3. Warten Sie, bis die Schemaänderungen für alle Endpunkte übernommen wurden.Wait until the schema changes are applied to all endpoints.

  4. Aktualisieren Sie das Datenbankschema, und fügen Sie die neue Spalte dem Synchronisierungsschema hinzu.Refresh the database schema and add the new column to the sync schema.

  5. Die Daten in der neuen Spalte werden beim nächsten Synchronisierungsvorgang synchronisiert.Data in the new column is synced during next sync operation.

Entfernen von SpaltenRemove columns

  1. Entfernen Sie die Spalten aus dem Synchronisierungsschema.Remove the columns from the sync schema. Die Datensynchronisierung hält die Synchronisierung der Daten in diesen Spalten an.Data Sync stops syncing data in these columns.

  2. Nehmen Sie die Schemaänderung vor.Make the schema change.

  3. Aktualisieren Sie das Datenbankschema.Refresh the database schema.

Aktualisieren von DatentypenUpdate data types

  1. Nehmen Sie die Schemaänderung vor.Make the schema change.

  2. Warten Sie, bis die Schemaänderungen für alle Endpunkte übernommen wurden.Wait until the schema changes are applied to all endpoints.

  3. Aktualisieren Sie das Datenbankschema.Refresh the database schema.

  4. Wenn der neue und der alte Datentyp nicht vollständig kompatibel sind, z.B. wenn Sie von int zu bigint wechseln, tritt möglicherweise ein Synchronisierungsfehler auf, bevor die Schritte zum Erstellen des Triggers abgeschlossen sind.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. Die Synchronisierung ist bei einem erneuten Versuch erfolgreich.Sync succeeds after a retry.

Umbenennen von Spalten oder TabellenRename columns or tables

Durch das Umbenennen von Spalten oder Tabellen wird die Datensynchronisierung angehalten.Renaming columns or tables makes Data Sync stop working. Erstellen Sie eine neue Tabelle oder Spalte, füllen Sie die Daten ein, und löschen Sie dann die alte Tabelle oder Spalte, anstatt sie umzubenennen.Create a new table or column, backfill the data, and then delete the old table or column instead of renaming.

Andere Typen von SchemaänderungenOther types of schema changes

Für andere Typen von Schemaänderungen – z.B. das Erstellen von gespeicherten Prozeduren oder das Löschen eines Indexes – ist das Aktualisieren des Synchronisierungsschemas nicht erforderlich.For other types of schema changes - for example, creating stored procedures or dropping an index- updating the sync schema is not required.

Problembehandlung bei der automatisierten Replikation von SchemaänderungenTroubleshoot automated schema change replication

Die in diesem Artikel beschriebene Replikationslogik funktioniert in einigen Situationen nicht mehr. Dies ist z.B. der Fall, wenn Sie eine Schemaänderung in einer lokalen Datenbank vorgenommen haben, die in der Azure SQL-Datenbank-Instanz nicht unterstützt wird.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. Dann schlägt die Synchronisierung der Tabelle der Schemaänderungsnachverfolgung fehl.In that case, syncing the schema change tracking table fails. Dieses Problem muss manuell behoben werden:You need fix this problem manually:

  1. Deaktivieren Sie den DDL-Trigger, und vermeiden Sie weitere Schemaänderungen, bis das Problem behoben ist.Disable the DDL trigger and avoid any further schema changes until the issue is fixed.

  2. Deaktivieren Sie in der Endpunktdatenbank, in der das Problem auftritt, den AFTER INSERT-Trigger an dem Endpunkt, an dem die Schemaänderung nicht vorgenommen werden kann.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. Durch diese Aktion kann der Schemaänderungsbefehl synchronisiert werden.This action allows the schema change command to be synced.

  3. Lösen Sie den Synchronisierungsvorgang aus, um die Tabelle der Schemaänderungsnachverfolgung zu synchronisieren.Trigger sync to sync the schema change tracking table.

  4. In der Endpunktdatenbank, in der das Problem auftritt, fragen Sie die Verlaufstabelle für die Schemaänderungen ab, um die ID des zuletzt angewandten Schemaänderungsbefehls zu erhalten.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. Fragen Sie die Tabelle der Schemaänderungsnachverfolgung ab, um alle Befehle aufzulisten, deren ID größer ist als der ID-Wert, den Sie im vorherigen Schritt abgerufen haben.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. Ignorieren Sie die Befehle, die in der Endpunktdatenbank nicht ausgeführt werden können.Ignore those commands that can't be executed in the endpoint database. Es ist erforderlich, dass Sie sich mit der Schemainkonsistenz befassen.You need to deal with the schema inconsistency. Nehmen Sie die Änderungen am ursprünglichen Schema zurück, wenn sich die Inkonsistenz auf Ihre Anwendung auswirkt.Revert the original schema changes if the inconsistency impacts your application.

    b.b. Wenden Sie die Befehle, die angewendet werden sollen, manuell an.Manually apply those commands that should be applied.

  6. Aktualisieren Sie die Verlaufstabelle für die Schemaänderungen, und setzen Sie die zuletzt verwendete ID auf den richtigen Wert.Update the schema change history table and set the last applied ID to the correct value.

  7. Überprüfen Sie nochmals, ob das Schema aktuell ist.Double-check whether the schema is up-to-date.

  8. Aktivieren Sie erneut den im zweiten Schritt deaktivierten AFTER INSERT-Trigger.Re-enable the AFTER INSERT trigger disabled in the second step.

  9. Aktivieren Sie erneut den im ersten Schritt deaktivierten DDL-Trigger.Re-enable the DDL trigger disabled in the first step.

Wenn Sie die Datensätze in der Tabelle der Schemaänderungsnachverfolgung bereinigen möchten, verwenden Sie DELETE anstelle von TRUNCATE.If you want to clean up the records in the schema change tracking table, use DELETE instead of TRUNCATE. Führen Sie niemals ein erneutes Seeding für die Identitätsspalte in der Tabelle der Schemaänderungsnachverfolgung mithilfe von DBCC CHECKIDENT aus.Never reseed the identity column in schema change tracking table by using DBCC CHECKIDENT. Sie können neue Tabellen der Schemaänderungsnachverfolgung erstellen und den Tabellennamen im DDL-Trigger aktualisieren, wenn ein erneutes Seeding erforderlich ist.You can create new schema change tracking tables and update the table name in the DDL trigger if reseeding is required.

Weitere ÜberlegungenOther Considerations

  • Datenbankbenutzer, die die Hub- und Mitgliederdatenbanken konfigurieren, müssen über ausreichende Berechtigungen verfügen, um die Schemaänderungsbefehle auszuführen.Database users who configure the hub and member databases need to have enough permission to execute the schema change commands.

  • Sie können weitere Filter im DDL-Trigger hinzufügen, um Schemaänderungen nur in ausgewählten Tabellen oder Vorgängen zu replizieren.You can add more filters in the DDL trigger to only replicate schema change in selected tables or operations.

  • Sie können Schemaänderungen nur in der Datenbank vornehmen, in der der DDL-Trigger erste wird.You can only make schema changes in the database where the DDL trigger is created.

  • Wenn Sie eine Änderung in einer lokalen SQL Server-Datenbank-Instanz vornehmen, stellen Sie sicher, dass die Schemaänderung in der Azure SQL-Datenbank-Instanz unterstützt wird.If you are making a change in an on-premises SQL Server database, make sure the schema change is supported in Azure SQL Database.

  • Wenn Schemaänderungen in anderen Datenbanken als der Datenbank vorgenommen werden, in der der DDL-Trigger erstellt wird, werden die Änderungen nicht repliziert.If schema changes are made in databases other than the database where the DDL trigger is created, the changes are not replicated. Zur Vermeidung dieses Problems können Sie DDL-Trigger erstellen, um Änderungen an anderen Endpunkten zu blockieren.To avoid this issue, you can create DDL triggers to block changes on other endpoints.

  • Wenn Sie das Schema der Tabelle der Schemaänderungsnachverfolgung ändern müssen, deaktivieren Sie den DDL-Trigger, bevor Sie die Änderung vornehmen, und wenden Sie die Änderung dann manuell auf alle Endpunkte an.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. Das Aktualisieren des Schemas in einem AFTER INSERT-Trigger für die gleiche Tabelle funktioniert nicht.Updating the schema in an AFTER INSERT trigger on the same table does not work.

  • Führen Sie kein erneutes Seeding der Identitätsspalte mit DBCC CHECKIDENT durch.Don't reseed the identity column by using DBCC CHECKIDENT.

  • Verwenden Sie TRUNCATE nicht, um Daten in der Tabelle der Schemaänderungsnachverfolgung zu bereinigen.Don't use TRUNCATE to clean up data in the schema change tracking table.

Nächste SchritteNext steps

Weitere Informationen zur SQL-Datensynchronisierung finden Sie unter:For more info about SQL Data Sync, see: