Sdílet prostřednictvím

Sledování změn dat (SQL Server)

  • Článek

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed Instance

SQL Server poskytuje dvě funkce, které sledují změny dat v databázi: záznam dat změn a sledování změn. Tyto funkce umožňují aplikacím určit změny DML (operace vložení, aktualizace a odstranění), které byly provedeny v uživatelských tabulkách v databázi. U stejné databáze je možné povolit zachytávání a sledování změn; nejsou vyžadovány žádné zvláštní aspekty. Edice SQL Serveru, které podporují sledování změn a zachytávání dat změn, najdete v tématu Edice a podporované funkce SQL Serveru 2022.

Výhody používání zachytávání dat změn nebo sledování změn

Schopnost dotazovat se na data, která se změnila v databázi, je důležitým požadavkem, aby některé aplikace byly efektivní. Aby vývojáři aplikací mohli určit změny dat, musí ve svých aplikacích implementovat vlastní metodu sledování pomocí kombinace triggerů, časových razítek sloupců a dalších tabulek. Vytváření těchto aplikací obvykle zahrnuje spoustu práce na implementaci, vede k aktualizacím schématu a často nese vysokou režii na výkon.

Použití zachytávání dat změn nebo sledování změn v aplikacích ke sledování změn v databázi má místo vývoje vlastního řešení následující výhody:

  • Došlo ke zkrácení doby vývoje. Vzhledem k tomu, že funkce jsou dostupné na SQL Serveru, nemusíte vyvíjet vlastní řešení.

  • Změny schématu se nevyžadují. Nemusíte přidávat sloupce, přidávat triggery ani vytvářet boční tabulku, ve které chcete sledovat odstraněné řádky nebo ukládat informace o sledování změn, pokud sloupce nejde přidat do uživatelských tabulek.

  • K dispozici je integrovaný mechanismus čištění. Odstraňování sledovacích změn se provádí automaticky na pozadí. Vlastní vyčištění dat uložených v boční tabulce není povinné.

  • K získání informací o změnách jsou k dispozici funkce.

  • Operace DML mají nízkou režii. Synchronní sledování změn bude mít vždy určitou režii. Použití sledování změn ale může pomoct minimalizovat režii. Často bude překryvná náročnost nižší než u alternativních řešení, zejména u těch řešení, která vyžadují využití triggerů.

  • Sledování změn je založeno na potvrzených transakcích. Pořadí změn je založeno na době potvrzení transakce. To umožňuje získat spolehlivé výsledky, pokud existují dlouhotrvající a překrývající se transakce. Vlastní řešení, která používají časové razítko hodnoty, musí být navržena tak, aby tyto scénáře zvládla.

  • K dispozici jsou standardní nástroje, které můžete použít ke konfiguraci a správě. SQL Server poskytuje standardní příkazy DDL, SQL Server Management Studio, zobrazení katalogu a oprávnění zabezpečení.

Rozdíly mezi zachytáváním dat změn a sledováním změn

V následující tabulce jsou uvedeny rozdíly mezi zachycením dat změn a sledováním změn. Mechanismus sledování v zachytávání dat změn zahrnuje asynchronní zachycení změn z transakčního protokolu, aby byly změny k dispozici po operaci DML. Ve sledování změn mechanismus sledování zahrnuje synchronní sledování změn v souladu s operacemi DML, aby informace o změnách byly k dispozici okamžitě.

Funkce Změna zachytávání dat Sledování změn
sledované změny
Změny DML Ano Ano
sledované informace
Historická data Ano Ne
Zda byl sloupec změněn Ano Ano
Typ DML Ano Ano

Změna zachytávání dat

Zachytávání dat změn poskytuje pro uživatelskou tabulku historické informace o změnách tím, že zachytí jak skutečnost, že byly provedeny změny DML, tak i skutečná data, která byla změněna. Změny se zaznamenávají pomocí asynchronního procesu, který čte transakční protokol a má malý dopad na systém.

Jak je znázorněno na následujícím obrázku, změny provedené v uživatelských tabulkách jsou zachyceny v odpovídajících tabulkách změn. Tyto tabulky změn poskytují historické zobrazení změn v průběhu času. Funkce záznamu změn dat, které poskytuje SQL Server, umožňují snadno a systematicky využívat data o změnách.

diagram znázorňující koncept zachytávání dat změn

