SQL Change data capture (CDC) – Tracking změn nad tabulkou

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

(1) 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;

change data capture nad databazi

( 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.

Error CDC

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

Zapnutí Change data capture (CDC) nad databází

( 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.

Change data capture systémové tabulky

(2) 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

USE AdventureworksDW2016CTP3
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo', --název schématu
@source_name   = N'DimCustomer', --název tabulky
@role_name     = NULL
GO
Zapnutí Change data capture (CDC) nad tabulkou
( 2.b ) Po zapnutí CDC nad tabulkou DimCustomer se stalo:
  • 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

change data capture sledování změn

Vytěžení informací o Change data capture

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

  • 3x UPDATE
  • 1x DELETE

Simulace změn v tabulce pod change data capture

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.

CDC záznam změn sloupců z logu

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 záznam z logu - časový kontext

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

_______________________________________________________________________________________________
Intelligent technologies - podniková řešení a školení
SQL Change data capture (CDC) – Tracking změn nad tabulkou
Hodnocení

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

This site uses Akismet to reduce spam. Learn how your comment data is processed.