Práce se změnami dat

platí pro:SQL Serverazure SQL Managed Instance

Data změn jsou k dispozici pro změnu příjemců zachytávání dat prostřednictvím funkcí s hodnotami tabulky (TVF). Všechny dotazy těchto funkcí vyžadují dva parametry k definování rozsahu pořadových čísel protokolu (LSN), které jsou způsobilé k zvážení při vývoji vrácené sady výsledků. Horní i dolní hodnoty LSN, které tento interval vázaly, se považují za zahrnuté v intervalu.

K dispozici je několik funkcí, které vám pomůžou určit vhodné hodnoty LSN pro použití při dotazování tvF. Funkce sys.fn_cdc_get_min_lsn vrátí nejmenší LSN přidruženou k intervalu platnosti instance zachycení. Interval platnosti je časový interval, pro který jsou data změn aktuálně k dispozici pro své instance zachycení. Funkce sys.fn_cdc_get_max_lsn vrátí největší LSN v intervalu platnosti. Funkce sys.fn_cdc_map_time_to_lsn a sys.fn_cdc_map_lsn_to_time jsou k dispozici, aby pomohly umístit hodnoty LSN na konvenční časovou osu.

Vzhledem k tomu, že zachytávání dat změn používá uzavřené intervaly dotazů, je někdy nutné vygenerovat další hodnotu LSN v posloupnosti, aby se zajistilo, že změny nebudou duplikovány v po sobě jdoucích oknech dotazů. Funkce sys.fn_cdc_increment_lsn a sys.fn_cdc_decrement_lsn jsou užitečné, když je vyžadována přírůstková úprava hodnoty LSN.

Ověření hranic LSN

Doporučujeme ověřit hranice LSN, které se mají použít v dotazu TVF před jejich použitím. Koncové body s hodnotou NULL nebo koncové body, které leží mimo interval platnosti instance zachycování, způsobí, že funkce TVF pro zachytávání změn dat vrátí chybu.

Například následující chyba se vrátí pro dotaz pro všechny změny, pokud parametr použitý k definování intervalu dotazu není platný nebo je mimo rozsah nebo je možnost filtru řádků neplatná.

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...

Odpovídající chyba vrácená pro dotaz net changes je následující:

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...

Poznámka:

Je rozpoznáno, že zpráva pro msg 313 je zavádějící a neobsahuje skutečnou příčinu selhání. Toto neohrabané použití pramení z nemožnosti explicitně vyvolat chybu uvnitř TVF. Nicméně se dospělo k závěru, že je vhodnější vrátit rozpoznatelnou, i když nepřesnou chybu, než jednoduše vrátit prázdný výsledek. Prázdná sada výsledků by nebyla rozlišitelná od platného dotazu, který nevrací žádné změny.

Při selhání autorizace se při dotazu na všechny změny vrátí chyba, jak je uvedeno níže:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.

Totéž platí při dotazu na čisté změny:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.

V aplikaci SQL Server Management Studio si prohlédněte šablonu Výčtu síťových změn pomocí příkazu TRY CATCH , která ukazuje, jak zachytit tyto známé chyby TVF a vrátit smysluplnější informace o selhání.

Návod

Chcete-li vyhledat šablony pro zachytávání dat v aplikaci SQL Server Management Studio, v nabídce Zobrazení vyberte Průzkumník šablon, rozbalte šablony SQL Serveru a potom rozbalte složku Change Data Capture .

Funkce dotazů

