Změnit sledování pro SQL Server je flexibilní a snadno-to-použití technologie pro sledování tabulky pro vložky, aktualizace a odstraní. V tomto příspěvku budu diskutovat o tom, jak začít se sledováním změn v SQL Serveru, a ukážu příklad, jak s ním začít.
Sledování Změn v produktu SQL Server
sledování Změn je lehký mechanismus pro sledování, které řádky mají být vložen, aktualizovány a odstraněny v tabulkách sledována sledování změn. Sledování změn se poprvé objevilo v SQL Server 2008 a od té doby je v každé verzi. Ještě lepší je, sledování změn je k dispozici v každém vydání SQL Server, a to i zdarma express edition.
Stručně řečeno, Zde je návod, jak to funguje: V tabulkách, které mají povoleno sledování změn, je čistá změna každého řádku sledována interně a je přístupná prostřednictvím funkcí sledování změn. Ke každé změně je připojeno ID verze a pro každý řádek, který je vložen, aktualizován nebo smazán, bude nové ID verze. Verze sledování změn je 8-bajtové celé číslo (BIGINT), které odráží nejnovější ID změny v této databázi. Je důležité si uvědomit, že verze sledování změn není specifická pro tabulku – operace DML v jakékoli sledované tabulce v každé databázi zvýší číslo verze. Číslo verze bude vždy sekvenční, ale nemusí být nutně sousedící v jedné tabulce, pokud je pro sledování změn povoleno více než jedna tabulka.
sledování změn je povoleno na úrovni databáze. Poté musí být každá tabulka, která bude sledována, individuálně zařazena do sledování změn. Každá tabulka, která má být sledována sledováním změn, musí mít primární klíč, protože se jedná o identifikátor na úrovni řádků používaný k hlášení operací DML v rámci sledování změn. Když povolíte sledování změn na úrovni tabulky, můžete se rozhodnout sledovat sloupce změněné v poslední aktualizaci, což vám poskytne lepší přehled o tom, co bylo změněno.
po nastavení je použití sledování změn relativně jednoduchý proces. Existuje několik funkcí – zejména CHANGE_TRACKING_CURRENT_VERSION () a CHANGETABLE (), které lze použít ke kontrole aktuálního razítka verze při sledování změn a načtení seznamu posledních změn. Za chvíli předvedu obě tyto funkce.
sledování změn není protokolování auditu
budu dávat pozor, abych nepoužil slova audit nebo protokolování k popisu sledování změn. Dovolte mi, abych byl jasný: toto není úplný protokolovací mechanismus. Historie změn není sledována vůbec – sledování změn pouze hlásí skutečnost, že ke změně došlo, ale nezachovává historii verzí. Zvažte případ řady dat s ID 1234. Tento řádek je vložen, poté 5krát aktualizován a poté smazán. Sledování změn nezobrazí historii vložení, aktualizace a odstranění; spíše by hlásil pouze čistou změnu, že řádek ID 1234 byl smazán. Pokud váš proces načítání vyžaduje podrobnou historii protokolování pro každou změnu (spíše než jen delta všech změn), budete muset použít něco jako sběr dat o změně.
nastavení sledování změn v SQL Serveru
povolení sledování změn na úrovni tabulky je dvoustupňový proces. Nejprve musí být v databázi povolena. To lze provést pomocí uživatelského rozhraní ve vlastnostech databáze na kartě sledování změn.
jak je znázorněno, není mnoho konfigurace při povolení sledování změn v databázi. Jednoduše nastavte hodnotu sledování změn na hodnotu True a nastavte sledování změn pro danou databázi. Volitelně lze také vylepšit hodnotu retenční doby. Výchozí hodnota je 2 dny, které jsem v tomto příkladu přepsal, abych místo toho použil 14 dní. Stejně jako u většiny operací uživatelského rozhraní existuje příkaz T-SQL, který dělá totéž. Příkaz k nastavení sledování změn v této databázi je níže.
po tomto kroku je povoleno sledování změn, ale zatím nic nesleduje. Stále musí být povoleno pro každou tabulku, která má být sledována. Uživatelské rozhraní vlastností tabulky to velmi usnadňuje.
jak je znázorněno, jednoduše změna hodnoty sledování změn na hodnotu True umožňuje sledování změn pro tuto tabulku. V tomto příkladu jsem se také rozhodl sledovat sloupce změněné během aktualizací(více o tom v trochu).
poslední krok výše by se opakoval pro každou tabulku, která má být sledována při sledování změn. Jakmile je sledování změn povoleno, všechny změny (vložení, aktualizace nebo odstranění) v této tabulce budou uloženy do mezipaměti sledování změn.
Nastavení Sledování Změn
Pro výše uvedený příklad, jdu vložit, aktualizace, a odstranit některé údaje ukazují, jak přistupovat sledování změn údajů získaných pro ty, DML operace. Zde je struktura tabulky.
dříve jsem ukázal, jak povolit sledování změn pro jednu tabulku pomocí uživatelského rozhraní. Dávám přednost použití T-SQL pro tento úkol, protože je snadněji opakovatelný. Povolení sledování změn pro tabulku, kterou jsem vytvořil výše, lze provést zde:
připomeňme, že jsem již zmínil, že sledování změn používá ID verze ke sledování aktuální verze sledovaných tabulek. Toto ID verze je naše značka časové osy pro detekci změn. Chcete-li získat tuto hodnotu, existuje velmi jednoduchá funkce: CHANGE_TRACKING_CURRENT_VERSION(). Používá se, jak je uvedeno níže.
v mém testovacím systému je tato hodnota 470 (protože jsem před tímto zápisem provedl několik testů). Toto je výchozí bod a jakékoli změny provedené od tohoto bodu vpřed by spustily nové číslo verze. Poznamenám si tuto hodnotu a nyní provedu některé změny v tabulce popsané výše. Vložím několik řádků, abych ukázal, jak sledování změn zobrazuje vložky.
po vložení těchto šesti řádků znovu zkontroluji hodnotu CHANGE_TRACKING_CURRENT_VERSION () a zjistím, že hodnota je nyní 476. To bylo zvýšeno o 6-one na řádek vložen, což je to, co bych očekával.
pomocí funkcí sledování změn
dále použijeme funkci sledování změn CHANGETABLE () k zobrazení čistých změn v této tabulce.
zlomit to dolů:
- CHANGETABLE je vyhodnocovaná tabulkou systém, funkce, která vrátí seznam změn uloženy v sledování změn
- ZMĚNY znamená, že hledám změny od uvedené verze
- @ver je proměnná I nastavit ukládání čísla verze. CHANGETABLE vrátí všechny výsledky odrážející změny od této verze. Všimněte si, že můžete použít proměnnou, jako já, nebo jen předat skalární číslo (pomocí doslovný 470 tady by dokázal to samé)
Když jsem se spustit kód výše, se zobrazí následující výsledek sada.
Toto mi říká verzi vložit a/nebo aktualizace operace (I, U nebo D pro insert, update nebo delete, respektive), sloupec maska pro operace aktualizace (více o tom za okamžik), a primární klíč pro řádek ovlivněny tuto změnu. Protože CHANGETABLE () vrátí tabulku, mohl bych se snadno připojit k tomuto výsledku nastavenému zpět do původní tabulky, abych viděl operaci změny spolu s aktuálními daty v této tabulce.
to bude vypadat trochu jinak pro operaci aktualizace. Dále provedu prohlášení o aktualizaci, ale nejprve si všimnu aktuální verze sledování změn(což je stále 476).
nyní prohlášení o aktualizaci, které aktualizuje dva řádky v tabulce:
Teď, když jsem spustit CHANGETABLE() kód z výše uvedeného, použití více nedávné sledování změn verze (476) jako výchozí bod, dostanu jiný výsledek nastavit:
To je metadata pro všechny změny od verze 476, která zahrnuje pouze dvě řady aktualizován z UPDATE prohlášení výše. Všimněte si, že verze pro vytvoření je null, protože tato změna byla aktualizací, nikoli vložkou. Také hodnota SYS_CHANGE_COLUMNS je nyní naplněna, i když hodnota nám ve skutečnosti neukazuje, co se (zatím) změnilo. To je ten správný čas mluvit o funkci sledování změn CHANGE_TRACKING_IS_COLUMN_IN_MASK (). Tato funkce zkontroluje, zda byl zadaný sloupec aktualizován od nejnovější verze. Jeho syntaxe je trochu nepředvídatelný, ale zkontrolovat, zda MiddleName byl aktualizován, dotaz bude vypadat takto:
Upřímně, já nevím, že jsem kdy používal CHANGE_TRACKING_IS_COLUMN_IN_MASK funkce. Je to trochu bolest, protože to musíte spustit pro každý sloupec, který chcete zkontrolovat. Většina mé práce je v datových skladech a narazil jsem na několik případů, kdy potřebuji přesně vědět, které sloupce byly aktualizovány – chci jen vědět, zda byl řádek aktualizován. U jiných scénářů (zejména v OLTP) však vidím potřebu.
předvedl jsem vložky a aktualizace. Podívejme se, jak by smazání vypadalo. Opět si poznamenám aktuální číslo verze-478 – pro další operaci. Nyní smažu jeden řádek dat:
po odstranění jednoho řádku spustím CHANGETABLE () znovu, abych zjistil, jaké zprávy o sledování změn pro tuto operaci.
najít jeden řádek jsem smazal v poslední operaci, s SYS_CHANGE_OPERATION nastavte na D (delete):
Nyní, pamatujte, že číslo verze je rozdíl! Číslo verze předané do CHANGETABLE () je výchozím bodem pro všechny změny vrácené touto funkcí. Prostřednictvím tohoto cvičení jsem kontroloval výsledky sledování změn po každé operaci DML. Mohu však nastavit počáteční číslo verze na libovolné platné číslo verze nebo jednoduše použít NULL, abyste získali všechny dostupné výsledky sledování změn pro tuto tabulku. Pro demonstraci nastavím hodnotu zpět na verzi 470-výchozí bod před všemi aktualizacemi-abych ukázal, jak bude vypadat celá historie. Když jsem znovu spustit CHANGETABLE() pomocí našeho původního sledování změn verze, dostanu následující:
Existuje několik předvídatelné nuance zde. Za prvé, řádek zobrazující ID záznamu 1 (což byl záznam Phoebe Buffay, který jsem smazal) se zobrazí jednoduše jako operace odstranění, i když byl tento řádek vložen a následně odstraněn od čísla počáteční verze. Nezapomeňte, že se zobrazí delta-každá operace proti tomuto řádku není zachována při sledování změn. U ID číslovaných 2 a 4-což byly dva řádky, které jsem vložil a následně aktualizoval-SYS_CHANGE_OPERACE zobrazuje vložku, i když jsme oba záznamy aktualizovali po vložení. Tell je, že SYS_CHANGE_VERSION a SYS_CHANGE_CREATION_VERSION v těchto řádcích se neshodují, což naznačuje, že poslední změnou nebyla vložka.
závěr
sledování změn je jednoduchý a lehký prostředek detekce změn v SQL Serveru. Použití sledování změn umožňuje snadnou identifikaci nových, změněných a smazaných dat, což eliminuje potřebu srovnání hrubou silou. V mém příštím příspěvku budu se podívat na to z ETL pohledu, integrace sledování změn do end-to-end procesu zatížení.