Notitie
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen u aan te melden of de directory te wijzigen.
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen de mappen te wijzigen.
Van toepassing op:
SQL ServerAzure SQL Managed Instance
Wijzigingsgegevens worden beschikbaar gesteld om gebruikers van gegevensopnamen te wijzigen via tabelwaardefuncties (TVF's). Voor alle query's van deze functies zijn twee parameters vereist om het bereik van LSN's (Log Sequence Numbers) te definiëren die in aanmerking komen voor overweging bij het ontwikkelen van de geretourneerde resultatenset. Zowel de bovenste als lagere LSN-waarden die het interval hebben gebonden, worden beschouwd als opgenomen binnen het interval.
Er zijn verschillende functies beschikbaar om de juiste LSN-waarden te bepalen voor gebruik bij het uitvoeren van query's op een TVF. De functie sys.fn_cdc_get_min_lsn retourneert de kleinste LSN die is gekoppeld aan een geldigheidsinterval van het capture-exemplaar. Het geldigheidsinterval is het tijdsinterval waarvoor wijzigingsgegevens momenteel beschikbaar zijn voor de capture-exemplaren. De functie sys.fn_cdc_get_max_lsn retourneert de grootste LSN in het geldigheidsinterval. De functies sys.fn_cdc_map_time_to_lsn en sys.fn_cdc_map_lsn_to_time zijn beschikbaar om LSN-waarden op een conventionele tijdlijn te plaatsen.
Omdat wijzigingsgegevens vastleggen gebruikmaakt van gesloten queryintervallen, is het soms nodig om de volgende LSN-waarde in een reeks te genereren om ervoor te zorgen dat wijzigingen niet worden gedupliceerd in opeenvolgende queryvensters. De functies sys.fn_cdc_increment_lsn en sys.fn_cdc_decrement_lsn zijn handig wanneer een incrementele aanpassing van een LSN-waarde vereist is.
LSN-grenzen valideren
U wordt aangeraden de LSN-grenzen te valideren die vóór het gebruik moeten worden gebruikt in een TVF-query. Null-eindpunten of eindpunten die buiten het geldigheidsinterval voor een capture-exemplaar liggen, dwingen ertoe dat een fout wordt geretourneerd door een wijzigingsgegevensopname TVF.
De volgende fout wordt bijvoorbeeld geretourneerd voor een query voor alle wijzigingen wanneer een parameter die wordt gebruikt om het queryinterval te definiëren, ongeldig is of buiten het bereik valt, of de optie voor het rijfilter ongeldig is.
Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...
De bijbehorende fout die wordt geretourneerd voor een query met nettowijzigingen is het volgende:
Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...
Opmerking
Het wordt erkend dat het bericht voor Msg 313 misleidend is en niet de werkelijke oorzaak van de fout aangeeft. Dit onhandige gebruik komt voort uit het onvermogen om een expliciete fout te genereren vanuit een TVF. Niettemin werd de waarde van het retourneren van een herkenbare, indien onnauwkeurige fout geacht de voorkeur te geven aan het retourneren van een leeg resultaat. Een lege resultatenset kan niet worden onderscheiden van een geldige query die geen wijzigingen retourneert.
Autorisatiefouten retourneren fouten bij het uitvoeren van query's op alle wijzigingen, zoals wordt weergegeven:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.
Hetzelfde geldt voor het uitvoeren van query's op nettowijzigingen:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.
Zie in SQL Server Management Studio de sjabloon Net Changes Using TRY CATCH gebruiken voor een demonstratie van het onderscheppen van deze bekende TVF-fouten en het retourneren van zinvollere informatie over de fout.
Aanbeveling
Als u sjablonen voor het vastleggen van gegevens wilt zoeken in SQL Server Management Studio, selecteert u Template Explorer, vouwt u SQL Server-sjablonen uit en vouwt u vervolgens de map Change Data Capture uit.
Queryfuncties
Afhankelijk van de kenmerken van de brontabel die wordt bijgehouden en de manier waarop het capture-exemplaar wordt geconfigureerd, worden een of twee TVF's gegenereerd voor het uitvoeren van query's op wijzigingsgegevens.
De functie cdc.fn_cdc_get_all_changes_<capture_instance> retourneert alle wijzigingen die zijn opgetreden voor het opgegeven interval. Deze functie wordt altijd gegenereerd. Vermeldingen worden altijd gesorteerd, eerst door de transactiedoorvoering LSN van de wijziging en vervolgens op een waarde die de wijziging in de transactie opvolgt. Afhankelijk van de gekozen optie voor rijfilters wordt de laatste rij geretourneerd bij bijwerken (rijfilteroptie 'alle') of worden zowel de nieuwe als de oude waarden geretourneerd bij de update (rijfilteroptie 'alle oude bijwerken').
De functie cdc.fn_cdc_get_net_changes_<capture_instance> wordt gegenereerd wanneer de parameter
@supports_net_changesis ingesteld1op wanneer de brontabel is ingeschakeld.Opmerking
Deze optie wordt alleen ondersteund als de brontabel een gedefinieerde primaire sleutel heeft of als de parameter @index_name is gebruikt om een unieke index te identificeren.
De
netchangesfunctie retourneert één wijziging per gewijzigde brontabelrij. Als er tijdens het opgegeven interval meer dan één wijziging voor de rij wordt geregistreerd, geven de kolomwaarden de uiteindelijke inhoud van de rij weer. Om de bewerking die nodig is om de doelomgeving bij te werken, correct te identificeren, moet de TVF rekening houden met zowel de eerste bewerking in de rij tijdens het interval als de laatste bewerking op de rij. Wanneer de rijfilteroptie 'all' is opgegeven, worden de bewerkingen die worden geretourneerd door een net changes-query ingevoegd, verwijderd of bijgewerkt (nieuwe waarden). Deze optie retourneert altijd het updatemasker als null omdat er kosten zijn gekoppeld aan het berekenen van een aggregaatmasker. Als u een aggregatiemasker nodig hebt dat alle wijzigingen in een rij weergeeft, gebruikt u de optie 'Alles met masker'. Als downstreamverwerking niet vereist dat invoegingen en updates worden onderscheiden, gebruikt u de optie Alles samenvoegen. In dit geval heeft de bewerkingswaarde slechts twee waarden: 1 voor verwijderen en 5 voor een bewerking die een invoegbewerking of een update kan zijn. Deze optie elimineert de extra verwerking die nodig is om te bepalen of de afgeleide bewerking een invoegbewerking of een update moet zijn en kan de prestaties van de query verbeteren wanneer deze differentiatie niet nodig is.
Het updatemasker dat wordt geretourneerd door een queryfunctie, is een compacte weergave die alle kolommen identificeert die zijn gewijzigd in een rij met wijzigingsgegevens. Deze informatie is doorgaans alleen vereist voor een kleine subset van de vastgelegde kolommen. Functies zijn beschikbaar om te helpen bij het extraheren van informatie uit het masker in een formulier dat beter kan worden gebruikt door toepassingen. De functie sys.fn_cdc_get_column_ordinal retourneert de rangtelpositie van een benoemde kolom voor een bepaald capture-exemplaar, terwijl de functie sys.fn_cdc_is_bit_set de pariteit van de bit in het opgegeven masker retourneert op basis van het rangnummer dat is doorgegeven in de functieaanroep. Met deze twee functies kunnen gegevens uit het updatemasker efficiënt worden geëxtraheerd en geretourneerd met de aanvraag voor wijzigingsgegevens. Zie in SQL Server Management Studio de sjabloon Net Changes Using All With Mask gebruiken voor een demonstratie van hoe deze functies worden gebruikt.
Scenario's voor queryfuncties
In de volgende secties worden veelvoorkomende scenario's beschreven voor het uitvoeren van querygegevens voor het vastleggen van gegevens met behulp van de queryfuncties cdc.fn_cdc_get_all_changes_<capture_instance> en cdc.fn_cdc_get_net_changes_<capture_instance>.
Query uitvoeren op alle wijzigingen binnen het geldigheidsinterval van het capture-exemplaar
De eenvoudigste aanvraag voor wijzigingsgegevens is een aanvraag die alle huidige wijzigingsgegevens retourneert in het geldigheidsinterval van een capture-exemplaar. Als u deze aanvraag wilt indienen, moet u eerst de onderste en bovenste LSN-grenzen van het geldigheidsinterval bepalen. Gebruik vervolgens deze waarden om de parameters @from_lsn te identificeren en @to_lsn door te geven aan de queryfunctie cdc.fn_cdc_get_all_changes_<capture_instance> of cdc.fn_cdc_get_net_changes_<capture_instance>. Gebruik de functie sys.fn_cdc_get_min_lsn om de ondergrens te verkrijgen en sys.fn_cdc_get_max_lsn om de bovengrens te verkrijgen. Zie in SQL Server Management Studio de sjabloon Alle wijzigingen opsommen voor het geldige bereik voor voorbeeldcode om te zoeken naar alle huidige geldige wijzigingen met behulp van de queryfunctie cdc.fn_cdc_get_all_changes_<capture_instance>. Zie in SQL Server Management Studio de sjabloon Net Changes for the Valid Range for a similar example of using the function cdc.fn_cdc_get_net_changes_<capture_instance>.
Query uitvoeren op alle nieuwe wijzigingen sinds de laatste set wijzigingen
Voor typische toepassingen is het uitvoeren van query's op wijzigingsgegevens een doorlopend proces, waarbij periodieke aanvragen worden ingediend voor alle wijzigingen die zijn opgetreden sinds de laatste aanvraag. Voor dergelijke query's kunt u de functie sys.fn_cdc_increment_lsn gebruiken om de ondergrens van de huidige query af te leiden van de bovengrens van de vorige query. Deze methode zorgt ervoor dat er geen rijen worden herhaald omdat het queryinterval altijd wordt behandeld als een gesloten interval waarbij beide eindpunten in het interval worden opgenomen. Gebruik vervolgens de functie sys.fn_cdc_get_max_lsn om het hoge eindpunt voor het nieuwe aanvraaginterval te verkrijgen. Zie in SQL Server Management Studio de sjabloon Alle wijzigingen opsommen sinds vorige aanvraag voor voorbeeldcode om het queryvenster systematisch te verplaatsen om alle wijzigingen sinds de laatste aanvraag te verkrijgen.
Query uitvoeren op alle nieuwe wijzigingen tot nu toe
Een typische beperking die wordt geplaatst voor de wijzigingen die door een queryfunctie worden geretourneerd, is om alleen de wijzigingen op te nemen die zijn opgetreden tussen de vorige aanvraag tot de huidige datum en tijd. Voor deze query past u de functie sys.fn_cdc_increment_lsn toe op de @from_lsn waarde die in de vorige aanvraag is gebruikt om de ondergrens te bepalen. Omdat de bovengrens voor het tijdsinterval wordt uitgedrukt als een specifiek tijdstip, moet deze worden geconverteerd naar een LSN-waarde voordat deze kan worden gebruikt door een queryfunctie. Voordat de datum/tijd-waarde kan worden geconverteerd naar een overeenkomende LSN-waarde, moet u ervoor zorgen dat het capture-proces alle wijzigingen heeft verwerkt die zijn doorgevoerd via de opgegeven bovengrens. Dit is vereist om ervoor te zorgen dat alle in aanmerking komende wijzigingen zijn doorgegeven aan de wijzigingstabel. Een manier om dit te doen, is om een wachtlus te structureren die periodiek controleert of de huidige maximumdoorvoeringstabel die is vastgelegd voor een databasewijzigingstabel de gewenste eindtijd van het aanvraaginterval overschrijdt.
Nadat de vertragingslus controleert of het opnameproces al alle relevante logboekvermeldingen heeft verwerkt, gebruikt u de functie sys.fn_cdc_map_time_to_lsn om het nieuwe hoge eindpunt te bepalen dat wordt uitgedrukt als een LSN-waarde. Als u ervoor wilt zorgen dat alle vermeldingen die zijn doorgevoerd via de opgegeven tijd, worden opgehaald, roept u de functie sys.fn_cdc_map_time_to_lsnaan en gebruikt u de optie 'grootste kleiner dan of gelijk aan'.
Opmerking
In perioden van inactiviteit wordt een dummyvermelding toegevoegd aan de tabel cdc.lsn_time_mapping om het feit te markeren dat het opnameproces de wijzigingen tot een bepaalde doorvoertijd heeft verwerkt. Hierdoor wordt voorkomen dat het opnameproces achter is gebleven wanneer er gewoon geen recente wijzigingen zijn die moeten worden verwerkt.
De sjabloon Alle wijzigingen tot nu opsommen laat zien hoe u met de vorige strategie query's kunt uitvoeren op wijzigingsgegevens.
Een doorvoertijd toevoegen aan een resultatenset met alle wijzigingen
De doorvoertijd van elke transactie met een gekoppelde vermelding in een databasewijzigingstabel is beschikbaar in de tabel cdc.lsn_time_mapping. Door de waarde __$start_lsn die wordt geretourneerd in een aanvraag voor alle wijzigingen samen te voegen met de start_lsn waarde van een cdc.lsn_time_mapping tabelvermelding, kunt u de tran_end_time samen met de wijzigingsgegevens retourneren om de wijziging te stempelen met de doorvoertijd van de transactie bij de bron. De sjabloon Doorvoertijd toevoegen aan alle wijzigingen resultaatset laat zien hoe u deze join uitvoert.
Wijzigingsgegevens samenvoegen met andere gegevens uit dezelfde transactie
Soms is het handig om wijzigingsgegevens samen te voegen met andere informatie die over de transactie is verzameld wanneer deze is doorgevoerd bij de bron. De tran_begin_lsn kolom in de tabel cdc.lsn_time_mapping bevat de informatie die nodig is om een dergelijke join uit te voeren. Wanneer de update van de bron plaatsvindt, moet de waarde voor database_transaction_begin_lsn uit de dynamische weergave van het systeem worden opgeslagen sys.dm_tran_database_transactions samen met andere gegevens die moeten worden gekoppeld aan de wijzigingsgegevens. Gebruik de functie fn_convertnumericlsntobinary om de database_transaction_begin_lsn en tran_begin_lsn waarden te vergelijken. De code voor het maken van deze functie is beschikbaar in de sjabloon Functie maken fn_convertnumericlsntobinary. De sjabloon Retourneert alle wijzigingen met een gegeven tran_begin_lsn en laat zien hoe dit van invloed is op de join.
Query's uitvoeren met datum/tijd-wrapperfuncties
Een typisch toepassingsscenario voor het uitvoeren van query's op wijzigingsgegevens is het periodiek aanvragen van wijzigingsgegevens met behulp van een schuifvenster dat is gebonden aan datum/tijd-waarden. Voor deze consumentenklasse biedt wijzigingsgegevens vastleggen de opgeslagen procedure sys.sp_cdc_generate_wrapper_function waarmee scripts worden gegenereerd voor het maken van aangepaste wrapperfuncties voor de queryfuncties voor het vastleggen van wijzigingengegevens. Met deze aangepaste wrappers kan het queryinterval worden uitgedrukt als een datum/tijd-paar.
Met aanroepende opties voor de opgeslagen procedure kunnen wrappers worden gegenereerd voor alle capture-exemplaren waartoe de beller toegang heeft of alleen een opgegeven instantie voor vastleggen. Ondersteunde opties omvatten ook de mogelijkheid om op te geven of het hoge eindpunt van het opname-interval moet worden geopend of gesloten, welke van de beschikbare vastgelegde kolommen moeten worden opgenomen in de resultatenset en welke van de opgenomen kolommen moeten zijn gekoppeld aan updatevlagmen. De procedure retourneert een resultatenset met twee kolommen: de gegenereerde functienaam, die kan worden afgeleid van de naam van het capture-exemplaar en de create-instructie voor de opgeslagen procedure van de wrapper. De functie voor het verpakken van alle wijzigingenquery wordt altijd gegenereerd. Als de @supports_net_changes parameter is ingesteld toen het capture-exemplaar werd gemaakt, wordt ook de functie voor het verpakken van de functie netwijzigingen gegenereerd.
Het is de verantwoordelijkheid van de ontwerper van de toepassing om de opgeslagen procedure voor het genereren van scripts aan te roepen om de opgeslagen procedures voor de wrapper te genereren en om de resulterende createscripts uit te voeren om de functies te maken. Dit gebeurt niet automatisch wanneer een capture-exemplaar wordt gemaakt.
Datum/tijd-wrappers zijn eigendom van de gebruiker en worden niet gemaakt in het standaardschema van de aanroeper. De gegenereerde functie is geschikt zonder aanpassingen voor de meeste gebruikers. Verdere aanpassing kan echter altijd worden toegepast op het gegenereerde script voordat u de functie maakt.
De naam van de functie voor het verpakken van alle wijzigingenquery wordt fn_all_changes_ gevolgd door de naam van het capture-exemplaar. Het voorvoegsel dat wordt gebruikt voor de net changes wrapper is fn_net_changes_. Beide functies hebben drie argumenten, net als de bijbehorende wijzigingsgegevensopname-TVF's. Het queryinterval voor de wrappers wordt echter gebonden door twee datum/tijd-waarden in plaats van door twee LSN-waarden. De @row_filter_option parameter voor beide sets functies is hetzelfde.
De gegenereerde wrapper-functies ondersteunen de volgende conventie voor het systematisch doorlopen van de tijdlijn voor het vastleggen van wijzigingengegevens: er wordt verwacht dat de @end_time parameter van het vorige interval wordt gebruikt als de @start_time parameter van het volgende interval. De wrapper-functie zorgt ervoor dat de datum/tijd-waarden worden toegewezen aan LSN-waarden en ervoor zorgen dat er geen gegevens verloren gaan of herhaald worden als deze conventie wordt gevolgd.
De wrappers kunnen worden gegenereerd ter ondersteuning van een gesloten bovengrens of een geopende bovengrens in het opgegeven queryvenster. Dat wil gezegd, de aanroeper kan opgeven of vermeldingen met een doorvoertijd die gelijk is aan de bovengrens van het extractieinterval moeten worden opgenomen binnen het interval. Standaard is de bovengrens opgenomen.
Hoewel de gegenereerde query-TVF's mislukken als er een null-waarde is opgegeven voor de @from_lsn waarde of de @to_lsn waarde, gebruiken de datum/tijd-wrapperfuncties null om de datum/tijd-wrappers toe te staan alle huidige wijzigingen te retourneren. Als null wordt doorgegeven als het lage eindpunt van het queryvenster aan de datum/tijd-wrapper, wordt het lage eindpunt van het geldigheidsinterval van het capture-exemplaar gebruikt in de onderliggende SELECT instructie die wordt toegepast op de query TVF. Als null wordt doorgegeven als het hoge eindpunt van het queryvenster, wordt het hoge eindpunt van het geldigheidsinterval van het capture-exemplaar gebruikt bij het selecteren van de query-TVF.
De resultatenset die wordt geretourneerd door een wrapper-functie bevat alle aangevraagde kolommen, gevolgd door een bewerkingskolom, opnieuw gecodeerd als een of twee tekens om de bewerking te identificeren die aan de rij is gekoppeld. Als updatevlagmen zijn aangevraagd, worden ze weergegeven als bitkolommen na de bewerkingscode, in de volgorde die is opgegeven in de @update_flag_list parameter. Zie sys.sp_cdc_generate_wrapper_function (Transact-SQL) voor meer informatie over de oproepopties voor het aanpassen van de gegenereerde datum/tijd-wrappers.
De sjabloon Instantiate a Wrapper TVF With Update Flag laat zien hoe u een gegenereerde wrapper-functie kunt aanpassen om een updatevlag voor een opgegeven kolom toe te voegen aan de resultatenset die wordt geretourneerd door een net wijzigingenquery. De sjabloon Instantiate CDC Wrapper TVFs voor een schema laat zien hoe u de Datetime Wrappers voor de Query TVFs kunt instantiëren voor alle capture-exemplaren die zijn gemaakt voor de brontabellen in een bepaald databaseschema.
Voor een voorbeeld waarin een datum/tijd-wrapper wordt gebruikt om query's uit te voeren op wijzigingsgegevens, raadpleegt u in SQL Server Management Studio de sjabloon Net-wijzigingen ophalen met wrapper met updatevlagmen. Deze sjabloon laat zien hoe u query's uitvoert op nettowijzigingen met een wrapper-functie wanneer de wrapper is geconfigureerd om updatevlagmen te retourneren. De rijfilteroptie 'alles met masker' is vereist voor de onderliggende queryfunctie om een niet-null-updatemasker bij de update te retourneren. Null-waarden worden doorgegeven voor zowel de onder- als hogere datum/tijd-intervalgrenzen om de functie aan te geven voor het gebruik van het lage eindpunt en het hoge eindpunt van het geldigheidsinterval voor het capture-exemplaar bij het uitvoeren van de onderliggende LSN-query. De query retourneert één rij voor elke wijziging in een bronrij die zich in het geldige bereik voor het capture-exemplaar heeft voorgedaan.
De DateTime Wrapper-functies gebruiken om te schakelen tussen capture-exemplaren
Wijzigingsgegevens vastleggen ondersteunt maximaal twee capture-exemplaren voor één bijgehouden brontabel. Het belangrijkste gebruik van deze mogelijkheid is om een overgang tussen meerdere capture-exemplaren mogelijk te maken wanneer DDL (Data Definition Language) wordt gewijzigd in de brontabel, de set beschikbare kolommen voor het bijhouden uitbreidt. Wanneer u overstapt naar een nieuw capture-exemplaar, kunt u hogere toepassingsniveaus beveiligen tegen wijzigingen in de namen van de onderliggende queryfuncties door een wrapper-functie te gebruiken om de onderliggende aanroep te verpakken. Zorg er vervolgens voor dat de naam van de wrapper-functie hetzelfde blijft. Wanneer de schakeloptie plaatsvindt, kan de oude wrapper-functie worden verwijderd en een nieuwe met dezelfde naam die verwijst naar de nieuwe queryfuncties. Als u eerst het gegenereerde script wijzigt om een wrapper-functie van dezelfde naam te maken, kunt u overschakelen naar een nieuw capture-exemplaar zonder dat dit van invloed is op hogere toepassingslagen.