Pokud potřebujeme synchronizovat změny v databázi do jiných databází, pak potřebujeme mechanizmus, kterým zjístíme, které záznamy se od poslední synchronizace změnily. Na to právě v SQL serveru slouží Change Tracking. Change Tracking je také možné využít pro aktualizací keší.
Change Tracking se zapíná na úrovni databáze. Zde bude změny uchovávat dva dny, starší změny pak zahodí. Interval CHANGE_RETENTION je tedy nutné nastavit delší než jak často budeme databáze synchronizovat.
ALTER DATABASE mojedb SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) GO
Připravíme si tabulky, na kterých si sledování změn otestujeme.
CREATE TABLE tab1( id INT IDENTITY(1,1), data1 NVARCHAR(50), data2 NVARCHAR(50), CONSTRAINT pk_tab1 PRIMARY KEY (id) ) GO CREATE TABLE tab2( id INT IDENTITY(1,1), data1 NVARCHAR(50), data2 NVARCHAR(50), CONSTRAINT pk_tab2 PRIMARY KEY (id) ) GO
Zapneme sledování změn na úrovni tabulek.
ALTER TABLE tab1 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON); GO ALTER TABLE tab2 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON); GO
Představíme si, že jsem provedli synchronizaci všech dat.
Zjístíme aktuální verzi změn. S tímto číslem se budeme při další synchronizaci doptávat na to, co se změnilo.
SELECT CHANGE_TRACKING_CURRENT_VERSION(); --vysledek 2
Nasimulujeme nějaké změny na tabulkách.
INSERT INTO tab1(data1, data2) VALUES('a','a')
INSERT INTO tab1(data1, data2) VALUES('b','b')
INSERT INTO tab2(data1, data2) VALUES('a','a')
INSERT INTO tab2(data1, data2) VALUES('b','b')
INSERT INTO tab1(data1, data2) VALUES('c','c')
UPDATE tab1 SET data1 = 'd' WHERE data1 = 'c'
GO
Zjístíme změněné záznamy pro novou synchronizaci. Číslo poslední známé verze (2) známe z minula.
SELECT * FROM CHANGETABLE(CHANGES tab1, 2) AS CT SELECT * FROM CHANGETABLE(CHANGES tab2, 2) AS CT /* SYS_CHANGE_VERSION SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS SYS_CHANGE_CONTEXT id -------------------- --------------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- 3 3 I NULL NULL 1 4 4 I NULL NULL 2 8 7 I NULL NULL 3 (3 row(s) affected) SYS_CHANGE_VERSION SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS SYS_CHANGE_CONTEXT id -------------------- --------------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- 5 5 I NULL NULL 1 6 6 I NULL NULL 2 (2 row(s) affected) */
Ve výsledku není vidět poslední update, který pro synchronizaci není podstatný. Přeneseme prostě vše co přibylo.
Pokud bychom byli ve verzi 7, update by vidět byl.
SELECT * FROM CHANGETABLE(CHANGES tab1, 7) AS CT /* SYS_CHANGE_VERSION SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS SYS_CHANGE_CONTEXT id -------------------- --------------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- 8 NULL U 0x0000000002000000 NULL 3 (1 row(s) affected) */
Také je možné vyhodnotit, který sloupec byl změněn, abychom zbytečně nesynchronizovali nezměněná data.
DECLARE @data1ColumnId int = COLUMNPROPERTY(OBJECT_ID('tab1'), 'data1', 'ColumnId')
DECLARE @data2ColumnId int = COLUMNPROPERTY(OBJECT_ID('tab1'), 'data2', 'ColumnId')
SELECT
CHANGE_TRACKING_IS_COLUMN_IN_MASK(@data1ColumnId, CT.SYS_CHANGE_COLUMNS) AS data1Zmenen,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(@data2ColumnId, CT.SYS_CHANGE_COLUMNS) AS data2Zmenen
FROM CHANGETABLE(CHANGES tab1, 7) AS CT
/*
data1Zmenen data2Zmenen
----------- -----------
1 0
(1 row(s) affected)
*/