V závislosti na charakteristikách sledované zdrojové tabulky a způsobu konfigurace instance zachycení se vygenerují buď jeden nebo dva TVF pro dotazování na data změn.

  • Funkce cdc.fn_cdc_get_all_changes_<capture_instance> vrátí všechny změny, ke kterým došlo v zadaném intervalu. Tato funkce je vždy generována. Položky jsou vždy vraceny seřazené, nejprve podle LSN potvrzení transakce dané změny a poté podle hodnoty, která určuje pořadí změny v rámci transakce. V závislosti na zvolené možnosti filtru řádků se při aktualizaci vrátí poslední řádek (možnost filtru řádku "vše") nebo se při aktualizaci vrátí nové i staré hodnoty (možnost filtru řádků "všechny aktualizace staré").

  • Funkce cdc.fn_cdc_get_net_changes_<capture_instance> se vygeneruje, když je parametr @supports_net_changes nastaven na 1 , když je povolena zdrojová tabulka.

    Poznámka:

    Tato možnost se podporuje pouze v případě, že zdrojová tabulka má definovaný primární klíč nebo pokud byl parametr @index_name použit k identifikaci jedinečného indexu.

    Funkce netchanges vrátí jednu změnu na upravený řádek zdrojové tabulky. Pokud se během zadaného intervalu zaprotokoluje pro řádek více než jedna změna, hodnoty sloupců budou odrážet konečný obsah řádku. Aby bylo možné správně určit operaci, kterou je nutné provést pro aktualizaci cílového prostředí, musí TVF zohlednit jak počáteční operaci provedenou na řádku během daného intervalu, tak konečnou operaci provedenou na řádku. Pokud je zadána možnost filtru řádků „all“, operace vrácené dotazem na čisté změny budou buď vložení, odstranění, nebo aktualizace (nové hodnoty). Tato možnost vždy vrátí masku aktualizace jako hodnotu null, protože k výpočtu agregované masky jsou spojené náklady. Pokud potřebujete agregační masku, která odráží všechny změny řádku, použijte možnost Vše s maskou. Pokud následné zpracování nevyžaduje rozlišování mezi vloženími a aktualizacemi, použijte možnost „all with merge“. V tomto případě bude hodnota operace nabývat pouze dvou hodnot: 1 pro smazání a 5 pro operaci, která může představovat buď vložení, nebo aktualizaci. Tato možnost eliminuje další zpracování potřebné k určení, zda má být odvozená operace vložení nebo aktualizace, a může zlepšit výkon dotazu, pokud toto rozlišení není nutné.

Maska aktualizace vrácená funkcí dotazu je kompaktní reprezentace, která identifikuje všechny sloupce, které se změnily v řádku změn dat. Tyto informace se obvykle vyžadují jenom pro malou podmnožinu zachycených sloupců. Funkce jsou k dispozici pro pomoc při extrahování informací z masky ve formě, která je přímo použitelná aplikacemi. Funkce sys.fn_cdc_get_column_ordinal vrátí pořadové umístění pojmenovaného sloupce pro danou instanci zachycení, zatímco funkce sys.fn_cdc_is_bit_set vrátí paritu bitu v zadané masce na základě pořadového čísla předávaného ve volání funkce. Tyto dvě funkce společně umožňují efektivně získávat informace z masky aktualizace a vracet je v rámci požadavku na data o změnách. V aplikaci SQL Server Management Studio si prohlédněte šablonu Enumerate Net Changes using All With Mask (Vše s maskou) a podívejte se, jak se tyto funkce používají.

Scénáře funkcí dotazů

Následující části popisují běžné scénáře dotazování na data funkce Change Data Capture pomocí funkcí pro dotazování cdc.fn_cdc_get_all_changes_<capture_instance> a cdc.fn_cdc_get_net_changes_<capture_instance>.

Dotaz na všechny změny v intervalu platnosti instance zachytávání

Nejjednodušší požadavek na data změn je ten, který vrací všechna aktuální data změn v intervalu platnosti instance zachycení. Pokud chcete tento požadavek provést, nejprve určete dolní a horní hranice LSN intervalu platnosti. Poté pomocí těchto hodnot určete parametry @from_lsn a @to_lsn, které byly předány funkci dotazu cdc.fn_cdc_get_all_changes_<capture_instance> nebo cdc.fn_cdc_get_net_changes_<capture_instance>. Pomocí funkce sys.fn_cdc_get_min_lsn získejte dolní mez a sys.fn_cdc_get_max_lsn získat horní mez. V aplikaci SQL Server Management Studio se ukázkový kód pro dotazování na všechny aktuální platné změny pomocí funkce dotazu cdc.fn_cdc_get_all_changes_<capture_instance> nachází v šabloně Enumerate All Changes for the Valid Range. V aplikaci SQL Server Management Studio najdete podobný příklad použití funkce cdc.fn_cdc_get_net_changes_<capture_instance> v šabloně Enumerate Net Changes for the Valid Range.

Dotaz na všechny nové změny od poslední sady změn

U typických aplikací bude dotazování na data změn průběžným procesem a provádění pravidelných požadavků na všechny změny, ke kterým došlo od posledního požadavku. U takových dotazů můžete pomocí funkce sys.fn_cdc_increment_lsn odvodit dolní mez aktuálního dotazu z horní hranice předchozího dotazu. Tato metoda zajišťuje, že se nebudou opakovat žádné řádky, protože interval dotazu se vždy považuje za uzavřený interval, ve kterém jsou v intervalu zahrnuty oba koncové body. Potom pomocí funkce sys.fn_cdc_get_max_lsn získejte koncový bod pro nový interval požadavku. V aplikaci SQL Server Management Studio si prohlédněte šablonu Výčet všech změn od předchozího požadavku pro vzorový kód, abyste mohli systematicky přesunout okno dotazu, aby se získaly všechny změny od posledního požadavku.

Dotaz na všechny nové změny k dnešnímu dni

Typickým omezením, které je u změn vrácených funkcí dotazu, je zahrnout pouze změny, ke kterým došlo mezi předchozím požadavkem až do aktuálního data a času. Pro tento dotaz použijte funkci sys.fn_cdc_increment_lsn na @from_lsn hodnotu použitou v předchozím požadavku k určení dolní hranice. Vzhledem k tomu, že horní mez časového intervalu je vyjádřena jako určitý bod v čase, musí být převedena na hodnotu LSN, aby ji mohl použít funkce dotazu. Než lze hodnotu datetime převést na odpovídající hodnotu LSN, je nutné zajistit, aby proces zachytávání zpracoval všechny změny potvrzené až do zadané horní meze. To je nutné, aby bylo zajištěno, že se všechny relevantní změny promítly do tabulky změn. Jedním ze způsobů, jak to udělat, je strukturovat smyčku čekání, která pravidelně kontroluje, jestli aktuální maximální potvrzení lsn zaznamenané pro jakoukoli tabulku změn databáze překročí požadovaný koncový čas intervalu požadavku.

Jakmile smyčka prodlevy ověří, že proces zachytávání již zpracoval všechny příslušné položky transakčního protokolu, použijte funkci sys.fn_cdc_map_time_to_lsn k určení nového horního koncového bodu vyjádřeného jako hodnota LSN. Aby bylo zajištěno, že budou načteny všechny položky zapsané do zadaného času, zavolejte funkci sys.fn_cdc_map_time_to_lsn, a použijte možnost „největší hodnota menší než nebo rovna“.

Poznámka:

V období nečinnosti se do tabulky cdc.lsn_time_mapping přidá fiktivní položka, která označuje skutečnost, že proces zachycení zpracoval změny až do zadané doby potvrzení. Tím se zabrání tomu, aby to vypadalo, že se proces snímání opozdil, když prostě nejsou žádné nedávné změny ke zpracování.

Šablona – Výčet všech změn až do teď ukazuje, jak použít předchozí strategii k dotazování na data změn.

Přidání doby potvrzení do sady výsledků všech změn

Čas potvrzení každé transakce s přidruženou položkou v tabulce změn databáze je k dispozici v tabulce cdc.lsn_time_mapping. Spojením hodnoty __$start_lsn vrácené v požadavku na všechny změny s hodnotou start_lsn záznamu tabulky cdc.lsn_time_mapping můžete spolu s daty změn vrátit také hodnotu tran_end_time a opatřit tak změnu časem potvrzení transakce ve zdroji. Šablona Přidat čas commitu k sadě výsledků všech změn ukazuje, jak toto spojení provést.

Spojení změn dat s jinými daty ze stejné transakce

Někdy je užitečné spojit údaje o změnách s dalšími informacemi získanými o transakci v okamžiku, kdy byla potvrzena na zdroji. Sloupec tran_begin_lsn v tabulce cdc.lsn_time_mapping poskytuje informace potřebné k provedení takového spojení. Když dojde k aktualizaci zdroje, musí být hodnota pro database_transaction_begin_lsn ze systémového dynamického zobrazení sys.dm_tran_database_transactions uložena spolu s dalšími informacemi, které mají být spojeny s daty změn. Pomocí funkce fn_convertnumericlsntobinary můžete porovnat database_transaction_begin_lsn hodnoty a tran_begin_lsn hodnoty. Kód pro vytvoření této funkce je k dispozici v šabloně Create Function fn_convertnumericlsntobinary. Šablona Vrátit všechny změny s daným parametrem tran_begin_lsn ukazuje, jak ovlivnit operaci join.

Dotaz pomocí funkcí obálky DateTime

Typickým scénářem aplikace pro dotazování na data změn je pravidelné vyžádání změn dat pomocí posuvného okna ohraničovaného hodnotami data a času. Pro tuto třídu uživatelů poskytuje funkce change data capture uloženou proceduru sys.sp_cdc_generate_wrapper_function, která generuje skripty pro vytváření vlastních obalových funkcí pro funkce pro dotazování na change data capture. Tyto vlastní obalové typy umožňují vyjádřit interval dotazu jako dvojici hodnot data a času.

Možnosti volání pro uloženou proceduru umožňují vygenerovat obálky pro všechny instance zachycení, ke kterým má volající přístup, nebo pouze zadanou instanci zachycení. Mezi podporované možnosti patří také možnost určit, zda má být otevřený nebo uzavřený vysoký koncový bod intervalu zachycení, které z dostupných zachycených sloupců by měly být zahrnuty do sady výsledků a které z zahrnutých sloupců by měly mít přidružené příznaky aktualizace. Procedura vrátí sadu výsledků se dvěma sloupci: vygenerovaný název funkce, který lze odvodit z názvu instance snímání, a příkaz CREATE pro zapouzdřující uloženou proceduru. Funkce, která zabalí všechny změny dotazu, se vždy vygeneruje. Pokud byl parametr @supports_net_changes nastaven při vytvoření instance zachycení, vygeneruje se také funkce, která obaluje funkci net changes.

Za volání uložené procedury pro generování skriptů za účelem vygenerování příkazů CREATE pro obalové uložené procedury a za spuštění výsledných skriptů CREATE k vytvoření funkcí odpovídá návrhář aplikace. K tomu nedojde automaticky při vytvoření instance zachycení.

Obaly typu datetime vlastní uživatel, ale nejsou vytvořeny ve výchozím schématu volajícího. Vygenerovaná funkce je vhodná bez úprav pro většinu uživatelů. Před vytvořením funkce je však možné u vygenerovaného skriptu vždy použít další přizpůsobení.

Název funkce pro zabalení dotazu na všechny změny je fn_all_changes_, za kterým následuje název instance zachytávání. Předpona použitá pro obálku net changes je fn_net_changes_. Obě funkce mají tři argumenty, stejně jako jejich přidružené funkce pro zachytávání dat změn. Interval dotazu pro obálky je však ohraničen dvěma hodnotami data a času namísto dvěma hodnotami LSN. Parametr @row_filter_option pro obě sady funkcí je stejný.

Vygenerované obalové funkce podporují následující konvenci pro systematické procházení časové osy zachytávání změn dat: Očekává se, že parametr @end_time předchozího intervalu bude použit jako parametr @start_time následujícího intervalu. Funkce obálky se postará o mapování hodnot data a času na hodnoty LSN a zajišťuje, aby se žádná data neztratila nebo opakovala, pokud je tato konvence dodržena.

