Arbeta med ändringsdata

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

Ändringsdata görs tillgängliga för att ändra datainsamlingskonsumenter via tabellvärdesfunktioner (TVF:er). Alla frågor i dessa funktioner kräver två parametrar för att definiera intervallet för log sequence numbers (LSN) som är berättigade att överväga när du utvecklar den returnerade resultatuppsättningen. Både de övre och lägre LSN-värdena som avgränsar intervallet anses ingå inom intervallet.

Det finns flera funktioner som hjälper dig att fastställa lämpliga LSN-värden för användning vid frågor mot en TVF. Funktionen sys.fn_cdc_get_min_lsn returnerar det minsta LSN som är associerat med ett intervall för avbildningsinstansens giltighet. Giltighetsintervallet är det tidsintervall för vilket ändringsdata för närvarande är tillgängliga för dess insamlingsinstanser. Funktionen sys.fn_cdc_get_max_lsn returnerar det största LSN i giltighetsintervallet. Funktionerna sys.fn_cdc_map_time_to_lsn och sys.fn_cdc_map_lsn_to_time är tillgängliga för att placera LSN-värden på en konventionell tidslinje.

Eftersom insamling av ändringsdata använder slutna frågeintervall är det ibland nödvändigt att generera nästa LSN-värde i en sekvens för att säkerställa att ändringarna inte dupliceras i efterföljande frågefönster. Funktionerna sys.fn_cdc_increment_lsn och sys.fn_cdc_decrement_lsn är användbara när en inkrementell justering av ett LSN-värde krävs.

Verifiera LSN-gränser

Vi rekommenderar att du verifierar de LSN-gränser som ska användas i en TVF-fråga innan de används. Null-slutpunkter eller slutpunkter som ligger utanför giltighetsintervallet för en avbildningsinstans framtvingar att ett fel returneras av en TVF för ändringsdatainsamling.

Följande fel returneras till exempel för en fråga för alla ändringar när en parameter som används för att definiera frågeintervallet inte är giltig eller ligger inom intervallet, eller om radfilteralternativet är ogiltigt.

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_ ...

Motsvarande fel som returneras för en fråga om nettoändringar är följande:

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_ ...

Auktoriseringsfel returnerar fel när du frågar efter alla ändringar, enligt följande:

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'.

Detsamma gäller när du frågar efter nettoändringar:

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'.

I SQL Server Management Studio läser du mallen Räkna upp nettoändringar med TRY CATCH för en demonstration av hur du fångar upp dessa kända TVF-fel och returnerar mer meningsfull information om felet.

Frågefunktioner

Beroende på egenskaperna för källtabellen som spåras och hur insamlingsinstansen konfigureras genereras antingen en eller två TVF:er för att köra frågor mot ändringsdata.

• Funktionen cdc.fn_cdc_get_all_changes_<capture_instance> returnerar alla ändringar som har inträffat för det angivna intervallet. Den här funktionen genereras alltid. Poster returneras alltid sorterade, först efter transaktionens inchecknings-LSN för ändringen och sedan efter ett värde som sekvenser ändringen i transaktionen. Beroende på det valda radfilteralternativet returneras antingen den sista raden vid uppdatering (radfilteralternativet "alla") eller så returneras både de nya och gamla värdena vid uppdatering (radfilteralternativet "all update old").

• Funktionen cdc.fn_cdc_get_net_changes_<capture_instance> genereras när parametern @supports_net_changes anges till 1 när källtabellen är aktiverad.

  Anmärkning

  Det här alternativet stöds bara om källtabellen har en definierad primärnyckel eller om parametern @index_name har använts för att identifiera ett unikt index.

  Funktionen netchanges returnerar en ändring per ändrad källtabellrad. Om mer än en ändring loggas för raden under det angivna intervallet återspeglar kolumnvärdena det slutliga innehållet i raden. För att korrekt identifiera den åtgärd som krävs för att uppdatera målmiljön måste TVF överväga både den inledande åtgärden på raden under intervallet och den sista åtgärden på raden. När radfilteralternativet "all" har angetts kommer de åtgärder som returneras av en fråga om nettoändringar antingen att infogas, tas bort eller uppdateras (nya värden). Det här alternativet returnerar alltid uppdateringsmasken som null eftersom det finns en kostnad som är associerad med beräkning av en aggregerad mask. Om du behöver en aggregeringsmask som återspeglar alla ändringar i en rad använder du alternativet "alla med mask". Om nedströmsbearbetning inte kräver att infogningar och uppdateringar särskiljs använder du alternativet "alla med sammanfogning". I det här fallet får åtgärdsvärdet endast två värden: 1 för borttagning och 5 för en åtgärd som kan vara antingen en infogning eller en uppdatering. Det här alternativet eliminerar den ytterligare bearbetning som krävs för att avgöra om den härledda åtgärden ska vara en infogning eller en uppdatering, och kan förbättra frågans prestanda när denna differentiering inte är nödvändig.

