Dela via

Spåra dataändringar (SQL Server)

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

SQL Server innehåller två funktioner som spårar ändringar av data i en databas: ändra datainsamling och ändringsspårning. De här funktionerna gör det möjligt för program att fastställa de DML-ändringar (infoga, uppdatera och ta bort åtgärder) som gjordes i användartabeller i en databas. Ändringsdatainsamling och ändringsspårning kan aktiveras i samma databas. inga särskilda överväganden krävs. De utgåvor av SQL Server som stöder insamling och ändringsspårning av ändringsdata finns i Utgåvor och funktioner som stöds i SQL Server 2022.

Fördelar med att använda insamling av ändringsdata eller ändringsspårning

Möjligheten att fråga efter data som har ändrats i en databas är ett viktigt krav för att vissa program ska vara effektiva. För att fastställa dataändringar måste programutvecklare vanligtvis implementera en anpassad spårningsmetod i sina program med hjälp av en kombination av utlösare, tidsstämpel kolumner och ytterligare tabeller. Att skapa dessa program innebär vanligtvis mycket arbete att implementera, leder till schemauppdateringar och medför ofta höga prestandakostnader.

Att använda ändringsdatainsamling eller ändringsspårning i program för att spåra ändringar i en databas, i stället för att utveckla en anpassad lösning, har följande fördelar:

  • Utvecklingstiden är kortare. Eftersom funktioner är tillgängliga i SQL Server behöver du inte utveckla en anpassad lösning.

  • Schemaändringar krävs inte. Du behöver inte lägga till kolumner, lägga till utlösare eller skapa sidotabeller där du kan spåra borttagna rader eller lagra information om ändringsspårning om kolumner inte kan läggas till i användartabellerna.

  • Det finns en inbyggd rensningsmekanism. Rensning för ändringsspårning utförs automatiskt i bakgrunden. Anpassad rensning för data som lagras i en sidotabell krävs inte.

  • Funktioner tillhandahålls för att hämta ändringsinformation.

  • DML-åtgärder har låg belastning. Synkron ändringsspårning har alltid vissa omkostnader. Om du använder ändringsspårning kan du dock minimera kostnaderna. Omkostnaderna är ofta mindre än användningen av alternativa lösningar, särskilt lösningar som kräver användning av utlösare.

  • Ändringsspårning baseras på genomförda transaktioner. Ordningen på ändringarna baseras på transaktionsincheckningstiden. Detta gör att tillförlitliga resultat kan erhållas när det finns långvariga och överlappande transaktioner. Anpassade lösningar som använder tidsstämpel värden måste utformas för att hantera dessa scenarier.

  • Standardverktyg är tillgängliga som du kan använda för att konfigurera och hantera. SQL Server tillhandahåller DDL-standardinstruktioner, SQL Server Management Studio, katalogvyer och säkerhetsbehörigheter.

Funktionsskillnader mellan ändringsdatainsamling och ändringsspårning

I följande tabell visas funktionsskillnaderna mellan insamling av ändringsdata och ändringsspårning. Spårningsmekanismen i insamling av ändringsdata omfattar en asynkron avbildning av ändringar från transaktionsloggen så att ändringar är tillgängliga efter DML-åtgärden. I ändringsspårning innebär spårningsmekanismen synkron spårning av ändringar i linje med DML-åtgärder så att ändringsinformation blir tillgänglig omedelbart.

Funktion Ändra datainsamling Ändringsspårning
Spårade ändringar
DML-ändringar Ja Ja
Spårad information
Historiska data Ja Nej
Om kolumnen har ändrats Ja Ja
DML-typ Ja Ja

Ändra datainsamling

Insamling av ändringsdata ger historisk ändringsinformation för en användartabell genom att samla in både det faktum att DML-ändringar har gjorts och de faktiska data som har ändrats. Ändringar registreras med hjälp av en asynkron process som läser transaktionsloggen och har en låg inverkan på systemet.

Som du ser i följande bild avbildas de ändringar som har gjorts i användartabeller i motsvarande ändringstabeller. Dessa ändringstabeller ger en historisk vy över ändringarna över tid. De ändra datainsamlingen funktioner som SQL Server tillhandahåller gör det möjligt att använda ändringsdata enkelt och systematiskt.

diagram som visar begreppet ändringsdatainsamling.

Säkerhetsmodell