Obálky lze vygenerovat tak, aby podporovaly uzavřenou horní mez nebo otevřenou horní mez v zadaném okně dotazu. To znamená, že volající může určit, zda mají být do intervalu zahrnuty položky s časem potvrzení změn rovným horní hranici intervalu extrakce. Ve výchozím nastavení je horní mez zahrnuta.

I když generované funkce TVF dotazu selžou, pokud je pro hodnotu @from_lsn nebo @to_lsn zadána hodnota null, obalové funkce pro datetime používají hodnotu null, aby mohly vrátit všechny aktuální změny. To znamená, že pokud je jako dolní mezní bod okna dotazu předána hodnota null obalové funkci datetime, použije se v podkladovém příkazu SELECT, který se použije na TVF dotazu, dolní mezní bod intervalu platnosti instance zachytávání. Podobně platí, že pokud je hodnota null předána jako koncový bod okna dotazu, použije se při výběru z TVF dotazu vysoký koncový bod intervalu platnosti instance zachycení.

Sada výsledků vrácená funkcí obálky obsahuje všechny požadované sloupce následované sloupcem operace, překódované jako jeden nebo dva znaky, aby bylo možné identifikovat operaci přidruženou k řádku. Pokud byly požadovány příznaky aktualizace, zobrazí se za kódem operace jako bitové sloupce v pořadí uvedeném v parametru @update_flag_list . Informace o možnostech volání pro přizpůsobení vygenerovaných obálek datetime naleznete v části sys.sp_cdc_generate_wrapper_function (Transact-SQL).

Šablona Vytvoření instance funkce obálky TVF s příznakem aktualizace ukazuje, jak přizpůsobit vygenerovanou funkci obálky tak, aby do sady výsledků vrácené dotazem na čisté změny připojovala příznak aktualizace pro zadaný sloupec. Šablona Vytvoření instancí obalových TVF CDC pro schéma ukazuje, jak vytvořit instance obalových funkcí Datetime pro TVF dotazů pro všechny instance zachytávání vytvořené pro zdrojové tabulky v daném schématu databáze.

Příklad, který k dotazování na data změn používá obálku datetime, naleznete v aplikaci SQL Server Management Studio šablonu Get Net Changes using Wrapper with Update Flags. Tato šablona ukazuje, jak zjišťovat výsledné změny pomocí obalové funkce, pokud je obalová funkce nakonfigurována tak, aby vracela příznaky aktualizace. Volba filtru řádků „all with mask“ („vše s maskou“) je vyžadována, aby podkladová funkce dotazu při aktualizaci vrátila masku aktualizace s hodnotou non-null. Hodnoty Null se předávají pro hranice dolního i horního intervalu data a času, aby funkce signalizovala použití nízkého koncového bodu a vysokého koncového bodu intervalu platnosti instance zachytávání při provádění základního dotazu založeného na LSN. Dotaz vrátí jeden řádek pro každou úpravu zdrojového řádku, ke které došlo v platném rozsahu instance zachycení.

Použití funkcí obálky DateTime k přechodu mezi instancemi zachycení

Zachytávání dat změn podporuje až dvě instance zachycení jedné sledované zdrojové tabulky. Hlavním účelem této funkce je usnadnit přechod mezi více instancemi snímání, když změny jazyka DDL (Data Definition Language) ve zdrojové tabulce rozšíří sadu dostupných sloupců pro sledování. Při přechodu na novou instanci zachycení je jedním ze způsobů, jak chránit vyšší úrovně aplikace před změnami v názvech základních funkcí dotazu, použít funkci obálky k zabalení základního volání. Pak se ujistěte, že název funkce obálky zůstane stejný. Když má dojít k přepnutí, lze starou obalovací funkci odstranit a vytvořit novou se stejným názvem, která odkazuje na nové dotazovací funkce. Když nejprve upravíte vygenerovaný skript tak, aby vytvořil funkci obálky se stejným názvem, můžete přepnout na novou instanci zachytávání, aniž by to mělo vliv na vyšší vrstvy aplikace.

Související obsah

  • Last updated on