Uppdateringsmasken som returneras från en frågefunktion är en kompakt representation som identifierar alla kolumner som ändrats i en rad med ändringsdata. Den här informationen krävs vanligtvis bara för en liten delmängd av de insamlade kolumnerna. Funktioner är tillgängliga för att hjälpa till att extrahera information från masken i ett formulär som är mer direkt användbart av program. Funktionen sys.fn_cdc_get_column_ordinal returnerar ordningspositionen för en namngiven kolumn för en viss avbildningsinstans, medan funktionen sys.fn_cdc_is_bit_set returnerar pariteten för biten i den angivna masken baserat på ordningstalet som skickades i funktionsanropet. Tillsammans tillåter dessa två funktioner att information från uppdateringsmasken extraheras effektivt och returneras med begäran om ändringsdata. I SQL Server Management Studio läser du mallen Räkna upp nettoändringar med alla med mask för en demonstration av hur dessa funktioner används.

Frågefunktionsscenarier

I följande avsnitt beskrivs vanliga scenarier för att fråga efter datainsamling av ändringsdata med hjälp av frågefunktionerna cdc.fn_cdc_get_all_changes_<capture_instance> och cdc.fn_cdc_get_net_changes_<capture_instance>.

Fråga efter alla ändringar inom insamlingsinstansens giltighetsintervall

Den enklaste begäran om ändringsdata är en begäran som returnerar alla aktuella ändringsdata i en insamlingsinstans giltighetsintervall. Om du vill göra den här begäran måste du först fastställa de nedre och övre LSN-gränserna för giltighetsintervallet. Använd sedan dessa värden för att identifiera parametrarna @from_lsn och @to_lsn skicka till frågefunktionen cdc.fn_cdc_get_all_changes_<capture_instance> eller cdc.fn_cdc_get_net_changes_<capture_instance>. Använd funktionen sys.fn_cdc_get_min_lsn för att hämta den nedre gränsen och sys.fn_cdc_get_max_lsn för att hämta den övre gränsen. I SQL Server Management Studio läser du mallen Räkna upp alla ändringar för det giltiga intervallet för exempelkod för att fråga efter alla aktuella giltiga ändringar med hjälp av frågefunktionen cdc.fn_cdc_get_all_changes_<capture_instance>. I SQL Server Management Studio läser du mallen Räkna upp nettoändringar för det giltiga intervallet för ett liknande exempel på hur du använder funktionen cdc.fn_cdc_get_net_changes_<capture_instance>.

Fråga efter alla nya ändringar sedan den senaste uppsättningen ändringar

För vanliga program är frågor om ändringsdata en pågående process som gör regelbundna begäranden för alla ändringar som har inträffat sedan den senaste begäran. För sådana frågor kan du använda funktionen sys.fn_cdc_increment_lsn för att härleda den nedre gränsen för den aktuella frågan från den övre gränsen för föregående fråga. Den här metoden säkerställer att inga rader upprepas eftersom frågeintervallet alltid behandlas som ett stängt intervall där båda slutpunkterna ingår i intervallet. Använd sedan funktionen sys.fn_cdc_get_max_lsn för att hämta den högsta slutpunkten för det nya begärandeintervallet. I SQL Server Management Studio läser du mallen Räkna upp alla ändringar sedan föregående begäran om exempelkod för att systematiskt flytta frågefönstret för att hämta alla ändringar sedan den senaste begäran.

Fråga efter alla nya ändringar fram till nu

En typisk begränsning som läggs på de ändringar som returneras av en frågefunktion är att endast inkludera de ändringar som inträffade mellan föregående begäran fram till aktuellt datum och tid. För den här frågan använder du funktionen sys.fn_cdc_increment_lsn för det @from_lsn värde som användes i föregående begäran för att fastställa den lägre gränsen. Eftersom den övre gränsen för tidsintervallet uttrycks som en viss tidpunkt måste den konverteras till ett LSN-värde innan det kan användas av en frågefunktion. Innan datetime-värdet kan konverteras till ett motsvarande LSN-värde måste du se till att avbildningsprocessen har bearbetat alla ändringar som har checkats in via den angivna övre gränsen. Detta krävs för att säkerställa att alla kvalificerande ändringar har spridits till ändringstabellen. Ett sätt att göra detta är att strukturera en vänteloop som regelbundet kontrollerar om den aktuella maximala incheckningen lsn som registrerats för en databasändringstabell överskrider önskad sluttid för begärandeintervallet.

När fördröjningsloopen verifierar att avbildningsprocessen redan har bearbetat alla relevanta loggposter använder du funktionen sys.fn_cdc_map_time_to_lsn för att fastställa den nya höga slutpunkten uttryckt som ett LSN-värde. För att säkerställa att alla poster som har checkats in under den angivna tiden hämtas anropar du funktionen sys.fn_cdc_map_time_to_lsnoch använder alternativet "största mindre än eller lika".

Mallen Räkna upp alla ändringar fram till nu visar hur du använder den tidigare strategin för att fråga efter ändringsdata.

Lägg till en incheckningstid i en resultatuppsättning för alla ändringar

Incheckningstiden för varje transaktion med en associerad post i en databasändringstabell är tillgänglig i tabellen cdc.lsn_time_mapping. Genom att koppla värdet __$start_lsn som returneras i en begäran om alla ändringar med start_lsn värdet för en cdc.lsn_time_mapping tabellpost kan du returnera tran_end_time tillsammans med ändringsdata för att stämpla ändringen med incheckningstiden för transaktionen vid källan. Mallen Addd Commit Time to All Changes Result Set visar hur du utför den här kopplingen.

Koppla ändringsdata till andra data från samma transaktion

Ibland är det användbart att koppla ändringsdata till annan information som samlats in om transaktionen när den checkas in i källan. Kolumnen tran_begin_lsn i tabellen cdc.lsn_time_mapping innehåller den information som behövs för att utföra en sådan koppling. När uppdateringen av källan sker måste värdet för database_transaction_begin_lsn från systemdynamikvyn sys.dm_tran_database_transactions sparas tillsammans med annan information som ska kopplas till ändringsdata. Använd funktionen fn_convertnumericlsntobinary för att jämföra database_transaction_begin_lsn värdena och tran_begin_lsn . Koden för att skapa den här funktionen är tillgänglig i mallen Skapa funktion fn_convertnumericlsntobinary. Mallen Return All Changes with a Given (Returnera alla ändringar med en given) tran_begin_lsn visar hur du påverkar kopplingen.

Fråga med hjälp av DateTime Wrapper Functions

Ett typiskt programscenario för att fråga efter ändringsdata är att regelbundet begära ändringsdata med hjälp av ett skjutfönster som avgränsas av datetime-värden. För den här klassen av konsumenter tillhandahåller ändringsdatainsamling den lagrade proceduren sys.sp_cdc_generate_wrapper_function som genererar skript för att skapa anpassade omslutningsfunktioner för frågefunktionerna för ändringsdatainsamling. Med de här anpassade omslutningarna kan frågeintervallet uttryckas som ett datetime-par.

Samtalsalternativ för den lagrade proceduren gör att omslutningar kan genereras för alla avbildningsinstanser som anroparen har åtkomst till eller endast en angiven avbildningsinstans. Alternativen som stöds inkluderar också möjligheten att ange om den höga slutpunkten för insamlingsintervallet ska vara öppen eller stängd, vilken av de tillgängliga insamlade kolumnerna som ska ingå i resultatuppsättningen och vilka av de inkluderade kolumnerna som ska ha associerade uppdateringsflaggor. Proceduren returnerar en resultatuppsättning med två kolumner: det genererade funktionsnamnet, som kan härledas från avbildningsinstansens namn, och create-instruktionen för den lagrade omslutningsproceduren. Funktionen för att omsluta frågan om alla ändringar genereras alltid. Om parametern @supports_net_changes angavs när avbildningsinstansen skapades genereras även funktionen för att omsluta funktionen net changes.

Det är programdesignerns ansvar att anropa den lagrade proceduren för skriptgenerering för att generera skapandeinstruktionerna för de lagrade omslutningsprocedurerna och att köra de resulterande skapa skripten för att skapa funktionerna. Detta sker inte automatiskt när en avbildningsinstans skapas.

Datetime-omslutningar ägs av användaren och skapas inte i standardschemat för anroparen. Den genererade funktionen är lämplig utan ändringar för de flesta användare. Ytterligare anpassning kan dock alltid tillämpas på det genererade skriptet innan du skapar funktionen.

Namnet på funktionen för att omsluta frågan fn_all_changes_ om alla ändringar följs av namnet på insamlingsinstansen. Prefixet som används för net changes wrapper är fn_net_changes_. Båda funktionerna har tre argument, precis som deras associerade TVF:er för ändringsdatainsamling. Frågeintervallet för omslutningarna begränsas dock av två datetime-värden i stället för två LSN-värden. Parametern @row_filter_option för båda uppsättningarna av funktioner är densamma.

De genererade omslutningsfunktionerna stöder följande konvention för att systematiskt gå på tidslinjen för insamling av ändringsdata: Parametern @end_time för föregående intervall förväntas användas som parameter för @start_time det efterföljande intervallet. Omslutningsfunktionen tar hand om att mappa datetime-värdena till LSN-värden och se till att inga data går förlorade eller upprepas om den här konventionen följs.

Omslutningarna kan genereras för att stödja antingen en stängd övre gräns eller en öppen övre gräns i det angivna frågefönstret. Det vill säga anroparen kan ange om poster som har en incheckningstid som är lika med den övre gränsen för extraheringsintervallet ska inkluderas inom intervallet. Som standard ingår den övre gränsen.

Även om de genererade fråge-TVF:erna misslyckas om de har angett ett null-värde för antingen @from_lsn värdet eller @to_lsn värdet, använder datetime-omslutningsfunktionerna null för att tillåta att datetime-omslutningarna returnerar alla aktuella ändringar. Om null skickas som den låga slutpunkten i frågefönstret till datetime-omslutningen används den låga slutpunkten för valideringsinstansens giltighetsintervall i den underliggande SELECT instruktionen som tillämpas på frågan TVF. På samma sätt, om null skickas som den högsta slutpunkten i frågefönstret, används den högsta slutpunkten för insamlingsinstansens giltighetsintervall när du väljer från frågan TVF.

Resultatuppsättningen som returneras av en omslutningsfunktion innehåller alla begärda kolumner följt av en åtgärdskolumn, omkodad som ett eller två tecken för att identifiera den åtgärd som är associerad med raden. Om uppdateringsflaggor har begärts visas de som bitkolumner efter åtgärdskoden i den ordning som anges i parametern @update_flag_list . Information om samtalsalternativen för att anpassa de genererade datetime-omslutningarna finns i sys.sp_cdc_generate_wrapper_function (Transact-SQL).

Mallen Instansiera en wrapper-TVF med uppdateringsflagga visar hur du anpassar en genererad omslutningsfunktion för att lägga till en uppdateringsflagga för en angiven kolumn i resultatuppsättningen som returneras av en fråga om nettoändringar. Mallen Instansiera CDC Wrapper-TVF:er för ett schema visar hur du instansierar Datetime Wrappers för Fråge-TVF:er för alla insamlingsinstanser som skapats för källtabellerna i ett visst databasschema.

Ett exempel som använder en datetime-omslutning för att fråga efter ändringsdata finns i Sql Server Management Studio i mallen Hämta nettoändringar med omslutning med uppdateringsflaggor. Den här mallen visar hur du frågar efter nettoändringar med en omslutningsfunktion när omslutningen har konfigurerats för att returnera uppdateringsflaggor. Radfilteralternativet "alla med mask" krävs för att den underliggande frågefunktionen ska returnera en uppdateringsmask som inte är null vid uppdateringen. Null-värden skickas för både de nedre och övre datetime-intervallgränserna för att signalera att funktionen använder den låga slutpunkten och den höga slutpunkten för giltighetsintervallet för avbildningsinstansen när den underliggande LSN-baserade frågan utförs. Frågan returnerar en rad för varje ändring av en källrad som har inträffat inom det giltiga intervallet för avbildningsinstansen.

Använda DateTime Wrapper Functions för att överföra mellan avbildningsinstanser

Ändringsdatainsamling stöder upp till två insamlingsinstanser för en enda spårad källtabell. Den huvudsakliga användningen av den här funktionen är att hantera en övergång mellan flera instanser av avbildningar när datadefinitionsspråket (DDL) ändras till källtabellen expanderar uppsättningen tillgängliga kolumner för spårning. När du övergår till en ny insamlingsinstans är ett sätt att skydda högre programnivåer från ändringar i namnen på de underliggande frågefunktionerna att använda en omslutningsfunktion för att omsluta det underliggande anropet. Kontrollera sedan att namnet på omslutningsfunktionen förblir detsamma. När växeln ska ske kan den gamla omslutningsfunktionen tas bort och en ny med samma namn skapas som refererar till de nya frågefunktionerna. Genom att först ändra det genererade skriptet för att skapa en omslutningsfunktion med samma namn kan du växla till en ny avbildningsinstans utan att påverka högre programlager.

Relaterat innehåll

• CDC med Azure SQL Database
• Spåra dataändringar (SQL Server)
• Vad är CDC (Change Data Capture)?
• Aktivera och inaktivera insamling av ändringsdata
• Administrera och övervaka insamling av ändringsdata