muutoksen seuranta SQL Server-palvelimelle on joustava ja helppokäyttöinen tekniikka insertien, päivitysten ja poistojen seurantaan. Tässä viestissä, aion keskustella alkuun muutoksen seuranta SQL Server, ja näyttää esimerkin siitä, miten päästä alkuun sen kanssa.
SQL Serverin Muutosseuranta
Muutosseuranta on kevyt mekanismi, jolla seurataan, mitkä rivit on lisätty, päivitetty ja poistettu taulukoista, joita seurataan muutosseurannalla. Muutos seuranta ilmestyi ensimmäisen kerran SQL Server 2008 ja on ollut jokaisessa versiossa siitä lähtien. Vielä parempi, muutos seuranta on saatavilla jokaisessa painos SQL Server, jopa ilmainen express edition.
pähkinänkuoressa, näin se toimii: Taulukoissa, joissa muutoksen seuranta on käytössä, kunkin rivin nettovaihtoa seurataan sisäisesti ja se on käytettävissä muutosten seurantatoimintojen kautta. Jokaiseen muutokseen on liitetty versiotunnus, ja jokaiselle riville, joka lisätään, päivitetään tai poistetaan, tulee uusi versiotunnus. Muutosseurantaversio on 8-tavuinen kokonaisluku (BIGINT), joka heijastaa kyseisen tietokannan viimeisintä muutostunnusta. On tärkeää huomata, että muutoksen seurantaversio ei ole taulukkokohtainen – DML-toiminto missä tahansa seuratussa taulukossa kussakin tietokannassa kasvattaa versionumeroa. Versionumero on aina peräkkäinen, mutta se ei välttämättä ole yhtenäinen yhden taulukon sisällä, jos muutosseurantaa varten on käytössä useampi kuin yksi taulukko.
muutosseuranta on käytössä tietokantatasolla. Sen jälkeen jokainen seurattava taulukko on erikseen värvättävä muutosseurantaan. Jokaisella muutosseurannan avulla seurattavalla taulukolla on oltava ensisijainen avain, koska tämä on rivitason tunniste, jota käytetään raportoimaan DML-operaatioista muutosseurannassa. Kun otat muutosten seurannan käyttöön taulukkotasolla, voit seurata viimeisimmässä päivityksessä muutettuja sarakkeita, mikä antaa sinulle paremman näkyvyyden siihen, mitä on muutettu.
perustamisen jälkeen muutosten seuranta on suhteellisen yksinkertainen prosessi. On olemassa muutamia toimintoja-erityisesti, CHANGE_TRACKING_CURRENT_VERSION() ja CHANGETABLE(), joita voidaan käyttää tarkistaa nykyisen version leima muutosseurannassa ja hakea luettelo viimeaikaisista muutoksista. Esittelen molemmat toiminnot pian.
Muutosseuranta ei ole Audit-lokia
Aion varoa käyttämästä sanoja audit tai logging kuvaamaan muutosseurantaa. Haluan tehdä tämän selväksi: tämä ei ole täydellinen kirjaamismekanismi. Muutoshistoriaa ei seurata lainkaan – muutosseuranta kertoo vain muutoksen tapahtuneen, mutta ei säilytä versiohistoriaa. Tarkastellaan tapauksessa rivi tietoja ID 1234. Tämä rivi lisätään, päivitetään 5 kertaa, ja sitten poistetaan. Muutosseuranta ei näytä lisää -, päivitys-ja poistohistoriaa; sen sijaan se ilmoittaa vain nettomuutoksen, että rivi ID 1234 on poistettu. Jos latausprosessi vaatii yksityiskohtaisen kirjaushistorian jokaiselle muutokselle (eikä vain kaikkien muutosten delta), sinun täytyy käyttää jotain muuta tietojen kaappausta.
SQL Server
Taulutason muutosseurannan käyttöönotto on kaksivaiheinen prosessi. Ensinnäkin, se on otettava käyttöön tietokantaan. Tämä voidaan tehdä käyttöliittymän kautta tietokannan ominaisuudet, on muutos seuranta-välilehti.
kuten näkyy, ei ole paljon konfiguroitavaa otettaessa muutosten seuranta käyttöön tietokannassa. Aseta muutoksen seuranta-arvo True-arvoksi, jos haluat määrittää muutoksen seurannan kyseiselle tietokannalle. Vaihtoehtoisesti retentioajan arvo voidaan virittää samoin. Oletusarvo on 2 päivää, jonka olen ohittanut tässä esimerkissä käyttääkseni 14 päivää sen sijaan. Kuten useimmissa KÄYTTÖLIITTYMÄOPERAATIOISSA, myös T-SQL-komennossa voi tehdä saman asian. Komento määrittää muutoksen seuranta tässä tietokannassa on alla.
tämän vaiheen jälkeen muutosseuranta on käytössä, mutta se ei vielä seuraa mitään. Se on vielä otettava käyttöön kunkin taulukon seurata. Table Properties UI tekee tästä erittäin helppoa.
kuten näkyy, yksinkertaisesti muuttamalla muutoksen seuranta-arvo True mahdollistaa muutoksen seurannan tässä taulukossa. Tässä esimerkissä päätin myös seurata sarakkeita muuttunut päivitysten aikana (lisää tästä vähän).
viimeinen edellä oleva vaihe toistettaisiin jokaisen seurattavan taulukon kohdalla muutosseurannassa. Kun muutosseuranta on käytössä, kaikki muutokset (lisäykset, päivitykset tai poistot) kyseiseen taulukkoon tallennetaan muutosseurantavälimuistiin.
muutosten seurannan määrittäminen
edellä mainitussa esimerkissä aion lisätä, päivittää ja poistaa joitakin tietoja osoittaakseni, miten käyttää näitä DML-toimintoja varten luotuja muutosseurantatietoja. Viitteeksi tässä on taulukon rakenne.
näytin aiemmin, miten yhden taulukon muutosseuranta voidaan ottaa käyttöön käyttöliittymällä. Käytän mieluummin T-SQL tähän tehtävään, koska se on helpommin toistettavissa. Muutosten seurannan mahdollistaminen yllä olevaan taulukkoon voidaan tehdä kuten tässä:
muista, että mainitsin aiemmin, että muutosseuranta käyttää versiotunnusta seurattavien taulukoiden nykyisen version seuraamiseen. Versiotunnus on aikajanamerkki muutosten havaitsemiseen. Kyseisen arvon hakemiseen on hyvin yksinkertainen funktio: CHANGE_TRACKING_CURRENT_VERSION(). Sitä käytetään alla esitetyllä tavalla.
minun testijärjestelmässäni tämä arvo on 470 (koska olen tehnyt useita testejä ennen tätä kirjoitusta). Tämä on lähtökohta, ja kaikki tästä eteenpäin tehdyt muutokset laukaisisivat uuden versionumeron. Teen muistiin, että arvo, ja nyt tehdä joitakin muutoksia edellä kuvattuun taulukkoon. Laitan kourallisen rivejä näyttämään, miten muutosseurantanäytöt lisääntyvät.
asetettuani nämä kuusi riviä tarkistan change_tracking_current_version () – arvon uudelleen ja huomaan, että arvo on nyt 476. Sitä on lisätty 6-yksi per rivi lisätään, mikä on mitä olisin odottanut.
käyttäen Muutosseurantafunktioita
seuraavaksi käytämme muutosseurantafunktiota CHANGETABLE() näyttääksemme nettomuutokset tässä taulukossa.
tämän hajottamiseksi:
- CHANGETABLE on taulukkoarvostettu järjestelmäfunktio, joka palauttaa luettelon muutoksista, jotka on tallennettu muutosseurantaan
- muutokset osoittavat, että etsin muutoksia, koska määritelty versio
- @ver on muuttuja, jonka asetin tallentamaan versionumeron. CHANGETABLE palauttaa kaikki tulokset, jotka heijastavat muutoksia tämän version jälkeen. Huomaa, että voit käyttää muuttujaa kuten minä, tai vain siirtää skalaariluku (käyttämällä kirjaimellista 470 tässä olisi saavutettu sama asia)
kun suoritan yllä olevan koodin, saan seuraavan tulosjoukon.
tämä kertoo minulle lisäyksen ja/tai päivityksen version, toiminnon (i, U, tai D, Jos lisätään, Päivitä tai poista), päivitystoimintojen sarakkeen maskin (lisää tästä hetkellisesti) ja rivin ensisijaisen avaimen, johon tämä muutos vaikutti. Koska CHANGETABLE () palauttaa taulukon, voisin helposti liittyä tämän tulosjoukon takaisin alkuperäiseen taulukkoon nähdäksesi muutosoperaation yhdessä nykyisen taulukon tietojen kanssa.
tämä näyttää hieman erilaiselta päivitysoperaatiossa. Seuraavaksi suoritan päivityksen lausuman, mutta ensin, aion huomata nykyisen version muutoksen seuranta (joka on edelleen 476).
nyt päivityslauseke, joka päivittää taulukossa kaksi riviä:
nyt kun suoritan CHANGETABLE () – koodin ylhäältä käyttäen uudempaa change tracking-versiota (476) lähtökohtana, saan erilaisen tulosjoukon:
tämä on kaikkien muutosten metatiedot versiosta 476 lähtien, joka sisältää vain kaksi riviä päivitettynä yllä olevasta PÄIVITYSLAUSEKKEESTA. Huomaa, että luontiversio on null, koska tämä muutos oli päivitys, ei insertti. Myös, sys_change_columns arvo on nyt asuttu, vaikka arvo ei oikeastaan näytä meille, mikä on muuttunut (vielä). Tämä on hyvä aika puhua muutoksen seurantatoiminto CHANGE_TRACKING_IS_COLUMN_IN_MASK(). Tämä toiminto tarkistaa, onko määritettyä saraketta päivitetty viimeisimmän version jälkeen. Sen syntaksi on hieman omituinen, mutta tarkistaakseni, onko MiddleName päivitetty, kysely näyttäisi tältä:
rehellisesti, en tiedä, että olen koskaan käyttänyt CHANGE_TRACKING_IS_COLUMN_IN_MASK funktiota. Se on hieman tuskaa, koska sinun täytyy ajaa tämä jokaisen sarakkeen haluat tarkistaa. Suurin osa työstäni on tietovarastointia, ja olen törmännyt harvoihin tapauksiin, joissa minun pitäisi tietää tarkalleen, mitkä sarakkeet on päivitetty – haluan vain tietää, onko rivi päivitetty. Kuitenkin, muissa skenaarioissa (erityisesti OLTP), näen tämän tarpeen.
olen osoittanut insertit ja päivitykset. Katsotaan, miltä poisto näyttäisi. Jälleen, teen muistiin nykyisen version numero-478 – seuraavaa operaatiota varten. Poistan nyt yhden rivin tietoja:
Poistettuani yhden rivin ajan CHANGETABLEN () uudelleen nähdäkseni mitä muutosseurantaraportteja tälle operaatiolle.
löydän viimeisen toiminnon poistetun rivin, jossa sys_change_operaatio on asetettu D: ksi (poista):
nyt, muista, että versionumero tekee eron täällä! CHANGETABLE-järjestelmään() siirretty versionumero on lähtökohta kaikille kyseisen funktion palauttamille muutoksille. Tämän harjoituksen kautta olen tarkistanut muutoksen seurantatulokset jokaisen DML-operaation jälkeen. Voin kuitenkin asettaa lähtöversion numeron mihin tahansa voimassa olevaan versionumeroon tai yksinkertaisesti käyttää NULL: ää saadaksesi kaikki käytettävissä olevat muutoksen seurantatulokset kyseiseen taulukkoon. Osoittaakseni, asetan arvon takaisin versioon 470 – lähtöpisteeseen ennen päivityksiä-näyttääkseni, miltä koko historia näyttäisi. Kun uudelleen CHANGETABLE() käyttämällä alkuperäistä muutos seuranta versio, saan seuraavat:
tässä on pari ennustettavaa vivahdetta. Ensinnäkin, rivi, joka näyttää tietuetunnus 1 (joka oli Phoebe Buffay ennätys poistin) näkyy yksinkertaisesti poistaa toimintaa, vaikka tämä rivi lisättiin ja myöhemmin poistettu alkaen Version numero. Muista, se on delta, joka näytetään – jokainen toiminto, että rivi ei säily muutoksen seuranta. Tunnuksille 2 ja 4 – jotka olivat kaksi riviä, jotka lisäsin ja myöhemmin päivitin – SYS_CHANGE_OPERATION näyttää insertin, vaikka päivitimme molemmat tietueet insertin jälkeen. Kertoo, että sys_change_version ja sys_change_creation_version näillä riveillä eivät täsmää, mikä osoittaa, että viimeisin muutos ei ollut insertti.
Conclusion
Change tracking on yksinkertainen ja kevyt keino muutoksen havaitsemiseen SQL Server-palvelimessa. Käyttämällä change tracking mahdollistaa uusien, muuttuneiden ja poistettujen tietojen helpon tunnistamisen, jolloin ei tarvita raakaa voimaa koskevia vertailuja. Seuraavassa viestissäni katson tätä ETL: n näkökulmasta integroimalla muutoksen seurannan päästä päähän-kuormitusprosessiin.