V aplikacích nebo komplikovaných podnikových informačních architekturách často potřebujeme z různých důvodů řešit historizaci záznamů a trackování změn hodnot. Většinou to bývá zejména z bezpečnostních důvodů => potřebujeme sledovat kdo a co mění, případně být schopni opravit chybu třeba ze strany uživatele nebo db admina.

Dalším dobrým důvodem pro nasazení CDC je třeba to, že potřebujeme data z aplikace později integrovat např. do datového skladu nebo jiné aplikace a potřebujeme identifikovat změnové záznamy pro přesun. S featurou change data capture (CDC) jsme schopni nastavit sledování změn nad určitou tabulkou v databázi.

Nastavení change data capture (CDC) – sledování změn nad tabulkou

Pomocí několika kroků provedeme nastavení CDC v databázi AdventureworksDW2016CTP3 (sample databaze od Microsoftu). Budeme chtít sledovat všechny změny nad tabulkou DimCustomer

Prvním krokem je zapnutí funkce change data capture nad určitou databází

( 1.a ) V systémové tabulce sys.databases existuje indikátor, kterým zkontrolujeme u jakých databází máme funkci zapnutou/vypnutou. V tomto případě je CDC všude vypnuto.

SELECT
[name]           AS [Databaze],
[is_cdc_enabled] AS [Indikator_CDC]
FROM sys.databases;

( 1.b ) Zapnutí CDC nad databází provedeme pomocí systémové procedury “sys.sp_cdc_enable_db“.

USE AdventureworksDW2016CTP3
GO
EXEC sys.sp_cdc_enable_db
GO

No vidíte, mám první HUPS. CDC nefunguje nad Express edicí, což je důležitá informace, kterou jsem na začátku nezmínil. Takže se musím přehodit na Developer Edition :)))

This instance of SQL Server is the Express Edition (64-bit). Change data capture is only available in the Enterprise, Developer, Enterprise Evaluation, and Standard editions.

Nad Developer Edicí nebo edicemi standard a vyšší vám CDC samozřejmě pojede.

( 1.c ) Po povolení CDC nad databází dojde k založení nového schématu cdc a několika systémových tabulek pro logování změn.

Zapnutí Change data capture nad tabulkou

( 2.a ) Trakování změn nad tabulkou zapneme opět pomocí systémové procedury, která se jmenuje “sys.sp_cdc_enable_table“

• Založily se 2 joby, které se starají o sledování změn
• V systémových tabulkách byla založena nová tabulka s názvem dbo_DimCustomer_CT, která bude obsahovat kompletní historii změn nad danou tabulkou. tato tabulka obsahuje kompletní seznam sloupců. Obsahuje také několik systémových sloupců navíc, které slouží k prási s historií nad tabulkou. Tyto jsou nejdůležitější:
  • __$start_lsn – identifikátor transakce, v rámci které byla změna provedena. V případě, že v rámci transakce proběhne více změn, tak změnové záznamy budou mít všechny stejné __$start_lsn
  • __$operation – operace, která se odehrála
    • DELETE = 1
    • INSERT = 2
    • hodnota před UPDATE = 3
    • hodnota po UPDATE = 4

Vytěžení informací o Change data capture

V tabulce nad kterou je nasazeno CDC, provedeme nějaké změny

• 3x UPDATE
• 1x DELETE

Jak vypadá tabulka dbo_DimCustomer_CT se sledováním změn? (vybral jsem pouze sloupce, které jsem měnil. Vidíte, že máme kompletní historii toho co se dělo. Z logu je vidět, že všechny UPDATE operace (prvních 6 záznamů) proběhlo v rámci 1 transakce a DELETE operaci jsem pouštěl zvlášť. U UPDATE operací máme v logu jak původní hodnotu, tak hodnotu novou.

Pokud chceme časový kontext, tak si skript trošku upravíme. V systémových tabulkách se nachází mapovací tabulka mezi __$start_lsn a časových kontextem

CDC featura toho umí hodně. Víc než jsem schopen popsat v 1 článku, pokračování snad někdy jindy.