Wijzigingsgegevens vastleggen, beheren en bewaken

van toepassing op:SQL ServerAzure SQL Managed Instance

In dit onderwerp wordt beschreven hoe u het vastleggen van wijzigingsgegevens voor SQL Server en Azure SQL Managed Instance beheert en bewaakt.

Zie CDC met Azure SQL Databasevoor Azure SQL Database, dat gebruikmaakt van een ander taakmechanisme.

Capture-taak

De capture-taak wordt gestart door de opgeslagen procedure zonder parameters uit te voeren sp_MScdc_capture_job. Deze opgeslagen procedure begint met het extraheren van de geconfigureerde waarden voor maxtrans, maxscans, continuousen pollinginterval voor de opnametaak uit msdb.dbo.cdc_jobs. Deze geconfigureerde waarden worden vervolgens doorgegeven als parameters aan de opgeslagen procedure sp_cdc_scan. Dit wordt gebruikt om sp_replcmds aan te roepen om de logboekscan uit te voeren.

Taakparameters vastleggen

Als u inzicht wilt krijgen in het gedrag van capture-taken, moet u begrijpen hoe de configureerbare parameters worden gebruikt door sp_cdc_scan.

parameter maxtrans

De parameter maxtrans geeft het maximum aantal transacties op dat in één scancyclus van het logboek kan worden verwerkt. Als tijdens de scan het aantal transacties dat moet worden verwerkt deze limiet bereikt, worden er geen extra transacties opgenomen in de huidige scan. Nadat een scancyclus is voltooid, is het aantal transacties dat is verwerkt altijd kleiner dan of gelijk aan maxtrans.

maxscans-parameter

De parameter maxscans geeft het maximum aantal scancycli op dat wordt geprobeerd om het logboek leeg te maken voordat het wordt geretourneerd (continue = 0) of het uitvoeren van een wachttijd (continue = 1).

parameter continuous

De continuous parameter bepaalt of sp_cdc_scan de controle overdraagt na het leegmaken van het logboek of het uitvoeren van het maximale aantal scancycli (eenmalige modus). Ook wordt bepaald of sp_cdc_scan wordt uitgevoerd totdat deze expliciet is gestopt (continue modus).

Modus met één opname

In de modus met één opname vraagt de capture-taak sp_cdc_scan om maximaal maxtrans scans uit te voeren om het logboek leeg te maken en terug te keren. Transacties naast maxtrans die aanwezig zijn in het logboek, worden later verwerkt in scans.

De modus Met één opname wordt gebruikt in gecontroleerde tests, waarbij het volume van transacties dat moet worden verwerkt bekend is en er voordelen zijn ten opzichte van het feit dat de taak automatisch wordt gesloten wanneer deze is voltooid. De modus met één opname wordt niet aanbevolen voor productiegebruik. Dit komt doordat het afhankelijk is van het taakschema om te beheren hoe vaak de scancyclus wordt uitgevoerd.

Wanneer u in de modus met één opname werkt, kunt u een bovengrens berekenen voor de verwachte doorvoer van de vastlegtaak en druk dit uit in transacties per seconde met behulp van de volgende berekening:

(maxtrans * maxscans) / number of seconds between scans

Zelfs als de tijd die nodig is om het logboek te scannen en de wijzigingstabellen te vullen niet aanzienlijk verschilt van 0, kan de gemiddelde doorvoer van de taak niet groter zijn dan de waarde die is verkregen door de maximaal toegestane transacties voor één scan te delen, vermenigvuldigd met het maximum aantal toegestane scans met het aantal seconden dat logboekverwerking scheidt.

Als de modus met één opname zou worden gebruikt om logboekscans te reguleren, moet het aantal seconden tussen logboekverwerking worden bepaald door de taakplanning. Wanneer dit soort gedrag gewenst is, is het uitvoeren van de opnametaak in de continue modus een betere manier om de logboekscan opnieuw te plannen.

Continue modus en polling-interval

In continue modus vraagt de capture-taak dat sp_cdc_scan continu moet worden uitgevoerd. Hierdoor kan de opgeslagen procedure een eigen wachtlus beheren door niet alleen op te geven voor maxtrans en maxscans, maar ook een waarde voor het aantal seconden tussen logboekverwerking (het polling-interval). In continue modus blijft de capture-taak actief en wordt er een WAITFOR uitgevoerd tussen het scannen van logboeken.

Notitie

Wanneer de waarde van het polling-interval groter is dan 0, is dezelfde bovengrens voor de doorvoer voor de terugkerende eenmalige taak ook van toepassing op de taakbewerking in de continue modus. Dat wil gezegd: (maxtrans * maxscans) gedeeld door een niet-nul polling-interval zal een bovengrens stellen voor het gemiddelde aantal transacties dat door de capture-taak kan worden verwerkt.

Aanpassing van taak vastleggen

Voor de capture-taak kunt u aanvullende logica toepassen om te bepalen of een nieuwe scan onmiddellijk begint of of er een slaapstand wordt opgelegd voordat een nieuwe scan wordt gestart in plaats van te vertrouwen op een vast polling-interval. De keuze kan worden gemaakt op basis van het tijdstip van de dag, bijvoorbeeld door het afdwingen van zeer lange slaapstanden tijdens piekactiviteitstijden, en zelfs overstappen op een polling-interval van 0 aan het einde van de dag wanneer het belangrijk is om de dagelijkse verwerking te voltooien en zich voor te bereiden op nachtelijke processen. De voortgang van het captureproces kan ook worden gemonitord om te bepalen wanneer alle transacties die vóór middernacht zijn voltooid, zijn gescand en in wijzigingentabellen zijn opgeslagen. Hierdoor kan de capture-taak worden beëindigd en opnieuw worden opgestart door een geplande dagelijkse herstart. Als u het gedrag wilt aanpassen, kunt u de taakstap die sp_cdc_scan aanroept, vervangen met een door de gebruiker geschreven wrapper voor sp_cdc_scan.

Opschoningstaak

Deze sectie bevat informatie over de werking van de opschoningstaak voor het vastleggen van wijzigingsgegevens.

Structuur van de opschoontaak

Change data capture gebruikt een op retentie gebaseerde opschoonstrategie om de grootte van de veranderingentabel te beheren. In SQL Server en Azure SQL Managed Instance bestaat het opschoonmechanisme uit een SQL Server Agent-Transact-SQL taak die wordt gemaakt wanneer de eerste databasetabel is ingeschakeld. Eén opschoningstaak verwerkt opschoning voor alle tabellen voor databasewijziging en past dezelfde retentiewaarde toe op alle gedefinieerde capture-exemplaren.

De opschoontaak wordt gestart door de parameterloze opgeslagen procedure sp_MScdc_cleanup_jobuit te voeren. Deze opgeslagen procedure begint met het extraheren van de geconfigureerde retentie- en drempelwaarden voor de opschoontaak uit msdb.dbo.cdc_jobs. De retentiewaarde wordt gebruikt om een nieuw laag watermerk voor de wijzigingstabellen te berekenen. Het opgegeven aantal minuten wordt afgetrokken van de maximumwaarde tran_end_time uit de tabel cdc.lsn_time_mapping om de nieuwe lage watermarkering te verkrijgen die wordt uitgedrukt als een datum/tijd-waarde. De CDC.lsn_time_mapping tabel wordt vervolgens gebruikt om deze datum/tijd-waarde te converteren naar een overeenkomende lsn waarde. Als dezelfde doorvoertijd wordt gedeeld door meerdere vermeldingen in de tabel, wordt de lsn die overeenkomt met de vermelding met de kleinste lsn gekozen als het nieuwe lage watermerk. Deze lsn waarde wordt doorgegeven aan sp_cdc_cleanup_change_tables om tabelvermeldingen uit de databasewijzigingstabellen te verwijderen.

Notitie

Het voordeel van het gebruik van de doorvoertijd van de recente transactie als basis voor het berekenen van het nieuwe lage watermerk is dat de wijzigingen gedurende de opgegeven tijd in wijzigingstabellen kunnen blijven. Dit gebeurt zelfs wanneer het opnameproces achterloopt. Alle vermeldingen met dezelfde committijd als het huidige lage watermerk blijven worden weergegeven in de wijzigingstabellen door het kleinste lsn te kiezen dat de gedeelde committijd heeft voor het daadwerkelijke lage watermerk.

Wanneer een opschoning wordt uitgevoerd, wordt het lage watermerk voor alle capture-instanties aanvankelijk bijgewerkt in één transactie. Vervolgens wordt geprobeerd verouderde vermeldingen uit de wijzigingstabellen en de cdc.lsn_time_mapping tabel te verwijderen. De configureerbare drempelwaarde beperkt het aantal vermeldingen dat in één instructie wordt verwijderd. Als u de verwijdering niet op een afzonderlijke tabel uitvoert, wordt niet voorkomen dat de bewerking wordt uitgevoerd op de resterende tabellen.

Aanpassen van opschoonwerk

Voor de opschoontaak liggen de aanpassingsmogelijkheden in de strategie die wordt gebruikt om te bepalen welke vermeldingen in de tabel moeten worden verwijderd. De enige ondersteunde strategie in de geleverde opschoontaak is een op tijd gebaseerde strategie. In dat geval wordt het nieuwe lage watermerk berekend door de toegestane bewaarperiode af te trekken van de doorvoertijd van de laatste transactie die is verwerkt. Omdat de onderliggende opschoonprocedures zijn gebaseerd op lsn in plaats van tijd, kan een willekeurig aantal strategieën worden gebruikt om de kleinste lsn te bepalen die in de wijzigingstabellen moeten worden bewaard. Slechts enkele hiervan zijn strikt op tijd gebaseerd. Kennis over de clients kan bijvoorbeeld worden gebruikt om een failsafe te bieden als downstreamprocessen waarvoor toegang tot de wijzigingstabellen nodig is, niet kunnen worden uitgevoerd. Ook, hoewel met de standaardstrategie dezelfde lsn wordt toegepast om de wijzigingentabellen van alle databases op te schonen, kan de onderliggende opschoonprocedure ook worden aangeroepen om op het niveau van het capture-exemplaar op te schonen.

Het proces bewaken

Door het proces voor het vastleggen van wijzigingengegevens te bewaken, kunt u bepalen of wijzigingen correct worden geschreven en met een redelijke latentie voor de wijzigingstabellen. Bewaking kan u ook helpen bij het identificeren van eventuele fouten die kunnen optreden. SQL Server bevat twee dynamische beheerviews waarmee u de vastlegging van wijzigingsgegevens kunt bewaken: sys.dm_cdc_log_scan_sessions en sys.dm_cdc_errors.

Sessies met lege resultatensets identificeren

Elke rij in sys.dm_cdc_log_scan_sessions vertegenwoordigt een logboekscansessie (behalve de rij met een id van 0). Een logboekscansessie is gelijk aan één uitvoering van sp_cdc_scan. Tijdens een sessie kan de scan wijzigingen retourneren of een leeg resultaat retourneren. Als de resultatenset leeg is, wordt de kolom empty_scan_count in sys.dm_cdc_log_scan_sessions ingesteld op 1. Als er opeenvolgende lege resultatensets zijn, bijvoorbeeld als de capture-taak continu wordt uitgevoerd, wordt de empty_scan_count in de laatste bestaande rij verhoogd. Als sys.dm_cdc_log_scan_sessions bijvoorbeeld al 10 rijen bevat voor scans die wijzigingen hebben geretourneerd en er vijf lege resultaten in een rij staan, bevat de weergave 11 rijen. De laatste rij heeft een waarde van 5 in de kolom empty_scan_count. Voer de volgende query uit om te bepalen welke sessies een lege scan hebben:

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

Latentie bepalen

De sys.dm_cdc_log_scan_sessions-beheerweergave bevat een kolom waarin de latentie voor elke opnamesessie wordt vastgelegd. Latentie wordt gedefinieerd als de verstreken tijd tussen een transactie die wordt doorgevoerd in een brontabel en de laatste vastgelegde transactie die wordt doorgevoerd in de wijzigingstabel. De latentiekolom wordt alleen ingevuld voor actieve sessies. Voor sessies met een waarde die groter is dan 0 in de kolom empty_scan_count, wordt de latentiekolom ingesteld op 0. De volgende query retourneert de gemiddelde latentie voor de meest recente sessies:

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

U kunt latentiegegevens gebruiken om te bepalen hoe snel of traag het opnameproces transacties verwerkt. Deze gegevens zijn het handigst wanneer het opnameproces continu wordt uitgevoerd. Als het opnameproces volgens een schema wordt uitgevoerd, kan latentie hoog zijn vanwege de vertraging tussen transacties die worden doorgevoerd in de brontabel en het opnameproces dat wordt uitgevoerd op het geplande tijdstip.

