Sdílet prostřednictvím

Automatizace replikace změn schématu v Synchronizaci dat AZURE SQL

  • Článek

Platí pro:Azure SQL Database

Synchronizace dat SQL umožňuje uživatelům synchronizovat data mezi databázemi ve službě Azure SQL Database a instancemi SQL Serveru jedním směrem nebo v obou směrech. Jedním z aktuálních omezení synchronizace dat SQL je nedostatek podpory replikace změn schématu. Pokaždé, když změníte schéma tabulky, musíte změny použít ručně ve všech koncových bodech, včetně centra a všech členů, a pak aktualizovat schéma synchronizace.

Tento článek představuje řešení pro automatickou replikaci změn schématu do všech koncových bodů synchronizace dat SQL.

  1. Toto řešení používá trigger DDL ke sledování změn schématu.
  2. Trigger vloží příkazy změn schématu do tabulky sledování.
  3. Tato sledovací tabulka se synchronizuje se všemi koncovými body pomocí služby Synchronizace dat.
  4. Triggery DML po vložení slouží k použití změn schématu na ostatních koncových bodech.

V tomto článku se jako příklad změny schématu používá ALTER TABLE, ale toto řešení také funguje pro jiné typy změn schématu.

Důležité

Doporučujeme, abyste si tento článek přečetli pečlivě, zejména v částech o řešení potíží a dalších aspektech, než začnete implementovat automatizovanou replikaci změn schématu ve vašem synchronizačním prostředí. Doporučujeme také číst data synchronizace napříč několika cloudovými a místními databázemi pomocí synchronizace dat SQL. Některé databázové operace můžou narušit řešení popsané v tomto článku. K řešení těchto problémů může být potřeba další znalosti domény SQL Serveru a Jazyka Transact-SQL.

Automating the replication of schema changes

Nastavení automatizované replikace změn schématu

Vytvoření tabulky pro sledování změn schématu

Vytvořte tabulku pro sledování změn schématu ve všech databázích ve skupině synchronizace:

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

Tato tabulka obsahuje sloupec identity ke sledování pořadí změn schématu. V případě potřeby můžete přidat další pole pro protokolování dalších informací.

Vytvoření tabulky pro sledování historie změn schématu

Na všech koncových bodech vytvořte tabulku pro sledování ID naposledy použitého příkazu změny schématu.

CREATE TABLE SchemaChangeHistory (
LastAppliedId bigint PRIMARY KEY
)
GO

INSERT INTO SchemaChangeHistory VALUES (0)

Vytvoření triggeru ALTER TABLE DDL v databázi, ve které se provádějí změny schématu

Vytvořte trigger DDL pro operace ALTER TABLE. Tento trigger je potřeba vytvořit pouze v databázi, kde se provádějí změny schématu. Aby nedocházelo ke konfliktům, povolte změny schématu pouze v jedné databázi ve skupině synchronizace.

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

Trigger vloží záznam do tabulky sledování změn schématu pro každý příkaz ALTER TABLE. Tento příklad přidá filtr, aby se zabránilo replikaci změn schématu provedených ve schématu DataSync, protože je s největší pravděpodobností provádí služba Synchronizace dat. Pokud chcete replikovat jenom určité typy změn schématu, přidejte další filtry.

Můžete také přidat další triggery pro replikaci dalších typů změn schématu. Můžete například vytvořit CREATE_PROCEDURE, ALTER_PROCEDURE a DROP_PROCEDURE triggery pro replikaci změn do uložených procedur.

Vytvoření triggeru na jiných koncových bodech pro použití změn schématu během vkládání