I det här avsnittet beskrivs säkerhetsmodellen för insamling av ändringsdata.

Konfiguration och administration

Om du vill aktivera eller inaktivera insamling av ändringsdata för en databas måste anroparen för sys.sp_cdc_enable_db (Transact-SQL) eller sys.sp_cdc_disable_db (Transact-SQL) vara medlem i den fasta servern sysadmin roll. Om du aktiverar och inaktiverar insamling av ändringsdata på tabellnivå måste anroparen av sys.sp_cdc_enable_table (Transact-SQL) och sys.sp_cdc_disable_table (Transact-SQL) antingen vara medlem i sysadmin-rollen eller medlem i databasen databas db_owner roll.

Användningen av lagrade procedurer för att stödja administration av jobb för insamling av ändringsdata är begränsad till medlemmar i servern sysadmin roll och medlemmar i databas db_owner roll.

Ändra uppräknings- och metadatafrågor

För att få åtkomst till de ändringsdata som är associerade med en insamlingsinstans måste användaren beviljas SELECT-åtkomst till alla insamlade kolumner i den associerade källtabellen. Om en gating-roll anges när avbildningsinstansen skapas måste anroparen också vara medlem i den angivna gatingrollen, och schemat för ändringsdatainsamling (cdc) måste ha SELECT-åtkomst till gating-rollen.

Andra allmänna funktioner för insamling av ändringsdata för åtkomst till metadata kommer att vara tillgängliga för alla databasanvändare via den offentliga rollen, även om åtkomsten till de returnerade metadata vanligtvis också kommer att begränsas med hjälp av SELECT-åtkomst till de underliggande källtabellerna och genom medlemskap i alla definierade gating-roller.

DDL-åtgärder för att ändra datainsamlingsaktiverade källtabeller

När en tabell är aktiverad för insamling av ändringsdata kan DDL-åtgärder endast tillämpas på tabellen av en medlem i den fasta serverrollen sysadmin, en medlem av -databasrollen db_ownereller en medlem av databasrollen db_ddladmin. Användare som har explicita bidrag för att utföra DDL-åtgärder i tabellen får fel 22914 om de försöker utföra dessa åtgärder.

Överväganden för datatyp för insamling av ändringsdata

Alla baskolumntyper stöds av insamling av ändringsdata. I följande tabell visas beteendet och begränsningarna för flera kolumntyper.

Typ av kolumn Ändringar som samlas in i ändringstabeller Begränsningar
Glesa kolumner Ja Stöder inte insamling av ändringar när du använder en kolumnuppsättning.
Beräknade kolumner Nej Ändringar i beräknade kolumner spåras inte. Kolumnen visas i ändringstabellen med lämplig typ, men har värdet NULL.
XML Ja Ändringar i enskilda XML-element spåras inte.
Tidsstämpel Ja Datatypen i ändringstabellen konverteras till binär.
BLOB-datatyper Ja Den tidigare bilden av BLOB-kolumnen lagras endast om själva kolumnen ändras.

SQL Server-funktionsintegrering

I det här avsnittet beskrivs hur följande funktioner interagerar med insamling av ändringsdata:

  • Databasspegling
  • Transaktionsreplikering
  • Återställa eller koppla databas

Databasspegling

En databas som är aktiverad för insamling av ändringsdata kan speglas. Följ dessa steg för att säkerställa att avbildning och rensning sker automatiskt i spegeln:

  1. Kontrollera att SQL Server-agenten körs på speglingen.

  2. Skapa avbildningsjobbet och rensningsjobbet på speglingen efter att huvuddatorn har växlat över till speglingen. Om du vill skapa jobben använder du den lagrade proceduren sys.sp_cdc_add_job (Transact-SQL).

Mer information om databasspegling finns i Database Mirroring (SQL Server).

Transaktionsreplikering

Ändring av datainsamling och transaktionsreplikering kan samexistera i samma databas, men populationen av ändringstabellerna hanteras på olika sätt när båda funktionerna är aktiverade. Ändringsdataregistrering och transaktions-replikering använder alltid samma procedur, sp_replcmds, för att läsa ändringar från transaktionsloggen. När ändringsdatainsamling är aktiverat på egen hand anropar ett SQL Server Agent-jobb sp_replcmds. När båda funktionerna är aktiverade i samma databas anropar Log Reader Agent sp_replcmds. Den här agenten fyller i både ändringstabellerna och distribution databastabellerna. För mer information, se Replikationsloggläsaragent.

Överväg ett scenario där ändringsdatainsamling är aktiverat på AdventureWorks2022-databasen och två tabeller är aktiverade för avbildning. För att fylla i ändringstabellerna anropar avbildningsjobbet sp_replcmds. Databasen är aktiverad för transaktionsreplikering och en publikation skapas. Nu skapas loggläsaragenten för databasen och loggningsjobbet tas bort. Loggläsaragenten fortsätter att genomsöka loggen från det senaste loggsekvensnumret som skrevs in i ändringstabellen. Detta säkerställer datakonsekvens i ändringstabellerna. Om transaktionsreplikering är inaktiverad i den här databasen tas Loggläsaragenten bort och insamlingsjobbet skapas om.

Anteckning

När log reader-agenten används för både ändringsdatainsamling och transaktionsreplikering skrivs replikerade ändringar först till distribution-databasen. Sedan skrivs insamlade ändringar till ändringstabellerna. Båda åtgärderna utförs tillsammans. Om det finns någon fördröjning i skrivning till distribution databas, kommer det att finnas en motsvarande svarstid innan ändringarna visas i ändringstabellerna.

Återställa eller bifoga en databas som är aktiverad för insamling av ändringsdata

SQL Server använder följande logik för att avgöra om insamling av ändringsdata förblir aktiverad när en databas har återställts eller kopplats:

  • Om en databas återställs till samma server med samma databasnamn är datainsamlingen fortfarande aktiverad.

  • Om en databas återställs till en annan server inaktiveras som standard insamling av ändringsdata och alla relaterade metadata tas bort.

    Om du vill behålla insamling av ändringsdata använder du alternativet KEEP_CDC när du återställer databasen. Mer information om det här alternativet finns i RESTORE.

  • Om en databas kopplas från och kopplas till samma server eller en annan server förblir ändringsdatainsamling aktiverad.

  • Om en databas är ansluten eller återställd med alternativet KEEP_CDC till någon annan utgåva än Standard eller Enterprise blockeras åtgärden eftersom ändringsdatainsamling kräver SQL Server Standard- eller Enterprise-utgåvor. Felmeddelandet 932 visas:

    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.

Du kan använda sys.sp_cdc_disable_db för att ta bort insamling av ändringsdata från en återställd eller ansluten databas.

Ändringsspårning

Ändringsspårning fångar det faktum att rader i en tabell har ändrats, men samlar inte in de data som har ändrats. Detta gör det möjligt för program att fastställa de rader som har ändrats med de senaste raddata som hämtas direkt från användartabellerna. Därför är ändringsspårningen mer begränsad i de historiska frågor som den kan besvara jämfört med insamling av ändringsdata. För de program som inte kräver historisk information är det dock mycket mindre lagringsutrymme på grund av att ändrade data inte samlas in. En synkron spårningsmekanism används för att spåra ändringarna. Detta har utformats för att ha minimala omkostnader för DML-åtgärderna.

Följande bild visar ett synkroniseringsscenario som skulle gynnas med hjälp av ändringsspårning. I scenariot kräver ett program följande information: alla rader i tabellen som har ändrats sedan den senaste gången tabellen synkroniserades och endast aktuella raddata. Eftersom en synkron mekanism används för att spåra ändringarna kan ett program utföra dubbelriktad synkronisering och på ett tillförlitligt sätt identifiera eventuella konflikter som kan ha inträffat.

diagram som visar begreppet ändringsspårning.

Ändringsspårning och Synkroniseringstjänster för ADO.NET

Sync Services för ADO.NET möjliggör synkronisering mellan databaser, vilket ger ett intuitivt och flexibelt API som gör att du kan skapa program som riktar sig mot offline- och samarbetsscenarier. Sync Services för ADO.NET tillhandahåller ett API för att synkronisera ändringar, men det spårar inte ändringar i servern eller peer-databasen. Du kan skapa ett anpassat system för ändringsspårning, men detta medför vanligtvis betydande komplexitet och prestandakostnader. Om du vill spåra ändringar i en server eller peer-databas rekommenderar vi att du använder ändringsspårning i SQL Server eftersom det är enkelt att konfigurera och ger spårning med höga prestanda.

Mer information om ändringsspårning och Synkroniseringstjänster för ADO.NET finns i följande länkar:

Relaterat innehåll