Udostępnij za pośrednictwem

Automatyzowanie replikacji zmian schematu w usłudze Azure SQL Data Sync

  • Artykuł

Dotyczy: Azure SQL Database

Usługa SQL Data Sync umożliwia użytkownikom synchronizowanie danych między bazami danych w usłudze Azure SQL Database i wystąpieniach programu SQL Server w jednym kierunku lub w obu kierunkach. Jednym z bieżących ograniczeń usługi SQL Data Sync jest brak obsługi replikacji zmian schematu. Za każdym razem, gdy zmieniasz schemat tabeli, musisz ręcznie zastosować zmiany we wszystkich punktach końcowych, w tym w centrum i wszystkich elementach członkowskich, a następnie zaktualizować schemat synchronizacji.

W tym artykule przedstawiono rozwiązanie do automatycznego replikowania zmian schematu do wszystkich punktów końcowych usługi SQL Data Sync.

  1. To rozwiązanie używa wyzwalacza DDL do śledzenia zmian schematu.
  2. Wyzwalacz wstawia polecenia zmiany schematu w tabeli śledzenia.
  3. Ta tabela śledzenia jest synchronizowana ze wszystkimi punktami końcowymi przy użyciu usługi Data Sync.
  4. Wyzwalacze DML po wstawieniu są używane do stosowania zmian schematu w innych punktach końcowych.

W tym artykule użyto metody ALTER TABLE jako przykładu zmiany schematu, ale to rozwiązanie działa również w przypadku innych typów zmian schematu.

Ważne

Zalecamy dokładne przeczytanie tego artykułu, zwłaszcza w sekcjach dotyczących rozwiązywania problemów i innych zagadnień przed rozpoczęciem implementowania automatycznej replikacji zmian schematu w środowisku synchronizacji. Zalecamy również odczytywanie danych synchronizacji między wieloma bazami danych w chmurze i lokalnych za pomocą usługi SQL Data Sync. Niektóre operacje bazy danych mogą spowodować przerwanie rozwiązania opisanego w tym artykule. Aby rozwiązać te problemy, może być wymagana dodatkowa wiedza na temat domeny programu SQL Server i języka Transact-SQL.

Automatyzowanie replikacji zmian schematu

Konfigurowanie automatycznej replikacji zmiany schematu

Tworzenie tabeli w celu śledzenia zmian schematu

Utwórz tabelę, aby śledzić zmiany schematu we wszystkich bazach danych w grupie synchronizacji:

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

Ta tabela zawiera kolumnę tożsamości do śledzenia kolejności zmian schematu. W razie potrzeby możesz dodać więcej pól, aby zarejestrować więcej informacji.

Tworzenie tabeli w celu śledzenia historii zmian schematu

We wszystkich punktach końcowych utwórz tabelę, aby śledzić identyfikator ostatnio zastosowanego polecenia zmiany schematu.

CREATE TABLE SchemaChangeHistory (
LastAppliedId bigint PRIMARY KEY
)
GO

INSERT INTO SchemaChangeHistory VALUES (0)

Tworzenie wyzwalacza DDL ALTER TABLE w bazie danych, w której są wprowadzane zmiany schematu

Utwórz wyzwalacz DDL dla operacji ALTER TABLE. Ten wyzwalacz należy utworzyć tylko w bazie danych, w której są wprowadzane zmiany schematu. Aby uniknąć konfliktów, zezwalaj tylko na zmiany schematu w jednej bazie danych w grupie synchronizacji.

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

Wyzwalacz wstawia rekord w tabeli śledzenia zmian schematu dla każdego polecenia ALTER TABLE. W tym przykładzie dodano filtr, aby uniknąć replikowania zmian schematu wprowadzonych w schemacie DataSync, ponieważ są one najprawdopodobniej wprowadzane przez usługę Data Sync. Dodaj więcej filtrów, jeśli chcesz replikować tylko niektóre typy zmian schematu.

Możesz również dodać więcej wyzwalaczy, aby replikować inne typy zmian schematu. Na przykład utwórz wyzwalacze CREATE_PROCEDURE, ALTER_PROCEDURE i DROP_PROCEDURE w celu replikowania zmian w procedurach składowanych.

