Delen via

Gegevenswijzigingen bijhouden (SQL Server)

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

SQL Server biedt twee functies waarmee wijzigingen in gegevens in een database worden bijgehouden: gegevens vastleggen en wijzigingen bijhouden. Met deze functies kunnen toepassingen de DML-wijzigingen (invoegen, bijwerken en verwijderen) bepalen die zijn aangebracht in gebruikerstabellen in een database. Het vastleggen van wijzigingen en het bijhouden van wijzigingen kan worden ingeschakeld voor dezelfde database; er zijn geen speciale overwegingen vereist. Zie -edities en ondersteunde functies van SQL Server 2022voor de edities van SQL Server die ondersteuning bieden voor het vastleggen en bijhouden van wijzigingen.

Voordelen van het gebruik van het vastleggen van wijzigingen of het bijhouden van wijzigingen

De mogelijkheid om query's uit te voeren op gegevens die zijn gewijzigd in een database, is een belangrijke vereiste voor een aantal toepassingen om efficiënt te zijn. Normaal gesproken moeten toepassingsontwikkelaars een aangepaste traceringsmethode in hun toepassingen implementeren met behulp van een combinatie van triggers, tijdstempel kolommen en extra tabellen. Het maken van deze toepassingen omvat meestal veel werk om te implementeren, leidt tot schema-updates en heeft vaak een hoge overhead voor prestaties.

Het gebruik van het vastleggen of bijhouden van wijzigingen in toepassingen om wijzigingen in een database bij te houden in plaats van een aangepaste oplossing te ontwikkelen, heeft de volgende voordelen:

  • Er is minder ontwikkeltijd. Omdat de functionaliteit beschikbaar is in SQL Server, hoeft u geen aangepaste oplossing te ontwikkelen.

  • Schemawijzigingen zijn niet vereist. U hoeft geen kolommen toe te voegen, triggers toe te voegen of een zijtabel te maken waarin verwijderde rijen kunnen worden bijgehouden of om bijhouden van wijzigingen op te slaan als kolommen niet kunnen worden toegevoegd aan de gebruikerstabellen.

  • Er is een ingebouwd opschoonmechanisme. Opschonen voor het bijhouden van wijzigingen wordt automatisch op de achtergrond uitgevoerd. Aangepaste opschoning voor gegevens die zijn opgeslagen in een zijtabel is niet vereist.

  • Functies worden geleverd om wijzigingsinformatie te verkrijgen.

  • Er is weinig overhead voor DML-bewerkingen. Synchrone wijzigingen bijhouden heeft altijd enige overhead. Het gebruik van wijzigingen bijhouden kan echter helpen de overhead te minimaliseren. De overhead is vaak minder dan die van het gebruik van alternatieve oplossingen, met name oplossingen die het gebruik van triggers vereisen.

  • Wijzigingen bijhouden is gebaseerd op vastgelegde transacties. De volgorde van de wijzigingen is gebaseerd op de doorvoertijd van transacties. Hierdoor kunnen betrouwbare resultaten worden verkregen wanneer er langlopende en overlappende transacties zijn. Aangepaste oplossingen die gebruikmaken van tijdstempel waarden moeten zijn ontworpen om deze scenario's te verwerken.

  • Standaardhulpprogramma's zijn beschikbaar die u kunt gebruiken om te configureren en beheren. SQL Server biedt standaard DDL-instructies, SQL Server Management Studio, catalogusweergaven en beveiligingsmachtigingen.

Functieverschillen tussen het vastleggen van wijzigingen en het bijhouden van wijzigingen

De volgende tabel bevat de functieverschillen tussen het vastleggen van wijzigingengegevens en het bijhouden van wijzigingen. Het traceringsmechanisme in het vastleggen van wijzigingengegevens omvat een asynchrone opname van wijzigingen uit het transactielogboek, zodat wijzigingen beschikbaar zijn na de DML-bewerking. Bij het bijhouden van wijzigingen omvat het traceringsmechanisme synchrone tracering van wijzigingen in overeenstemming met DML-bewerkingen, zodat wijzigingsinformatie onmiddellijk beschikbaar is.

Kenmerk Gegevenswijzigingen vastleggen Wijzigingen bijhouden
Bijgehouden wijzigingen
DML-wijzigingen Ja Ja
bijgehouden gegevens
Historische gegevens Ja Nee
Of de kolom is gewijzigd Ja Ja
DML-type Ja Ja

Gegevenswijzigingen vastleggen

Wijzigingsgegevens vastleggen biedt historische wijzigingsinformatie voor een gebruikerstabel door zowel het feit dat DML-wijzigingen zijn aangebracht als de werkelijke gegevens vast te leggen die zijn gewijzigd. Wijzigingen worden vastgelegd met behulp van een asynchroon proces dat het transactielogboek leest en een lage impact heeft op het systeem.

Zoals wordt weergegeven in de volgende afbeelding, worden de wijzigingen die zijn aangebracht in gebruikerstabellen vastgelegd in de bijbehorende wijzigingstabellen. Deze wijzigingstabellen bieden een historisch overzicht van de wijzigingen in de loop van de tijd. De wijzigingsgegevens vastleggen functies die SQL Server biedt, zodat de wijzigingsgegevens eenvoudig en systematisch kunnen worden gebruikt.

diagram met het concept van het vastleggen van wijzigingsgegevens.

Beveiligingsmodel

In deze sectie wordt het beveiligingsmodel voor het vastleggen van wijzigingengegevens beschreven.

Configuratie en beheer

Als u het vastleggen van wijzigingen voor een database wilt in- of uitschakelen, moet de aanroeper van sys.sp_cdc_enable_db (Transact-SQL) of sys.sp_cdc_disable_db (Transact-SQL ) lid zijn van de vaste server sysadmin rol. Als u het vastleggen van wijzigingengegevens op tabelniveau inschakelt en uitschakelt, moet de aanroeper van sys.sp_cdc_enable_table (Transact-SQL) en sys.sp_cdc_disable_table (Transact-SQL) lid zijn van de rol sysadmin of lid zijn van de database database db_owner rol.

Het gebruik van de opgeslagen procedures ter ondersteuning van het beheer van taken voor het vastleggen van wijzigingengegevens is beperkt tot leden van de server sysadmin rol en leden van de database db_owner rol.

Query's voor opsommingen en metagegevens wijzigen

Als u toegang wilt krijgen tot de wijzigingsgegevens die zijn gekoppeld aan een opname-exemplaar, moet de gebruiker SELECT-toegang krijgen tot alle vastgelegde kolommen van de gekoppelde brontabel. Als er bovendien een gatingsrol wordt opgegeven wanneer het opname-exemplaar wordt gemaakt, moet de aanroeper ook lid zijn van de opgegeven gatingsrol en moet het schema voor veranderingen in gegevensvastlegging (cdc) SELECT-toegang hebben tot de gatingsrol.

Andere algemene functies voor het vastleggen van wijzigingsgegevens om metagegevens te openen zullen toegankelijk zijn voor alle databasegebruikers via de openbare rol. Hoewel de toegang tot de geretourneerde metagegevens doorgaans ook wordt beperkt door gebruik te maken van SELECT-toegang tot de onderliggende brontabellen en door lidmaatschap van gedefinieerde beperkende rollen.

DDL-bewerkingen voor het wijzigen van brontabellen waarvoor gegevensopname is ingeschakeld

Wanneer een tabel is ingeschakeld voor het vastleggen van wijzigingsgegevens, kunnen DDL-bewerkingen alleen worden toegepast op de tabel door een lid van de vaste serverfunctie sysadmin, lid van de -databaserol db_ownerof een lid van de -databaserol db_ddladmin. Gebruikers die expliciete subsidies hebben om DDL-bewerkingen uit te voeren in de tabel, krijgen fout 22914 als ze deze bewerkingen proberen.

Overwegingen voor gegevenstypen voor het vastleggen van wijzigingen in gegevens

Alle basiskolomtypen worden ondersteund door Change Data Capture. De volgende tabel bevat het gedrag en de beperkingen voor verschillende kolomtypen.

Type van kolom Wijzigingen vastgelegd in wijzigingentabellen Beperkingen
Spaarzame kolommen Ja Biedt geen ondersteuning voor het vastleggen van wijzigingen bij het gebruik van een columnset.
Berekende kolommen Nee Wijzigingen in berekende kolommen worden niet bijgehouden. De kolom wordt weergegeven in de wijzigingstabel met het juiste type, maar heeft een waarde van NULL.
XML Ja Wijzigingen in afzonderlijke XML-elementen worden niet bijgehouden.
Tijdstempel Ja Het gegevenstype in de wijzigingstabel wordt geconverteerd naar binair.
BLOB-gegevenstypen Ja De vorige afbeelding van de BLOB-kolom wordt alleen opgeslagen als de kolom zelf wordt gewijzigd.

Integratie van SQL Server-functies

In deze sectie wordt beschreven hoe de volgende functies communiceren met het vastleggen van gewijzigde gegevens.

  • Databasespiegeling
  • Transactionele replicatie
  • Database herstellen of koppelen

Database-spiegeling

Een database die is ingeschakeld voor het vastleggen van wijzigingsgegevens, kan worden gespiegeld. Volg deze stappen om ervoor te zorgen dat het vastleggen en opschonen automatisch plaatsvindt in de spiegel:

  1. Zorg ervoor dat SQL Server Agent wordt uitgevoerd op de mirror.

  2. Maak de verzamelingstaak en de opruimtaak op de mirror nadat de principal is overgeschakeld naar de mirror. Als u de taken wilt maken, gebruikt u de opgeslagen procedure sys.sp_cdc_add_job (Transact-SQL).

Zie DATABASEspiegeling (SQL Server)voor meer informatie over databasespiegeling.

Transactionele replicatie

Het vastleggen van gegevens en transactionele replicatie kan naast elkaar bestaan in dezelfde database, maar de populatie van de wijzigingstabellen wordt anders verwerkt wanneer beide functies zijn ingeschakeld. Wijziging van gegevensopname en transactionele replicatie gebruiken altijd dezelfde procedure, sp_replcmds, om wijzigingen uit het transactielogboek te lezen. Wanneer alleen het vastleggen van wijzigingen in gegevens is ingeschakeld, roept een SQL Server Agent-taak sp_replcmdsaan. Wanneer beide functies zijn ingeschakeld voor dezelfde database, roept de logboeklezeragent sp_replcmdsaan. Deze agent vult zowel de wijzigingstabellen als de distribution databasetabellen in. Zie Agent voor replicatielogboeklezervoor meer informatie.

Overweeg een scenario waarin wijzigingsgegevens vastleggen is ingeschakeld op de AdventureWorks2022-database en twee tabellen zijn ingeschakeld voor vastleggen. Als u de wijzigingstabellen wilt vullen, roept de capture-taak sp_replcmds. De database is ingeschakeld voor transactionele replicatie en er wordt een publicatie gemaakt. De logboeklezeragent wordt nu gemaakt voor de database en de opnametaak wordt verwijderd. De logboeklezeragent blijft het logboek scannen vanaf het laatste logboekreeksnummer dat is doorgevoerd in de wijzigingstabel. Dit zorgt voor gegevensconsistentie in de wijzigingstabellen. Als transactionele replicatie is uitgeschakeld in deze database, wordt de logboeklezeragent verwijderd en wordt de opnametaak opnieuw gemaakt.

Notitie

Wanneer de logboeklezeragent wordt gebruikt voor zowel het vastleggen van wijzigingsgegevens als transactionele replicatie, worden gerepliceerde wijzigingen eerst naar de distribution-database geschreven. Vervolgens worden vastgelegde wijzigingen naar de wijzigingstabellen geschreven. Beide bewerkingen worden samen doorgevoerd. Als er een latentie is bij het schrijven naar de distribution-database, is er een overeenkomstige latentie voordat wijzigingen in de wijzigingstabellen worden weergegeven.

Een database herstellen of koppelen die is ingeschakeld voor het vastleggen van gegevenswijzigingen

SQL Server gebruikt de volgende logica om te bepalen of het vastleggen van wijzigingen van gegevens is ingeschakeld nadat een database is hersteld of gekoppeld:

  • Als een database wordt hersteld naar dezelfde server met dezelfde databasenaam, blijft wijzigingsgegevens vastleggen ingeschakeld.

  • Als een database wordt hersteld naar een andere server, wordt het vastleggen van wijzigingen van gegevens standaard uitgeschakeld en worden alle gerelateerde metagegevens verwijderd.

    Als u het vastleggen van wijzigingen wilt behouden, gebruikt u de optie KEEP_CDC bij het herstellen van de database. Zie RESTOREvoor meer informatie over deze optie.

  • Als een database is losgekoppeld en is gekoppeld aan dezelfde server of een andere server, blijft wijzigingsgegevens vastleggen ingeschakeld.

  • Als een database is gekoppeld aan of hersteld met de KEEP_CDC optie voor een andere editie dan Standard of Enterprise, wordt de bewerking geblokkeerd omdat voor het vastleggen van wijzigingsgegevens SQL Server Standard- of Enterprise-edities zijn vereist. Foutbericht 932 wordt weergegeven:

    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.

U kunt sys.sp_cdc_disable_db gebruiken om het vastleggen van wijzigingsgegevens uit een herstelde of gekoppelde database te verwijderen.

Wijzigingen bijhouden

Wijzigingen bijhouden legt vast dat rijen in een tabel zijn gewijzigd, maar niet de gegevens vastleggen die zijn gewijzigd. Hierdoor kunnen toepassingen bepalen welke rijen zijn gewijzigd met de meest recente rijgegevens die rechtstreeks uit de gebruikerstabellen worden verkregen. Daarom is het bijhouden van wijzigingen beperkter in de historische vragen die kunnen worden beantwoord in vergelijking met het vastleggen van wijzigingengegevens. Voor toepassingen waarvoor geen historische gegevens nodig zijn, is er echter veel minder opslagoverhead omdat de gewijzigde gegevens niet worden vastgelegd. Er wordt een synchrone traceringsmechanisme gebruikt om de wijzigingen bij te houden. Dit is ontworpen om minimale overhead voor de DML-bewerkingen te hebben.

In de volgende afbeelding ziet u een synchronisatiescenario dat kan profiteren van het gebruik van wijzigingen bijhouden. In het scenario vereist een toepassing de volgende informatie: alle rijen in de tabel die zijn gewijzigd sinds de laatste keer dat de tabel is gesynchroniseerd, en alleen de huidige rijgegevens. Omdat een synchroon mechanisme wordt gebruikt om de wijzigingen bij te houden, kan een toepassing tweerichtingssynchronisatie uitvoeren en op betrouwbare wijze conflicten detecteren die zich mogelijk hebben voorgedaan.

diagram met het concept van het bijhouden van wijzigingen.

Wijzigingen volgen en Synchronisatie Services voor ADO.NET

Sync Services voor ADO.NET maakt synchronisatie tussen databases mogelijk en biedt een intuïtieve en flexibele API waarmee u toepassingen kunt bouwen die zich richten op offline- en samenwerkingsscenario's. Sync Services voor ADO.NET biedt een API voor het synchroniseren van wijzigingen, maar er worden geen wijzigingen in de server- of peerdatabase bijgehouden. U kunt een aangepast systeem voor het bijhouden van wijzigingen maken, maar dit introduceert doorgaans aanzienlijke complexiteit en prestatieoverhead. Als u wijzigingen in een server- of peerdatabase wilt bijhouden, raden we u aan wijzigingen bij te houden in SQL Server, omdat het eenvoudig te configureren is en hoge prestaties biedt.

Gebruik de volgende koppelingen voor meer informatie over het bijhouden van wijzigingen en synchronisatieservices voor ADO.NET:

Verwante inhoud