endringssporing FOR SQL Server er en fleksibel og brukervennlig teknologi for overvåking av tabeller for innlegg, oppdateringer og sletter. I dette innlegget vil jeg diskutere komme i gang med endringssporing I SQL Server, og vil vise et eksempel på hvordan du kommer i gang med DET.
Endringssporing I SQL Server
endringssporing Er en lettvektsmekanisme for sporing av hvilke rader som er satt inn, oppdatert og slettet i tabeller som overvåkes av endringssporing. Endringssporing først dukket opp I SQL Server 2008 og har vært i hver versjon siden. Enda bedre, endringssporing er tilgjengelig i hver utgave AV SQL Server, selv free express edition.
I et nøtteskall, Slik fungerer det: I tabeller som har endringssporing aktivert, spores nettoendringen til hver rad internt og er tilgjengelig gjennom endringssporingsfunksjoner. Hver endring har en versjon-ID knyttet til den, og det vil være en ny versjon-ID for hver rad som settes inn, oppdateres eller slettes. Endringssporingsversjonen er ET 8-byte heltall (BIGINT) som gjenspeiler den nyeste endrings-IDEN i databasen. Det er viktig å merke seg at endringssporingsversjonen ikke er tabellspesifikk – EN dml-operasjon i en sporet tabell i hver database øker versjonsnummeret. Versjonsnummeret vil alltid være sekvensielt, men vil ikke nødvendigvis være sammenhengende i en enkelt tabell hvis det er mer enn en tabell aktivert for endringssporing.
Endringssporing er aktivert på databasenivå. Etter det må hver tabell som skal overvåkes, være individuelt opptatt i endringssporing. Hver tabell som skal overvåkes av endringssporing, må ha en primærnøkkel, da dette er radnivåidentifikatoren som brukes til å rapportere OM dml-operasjoner i endringssporing. Når du aktiverer endringssporing på tabellnivå, kan du velge å spore kolonnen(e) som er endret i den nyeste oppdateringen, noe som gir deg større synlighet i det som ble endret.
når det er satt opp, er det en relativt enkel prosess å bruke endringssporing. DET er noen funksjoner – SPESIELT CHANGE_TRACKING_CURRENT_VERSION () og CHANGETABLE (), som kan brukes til å sjekke gjeldende versjonsstempel i endringssporing og hente listen over siste endringer. Jeg vil demonstrere begge disse funksjonene snart.
Endringssporing Er Ikke Revisjonslogging
jeg skal være forsiktig så jeg ikke bruker ordene revisjon eller logging for å beskrive endringssporing. La meg være klar: dette er ikke en full loggingsmekanisme. Endringsloggen spores ikke i det hele tatt – endringssporing rapporterer bare at det har skjedd en endring, men beholder ikke versjonsloggen. Tenk på tilfelle av en rad med data med EN ID på 1234. Den raden er satt inn, deretter oppdatert 5 ganger, og deretter slettet. Endringssporing vil ikke vise historikken for sett inn, oppdater og slett; snarere ville det bare rapportere nettendringen, at rad-ID 1234 ble slettet. Hvis lastprosessen krever detaljert loggingshistorikk for hver endring (i stedet for bare deltaet for alle endringer), må du bruke noe som change data capture.
Sette Opp Endringssporing I SQL Server
Aktivere sporing på tabellnivå er en to-trinns prosess. Først må det være aktivert på databasen. Dette kan gjøres VIA BRUKERGRENSESNITTET I Databaseegenskapene, i Kategorien Endringssporing.
som vist er det ikke mye å konfigurere når du aktiverer endringssporing i en database. Bare sett Endringssporingsverdien Til True for å sette opp endringssporing for den databasen. Eventuelt Kan Oppbevaringsperioden verdien bli forskjøvet i tillegg. Standardverdien er 2 dager, som jeg har overstyrt i dette eksemplet for å bruke 14 dager i stedet. Som med de fleste UI-operasjoner, er DET EN T-SQL-kommando for å gjøre det samme. Kommandoen for å sette opp endringssporing på denne databasen er under.
etter dette trinnet er endringssporing aktivert, men den sporer ikke noe ennå. Det må fortsatt være aktivert for hvert bord som skal spores. Tabellen Egenskaper UI gjør dette veldig enkelt.
som vist, gjør det enkelt å endre endringssporingsverdien Til True endringssporing for denne tabellen. I dette eksemplet valgte jeg også å spore kolonnene endret under oppdateringer(mer om dette litt).
det siste trinnet ovenfor vil gjentas for hver tabell som skal spores i endringssporing. Når endringssporing er aktivert, vil eventuelle endringer (innsettinger, oppdateringer eller sletting) i tabellen bli lagret i hurtigbufferen for endringssporing.
Sette Opp Endringssporing
for eksemplet ovenfor skal jeg sette inn, oppdatere og slette noen data for å demonstrere hvordan du får tilgang til endringssporingsdataene som genereres for DISSE dml-operasjonene. For referanse, her er tabellstrukturen.
jeg viste tidligere hvordan du aktiverer endringssporing for et enkelt bord ved HJELP AV BRUKERGRENSESNITTET. Jeg foretrekker Å bruke T-SQL for denne oppgaven, siden det er lettere repeterbart. Aktivering av endringssporing for tabellen jeg opprettet ovenfor, kan gjøres som vist her:
Husk at jeg tidligere nevnte at endringssporing bruker en versjon-ID for å spore gjeldende versjon av de sporede tabellene. Den versjonen ID er vår tidslinje markør for å oppdage endringer. FOR å hente den verdien, er det en veldig enkel funksjon: CHANGE_TRACKING_CURRENT_VERSION (). Den brukes som vist nedenfor.
på testsystemet mitt er denne verdien 470 (siden jeg har kjørt flere tester før denne skrivingen). Dette er utgangspunktet, og eventuelle endringer fra dette punktet vil utløse et nytt versjonsnummer. Jeg skal notere den verdien, og vil nå gjøre noen endringer i tabellen beskrevet ovenfor. Jeg setter inn en håndfull rader for å vise hvordan endringssporing viser innlegg.
etter å ha satt inn disse seks radene, sjekker jeg VERDIEN CHANGE_TRACKING_CURRENT_VERSION() igjen og finner ut at verdien nå er 476. Det har blitt okt med 6-en per rad satt inn, noe som jeg forventer.
Bruke Endringssporingsfunksjoner
Deretter bruker Vi endringssporingsfunksjonen CHANGETABLE () for å vise nettoendringene i denne tabellen.
for å bryte dette ned:
- CHANGETABLE er tabellverdisystemfunksjonen som vil returnere listen over endringer som er lagret i endringssporing
- ENDRINGER indikerer at jeg leter etter endringene siden den angitte versjonen
- @ver er variabelen jeg satte opp for å lagre versjonsnummeret. CHANGETABLE vil returnere alle resultatene som gjenspeiler endringer siden denne versjonen. Merk at du kan bruke en variabel som jeg gjorde, eller bare passere i et skalarnummer (ved å bruke den bokstavelige 470 her ville ha oppnådd det samme)
Når jeg kjører koden ovenfor, får jeg følgende resultatsett.
dette forteller meg hvilken versjon av sett inn og/eller oppdateringen, operasjonen (I, U eller d for sett inn, oppdater eller slett, henholdsvis), kolonnemasken for oppdateringsoperasjoner (mer om dette øyeblikkelig) og primærnøkkelen for raden som påvirkes av denne endringen. FORDI CHANGETABLE () returnerer et bord, kan jeg lett bli med i dette resultatsettet tilbake til det opprinnelige tabellen for å se endringsoperasjonen sammen med gjeldende data i tabellen.
Dette vil se litt annerledes ut for en oppdateringsoperasjon. Deretter skal jeg utføre en oppdateringserklæring, men først skal jeg merke den nåværende versjonen av endringssporing (som fortsatt er 476).
nå update-setningen, som oppdaterer to rader i tabellen:
nå når JEG kjører CHANGETABLE () – koden ovenfra, bruker jeg den nyere endringssporingsversjonen (476) som utgangspunkt, får jeg et annet resultatsett:
dette er metadataene for alle endringene siden versjon 476, som bare inneholder de to radene som er oppdatert fra UPDATE-setningen ovenfor. Legg merke til at opprettelsesversjonen er null, fordi denne endringen var en oppdatering, ikke en innsats. OGSÅ SYS_CHANGE_COLUMNS-verdien er nå fylt ut, selv om verdien ikke viser oss hva som har endret seg (ennå). Dette er en god tid å snakke om endringssporingsfunksjonen CHANGE_TRACKING_IS_COLUMN_IN_MASK (). Denne funksjonen vil sjekke om den angitte kolonnen har blitt oppdatert siden den nyeste versjonen. Dens syntaks er litt quirky, men for å sjekke om Mellomnavnet ble oppdatert, ville spørringen se slik ut:
Ærlig, Jeg vet ikke At JEG noen gang har brukt CHANGE_TRACKING_IS_COLUMN_IN_MASK-funksjonen. Det er litt vondt fordi du må kjøre dette for hver kolonne du vil sjekke. Det meste av arbeidet mitt er i datavarehus – og jeg har kjørt inn i få tilfeller der jeg trenger å vite nøyaktig hvilke kolonner som ble oppdatert-jeg vil bare vite om raden er oppdatert. Men for andre scenarier (spesielt I OLTP), kan jeg se behovet for dette.
jeg har vist innlegg og oppdateringer. La oss se på hva en sletting ville se ut. Igjen vil jeg notere det nåværende versjonsnummeret-478 – for neste operasjon. Jeg vil nå slette en rad med data:
etter å ha slettet en rad, kjører JEG CHANGETABLE () igjen for å se hvilke endringssporingsrapporter for denne operasjonen.
jeg finner den ene raden jeg slettet i den siste operasjonen, MED SYS_CHANGE_OPERATION satt Til D (slett):
nå, husk at versjonsnummeret gjør en forskjell her! Versjonsnummeret som sendes INN I CHANGETABLE (), er utgangspunktet for eventuelle endringer som returneres av denne funksjonen. Gjennom denne øvelsen har jeg sjekket endringssporingsresultatene etter hver DML-operasjon. Imidlertid kan jeg sette startversjonsnummeret til et gyldig versjonsnummer, eller bare bruke NULL for å få alle tilgjengelige endringssporingsresultater for den tabellen. For å demonstrere, setter jeg verdien tilbake til versjon 470-utgangspunktet før noen oppdateringer – for å vise hvordan hele historien vil se ut. NÅR JEG kjører CHANGETABLE () ved hjelp av vår opprinnelige endringssporingsversjon, får jeg følgende:
Det er et par forutsigbare nyanser her. Først og fremst viser raden som viser rekord-ID på 1 (Som Var Phoebe Buffay-posten jeg slettet) bare som en sletteoperasjon, selv om denne raden ble satt inn og deretter slettet siden startversjonsnummeret. Husk at det er deltaet som vil bli vist-hver operasjon mot den raden beholdes ikke i endringssporing. For Id-er nummerert 2 og 4-som var de to radene jeg satte inn og deretter oppdatert-VISER SYS_CHANGE_OPERATION en innsats, selv om vi oppdaterte begge postene etter innsats. Tell er AT SYS_CHANGE_VERSION og SYS_CHANGE_CREATION_VERSION på disse radene ikke samsvarer, noe som indikerer at den siste endringen ikke var innsatsen.
Konklusjon
Endringssporing er en enkel og lett måte å endre gjenkjenning I SQL Server. Bruk av endringssporing gjør det enkelt å identifisere nye, endrede og slettede data, noe som eliminerer behovet for brute-force-sammenligninger. I mitt neste innlegg ser jeg på dette fra et ETL-perspektiv, og integrerer endringssporing i en ende-til-ende-lastprosess.