Tato aktivační událost spustí příkaz změny schématu při synchronizaci s jinými koncovými body. Tento trigger je potřeba vytvořit na všech koncových bodech, s výjimkou toho, kde se provádějí změny schématu (to znamená v databázi, kde se trigger AlterTableDDLTrigger DDL vytvoří v předchozím 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

Tato aktivační událost se spustí po vložení a zkontroluje, jestli má aktuální příkaz běžet dál. Logika kódu zajišťuje, že se nepřeskočil žádný příkaz změny schématu a všechny změny se použijí i v případě, že je vložení mimo pořadí.

Synchronizace tabulky sledování změn schématu se všemi koncovými body

Tabulku sledování změn schématu můžete synchronizovat se všemi koncovými body pomocí existující skupiny synchronizace nebo nové skupiny synchronizace. Ujistěte se, že změny v tabulce sledování je možné synchronizovat se všemi koncovými body, zejména pokud používáte jednosměrnou synchronizaci.

Tabulku historie změn schématu nesynchronizujete, protože tato tabulka udržuje jiný stav v různých koncových bodech.

Použití změn schématu ve skupině synchronizace

Replikují se pouze změny schématu provedené v databázi, ve které je vytvořena aktivační událost DDL. Změny schématu provedené v jiných databázích se nereplikují.

Po replikaci změn schématu do všech koncových bodů je také potřeba provést další kroky k aktualizaci schématu synchronizace, aby se spustilo nebo přestalo synchronizovat nové sloupce.

Přidání nových sloupců

  1. Proveďte změnu schématu.

  2. Vyhněte se změnám dat, ve kterých jsou nové sloupce zahrnuté, dokud nedokončíte krok, který trigger vytvoří.

  3. Počkejte, až se změny schématu použijí na všechny koncové body.

  4. Aktualizujte schéma databáze a přidejte nový sloupec do schématu synchronizace.

  5. Data v novém sloupci se synchronizují během další operace synchronizace.

Odebrání sloupců

  1. Odeberte sloupce ze schématu synchronizace. Synchronizace dat zastaví synchronizaci dat v těchto sloupcích.

  2. Proveďte změnu schématu.

  3. Aktualizujte schéma databáze.

Aktualizace datových typů

  1. Proveďte změnu schématu.

  2. Počkejte, až se změny schématu použijí na všechny koncové body.

  3. Aktualizujte schéma databáze.

  4. Pokud nové a staré datové typy nejsou plně kompatibilní – například pokud změníte na intbigint – synchronizace může selhat před dokončením kroků, které vytvoří triggery. Synchronizace proběhne úspěšně po opakování.

Přejmenování sloupců nebo tabulek

Přejmenování sloupců nebo tabulek způsobí, že synchronizace dat přestane fungovat. Vytvořte novou tabulku nebo sloupec, znovu vyplňte data a potom místo přejmenování odstraňte starou tabulku nebo sloupec.

Jiné typy změn schématu

U jiných typů změn schématu – například vytvoření uložených procedur nebo vyřazení indexu – aktualizace schématu synchronizace není nutná.

Řešení potíží s automatizovanou replikací změn schématu

Logika replikace popsaná v tomto článku přestane v některých situacích fungovat, například pokud jste provedli změnu schématu v místní databázi, která není ve službě Azure SQL Database podporovaná. V takovém případě synchronizace tabulky sledování změn schématu selže. Tento problém potřebujete vyřešit ručně:

  1. Zakažte trigger DDL a vyhněte se dalším změnám schématu, dokud se problém nevyřeší.

  2. V databázi koncových bodů, kde k problému dochází, zakažte trigger AFTER INSERT na koncovém bodu, kde se změna schématu nedá provést. Tato akce umožňuje synchronizaci příkazu změny schématu.

  3. Aktivace synchronizace pro synchronizaci tabulky sledování změn schématu

  4. V databázi koncových bodů, kde k problému dochází, zadejte dotaz na tabulku historie změn schématu a získejte ID posledního použitého příkazu změny schématu.

  5. Dotazem na tabulku sledování změn schématu zobrazíte seznam všech příkazů s ID větší než hodnota ID, kterou jste získali v předchozím kroku.

    a. Ignorujte tyto příkazy, které nelze spustit v databázi koncového bodu. Potřebujete řešit nekonzistence schématu. Pokud má nekonzistence vliv na vaši aplikaci, vraťte původní změny schématu.

    b. Ručně použijte tyto příkazy, které by se měly použít.

  6. Aktualizujte tabulku historie změn schématu a nastavte ID naposledy použité na správnou hodnotu.

  7. Pečlivě zkontrolujte, jestli je schéma aktuální.

  8. V druhém kroku znovu povolte trigger AFTER INSERT.

  9. V prvním kroku znovu povolte trigger DDL zakázaný.

Pokud chcete vyčistit záznamy v tabulce sledování změn schématu, použijte místo funkce TRUNCATE delete. Sloupec identity v tabulce sledování změn schématu nikdy nepředávejte pomocí DBCC CHECKIDENT. Můžete vytvořit nové tabulky sledování změn schématu a aktualizovat název tabulky v triggeru DDL, pokud je vyžadováno obnovení.

Ostatní úvahy

  • Uživatelé databáze, kteří konfigurují centrální a členské databáze, musí mít dostatečná oprávnění ke spuštění příkazů pro změnu schématu.

  • Do triggeru DDL můžete přidat další filtry, které replikují pouze změnu schématu ve vybraných tabulkách nebo operacích.

  • Změny schématu můžete provádět pouze v databázi, ve které je vytvořen trigger DDL.

  • Pokud provádíte změnu v databázi SQL Serveru, ujistěte se, že je změna schématu podporovaná ve službě Azure SQL Database.

  • Pokud se změny schématu provádějí v jiných databázích než v databázi, ve které je vytvořen trigger DDL, změny se nereplikují. Pokud se chcete tomuto problému vyhnout, můžete vytvořit triggery DDL, které zablokují změny v jiných koncových bodech.

  • Pokud potřebujete změnit schéma tabulky sledování změn schématu, před provedením změny zakažte trigger DDL a pak změnu použijte ručně u všech koncových bodů. Aktualizace schématu v triggeru AFTER INSERT ve stejné tabulce nefunguje.

  • Sloupec identity nepředávejte pomocí DBCC CHECKIDENT.

  • Nepoužívejte funkci TRUNCATE k vyčištění dat v tabulce sledování změn schématu.

Další kroky

Další informace o Synchronizaci dat SQL: