Vad är ändringsdatainsamling (CDC)?

Gäller för:SQL ServerAzure SQL Managed Instance

I den här artikeln får du lära dig mer om CDC (Change Data Capture), som registrerar aktivitet i en databas när tabeller och rader har ändrats.

Den här artikeln förklarar hur CDC fungerar med SQL Server och Azure SQL Managed Instance. Information om Azure SQL Database finns i CDC med Azure SQL Database.

Översikt

Ändringsdatainsamling använder SQL Server-agenten för att logga infogningar, uppdateringar och borttagningar som inträffar i en tabell. Det gör därför dessa dataändringar tillgängliga för att enkelt kunna användas med hjälp av ett relationsformat. Kolumndata och viktiga metadata, som behövs för att tillämpa dessa ändringsdata på en målmiljö, samlas in för de ändrade raderna och lagras i tabeller som speglar kolumnstrukturen i de spårade källtabellerna. Dessutom finns tabellvärdesfunktioner tillgängliga för systematisk åtkomst till dessa ändringsdata från konsumenter.

Ett bra exempel på en datakonsument som den här tekniken riktar sig mot är ett extraherings-, transformerings- och inläsningsprogram (ETL). Ett ETL-program läser in inkrementellt ändringsdata från SQL Server-källtabeller till ett informationslager eller ett dataarkiv. Även om representationen av källtabellerna i informationslagret måste återspegla ändringar i källtabellerna, är en teknik från slutpunkt till slutpunkt som uppdaterar en replik av källan inte lämplig. I stället behöver du en tillförlitlig dataström med ändringsdata som är strukturerade så att konsumenterna kan tillämpa dem på olika målrepresentationer av data. SQL Server-insamling av ändringsdata ger den här tekniken.

Dataflöde

Följande bild visar huvuddataflödet för insamling av ändringsdata.

Källan till ändringsdata för insamling av ändringsdata är SQL Server-transaktionsloggen. När infogningar, uppdateringar och borttagningar tillämpas på spårade källtabeller läggs poster som beskriver dessa ändringar till i loggen. Loggen fungerar som indata till insamlingsprocessen. Sedan läser den loggen och lägger till information om ändringar i den spårade tabellens associerade ändringstabell. Funktioner tillhandahålls som ska räkna upp de ändringar som visas i ändringstabellerna under ett angivet intervall och returnera informationen i form av en filtrerad resultatuppsättning. Den filtrerade resultatuppsättningen används vanligtvis av en programprocess för att uppdatera en representation av källan i en extern miljö.

Avbilda instans

Innan ändringar i enskilda tabeller i en databas kan spåras måste ändringsdatainsamling uttryckligen aktiveras för databasen. Detta görs med hjälp av den lagrade proceduren sys.sp_cdc_enable_db. När databasen är aktiverad kan källtabeller identifieras som spårade tabeller med hjälp av den lagrade proceduren sys.sp_cdc_enable_table. När en tabell är aktiverad för insamling av ändringsdata skapas en associerad instans för att stödja spridning av ändringsdata i källtabellen. Avbildningsinstansen består av en ändringstabell och upp till två frågefunktioner. Metadata som beskriver konfigurationsinformationen för insamlingsinstansen behålls i metadatatabellerna för ändringsdatainsamling cdc.change_tables, cdc.index_columns och cdc.captured_columns. Den här informationen kan hämtas med hjälp av den lagrade proceduren sys.sp_cdc_help_change_data_capture.

Alla objekt som är associerade med en avbildningsinstans skapas i schemat för insamling av ändringsdata för den aktiverade databasen. Kraven för namnet på insamlingsinstansen är att det är ett giltigt objektnamn och att det är unikt för databasinsamlingsinstanserna. Som standardinställning är namnet <schema name_table name> för källtabellen. Dess associerade ändringstabell namnges genom att _CT läggs till i avbildningsinstansens namn. Funktionen som används för att fråga efter alla ändringar namnges genom att lägga till fn_cdc_get_all_changes_ till fångstinstansens namn. Om avbildningsinstansen har konfigurerats för att stödja nettoändringar, skapas även frågefunktionen net_changes och namnges genom att lägga till fn_cdc_get_net_changes_ framför namnet på avbildningsinstansen.

Ändra tabell

De första fem kolumnerna i en ändringsdatainsamlingstabell är metadatakolumner. Dessa ger ytterligare information som är relevant för den registrerade ändringen. De återstående kolumnerna speglar de identifierade insamlade kolumnerna från källtabellen i namn och vanligtvis i typ. Dessa kolumner innehåller de insamlade kolumndata som samlas in från källtabellen.