Model zabezpečení

Tato část popisuje model zabezpečení zachytávání dat změn.

Konfigurace a správa

Pokud chcete povolit nebo zakázat zachycování změn pro databázi, volající sys.sp_cdc_enable_db (Transact-SQL) nebo sys.sp_cdc_disable_db (Transact-SQL) musí být členem pevné role systému správce serveru . Povolení a zakázání zachytávání dat změn na úrovni tabulky vyžaduje volajícího sys.sp_cdc_enable_table (Transact-SQL) a sys.sp_cdc_disable_table (Transact-SQL) buď členem role správce systému, nebo členem databázové role db_owner databáze.

Použití uložených procedur pro podporu správy úloh zachytávání dat změn je omezeno na členy serveru správce systému role a členy role databáze db_owner.

Změna výčtu a dotazů na metadata

Pokud chcete získat přístup k datům změn přidruženým k instanci zachycení, musí být uživateli udělen přístup SELECT ke všem zachyceným sloupcům přidružené zdrojové tabulky. Kromě toho, pokud je při vytvoření instance zachycení zadána role gating, volající musí být také členem zadané role gating a schéma zachycení dat změn (cdc) musí mít přístup SELECT k roli gating.

Další obecné funkce pro zachycení změny dat pro přístup k metadatům budou přístupné všem uživatelům databáze prostřednictvím veřejné role, i když přístup k poskytnutým metadatům je obvykle omezován pomocí přístupu SELECT k podkladovým zdrojovým tabulkám a členstvím v jakýchkoli omezených rolích.

Operace DDL pro změnu zdrojových tabulek s povoleným zachycením dat

Pokud je tabulka povolena pro zachytávání změn dat, operace DDL lze použít pouze na tabulku členem pevné role serveru administrátor systému, členem role databáze db_ownernebo členem role databáze db_ddladmin. Uživatelům, kteří mají explicitní udělení oprávnění k provádění operací DDL v tabulce, se při pokusu o tyto operace zobrazí chyba 22914.

Důležité informace o datovém typu pro zachytávání dat změn

Zachytávání dat změn podporuje všechny základní typy sloupců. Následující tabulka uvádí chování a omezení pro několik typů sloupců.

Typ sloupce Změny zachycené v tabulkách změn Omezení
Řídké sloupce Ano Nepodporuje zachytávání změn při použití sady sloupců.
Počítané sloupce Ne Změny počítaných sloupců se nesledují. Sloupec se zobrazí v tabulce změn s odpovídajícím typem, ale bude mít hodnotu NULL.
XML Ano Změny jednotlivých elementů XML se nesledují.
Časová značka Ano Datový typ v tabulce změn se převede na binární.
Datové typy BLOB Ano Předchozí obrázek sloupce BLOB se uloží jenom v případě, že se samotný sloupec změní.

Integrace funkcí SQL Serveru

Tato část popisuje, jak následující funkce pracují se zachytáváním dat změn:

  • Zrcadlení databáze
  • Transakční replikace
  • Obnovení nebo připojení databáze

Zrcadlení databáze

Je možné zrcadlit databázi, která je konfigurovaná pro zachytávání změn dat. Aby zrcadlo automaticky zachytilo a uklidilo, postupujte takto:

  1. Ujistěte se, že na zrcadle běží agent SQL Serveru.

  2. Po převzetí do zrcadla vytvořte na zrcadle úlohu zachycení a úlohu vyčištění. K vytvoření úloh použijte uloženou proceduru sys.sp_cdc_add_job (Transact-SQL).

Další informace o zrcadlení databáze naleznete v tématu Zrcadlení databáze (SQL Server).

Transakční replikace

Zachytávání změn a transakční replikace můžou existovat ve stejné databázi, ale základní soubor tabulek změn se zpracovává jinak, když jsou povoleny obě funkce. Změny zachytávání dat a transakční replikace vždy používají stejný postup, sp_replcmds, ke čtení změn z transakčního protokolu. Pokud je záznam dat změn povolen samostatně, úloha agenta SQL Serveru volá sp_replcmds. Pokud jsou obě funkce povoleny ve stejné databázi, agent Log Reader volá sp_replcmds. Tento agent naplní tabulky změn i tabulky databáze distribution. Další informace najdete v Agentovi pro čtení protokolu replikace .

Představte si scénář, ve kterém je v databázi AdventureWorks2022 povolená zachytávání dat změn a pro zachycení jsou povolené dvě tabulky. Pro naplnění tabulek změn volá úloha zachycení sp_replcmds. Databáze je povolena pro transakční replikaci a vytvoří se publikace. Teď se pro databázi vytvoří agent Log Reader a úloha zachycení se odstraní. Agent Log Reader bude dále prohledávat protokol od posledního pořadového čísla protokolu, které bylo uloženo do tabulky změn. Tím zajistíte konzistenci dat v tabulkách změn. Pokud je v této databázi zakázaná transakční replikace, odebere se agent Log Reader a úloha zachytávání se znovu vytvoří.

Poznámka

Když se agent Log Reader používá pro zachytávání dat změn i transakční replikaci, replikované změny se nejprve zapíšou do distribution databáze. Zachycené změny se pak zapíšou do tabulek změn. Obě operace jsou zavázány společně. Pokud se do databáze distribution zapisuje nějaká latence, bude před zobrazením změn v tabulkách změn odpovídající latence.

Obnovení nebo připojení databáze s povoleným zachytáváním dat změn

SQL Server používá následující logiku k určení, jestli po obnovení nebo připojení databáze zůstane povolené zachytávání dat změn:

  • Pokud je databáze obnovena na stejný server se stejným názvem databáze, zůstane povolené zachytávání dat změn.

  • Pokud se databáze obnoví na jiný server, ve výchozím nastavení je zachytávání dat změn zakázané a všechna související metadata se odstraní.

    Pokud chcete zachovat zachytávání dat změn, použijte při obnovování databáze možnost KEEP_CDC. Další informace o této možnosti naleznete v tématu RESTORE.

  • Pokud je databáze odpojená a připojená ke stejnému serveru nebo jinému serveru, zůstane zachytávání dat změn povolené.

  • Pokud je databáze připojena nebo obnovena s možností KEEP_CDC k jakékoli jiné edici než Standard nebo Enterprise, operace se zablokuje, protože zachytávání dat změn vyžaduje edice SQL Server Standard nebo Enterprise. Zobrazí se chybová zpráva 932:

    SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

Pomocí sys.sp_cdc_disable_db můžete odebrat záznam změn dat z obnovené nebo připojené databáze.

Sledování změn

Sledování změn zachycuje skutečnost, že se změnily řádky v tabulce, ale nezachytává změněná data. To umožňuje aplikacím určit řádky, které se změnily, s nejnovějšími daty řádků získanými přímo z uživatelských tabulek. Sledování změn je proto omezenější v historických otázkách, na které může odpovědět v porovnání se zachytáváním dat změn. U aplikací, které nevyžadují historické informace, je ale mnohem menší režijní náklady na úložiště kvůli tomu, že se změněná data nezachytí. Ke sledování změn se používá synchronní mechanismus sledování. Tato funkce byla navržena s minimální zátěží pro operace DML.

Následující obrázek znázorňuje scénář synchronizace, který by byl přínosný pomocí sledování změn. Ve scénáři aplikace vyžaduje následující informace: všechny řádky v tabulce, které byly změněny od poslední synchronizace tabulky, a pouze data aktuálního řádku. Vzhledem k tomu, že synchronní mechanismus se používá ke sledování změn, může aplikace provádět obousměrnou synchronizaci a spolehlivě detekovat případné konflikty, ke kterým mohlo dojít.

diagram znázorňující koncept sledování změn

Sledování změn a synchronizační služby pro ADO.NET

Synchronizační služby pro ADO.NET umožňují synchronizaci mezi databázemi a poskytují intuitivní a flexibilní rozhraní API, které umožňuje vytvářet aplikace, které cílí na scénáře offline a spolupráce. Synchronizační služby pro ADO.NET poskytují rozhraní API pro synchronizaci změn, ale ve skutečnosti nesleduje změny v serverové nebo partnerské databázi. Můžete vytvořit vlastní systém sledování změn, ale obvykle to představuje významnou složitost a režii na výkon. Ke sledování změn v serverové nebo partnerské databázi doporučujeme použít sledování změn na SQL Serveru, protože je snadné nakonfigurovat a zajistit sledování vysokého výkonu.

Další informace o sledování změn a synchronizačních službách pro ADO.NET najdete na následujících odkazech:

Související obsah