Poznámka:
Přístup k této stránce vyžaduje autorizaci. Můžete se zkusit přihlásit nebo změnit adresáře.
Přístup k této stránce vyžaduje autorizaci. Můžete zkusit změnit adresáře.
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 nebo koncové body s hodnotou null, které leží mimo interval platnosti instance zachycení, vynutí vrácení chyby záznamu TVF pro zachytávání dat změn.
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 nepříjemné použití vychází z nemožnosti vyvolat explicitní chybu z tvF. Nicméně hodnota vrácení rozpoznatelné, pokud nepřesné, chyba byla považována za vhodnější, aby jednoduše vrátila prázdný výsledek. Prázdná sada výsledků by nebyla rozlišitelná od platného dotazu, který nevrací žádné změny.
Selhání autorizace vrátí selhání při dotazování na všechny změny, jak je znázorněno na obrázku:
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 dotazování 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 vráceny seřazeny, nejprve podle potvrzení transakce LSN změny a potom podle hodnoty, která sekvencuje změnu v rámci své 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_changesnastaven na1, 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
netchangesvrá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ě identifikovat operaci potřebnou k aktualizaci cílového prostředí, musí tvF zvážit počáteční operaci na řádku během intervalu i poslední operace na řádku. Pokud je zadána možnost filtru řádků All, operace vrácené dotazem pro čisté změny se vloží, odstraní nebo aktualizuje (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 podřízené zpracování nevyžaduje vložení a aktualizace k rozlišení, použijte možnost "vše se sloučením". V tomto případě bude hodnota operace trvat pouze dvě hodnoty: 1 pro odstranění a 5 pro operaci, která může být vložení nebo aktualizace. 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í extrakci a vrácení informací z masky aktualizace a vrácení s požadavkem na data změn. 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 zachytávání dat změn pomocí funkcí cdc.fn_cdc_get_all_changes_<capture_instance> dotazu 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. Tyto hodnoty pak použijte k identifikaci parametrů @from_lsn a @to_lsn předání do funkce cdc.fn_cdc_get_all_changes_<capture_instance> dotazu 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 podívejte na šablonu Enumerate All Changes for the Valid Range for the Valid Range for sample code to query for all current valid changes by using the query function cdc.fn_cdc_get_all_changes_<capture_instance>. V aplikaci SQL Server Management Studio se podívejte na šablonu Enumerate Net Changes for the Valid Range for a similar example of using the function cdc.fn_cdc_get_net_changes_<capture_instance>.
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 až doteď
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. Před převodem hodnoty datetime na odpovídající hodnotu LSN je nutné zajistit, aby proces zachycení zpracoval všechny změny potvrzené přes zadanou horní mez. To je nutné k zajištění toho, aby se všechny oprávněné změny rozšířily 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 zpoždění ověří, že proces zachycení již zpracoval všechny příslušné položky protokolu, použijte funkci sys.fn_cdc_map_time_to_lsn k určení nového koncového bodu nejvyššího bodu vyjádřeného jako hodnota LSN. Chcete-li zajistit, aby byly načteny všechny položky potvrzené zadaným časem, zavolejte funkci sys.fn_cdc_map_time_to_lsna použijte možnost "největší menší než nebo rovno".
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í, aby se proces zachycení zpozdil, když se prostě neprojeví žádné nedávné změny procesu.
Š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ácenou v požadavku na všechny změny s start_lsn hodnotou cdc.lsn_time_mapping položky tabulky můžete vrátit tran_end_time spolu s daty změn, které změnu označí časem potvrzení transakce ve zdroji. Šablona Připojit čas potvrzení ke všem změnám sada výsledků ukazuje, jak provést toto spojení.
Spojení změn dat s jinými daty ze stejné transakce
Někdy je užitečné spojit data změn s dalšími informacemi shromážděnými o transakci při potvrzení ve 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átí všechny změny s daným tran_begin_lsn ukazuje, jak ovlivnit spojení.
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 příjemců poskytuje funkce change data capture uloženou proceduru sys.sp_cdc_generate_wrapper_function , která generuje skripty pro vytváření vlastních funkcí obálky pro funkce dotazu pro zachytávání dat změn. Tyto vlastní obálky umožňují vyjádřit interval dotazu jako pár datetime.
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ý je odvozený z názvu instance zachycení, a příkaz create pro uloženou proceduru obálky. Funkce, která zabalí všechny změny dotazu, se vždy vygeneruje.
@supports_net_changes Pokud byl parametr nastaven při vytvoření instance zachycení, funkce, která zabalí funkci net changes, se také vygeneruje.
Je zodpovědností návrháře aplikace volat uloženou proceduru generování skriptů, aby vygenerovala příkazy create pro uložené procedury obálky a aby spustil výsledné vytváření skriptů pro vytvoření funkcí. K tomu nedojde automaticky při vytvoření instance zachycení.
Obálky datetime vlastní uživatel a 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í.
Za názvem funkce, která zabalí všechny změny dotazu fn_all_changes_ , následuje název instance zachycení. 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é funkce obálky podporují následující konvenci pro systematicky procházenou časovou osu zachycení dat změn: Očekává se, že @end_time parametr předchozího intervalu se použije jako @start_time parametr následné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í položky s časem potvrzení rovnající se horní hranici intervalu extrakce se mají zahrnout do intervalu. Ve výchozím nastavení je horní mez zahrnuta.
I když se vygenerovaný dotaz TVF nezdaří, pokud zadáte hodnotu null pro @from_lsn hodnotu nebo @to_lsn hodnotu, funkce obálky datetime používají hodnotu null, aby obálky datetime vrátily všechny aktuální změny. To znamená, že pokud je hodnota null předána jako koncový bod okna dotazu do obálky datetime, použije se koncový bod doby platnosti instance zachycení v podkladovém SELECT příkazu použitém pro TVF dotazu. 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álky datetime naleznete v tématu sys.sp_cdc_generate_wrapper_function (Transact-SQL).
Šablona Vytvoří instanci obálky TVF s příznakem aktualizace ukazuje, jak přizpůsobit vygenerovanou funkci obálky pro přidání příznaku aktualizace pro zadaný sloupec do sady výsledků vrácené čistým dotazem změn. Šablona Vytvoří instanci CDC Wrapper TVFs pro schéma ukazuje, jak vytvořit instanci obálky Datetime pro TVF dotazu pro všechny instance zachycení 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 dotazovat na čisté změny pomocí funkce obálky, když je obálka nakonfigurována tak, aby vracela příznaky aktualizace. Možnost filtru řádků "all with mask" (vše s maskou) je vyžadována, aby základní funkce dotazu vrátila masku aktualizace, která není 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 použitím této funkce je přizpůsobit přechod mezi několika instancemi zachycení, když se jazyk 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ž dojde k přepnutí, můžete přehodit starou funkci obálky a novou s názvem vytvořeným, který odkazuje na nové funkce dotazu. 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.