Tworzenie wyzwalacza w innych punktach końcowych w celu zastosowania zmian schematu podczas wstawiania

Ten wyzwalacz wykonuje polecenie zmiany schematu po zsynchronizowaniu z innymi punktami końcowymi. Należy utworzyć ten wyzwalacz we wszystkich punktach końcowych, z wyjątkiem tego, w którym wprowadzono zmiany schematu (czyli w bazie danych, w której jest tworzony wyzwalacz AlterTableDDLTrigger DDL w poprzednim kroku).

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

Ten wyzwalacz jest uruchamiany po wstawieniu i sprawdza, czy bieżące polecenie powinno zostać uruchomione dalej. Logika kodu gwarantuje, że nie pominięto instrukcji zmiany schematu, a wszystkie zmiany są stosowane nawet wtedy, gdy wstawienie jest poza kolejnością.

Synchronizowanie tabeli śledzenia zmian schematu ze wszystkimi punktami końcowymi

Tabelę śledzenia zmian schematu można zsynchronizować ze wszystkimi punktami końcowymi przy użyciu istniejącej grupy synchronizacji lub nowej grupy synchronizacji. Upewnij się, że zmiany w tabeli śledzenia można zsynchronizować ze wszystkimi punktami końcowymi, szczególnie w przypadku korzystania z synchronizacji jednokierunkowej.

Nie synchronizuj tabeli historii zmian schematu, ponieważ ta tabela zachowuje inny stan w różnych punktach końcowych.

Stosowanie zmian schematu w grupie synchronizacji

Tylko zmiany schematu wprowadzone w bazie danych, w której jest tworzony wyzwalacz DDL, są replikowane. Zmiany schematu wprowadzone w innych bazach danych nie są replikowane.

Po zreplikowaniu zmian schematu do wszystkich punktów końcowych należy również wykonać dodatkowe kroki, aby zaktualizować schemat synchronizacji, aby rozpocząć lub przestać synchronizować nowe kolumny.

Dodawanie nowych kolumn

  1. Wprowadź zmianę schematu.

  2. Unikaj wszelkich zmian danych, w których są zaangażowane nowe kolumny do momentu ukończenia kroku, który tworzy wyzwalacz.

  3. Poczekaj, aż zmiany schematu zostaną zastosowane do wszystkich punktów końcowych.

  4. Odśwież schemat bazy danych i dodaj nową kolumnę do schematu synchronizacji.

  5. Dane w nowej kolumnie są synchronizowane podczas następnej operacji synchronizacji.

Usuwanie kolumn

  1. Usuń kolumny ze schematu synchronizacji. Usługa Data Sync zatrzymuje synchronizowanie danych w tych kolumnach.

  2. Wprowadź zmianę schematu.

  3. Odśwież schemat bazy danych.

Aktualizowanie typów danych

  1. Wprowadź zmianę schematu.

  2. Poczekaj, aż zmiany schematu zostaną zastosowane do wszystkich punktów końcowych.

  3. Odśwież schemat bazy danych.

  4. Jeśli nowe i stare typy danych nie są w pełni zgodne — na przykład jeśli zmienisz się z int na bigint , synchronizacja może zakończyć się niepowodzeniem przed wykonaniem kroków, które tworzą wyzwalacze. Synchronizacja zakończy się pomyślnie po ponowieniu próby.

Zmienianie nazw kolumn lub tabel

Zmiana nazw kolumn lub tabel sprawia, że usługa Data Sync przestanie działać. Utwórz nową tabelę lub kolumnę, uzupełnij dane, a następnie usuń starą tabelę lub kolumnę zamiast zmienić nazwę.

Inne typy zmian schematu

W przypadku innych typów zmian schematu — na przykład tworzenie procedur składowanych lub usuwanie indeksu — aktualizowanie schematu synchronizacji nie jest wymagane.

Rozwiązywanie problemów z automatyczną replikacją zmian schematu

Logika replikacji opisana w tym artykule przestaje działać w niektórych sytuacjach— na przykład w przypadku wprowadzenia zmiany schematu w lokalnej bazie danych, która nie jest obsługiwana w usłudze Azure SQL Database. W takim przypadku synchronizacja tabeli śledzenia zmian schematu kończy się niepowodzeniem. Ten problem należy rozwiązać ręcznie:

  1. Wyłącz wyzwalacz DDL i unikaj dalszych zmian schematu do momentu rozwiązania problemu.

  2. W bazie danych punktu końcowego, w której występuje problem, wyłącz wyzwalacz AFTER INSERT w punkcie końcowym, w którym nie można wprowadzić zmiany schematu. Ta akcja umożliwia synchronizowanie polecenia zmiany schematu.

  3. Wyzwalanie synchronizacji w celu zsynchronizowania tabeli śledzenia zmian schematu.

  4. W bazie danych punktu końcowego, w której występuje problem, wykonaj zapytanie względem tabeli historii zmian schematu, aby uzyskać identyfikator ostatniego zastosowanego polecenia zmiany schematu.

  5. Wykonaj zapytanie względem tabeli śledzenia zmian schematu, aby wyświetlić listę wszystkich poleceń o identyfikatorze większym niż wartość identyfikatora pobraną w poprzednim kroku.

    a. Zignoruj te polecenia, których nie można wykonać w bazie danych punktu końcowego. Musisz radzić sobie z niespójnością schematu. Przywróć oryginalny schemat, jeśli niespójność ma wpływ na aplikację.

    b. Ręcznie zastosuj te polecenia, które należy zastosować.

  6. Zaktualizuj tabelę historii zmian schematu i ustaw ostatni zastosowany identyfikator na poprawną wartość.

  7. Sprawdź dokładnie, czy schemat jest aktualny.

  8. Włącz ponownie wyzwalacz AFTER INSERT wyłączony w drugim kroku.

  9. Ponownie włącz wyzwalacz DDL wyłączony w pierwszym kroku.

Jeśli chcesz wyczyścić rekordy w tabeli śledzenia zmian schematu, użyj polecenia DELETE zamiast TRUNCATE. Nigdy nie zmieniono kolumny tożsamości w tabeli śledzenia zmian schematu przy użyciu funkcji DBCC CHECKIDENT. W razie potrzeby można utworzyć nowe tabele śledzenia zmian schematu i zaktualizować nazwę tabeli w wyzwalaczu DDL.

Inne zagadnienia

  • Użytkownicy bazy danych, którzy konfigurują bazy danych centrum i członkowskie, muszą mieć wystarczające uprawnienia do wykonywania poleceń zmiany schematu.

  • Możesz dodać więcej filtrów w wyzwalaczu DDL, aby replikować tylko zmiany schematu w wybranych tabelach lub operacjach.

  • Zmiany schematu można wprowadzać tylko w bazie danych, w której jest tworzony wyzwalacz DDL.

  • Jeśli wprowadzasz zmiany w bazie danych programu SQL Server, upewnij się, że zmiana schematu jest obsługiwana w usłudze Azure SQL Database.

  • Jeśli zmiany schematu są wprowadzane w bazach danych innych niż baza danych, w której jest tworzony wyzwalacz DDL, zmiany nie są replikowane. Aby uniknąć tego problemu, możesz utworzyć wyzwalacze DDL, aby blokować zmiany w innych punktach końcowych.

  • Jeśli musisz zmienić schemat tabeli śledzenia zmian schematu, wyłącz wyzwalacz DDL przed wprowadzeniem zmiany, a następnie ręcznie zastosuj zmianę do wszystkich punktów końcowych. Aktualizowanie schematu w wyzwalaczu AFTER INSERT w tej samej tabeli nie działa.

  • Nie zmieniaj nazwy kolumny tożsamości przy użyciu polecenia DBCC CHECKIDENT.

  • Nie używaj narzędzia TRUNCATE do czyszczenia danych w tabeli śledzenia zmian schematu.

Następne kroki

Aby uzyskać więcej informacji na temat usługi SQL Data Sync, zobacz: