Wat is gegevensvastlegging bij wijzigingen (CDC)?
van toepassing op:
SQL ServerAzure SQL Managed Instance
In dit artikel vindt u informatie over het vastleggen van wijzigingen in gegevensopname (CDC), waarmee activiteiten in een database worden vastgelegd wanneer tabellen en rijen zijn gewijzigd.
In dit artikel wordt uitgelegd hoe CDC werkt met SQL Server en Azure SQL Managed Instance. Zie voor Azure SQL Database CDC met Azure SQL Database.
Overzicht
Wijzigingsgegevens vastleggen maakt gebruik van de SQL Server Agent om invoegingen, updates en verwijderingen in een tabel te registreren. Het maakt deze gegevenswijzigingen dus toegankelijk om eenvoudig te worden gebruikt met behulp van een relationele indeling. De kolomgegevens en essentiële metagegevens die nodig zijn om deze wijzigingsgegevens op een doelomgeving toe te passen, worden vastgelegd voor de gewijzigde rijen en opgeslagen in wijzigingstabellen die de kolomstructuur van de bijgehouden brontabellen weerspiegelen. Bovendien zijn tabelwaardefuncties beschikbaar voor systematische toegang tot deze wijzigingsgegevens door consumenten.
Een goed voorbeeld van een gegevensgebruiker die deze technologiedoelen heeft, is een ETL-toepassing (extractie, transformatie en laden). Een ETL-toepassing laadt stap voor stap gewijzigde gegevens van SQL Server-brontabellen naar een datawarehouse of datamart. Hoewel de weergave van de brontabellen in het datawarehouse wijzigingen in de brontabellen moet weerspiegelen, is een end-to-end-technologie die een replica van de bron vernieuwt niet geschikt. In plaats daarvan hebt u een betrouwbare stroom van wijzigingsgegevens nodig die is gestructureerd, zodat consumenten deze kunnen toepassen op verschillende doelweergaven van de gegevens. Sql Server change data capture biedt deze technologie.
Gegevensstroom
In de volgende afbeelding ziet u de belangrijkste gegevensstroom voor het vastleggen van wijzigingsgegevens.
De bron van wijzigingsgegevens voor het vastleggen van wijzigingengegevens is het SQL Server-transactielogboek. Wanneer invoegingen, updates en verwijderingen worden toegepast op bijgehouden brontabellen, worden vermeldingen die deze wijzigingen beschrijven, toegevoegd aan het logboek. Het logboek fungeert als invoer voor het opnameproces. Vervolgens wordt het logboek gelezen en wordt informatie toegevoegd over wijzigingen in de gekoppelde wijzigingstabel van de bijgehouden tabel. Functies worden geleverd om de wijzigingen op te sommen die in de wijzigingstabellen worden weergegeven in een opgegeven bereik, waardoor de informatie wordt geretourneerd in de vorm van een gefilterde resultatenset. De gefilterde resultatenset wordt doorgaans gebruikt door een toepassingsproces om een weergave van de bron in een externe omgeving bij te werken.
Exemplaar vastleggen
Voordat wijzigingen in afzonderlijke tabellen in een database kunnen worden bijgehouden, moet wijzigingsgegevensopname expliciet worden ingeschakeld voor de database. Dit wordt gedaan met behulp van de opgeslagen procedure sys.sp_cdc_enable_db. Wanneer de database is ingeschakeld, kunnen brontabellen worden geïdentificeerd als bijgehouden tabellen met behulp van de opgeslagen procedure sys.sp_cdc_enable_table. Wanneer een tabel is ingeschakeld voor het vastleggen van wijzigingsgegevens, wordt er een gekoppeld exemplaar voor vastleggen gemaakt ter ondersteuning van de verspreiding van de wijzigingsgegevens in de brontabel. Het capture-exemplaar bestaat uit een wijzigingstabel en maximaal twee queryfuncties. Metagegevens die de configuratiedetails van het opname-exemplaar beschrijven, worden bewaard in de metagegevenstabellen voor het vastleggen van wijzigingengegevens cdc.change_tables, cdc.index_columnsen cdc.captured_columns. Deze informatie kan worden opgehaald met behulp van de opgeslagen procedure sys.sp_cdc_help_change_data_capture.
Alle objecten die zijn gekoppeld aan een opname-exemplaar, worden gemaakt in het schema voor het vastleggen van wijzigingengegevens van de ingeschakelde database. De vereisten voor de naam van het capture-exemplaar zijn dat het een geldige objectnaam is en dat het uniek is voor de database capture-exemplaren. De naam is standaard <schemanaam_tabelnaam> van de brontabel. De bijbehorende wijzigingstabel wordt benoemd door _CT toe te voegen aan de naam van het capture-exemplaar. De functie die wordt gebruikt om query's uit te voeren op alle wijzigingen, wordt benoemd door fn_cdc_get_all_changes_ vooraf te laten gaan aan de naam van het capture-exemplaar. Als het capture-exemplaar is geconfigureerd ter ondersteuning van netwijzigingen, wordt de net_changes queryfunctie ook gemaakt en benoemd door fn_cdc_get_net_changes_ vooraf te laten gaan aan de naam van het capture-exemplaar.
Belangrijk
Het maximum aantal capture-exemplaren dat gelijktijdig aan één brontabel kan worden gekoppeld, is twee.
Tabel wijzigen
De eerste vijf kolommen van een wijzigingsgegevensopnametabel zijn metagegevenskolommen. Deze bieden aanvullende informatie die relevant is voor de vastgelegde wijziging. De resterende kolommen weerspiegelen de geïdentificeerde opgenomen kolommen uit de brontabel in naam en, doorgaans, in type. Deze kolommen bevatten de vastgelegde kolomgegevens die zijn verzameld uit de brontabel.
Elke invoeg- of verwijderbewerking die wordt toegepast op een brontabel, wordt weergegeven als één rij in de wijzigingstabel. De gegevenskolommen van de rij die het resultaat zijn van een invoegbewerking bevatten de kolomwaarden na de invoegbewerking. De gegevenskolommen van de rij die het resultaat zijn van een verwijderbewerking bevatten de kolomwaarden vóór de verwijdering. Voor een updatebewerking is een invoer met één rij vereist om de kolomwaarden vóór de update te identificeren en een tweede rijvermelding om de kolomwaarden na de update te identificeren.
Elke rij in een wijzigingstabel bevat ook andere metagegevens om de wijzigingsactiviteit te interpreteren. De kolom __$start_lsn identificeert het volgnummer van het doorvoerlogboek (LSN) dat is toegewezen aan de wijziging. De doorvoer-LSN identificeert wijzigingen die zijn doorgevoerd binnen dezelfde transactie en ordent die transacties. De kolom __$seqval kan worden gebruikt om meer wijzigingen te bestellen die zich in dezelfde transactie voordoen. De kolom __$-bewerking registreert de bewerking die is gekoppeld aan de wijziging: 1 = verwijderen, 2 = invoegen, 3 = bijwerken (vóór afbeelding) en 4 = bijwerken (na afbeelding). De kolom __$update_mask is een variabele bitmasker met één gedefinieerde bit voor elke vastgelegde kolom. Voor het invoegen en verwijderen van vermeldingen is voor het updatemasker alle bits ingesteld. Bijwerken van rijen zal echter die bits hebben ingesteld die overeenkomen met de gewijzigde kolommen.
Geldigheidsinterval
Het geldigheidsinterval voor wijzigingsgegevens voor een database is de tijd waarin wijzigingsgegevens beschikbaar zijn voor het vastleggen van exemplaren. Het geldigheidsinterval begint wanneer de eerste capture-instantie voor een databasetabel wordt gemaakt en loopt door tot het heden.
Databank
Gegevens die in wijzigingstabellen worden opgeslagen, groeien onbeheersbaar als u de gegevens niet periodiek en systematisch verwijdert. Het opschoningsproces voor het vastleggen van wijzigingengegevens is verantwoordelijk voor het afdwingen van het op retentie gebaseerde opschoonbeleid. Eerst wordt het lage eindpunt van het geldigheidsinterval verplaatst om te voldoen aan de tijdsbeperking. Vervolgens worden verlopen wijzigingstabelvermeldingen verwijderd. Standaard worden drie dagen aan gegevens bewaard.
Aan de bovenkant, wanneer het vastleggingsproces elke nieuwe batch wijzigingsgegevens voltooit, worden nieuwe vermeldingen toegevoegd aan cdc.lsn_time_mapping voor elke transactie die wijzigingen in de tabel heeft. In de toewijzingstabel worden zowel een doorvoerlogboekreeksnummer (LSN) als een doorvoertijd (kolommen start_lsn en tran_end_time) bewaard. De maximale LSN-waarde die in cdc.lsn_time_mapping wordt gevonden, vertegenwoordigt het hoge waterteken van het geldigheidsvenster van de database. De bijbehorende committijd wordt gebruikt als basis van waaruit retentie-gebaseerde opschoning een nieuw laag watermerk berekent.
Omdat met het opnameproces wijzigingsgegevens uit het transactielogboek worden geëxtraheerd, is er een ingebouwde latentie tussen de tijd dat een wijziging wordt doorgevoerd in een brontabel en de tijd waarop de wijziging wordt weergegeven in de bijbehorende wijzigingstabel. Hoewel deze latentie doorgaans klein is, is het echter belangrijk om te onthouden dat wijzigingsgegevens pas beschikbaar zijn als het opnameproces de gerelateerde logboekvermeldingen heeft verwerkt.
Exemplaar vastleggen
Hoewel het gebruikelijk is dat het geldigheidsinterval van de database en het geldigheidsinterval van het afzonderlijke capture-exemplaar overeenkomen, is dit echter niet altijd waar. Het geldigheidsinterval van het capture-exemplaar wordt gestart wanneer het opnameproces de opname-instantie herkent en begint met het vastleggen van gekoppelde wijzigingen in de wijzigingstabel. Als er dus op verschillende momenten opslaginstanties worden gecreëerd, zal elk een ander laag eindpunt hebben. De kolom start_lsn van de resultatenset die wordt geretourneerd door sys.sp_cdc_help_change_data_capture toont de huidige ondergrens voor elke gedefinieerde capture-instantie. Wanneer tijdens het opschoningsproces tabelvermeldingen worden opgeschoond, worden de start_lsn-waarden voor alle capture-exemplaren aangepast aan de nieuwe ondergrens voor beschikbare wijzigingsgegevens. Alleen die instances waarbij de start_lsn waarden momenteel lager zijn dan het nieuwe lagere watermerk, worden aangepast. Als er na verloop van tijd geen nieuwe capture-exemplaren worden gemaakt, vallen de geldigheidsintervallen voor alle afzonderlijke exemplaren meestal samen met het geldigheidsinterval van de database.
Het geldigheidsinterval is belangrijk voor gebruikers van wijzigingsgegevens, omdat het extractieinterval voor een aanvraag volledig moet worden gedekt door het huidige geldigheidsinterval voor wijzigingsgegevens voor de opname-instantie. Als het lage eindpunt van het extractie-interval links van het lage eindpunt van het geldigheidsinterval staat, kunnen er wijzigingsgegevens ontbreken vanwege agressieve opschoning. Als het hoge eindpunt van het extractieinterval rechts van het hoge eindpunt van het geldigheidsinterval ligt, geeft dit aan dat het opnameproces nog niet is verwerkt door de tijd die wordt vertegenwoordigd door het extractieinterval en dat er ook wijzigingsgegevens ontbreken.
De functie sys.fn_cdc_get_min_lsn wordt gebruikt om de huidige minimale LSN voor een capture-exemplaar op te halen, terwijl sys.fn_cdc_get_max_lsn wordt gebruikt om de huidige maximale LSN-waarde op te halen. Wanneer u een query uitvoert op de wijzigingsgegevens, als het opgegeven LSN-bereik zich niet binnen deze twee LSN-waarden bevindt, mislukken de queryfuncties voor het vastleggen van wijzigingen in gegevens.
Wijzigingen in de brontabel verwerken
Het aanpassen van kolomwijzigingen in de brontabellen die worden bijgehouden, is een moeilijk probleem voor downstreamgebruikers. Hoewel het inschakelen van wijzigingsgegevens vastleggen in een brontabel niet voorkomt dat dergelijke DDL-wijzigingen optreden, vermindert wijzigingsgegevens vastleggen het effect op consumenten door de geleverde resultatensets te behouden die via de API worden geretourneerd, zelfs als de kolomstructuur van de onderliggende brontabel verandert. Deze vaste kolomstructuur wordt ook weergegeven in de onderliggende wijzigingstabel waartoe de gedefinieerde queryfuncties toegang hebben.
Het opnameproces dat verantwoordelijk is voor het invullen van de wijzigingstabel, is geschikt voor een tabel met een vaste kolomstructuur door nieuwe kolommen te negeren die niet zijn geïdentificeerd voor vastleggen wanneer de brontabel is ingeschakeld voor het vastleggen van wijzigingen. Als een bijgehouden kolom wordt verwijderd, worden null-waarden opgegeven voor de kolom in de volgende wijzigingsvermeldingen. Als een bestaande kolom echter een wijziging in het gegevenstype ondergaat, wordt de wijziging doorgegeven aan de wijzigingstabel om ervoor te zorgen dat het opnamemechanisme geen gegevensverlies voor bijgehouden kolommen introduceert. Tijdens het opnameproces worden ook gedetecteerde wijzigingen in de kolomstructuur van bijgehouden tabellen in de cdc.ddl_history tabel geplaatst. Gebruikers die willen worden gewaarschuwd voor aanpassingen die mogelijk moeten worden aangebracht in downstream-toepassingen, gebruiken de opgeslagen procedure sys.sp_cdc_get_ddl_history.
Doorgaans behoudt het huidige opnameexemplaar zijn vorm wanneer DDL-wijzigingen worden toegepast op de bijbehorende brontabel. Het is echter mogelijk om een tweede capture-exemplaar te maken voor de tabel die de nieuwe kolomstructuur weerspiegelt. Met deze optie kan het opnameproces wijzigingen aanbrengen in dezelfde brontabel in twee afzonderlijke wijzigingstabellen met twee verschillende kolomstructuren. Hoewel één wijzigingstabel de huidige operationele programma's kan blijven invoeren, kan de tweede een ontwikkelomgeving stimuleren die de nieuwe kolomgegevens probeert op te nemen. Wanneer het opnamemechanisme beide wijzigingstabellen tegelijkertijd invult, kan een overgang van de ene naar de andere zonder verlies van wijzigingsgegevens plaatsvinden. Dit kan gebeuren wanneer de twee wijzigingsgegevens tijdlijnen overlappen. Wanneer de overgang wordt beïnvloed, kan het verouderde capture-exemplaar worden verwijderd.
Belangrijk
Het maximum aantal capture-exemplaren dat gelijktijdig aan één brontabel kan worden gekoppeld, is twee.
Relatie met logboeklezeragent
De logica voor het vastleggen van wijzigingengegevens wordt ingesloten in de opgeslagen procedure sp_replcmds, een interne serverfunctie die is gebouwd als onderdeel van sqlservr.exe en ook wordt gebruikt door transactionele replicatie om wijzigingen uit het transactielogboek te verzamelen. In SQL Server en Azure SQL Managed Instance maakt u, wanneer het vastleggen van gegevens alleen voor een database is ingeschakeld, de SQL Server Agent-capturetaak voor wijzigingsgegevens als het voertuig voor het aanroepen van sp_replcmds. Wanneer replicatie ook aanwezig is, wordt de transactionele logboeklezer alleen gebruikt om te voldoen aan de behoeften van wijzigingsgegevens voor beide consumenten. Deze strategie vermindert het aantal logboekconflicten aanzienlijk wanneer zowel replicatie als wijzigingsgegevens vastleggen zijn ingeschakeld voor dezelfde database.
De wisseling tussen deze twee operationele modi voor het vastleggen van wijzigingsgegevens gebeurt automatisch wanneer er een wijziging optreedt in de replicatiestatus van een database waarop Change Data Capture is ingeschakeld.
Notitie
In SQL Server en Azure SQL Managed Instance moet SQL Server Agent actief zijn om beide exemplaren van de opnamelogica uit te voeren.
De belangrijkste taak van het opnameproces is het scannen van het logboek en het schrijven van kolomgegevens en transactiegerelateerde informatie naar de wijzigingentabellen voor gegevensvastlegging. Om ervoor te zorgen dat er een transactie-consistente grens is tussen alle wijzigingentabellen die worden gevuld voor het vastleggen van gegevens, opent en voltooit het verzamelproces zijn eigen transactie bij elke scancyclus. Er wordt gedetecteerd wanneer tabellen nieuw zijn ingeschakeld voor het vastleggen van wijzigingengegevens en deze automatisch bevat in de set tabellen die actief worden gecontroleerd op wijzigingsvermeldingen in het logboek. Op dezelfde manier wordt ook gedetecteerd dat het vastleggen van wijzigingsgegevens wordt uitgeschakeld, waardoor de brontabel wordt verwijderd uit de set tabellen die actief worden bewaakt op wijzigingsgegevens. Wanneer de verwerking van een sectie van het logboek is voltooid, geeft het opnameproces de logica voor het afkappen van het serverlogboek aan, die deze informatie gebruikt om logboekvermeldingen te identificeren die in aanmerking komen voor afkapping.
Belangrijk
Wanneer een database is ingeschakeld voor het vastleggen van wijzigingsgegevens, zelfs als de herstelmodus is ingesteld op eenvoudig herstel, gaat het afkappingspunt van het logboek pas verder totdat alle wijzigingen die zijn gemarkeerd voor vastleggen zijn verzameld door het opnameproces. Als het opnameproces niet wordt uitgevoerd en er wijzigingen moeten worden verzameld, wordt het logboek niet afgekapt door CHECKPOINT uit te voeren.
Het opnameproces wordt ook gebruikt voor het bijhouden van de geschiedenis van de DDL-wijzigingen in bijgehouden tabellen. De DDL-instructies die zijn gekoppeld aan wijzigingsgegevensopname maken vermeldingen in het transactielogboek van de database wanneer een database of tabel voor het vastleggen van wijzigingen wordt verwijderd of kolommen van een tabel met wijzigingsgegevens worden toegevoegd, gewijzigd of verwijderd. Deze logboekvermeldingen worden verwerkt door het opnameproces, waarna de bijbehorende DDL-gebeurtenissen worden geplaatst in de cdc.ddl_history tabel. U kunt informatie verkrijgen over DDL-gebeurtenissen die van invloed zijn op bijgehouden tabellen met behulp van de opgeslagen procedure sys.sp_cdc_get_ddl_history.
Waarschuwing
- MaxCmdsInTran is niet ontworpen om altijd ingeschakeld te zijn. Het bestaat om situaties te omzeilen waarbij iemand per ongeluk een groot aantal DML-bewerkingen in één transactie heeft uitgevoerd (waardoor de distributie van opdrachten wordt vertraagd totdat de hele transactie zich in de distributiedatabase bevindt, vergrendelingen worden bewaard, enzovoort). Als u regelmatig in deze situatie valt, controleert u uw toepassingslogica om manieren te vinden om de transactiegrootte te verkleinen.
- MaxCmdsInTran- wordt niet ondersteund als zowel CDC als replicatie is ingeschakeld voor de opgegeven publicatiedatabase. Het gebruik van MaxCmdsInTran- in deze configuratie kan leiden tot gegevensverlies in CDC-wijzigingstabellen. Het kan ook PK-fouten veroorzaken als de MaxCmdsInTran- parameter wordt toegevoegd en verwijderd tijdens het repliceren van een grote transactie.
Agentenbanen
Twee SQL Server Agent-taken worden meestal gekoppeld aan een database waarvoor wijzigingsgegevens zijn vastgelegd: een database die wordt gebruikt om de databasewijzigingstabellen te vullen en een die verantwoordelijk is voor het opschonen van de tabel. Beide taken bestaan uit één stap waarmee een Transact-SQL opdracht wordt uitgevoerd. De Transact-SQL-opdracht die wordt aangeroepen, is een opgeslagen procedure voor het vastleggen van wijzigingengegevens waarmee de logica van de taak wordt geïmplementeerd. De taken worden gemaakt wanneer de eerste tabel van de database is ingeschakeld voor het vastleggen van wijzigingsgegevens. De schoonmaaktaak wordt altijd aangemaakt. De capture-taak wordt alleen gemaakt als er geen gedefinieerde transactionele publicaties voor de database zijn. De opnametaak wordt ook gemaakt wanneer zowel wijzigingsgegevens vastleggen als transactionele replicatie zijn ingeschakeld voor een database en de transactionele logboeklezertaak wordt verwijderd omdat de database geen publicaties meer heeft gedefinieerd.
Zowel de opname- als opschoontaken worden gemaakt met behulp van standaardparameters. De capture-taak wordt onmiddellijk gestart. Het wordt continu uitgevoerd, waarbij maximaal 1000 transacties per scancyclus worden verwerkt met een wachttijd van 5 seconden tussen cycli. De opschoontaak wordt dagelijks om 2:00 uur uitgevoerd. Wijzigingstabelvermeldingen worden gedurende 4320 minuten of drie dagen bewaard, waarbij maximaal 5000 vermeldingen met één verwijderopdracht worden verwijderd.
De agenttaken voor het vastleggen van wijzigingsgegevens worden verwijderd wanneer wijzigingsgegevens vastlegging voor een database is uitgeschakeld. De opnametaak kan ook worden verwijderd wanneer de eerste publicatie wordt toegevoegd aan een database en zowel wijzigingsgegevens vastleggen als transactionele replicatie zijn ingeschakeld.
Intern worden agenttaken voor het vastleggen van gegevens gemaakt en verwijderd met behulp van respectievelijk de opgeslagen procedures sys.sp_cdc_add_job en sys.sp_cdc_drop_job. Deze opgeslagen procedures worden ook weergegeven, zodat beheerders het maken en verwijderen van deze taken kunnen beheren.
Een beheerder heeft geen expliciete controle over de standaardconfiguratie van de agenttaken voor het vastleggen van wijzigingengegevens. De opgeslagen procedure sys.sp_cdc_change_job wordt verstrekt om toe te staan dat de standaardconfiguratieparameters worden gewijzigd. Bovendien kan met de opgeslagen procedure sys.sp_cdc_help_jobs huidige configuratieparameters worden weergegeven. Zowel de capture-taak als de opschoontaak extraheert configuratieparameters uit de tabel msdb.dbo.cdc_jobs bij het opstarten. Wijzigingen die zijn aangebracht in deze waarden met behulp van sys.sp_cdc_change_job worden pas van kracht nadat de taak is gestopt en opnieuw is gestart.
Er zijn twee andere opgeslagen procedures beschikbaar om de agenttaken voor het vastleggen van wijzigingen in gegevens te starten en te stoppen: sys.sp_cdc_start_job en sys.sp_cdc_stop_job.
Notitie
Het starten en stoppen van de opnametaak leidt niet tot verlies van wijzigingsgegevens. Hiermee voorkomt u dat het opnameproces het logboek actief scant op wijzigingsvermeldingen die in de wijzigingstabellen moeten worden opgeslagen. Een redelijke strategie om te voorkomen dat logboekscans belasting toevoegen tijdens perioden van piekvraag is door de opnametaak te stoppen en opnieuw te starten wanneer de vraag wordt verminderd.
Beide SQL Server Agent-taken zijn zo ontworpen dat ze voldoende flexibiliteit en configuratiemogelijkheden bieden om aan de basisbehoeften van omgevingen voor gegevensregistratie te voldoen. In beide gevallen zijn echter de onderliggende opgeslagen procedures die de kernfunctionaliteit bieden beschikbaar gemaakt, zodat verdere aanpassing mogelijk is.
Change data capture kan niet goed functioneren wanneer de Database Engine-service of de SQL Server Agent-service wordt uitgevoerd onder het NETWORK SERVICE-account. Dit kan leiden tot fout 22832.
Interoperabiliteit met andere functies
Wijzigingsgegevens vastleggen heeft enkele beperkingen bij het werken met andere SQL Server-functies. Bekijk Interoperabiliteits- voor meer informatie.
Bekende problemen
Raadpleeg Bekende problemen met CDC-voor bekende problemen en fouten die zijn gekoppeld aan het vastleggen van wijzigingsgegevens.