Varje infognings- eller borttagningsåtgärd som tillämpas på en källtabell visas som en enskild rad i ändringstabellen. Datakolumnerna i raden som är resultatet av en infogningsåtgärd innehåller kolumnvärdena efter infogningen. Datakolumnerna på raden som är resultatet av en borttagningsåtgärd innehåller kolumnvärdena före borttagningen. En uppdateringsåtgärd kräver en radpost för att identifiera kolumnvärdena före uppdateringen och en andra radpost för att identifiera kolumnvärdena efter uppdateringen.

Varje rad i en ändringstabell innehåller även andra metadata för att tillåta tolkning av ändringsaktiviteten. Kolumnen __$start_lsn identifierar det incheckningsloggsekvensnummer (LSN) som tilldelades ändringen. Commit LSN identifierar både ändringar som har genomförts inom samma transaktion och ordnar dessa transaktioner. Kolumnen __$seqval kan användas för att beställa fler ändringar som sker i samma transaktion. Kolumnen __$operation registrerar den åtgärd som är associerad med ändringen: 1 = delete, 2 = insert, 3 = update (före bild) och 4 = update (efter bild). Kolumnen __$update_mask är en variabel bitmask med en definierad bit för varje infångad kolumn. För infoga och ta bort poster har uppdateringsmasken alla bitar inställda. Uppdaterade rader kommer dock att ha de bitar som är inställda och motsvarar ändrade kolumner.

Giltighetsintervall

Giltighetsintervallet för insamling av ändringsdata för en databas är den tid då ändringsdata är tillgängliga för insamlingsinstanser. Giltighetsintervallet börjar när den första avbildningsinstansen skapas för en databastabell och fortsätter tills nu.

Databas

Data som deponeras i ändringstabeller blir ohanterligt om du inte regelbundet och systematiskt rensar data. Rensningsprocessen för ändringsdatainsamling ansvarar för att framtvinga den kvarhållningsbaserade rensningsprincipen. Först flyttas den låga slutpunkten för giltighetsintervallet för att uppfylla tidsbegränsningen. Sedan tar den bort förfallna poster i ändringstabeller. Som standard behålls tre dagars data.

När insamlingsprocessen bekräftar varje ny batch med ändringsdata läggs nya poster till cdc.lsn_time_mapping för varje transaktion som har ändringstabellposter. I mappningstabellen behålls både ett incheckningsloggsekvensnummer (LSN) och en transaktionsincheckningstid (kolumner start_lsn respektive tran_end_time). Det maximala LSN-värdet som finns i cdc.lsn_time_mapping representerar högvattenmärket i databasens giltighetsfönster. Dess motsvarande "commit-tid" används som bas från vilken kvarhållningsbaserad rensning beräknar en ny lägsta gräns.

Eftersom insamlingsprocessen extraherar ändringsdata från transaktionsloggen finns det en inbyggd svarstid mellan den tid då en ändring checkas in i en källtabell och den tid då ändringen visas i den associerade ändringstabellen. Även om den här svarstiden vanligtvis är liten är det ändå viktigt att komma ihåg att ändringsdata inte är tillgängliga förrän avbildningsprocessen har bearbetat de relaterade loggposterna.

Avbilda instans

Även om det är vanligt att databasens giltighetsintervall och giltighetsintervallet för enskilda avbildningsinstanser sammanfaller är det dock inte alltid sant. Giltighetsintervallet för avbildningsinstansen startar när avbildningsprocessen identifierar avbildningsinstansen och börjar logga associerade ändringar i dess ändringstabell. Om inspelningsinstanser skapas vid olika tidpunkter kommer varje instans att ha en annan låg slutpunkt. Start_lsn-kolumnen i resultatuppsättningen som returneras av sys.sp_cdc_help_change_data_capture visar den nuvarande lägsta slutpunkten för varje definierad capture-instans. När rensningsprocessen rensar poster i ändringstabellen justeras start_lsn-värdena för alla capture instances för att återspegla det nya lågvattenmärket för tillgängliga förändringsdata. Endast de avbildningsinstanser som har start_lsn värden som för närvarande är mindre än det nya lågvattenmärket justeras. Om inga nya avbildningsinstanser skapas med tiden tenderar giltighetsintervallen för alla enskilda instanser att sammanfalla med databasens giltighetsintervall.

Giltighetsintervallet är viktigt för användare av ändringsdata eftersom extraheringsintervallet för en begäran måste omfattas helt av det aktuella giltighetsintervallet för insamling av ändringsdata för insamlingsinstansen. Om den låga slutpunkten för extraheringsintervallet är till vänster om den låga slutpunkten för giltighetsintervallet kan ändringsdata saknas på grund av aggressiv rensning. Om den höga slutpunkten för extraheringsintervallet är till höger om den höga slutpunkten för giltighetsintervallet, indikerar det att avbildningsprocessen ännu inte har bearbetats under den tid som representeras av extraheringsintervallet, och det kan också saknas ändringsdata.

Funktionen sys.fn_cdc_get_min_lsn används för att hämta det aktuella lägsta LSN för en avbildningsinstans, medan sys.fn_cdc_get_max_lsn används för att hämta det aktuella maximala LSN-värdet. Om det angivna LSN-intervallet inte ligger inom dessa två LSN-värden misslyckas frågefunktionerna för ändringsdatainsamling när du frågar efter ändringsdata.

Hantera ändringar i källtabellen

Att anpassa kolumnändringar i källtabellerna som spåras är ett svårt problem för nedströmsanvändare. Även om aktivering av ändringsdatainsamling i en källtabell inte hindrar sådana DDL-ändringar från att inträffa, minskar insamlingen av ändringsdata effekten på konsumenterna genom att bevara de levererade resultatuppsättningarna som returneras via API:et, även när kolumnstrukturen i den underliggande källtabellen ändras. Den här fasta kolumnstrukturen återspeglas också i den underliggande ändringstabellen som de definierade frågefunktionerna har åtkomst till.

Insamlingsprocessen som ansvarar för att fylla i ändringstabellen rymmer en ändringstabell för fast kolumnstruktur genom att ignorera alla nya kolumner som inte har identifierats för avbildning när källtabellen aktiverades för insamling av ändringsdata. Om en spårad kolumn tas bort anges null-värden för kolumnen i de efterföljande ändringsposterna. Men om en befintlig kolumn genomgår en ändring i sin datatyp sprids ändringen till ändringstabellen för att säkerställa att insamlingsmekanismen inte medför dataförlust för spårade kolumner. Insamlingsprocessen publicerar även eventuella identifierade ändringar i kolumnstrukturen för spårade tabeller i cdc.ddl_history-tabellen. Konsumenter som vill få aviseringar om justeringar som kan behöva göras i underordnade program använder den lagrade proceduren sys.sp_cdc_get_ddl_history.

Normalt fortsätter den aktuella avbildningsinstansen att behålla sin form när DDL-ändringar tillämpas på den associerade källtabellen. Det är dock möjligt att skapa en andra avbildningsinstans för tabellen som återspeglar den nya kolumnstrukturen. Med det här alternativet kan avbildningsprocessen göra ändringar i samma källtabell i två distinkta ändringstabeller med två olika kolumnstrukturer. Medan en ändringstabell kan fortsätta att mata in aktuella operativa program, kan den andra köra en utvecklingsmiljö som försöker införliva nya kolumndata. Att tillåta insamlingsmekanismen att fylla i båda ändringstabellerna tillsammans innebär att en övergång från en till en annan kan utföras utan förlust av ändringsdata. Detta kan inträffa när de två tidslinjerna för ändringsdatainsamling överlappar varandra. När övergången påverkas kan den föråldrade avbildningsinstansen tas bort.

Förhållande med loggläsaragent

Logiken för ändringsdatainsamlingsprocessen är inbäddad i den lagrade proceduren sp_replcmds, en intern serverfunktion som skapats som en del av sqlservr.exe och som även används av transaktionsreplikering för att hämta ändringar från transaktionsloggen. I SQL Server och Azure SQL Managed Instance, när endast ändringsdatainsamling är aktiverat för en databas, skapar du ändringsdatainsamlingsjobbet för SQL Server Agent som ett verktyg för att anropa sp_replcmds. När replikering också finns används enbart transaktionsloggläsaren för att uppfylla behoven för ändringsdata för båda dessa konsumenter. Den här strategin minskar loggkonkurringen avsevärt när både replikering och insamling av ändringsdata är aktiverade för samma databas.

Växlingen mellan dessa två driftlägen för insamling av ändringsdata sker automatiskt när replikeringsstatusen för en databas med ändringsdatainsamling har ändrats.

Huvuduppgiften för insamlingsprocessen är att genomsöka loggen och skriva kolumndata och transaktionsrelaterad information till ändringsdatainsamlingens ändringstabeller. För att säkerställa en transaktionsmässigt konsekvent gräns över alla ändringsdatainsamlingsändringstabeller som fylls i öppnas avbildningsprocessen och genomför en egen transaktion i varje genomsökningscykel. Den identifierar när tabeller nyligen har aktiverats för insamling av ändringsdata och innehåller dem automatiskt i den uppsättning tabeller som aktivt övervakas för ändringsposter i loggen. På samma sätt identifieras inaktivering av ändringsdatainsamling, vilket gör att källtabellen tas bort från den uppsättning tabeller som aktivt övervakas för ändringsdata. När bearbetningen av en del av loggen är klar signalerar insamlingsprocessen logiken för serverloggens trunkering, som använder den här informationen för att identifiera loggposter som är berättigade till trunkering.

Insamlingsprocessen används också för att upprätthålla historiken för DDL-ändringar i spårade tabeller. De DDL-instruktioner som är associerade med ändringsdatainsamling gör poster i databastransaktionsloggen när en databas eller tabell med datainsamling som aktiveras för ändringsdata tas bort eller kolumner i en ändringsdatafångstaktiverad tabell läggs till, ändras eller tas bort. Dessa loggposter bearbetas av insamlingsprocessen, som sedan publicerar de associerade DDL-händelserna i tabellen cdc.ddl_history. Du kan hämta information om DDL-händelser som påverkar spårade tabeller med hjälp av den lagrade proceduren sys.sp_cdc_get_ddl_history.

Agentjobb

Två SQL Server Agent-jobb är vanligtvis associerade med en aktiverad databas med ändringsdatainsamling: ett som används för att fylla i databasändringstabellerna och ett som ansvarar för rensning av ändringstabeller. Båda arbetena består av ett enda steg som kör kommandot Transact-SQL. Kommandot Transact-SQL som anropas är en ändringsdatainsamlingsdefinierad lagrad procedur som implementerar jobbets logik. Jobben skapas när den första tabellen i databasen är aktiverad för insamling av ändringsdata. Rensningsjobbet skapas alltid. Avbildningsjobbet skapas bara om det inte finns några definierade transaktionspublikationer för databasen. Insamlingstjänsten skapas också när både ändringsdatainsamling och transaktionsreplikering är aktiverade för en databas, och transaktionsloggläsarjobbet tas bort eftersom databasen inte längre har definierade publikationer.

Både avbildnings- och rensningsjobben skapas med hjälp av standardparametrar. Avbildningsjobbet startas omedelbart. Den körs kontinuerligt och bearbetar högst 1 000 transaktioner per genomsökningscykel med en väntetid på 5 sekunder mellan cyklerna. Rensningsjobbet körs dagligen klockan 02.00. Det behåller tabellposter för ändringar i 4320 minuter eller 3 dagar och tar bort högst 5000 poster med ett enda borttagningskommando.

Agentjobben för ändringsdatainsamling tas bort när insamling av ändringsdata inaktiveras för en databas. Insamlingsarbetet kan också avlägsnas när den första publikationen läggs till i en databas och både ändringsdatainfångning och transaktionsreplikering är aktiverade.

Internt skapas och släpps agentjobb för ändringsdatainsamling med hjälp av lagrade procedurer sys.sp_cdc_add_job respektive sys.sp_cdc_drop_job. Dessa lagrade procedurer exponeras också så att administratörer kan styra skapandet och borttagningen av dessa jobb.

En administratör har ingen explicit kontroll över standardkonfigurationen för agentjobben för ändringsdatainsamling. Den lagrade proceduren sys.sp_cdc_change_job tillhandahålls för att tillåta att standardkonfigurationsparametrarna ändras. Dessutom gör den lagrade proceduren sys.sp_cdc_help_jobs att aktuella konfigurationsparametrar kan visas. Både avbildningsjobbet och rensningsjobbet extraherar konfigurationsparametrar från tabellen msdb.dbo.cdc_jobs vid start. Ändringar som görs i dessa värden med hjälp av sys.sp_cdc_change_job börjar inte gälla förrän jobbet har stoppats och startats om.

Två andra lagrade procedurer tillhandahålls för att tillåta att agentjobb för ändringsdatainsamling startas och stoppas: sys.sp_cdc_start_job och sys.sp_cdc_stop_job.

Båda SQL Server Agent-jobben har utformats för att vara tillräckligt flexibla och tillräckligt konfigurerbara för att uppfylla de grundläggande behoven i miljöer för insamling av ändringsdata. I båda fallen har dock de underliggande lagrade procedurerna som tillhandahåller kärnfunktionerna exponerats så att ytterligare anpassning är möjlig.

Ändringsdatainsamling kan inte fungera korrekt när tjänsten Database Engine eller SQL Server Agent körs under NETWORK SERVICE-kontot. Detta kan resultera i fel 22832.

Samverkan med andra funktioner

Ändringsdatainsamling har vissa begränsningar när du arbetar med andra SQL Server-funktioner. Läs mer i Samverkan .

Kända problemområden

Kända problem och fel som är associerade med insamling av ändringsdata finns i Kända problem med CDC.

Se även

• Kända problem och begränsningar
• Arbeta med ändringsdata
• Spåra ändringar i data
• Aktivera och inaktivera insamling av ändringsdata
• Administrera och övervaka insamling av ändringsdata
• Temporala tabeller