Automatisera replikeringen av schemaändringar i Azure SQL Data Sync

Gäller för:Azure SQL Database

MED SQL Data Sync kan användare synkronisera data mellan databaser i Azure SQL Database och SQL Server-instanser i en riktning eller i båda riktningarna. En av de aktuella begränsningarna för SQL Data Sync är bristande stöd för replikering av schemaändringar. Varje gång du ändrar tabellschemat måste du tillämpa ändringarna manuellt på alla slutpunkter, inklusive hubben och alla medlemmar, och sedan uppdatera synkroniseringsschemat.

Den här artikeln introducerar en lösning för att automatiskt replikera schemaändringar till alla SQL Data Sync-slutpunkter.

1. Den här lösningen använder en DDL-utlösare för att spåra schemaändringar.
2. Utlösaren infogar schemaändringskommandona i en spårningstabell.
3. Den här spårningstabellen synkroniseras till alla slutpunkter med datasynkroniseringstjänsten.
4. DML-utlösare efter infogning används för att tillämpa schemaändringarna på de andra slutpunkterna.

Den här artikeln använder ALTER TABLE som ett exempel på en schemaändring, men den här lösningen fungerar även för andra typer av schemaändringar.

Viktigt!

Vi rekommenderar att du läser den här artikeln noggrant, särskilt avsnitten om felsökning och andra överväganden, innan du börjar implementera automatisk schemaändringsreplikering i synkroniseringsmiljön. Vi rekommenderar också att du läser Synkronisera data i flera molndatabaser och lokala databaser med SQL Data Sync. Vissa databasåtgärder kan bryta lösningen som beskrivs i den här artikeln. Ytterligare domänkunskaper om SQL Server och Transact-SQL kan krävas för att felsöka dessa problem.

Konfigurera automatisk schemaändringsreplikering

Skapa en tabell för att spåra schemaändringar

Skapa en tabell för att spåra schemaändringar i alla databaser i synkroniseringsgruppen:

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

Den här tabellen har en identitetskolumn för att spåra ordningen på schemaändringar. Du kan lägga till fler fält för att logga mer information om det behövs.

Skapa en tabell för att spåra historiken för schemaändringar

På alla slutpunkter skapar du en tabell för att spåra ID:t för det senast tillämpade schemaändringskommandot.

CREATE TABLE SchemaChangeHistory (
LastAppliedId bigint PRIMARY KEY
)
GO

INSERT INTO SchemaChangeHistory VALUES (0)

Skapa en ALTER TABLE DDL-utlösare i databasen där schemaändringar görs

Skapa en DDL-utlösare för ALTER TABLE-åtgärder. Du behöver bara skapa den här utlösaren i databasen där schemaändringar görs. Undvik konflikter genom att endast tillåta schemaändringar i en databas i en synkroniseringsgrupp.

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

Utlösaren infogar en post i schemaändringsspårningstabellen för varje ALTER TABLE-kommando. Det här exemplet lägger till ett filter för att undvika replikering av schemaändringar som görs under schemat DataSync, eftersom dessa troligen görs av datasynkroniseringstjänsten. Lägg till fler filter om du bara vill replikera vissa typer av schemaändringar.

Du kan också lägga till fler utlösare för att replikera andra typer av schemaändringar. Du kan till exempel skapa CREATE_PROCEDURE, ALTER_PROCEDURE och DROP_PROCEDURE utlösare för att replikera ändringar i lagrade procedurer.

Skapa en utlösare på andra slutpunkter för att tillämpa schemaändringar under infogning

Den här utlösaren kör schemaändringskommandot när det synkroniseras till andra slutpunkter. Du måste skapa den här utlösaren på alla slutpunkter, förutom den där schemaändringar görs (d.s. i databasen där DDL-utlösaren AlterTableDDLTrigger skapas i föregående steg).

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

Den här utlösaren körs efter infogningen och kontrollerar om det aktuella kommandot ska köras härnäst. Kodlogik säkerställer att ingen schemaändringsinstruktion hoppas över och att alla ändringar tillämpas även om infogningen är ur funktion.

Synkronisera schemaändringsspårningstabellen till alla slutpunkter

Du kan synkronisera tabellen för schemaändringsspårning till alla slutpunkter med hjälp av den befintliga synkroniseringsgruppen eller en ny synkroniseringsgrupp. Kontrollera att ändringarna i spårningstabellen kan synkroniseras till alla slutpunkter, särskilt när du använder enriktad synkronisering.

Synkronisera inte tabellen för schemaändringshistorik eftersom tabellen har olika tillstånd på olika slutpunkter.

Tillämpa schemaändringarna i en synkroniseringsgrupp

Endast schemaändringar som görs i databasen där DDL-utlösaren skapas replikeras. Schemaändringar som görs i andra databaser replikeras inte.

När schemaändringarna har replikerats till alla slutpunkter måste du också vidta extra åtgärder för att uppdatera synkroniseringsschemat för att starta eller sluta synkronisera de nya kolumnerna.

Lägga till nya kolumner

1. Gör schemaändringen.

2. Undvik dataändringar där de nya kolumnerna ingår tills du har slutfört steget som skapar utlösaren.

3. Vänta tills schemaändringarna tillämpas på alla slutpunkter.

4. Uppdatera databasschemat och lägg till den nya kolumnen i synkroniseringsschemat.

5. Data i den nya kolumnen synkroniseras under nästa synkroniseringsåtgärd.

Ta bort kolumner

1. Ta bort kolumnerna från synkroniseringsschemat. Datasynkronisering slutar synkronisera data i dessa kolumner.

2. Gör schemaändringen.

3. Uppdatera databasschemat.

Uppdatera datatyper

1. Gör schemaändringen.

2. Vänta tills schemaändringarna tillämpas på alla slutpunkter.

3. Uppdatera databasschemat.

4. Om de nya och gamla datatyperna inte är helt kompatibla , till exempel om du ändrar från int till bigint – kan synkroniseringen misslyckas innan stegen som skapar utlösarna har slutförts. Synkroniseringen lyckas efter ett nytt försök.

Byt namn på kolumner eller tabeller

Om du byter namn på kolumner eller tabeller slutar Data Sync att fungera. Skapa en ny tabell eller kolumn, fyll på data igen och ta sedan bort den gamla tabellen eller kolumnen i stället för att byta namn.

Andra typer av schemaändringar

För andra typer av schemaändringar , till exempel att skapa lagrade procedurer eller ta bort ett index, krävs inte uppdatering av synkroniseringsschemat.

Felsöka automatisk schemaändringsreplikering

Replikeringslogik som beskrivs i den här artikeln slutar fungera i vissa situationer, till exempel om du har gjort en schemaändring i en lokal databas som inte stöds i Azure SQL Database. I så fall misslyckas synkroniseringen av schemaändringsspårningstabellen. Du behöver åtgärda problemet manuellt:

1. Inaktivera DDL-utlösaren och undvik ytterligare schemaändringar tills problemet har åtgärdats.

2. I slutpunktsdatabasen där problemet inträffar inaktiverar du AFTER INSERT-utlösaren på slutpunkten där schemaändringen inte kan göras. Med den här åtgärden kan schemaändringskommandot synkroniseras.

3. Utlös synkronisering för att synkronisera schemaändringsspårningstabellen.

4. I slutpunktsdatabasen där problemet inträffar frågar du tabellen schemaändringshistorik för att hämta ID:t för det senast tillämpade schemaändringskommandot.

5. Fråga schemaändringsspårningstabellen om du vill visa en lista över alla kommandon med ett ID som är större än det ID-värde som du hämtade i föregående steg.

   a. Ignorera de kommandon som inte kan köras i slutpunktsdatabasen. Du måste hantera schemainkonsekvensen. Återställ de ursprungliga schemaändringarna om inkonsekvensen påverkar ditt program.

   b. Använd de kommandon som ska tillämpas manuellt.

6. Uppdatera tabellen för schemaändringshistorik och ange det senast tillämpade ID:t till rätt värde.

7. Dubbelkolla om schemat är uppdaterat.

8. Återaktivera AFTER INSERT-utlösaren inaktiverad i det andra steget.

9. Återaktivera DDL-utlösaren inaktiverad i det första steget.

Om du vill rensa posterna i tabellen för schemaändringsspårning använder du DELETE i stället för TRUNCATE. Identitetskolumnen har aldrig återställts i schemaändringsspårningstabellen med hjälp av DBCC CHECKIDENT. Du kan skapa nya schemaändringsspårningstabeller och uppdatera tabellnamnet i DDL-utlösaren om en återställning krävs.

Andra överväganden

• Databasanvändare som konfigurerar hubb- och medlemsdatabaser måste ha tillräcklig behörighet för att köra schemaändringskommandona.

• Du kan lägga till fler filter i DDL-utlösaren för att endast replikera schemaändringar i valda tabeller eller åtgärder.

• Du kan bara göra schemaändringar i databasen där DDL-utlösaren skapas.

• Om du gör en ändring i en SQL Server-databas kontrollerar du att schemaändringen stöds i Azure SQL Database.

• Om schemaändringar görs i andra databaser än den databas där DDL-utlösaren skapas replikeras inte ändringarna. För att undvika det här problemet kan du skapa DDL-utlösare för att blockera ändringar på andra slutpunkter.

• Om du behöver ändra schemat för schemaändringsspårningstabellen inaktiverar du DDL-utlösaren innan du gör ändringen och tillämpar sedan ändringen manuellt på alla slutpunkter. Det går inte att uppdatera schemat i en AFTER INSERT-utlösare i samma tabell.

• Återställ inte identitetskolumnen med hjälp av DBCC CHECKIDENT.

• Använd inte TRUNCATE för att rensa data i schemaändringsspårningstabellen.

Nästa steg

Mer information om SQL Data Sync finns i:

• Översikt – Synkronisera data i flera moln och lokala databaser med Azure SQL Data Sync
• Konfigurera datasynkronisering
  • I portalen – Självstudie: Konfigurera SQL Data Sync för att synkronisera data mellan Azure SQL Database och SQL Server
  • Med PowerShell
    • Använda PowerShell för att synkronisera mellan flera databaser i Azure SQL Database
    • Använda PowerShell för att synkronisera mellan en databas i Azure SQL Database och en databas i en SQL Server-instans
• Datasynkroniseringsagent – Datasynkroniseringsagent för Azure SQL Data Sync
• Metodtips – Metodtips för Azure SQL Data Sync
• Övervaka – Övervaka SQL Data Sync med Azure Monitor-loggar
• Felsökning – Felsöka problem med Azure SQL Data Sync
• Uppdatera synkroniseringsschemat
  • Med PowerShell – Använd PowerShell för att uppdatera synkroniseringsschemat i en befintlig synkroniseringsgrupp