a változáskövetés az SQL Server számára rugalmas és könnyen használható technológia a Beszúrások, frissítések és törlések felügyeleti tábláihoz. Ebben a bejegyzésben megvitatom az SQL Server változáskövetésének első lépéseit, és bemutatok egy példát arra, hogyan kell elkezdeni.
változáskövetés az SQL Server-ben
a változáskövetés egy könnyű mechanizmus annak nyomon követésére, hogy mely sorok kerültek beillesztésre, frissítésre és törlésre a változáskövetés által felügyelt táblákba. A változáskövetés először az SQL Server 2008-ban jelent meg, azóta minden verzióban megtalálható. Még jobb, a változáskövetés az SQL Server minden kiadásában elérhető, még az ingyenes express kiadásban is.
dióhéjban, itt van, hogyan működik: Azokban a táblázatokban, amelyekben engedélyezve van a változáskövetés, az egyes sorok nettó változása belső nyomon követésre kerül, és a változáskövetési funkciókon keresztül érhető el. Minden változáshoz hozzá van csatolva egy verzióazonosító, és minden beillesztett, frissített vagy törölt sorhoz új verzióazonosító lesz. A változáskövetési verzió egy 8 bájtos egész szám (BIGINT), amely az adott adatbázis legutóbbi változási azonosítóját tükrözi. Fontos megjegyezni, hogy a változáskövetési verzió nem táblázatspecifikus – az egyes adatbázisok bármely lánctalpas táblájában végzett DML-művelet növeli a verziószámot. A verziószám mindig szekvenciális lesz, de nem feltétlenül lesz összefüggő egyetlen táblán belül, ha egynél több tábla van engedélyezve a változáskövetéshez.
a változáskövetés adatbázis szinten engedélyezett. Ezt követően minden megfigyelt táblát külön-külön be kell vonni a változáskövetésbe. Minden változáskövetéssel nyomon követendő táblának rendelkeznie kell egy elsődleges kulccsal, mivel ez a sorszintű azonosító, amelyet a VÁLTOZÁSKÖVETÉSEN belüli DML-műveletek jelentésére használnak. Ha engedélyezi a változások nyomon követését a táblázat szintjén, választhatja a legutóbbi frissítésben megváltozott oszlop(ok) nyomon követését, amely nagyobb láthatóságot biztosít a megváltozott dolgokban.
a beállítás után a változáskövetés viszonylag egyszerű folyamat. Van néhány funkció – nevezetesen a CHANGE_TRACKING_CURRENT_VERSION() és a CHANGETABLE (), amelyek segítségével ellenőrizheti az aktuális verzió bélyegzőjét a változáskövetés során, és lekérheti a legutóbbi változások listáját. Hamarosan bemutatom mindkét funkciót.
A változáskövetés nem naplózási naplózás
vigyázni fogok, hogy ne használjam az audit vagy naplózás szavakat a változáskövetés leírására. Hadd legyek világos: ez nem egy teljes naplózási mechanizmus. A változás előzményeit a rendszer egyáltalán nem követi nyomon – a változáskövetés csak arról számol be, hogy változás történt, de nem őrzi meg a verzióelőzményeket. Vegyük figyelembe az 1234 azonosítóval rendelkező adatsor esetét. Ez a sor beillesztésre kerül, majd 5 alkalommal frissül, majd törlődik. A változáskövetés nem jeleníti meg a Beszúrás, frissítés és törlés előzményeit; inkább csak a nettó változást jelentené, az 1234 sorazonosítót törölték. Ha a betöltési folyamat részletes naplózási előzményeket igényel minden változáshoz (nem csak az összes változás delta-ját), akkor valami olyasmit kell használnia, mint az adatrögzítés módosítása.
változáskövetés beállítása az SQL Server rendszerben
a táblázatszintű változáskövetés engedélyezése kétlépcsős folyamat. Először engedélyezni kell az adatbázisban. Ezt az adatbázis tulajdonságainak felhasználói felületén, a változás követés lapon lehet elvégezni.
az ábrán látható módon nem sok mindent kell konfigurálni, amikor engedélyezi a változáskövetést egy adatbázisban. Egyszerűen állítsa True értékre a változáskövetés értékét az adott adatbázis változáskövetésének beállításához. Opcionálisan a megőrzési időszak értéke is módosítható. Az alapértelmezett érték 2 nap, amelyet ebben a példában felülírtam, hogy helyette 14 napot használjak. Mint a legtöbb UI műveletnél, van egy T-SQL parancs is, hogy ugyanezt tegye. Az alábbi parancs a változáskövetés beállítására szolgál ezen az adatbázison.
e lépés után a változáskövetés engedélyezve van, de még nem követ semmit. Még engedélyezni kell az egyes táblák nyomon követését. A Táblázat tulajdonságai UI teszi ezt nagyon egyszerű.
az ábrán látható módon a változáskövetési érték True értékre változtatása lehetővé teszi a táblázat változáskövetését. Ebben a példában azt is választottam, hogy nyomon követem a frissítések során megváltozott oszlopokat (erről bővebben egy kicsit).
a fenti utolsó lépés megismétlődik minden egyes táblázatban, amelyet nyomon kell követni a változáskövetés során. Ha a változáskövetés engedélyezve van, az adott tábla módosításai (Beszúrások, frissítések vagy törlések) a változáskövetés gyorsítótárában lesznek tárolva.
változáskövetés beállítása
a fenti példában beszúrok, frissítek és törölök néhány adatot, hogy bemutassam, hogyan lehet hozzáférni az adott DML műveletekhez generált változáskövetési adatokhoz. Referenciaként itt van a táblázat szerkezete.
korábban megmutattam, hogyan lehet engedélyezni a változáskövetést egyetlen táblához a felhasználói felület segítségével. Inkább a T-SQL-t használom erre a feladatra, mivel ez könnyebben megismételhető. A fent létrehozott táblázat változáskövetésének engedélyezése az itt látható módon történhet:
emlékezzünk arra, hogy korábban említettem, hogy a változáskövetés verzióazonosítót használ a nyomon követett táblák aktuális verziójának nyomon követésére. Ez a verzióazonosító a mi idővonal-jelölőnk a változások észleléséhez. Ennek az értéknek a lekéréséhez van egy nagyon egyszerű funkció: CHANGE_TRACKING_CURRENT_VERSION (). Az alábbiak szerint használják.
a tesztrendszeremen ez az érték 470 (mivel több tesztet futtattam az írás előtt). Ez a kiindulási pont, és az ettől a ponttól kezdve végrehajtott változtatások új verziószámot váltanak ki. Megjegyzem ezt az értéket, és most néhány változtatást hajtok végre a fent leírt táblázatban. Beszúrok egy maroknyi sort, hogy megmutassam, hogyan jelenik meg a változások követése.
a hat sor beszúrása után újra ellenőrzöm a CHANGE_TRACKING_CURRENT_VERSION() értéket, és megállapítom, hogy az érték most 476. Ez nőtt 6 – egy soronként egészül ki, ami az, amit elvárnék.
változáskövetési funkciók használata
ezután a változáskövetési funkció CHANGETABLE() segítségével mutatjuk be a táblázat nettó változásait.
ezt lebontani:
- a CHANGETABLE a táblázat értékű rendszerfunkció, amely visszaadja a változások nyomon követésében tárolt változások listáját
- változások azt jelzi, hogy a változásokat keresem, mivel a megadott verzió
- @ver az a változó, amelyet a verziószám tárolására állítottam be. A CHANGETABLE visszaadja az összes eredményt, amely a verzió óta bekövetkezett változásokat tükrözi. Ne feledje, hogy használhat egy változót, mint én, vagy csak átadhat egy skaláris számot (a szó szerinti 470 használatával itt ugyanezt sikerült volna elérni)
a fenti kód futtatásakor a következő eredménykészletet kapom.
ez megmondja a Beszúrás és/vagy frissítés verzióját, a műveletet (I, U vagy D a Beszúrás, frissítés vagy törlés esetén), a frissítési műveletek oszlopmaszkját (erről bővebben röviden), valamint a változás által érintett sor elsődleges kulcsát. Mivel a CHANGETABLE () egy táblát ad vissza, könnyen összekapcsolhatom ezt az eredményt az eredeti táblával, hogy láthassam a változtatási műveletet a táblázat aktuális adataival együtt.
ez egy kicsit másképp fog kinézni egy frissítési műveletnél. Ezután végrehajtok egy frissítési utasítást, de először megjegyzem a változáskövetés jelenlegi verzióját (amely még mindig 476).
most az update utasítás, amely frissíti a táblázat két sorát:
Most, amikor fentről futtatom a CHANGETABLE () kódot, a legújabb változáskövetési verziót (476) használva kiindulópontként, más eredménykészletet kapok:
ez a 476-os verzió óta bekövetkezett összes változás metaadata, amely csak a fenti frissítési utasításból frissített két sort tartalmazza. Figyelje meg, hogy a létrehozási verzió null, mert ez a változás frissítés volt, nem betét. Ezenkívül a SYS_CHANGE_COLUMNS érték most lakott, bár az érték nem igazán mutatja meg ,mi változott (még). Ez egy jó alkalom, hogy beszéljünk a change_tracking_is_column_in_mask () változáskövetési funkcióról. Ez a funkció ellenőrzi, hogy a megadott oszlop frissült-e a legutóbbi verzió óta. Szintaxisa kissé furcsa, de annak ellenőrzésére, hogy a MiddleName frissült-e, a lekérdezés így néz ki:
őszintén szólva, nem tudom, hogy valaha is használtam-e a CHANGE_TRACKING_IS_COLUMN_IN_MASK funkciót. Ez egy kicsit a fájdalom, mert meg kell futtatni ezt minden oszlop ellenőrizni kívánt. Munkám nagy része adattárházakban zajlik, és kevés olyan esetbe ütköztem, amikor pontosan tudnom kell, mely oszlopokat frissítették – csak azt szeretném tudni, hogy a sor frissült-e. Más forgatókönyvek esetében (különösen az OLTP-ben) azonban látom ennek szükségességét.
bemutattam a beszúrásokat és a frissítéseket. Nézzük meg, hogy nézne ki egy törlés. Ismét feljegyezem a jelenlegi verziószámot – 478-a következő művelethez. Most törölök egy sor adatot:
miután töröltem egy sort, újra futtatom a CHANGETABLE() függvényt, hogy lássam, milyen változáskövetési jelentések vannak ehhez a művelethez.
azt az egy sort találom, amelyet az utolsó műveletben töröltem, a SYS_CHANGE_OPERATION értéke D (törlés):
ne feledje, hogy a verziószám itt különbséget tesz! A VÁLTOZÓTÁBLA() függvénybe átadott Verziószám A függvény által visszaadott változások kiindulópontja. Ezzel a gyakorlattal minden DML művelet után ellenőriztem a változáskövetési eredményeket. A kezdő verziószámot azonban bármilyen érvényes verziószámra állíthatom, vagy egyszerűen A NULL használatával megkaphatom az adott táblázat összes elérhető változáskövetési eredményét. A bemutatáshoz visszaállítom az értéket a 470 – es verzióra – a frissítések előtti kiindulási pontra -, hogy megmutassam, hogyan nézne ki a teljes előzmények. Amikor újra futtatom a CHANGETABLE() programot az eredeti változáskövetési verziónkkal, a következőket kapom:
itt van néhány kiszámítható árnyalat. Először is, az 1 rekord azonosítóját mutató sor (amely a Phoebe Buffay rekord volt, amelyet töröltem) egyszerűen törlési műveletként jelenik meg, annak ellenére, hogy ezt a sort beillesztették, majd később törölték a kezdő Verziószám óta. Ne feledje, hogy a delta jelenik meg – az adott sorhoz tartozó minden művelet nem marad meg a változáskövetésben. A 2. és 4. számú azonosítók esetében – amelyek a két sor, amelyeket beszúrtam és később frissítettem-a SYS_CHANGE_OPERATION egy beszúrást jelenít meg, annak ellenére, hogy mindkét rekordot a Beszúrás után frissítettük. Ez azt jelzi, hogy a sys_change_version és a SYS_CHANGE_CREATION_VERSION ezen sorokban nem egyeznek, ami azt jelzi, hogy a legutóbbi változás nem a Beszúrás volt.
következtetés
a változáskövetés az SQL Server változásérzékelésének egyszerű és könnyű eszköze. A változáskövetés lehetővé teszi az új, megváltozott és törölt adatok egyszerű azonosítását, így nincs szükség brute-force összehasonlításra. A következő bejegyzésemben ezt ETL szempontból fogom megvizsgálni, integrálva a változáskövetést egy végponttól végpontig terjedő terhelési folyamatba.