Een andere belangrijke maatstaf voor de efficiëntie van het capture-proces is de doorvoer. Dit is het gemiddelde aantal opdrachten per seconde dat tijdens elke sessie wordt verwerkt. Als u de doorvoer van een sessie wilt bepalen, deelt u de waarde in de kolom command_count door de waarde in de duurkolom. De volgende query retourneert de gemiddelde doorvoer voor de meest recente sessies:

SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

Gegevensverzamelaar gebruiken om steekproeven te verzamelen

Met de SQL Server-gegevensverzamelaar kunt u momentopnamen van gegevens verzamelen uit elke tabel- of dynamische beheerweergave en een prestatiedatawarehouse bouwen. Wanneer wijzigingsgegevens vastleggen is ingeschakeld voor een database, is het handig om momentopnamen van de sys.dm_cdc_log_scan_sessions weergave en de sys.dm_cdc_errors weergave met regelmatige tussenpozen te maken voor latere analyse. Met de volgende procedure stelt u een gegevensverzamelaar in voor het verzamelen van voorbeeldgegevens uit de sys.dm_cdc_log_scan_sessions-beheerweergave.

Gegevensverzameling configureren

1. Gegevensverzamelaar inschakelen en een beheerdatawarehouse configureren. Zie Gegevensverzameling beherenvoor meer informatie.

2. Voer de volgende code uit om een aangepaste collector te maken voor het vastleggen van wijzigingengegevens.

   USE msdb;  
   
   DECLARE @schedule_uid uniqueidentifier;  
   
   -- Collect and upload data every 5 minutes  
   SELECT @schedule_uid = (  
   SELECT schedule_uid from sysschedules_localserver_view
   WHERE name = N'CollectorSchedule_Every_5min')  
   
   DECLARE @collection_set_id int;  
   
   EXEC dbo.sp_syscollector_create_collection_set  
   @name = N' CDC Performance Data Collector',  
   @schedule_uid = @schedule_uid,
   @collection_mode = 0,
   @days_until_expiration = 30,
   @description = N'This collection set collects CDC metadata',  
   @collection_set_id = @collection_set_id output;  
   
   -- Create a collection item using statistics from
   -- the change data capture dynamic management view.  
   DECLARE @parameters xml;  
   DECLARE @collection_item_id int;  
   
   SELECT @parameters = CONVERT(xml,
       N'<TSQLQueryCollector>  
           <Query>  
             <Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value>  
             <OutputTable>cdc_log_scan_data</OutputTable>  
           </Query>  
         </TSQLQueryCollector>');  
   
   EXEC dbo.sp_syscollector_create_collection_item  
   @collection_set_id = @collection_set_id,  
   @collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',  
   @name = ' CDC Performance Data Collector',  
   @frequency = 5,
   @parameters = @parameters,  
   @collection_item_id = @collection_item_id output;
   
   GO  
   

3. Vouw in SQL Server Management Studio Managementuit en vouw gegevensverzamelinguit. Klik met de rechtermuisknop op CDC Performance Data Collectoren klik vervolgens op Gegevensverzameling starten.

4. Zoek in het in stap 1 geconfigureerde datawarehouse de tabel custom_snapshots.cdc_log_scan_data op. Deze tabel biedt een historische momentopname van gegevens uit logboekscansessies. Deze gegevens kunnen worden gebruikt om latentie, doorvoer en andere prestatiemetingen in de loop van de tijd te analyseren.

Script-upgrademodus

Wanneer u cumulatieve updates of servicepacks toepast op een exemplaar, kan het exemplaar bij het opnieuw opstarten de scriptupgrademodus inschakelen. In deze modus kan SQL Server een stap uitvoeren om interne CDC-tabellen te analyseren en bij te werken, wat kan leiden tot het opnieuw maken van objecten, zoals indexen in opnametabellen. Afhankelijk van de hoeveelheid gegevens die nodig is, kan deze stap enige tijd duren of kan een hoog transactielogboekgebruik veroorzaken voor ingeschakelde CDC-databases.

Verwante inhoud

• CDC met Azure SQL Database
• Gegevenswijzigingen bijhouden (SQL Server)
• Over het vastleggen van gegevenswijzigingen (SQL Server)
• In- en uitschakelen van change data capture (SQL Server)
• Werken met Gewijzigde Gegevens (SQL Server)