Change Tracking – sledování změn pro synchronizaci databází

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)
*/

Napsat komentář

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *

Můžete používat následující HTML značky a atributy: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>