Megosztás:

Adatváltozások nyomon követése (SQL Server)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példánySQL-adatbázis a Microsoft Fabricben

Az SQL Server két olyan funkciót biztosít, amelyek nyomon követik az adatbázis adatainak változásait: adatrögzítési és változáskövetési. Ezek a funkciók lehetővé teszik az alkalmazások számára, hogy meghatározzák az adatbázis felhasználói tábláiban végrehajtott DML-módosításokat (beszúrási, frissítési és törlési műveleteket). A változásadat-rögzítés és a változáskövetés engedélyezhető ugyanazon az adatbázisban; nincs szükség különleges szempontokra. Az SQL Server adatrögzítést és változáskövetést támogató kiadásaiért lásd az SQL Server 2022 kiadásait és támogatott funkcióit.

A változásadat-rögzítés vagy a változáskövetés használatának előnyei

Az adatbázisokban megváltozott adatok lekérdezésének képessége fontos követelmény, hogy egyes alkalmazások hatékonyak legyenek. Az adatváltozások meghatározásához az alkalmazásfejlesztőknek általában egyéni nyomkövetési módszert kell implementálniuk az alkalmazásaikban triggerek, időbélyeg oszlopok és további táblák kombinációjával. Ezeknek az alkalmazásoknak a létrehozása általában sok munkát igényel a megvalósításhoz, sémafrissítésekhez vezet, és gyakran nagy teljesítménybeli többletterhelést jelent.

A változásadatok rögzítésének vagy változáskövetésének használata az alkalmazásokban az adatbázisok változásainak nyomon követésére egyéni megoldás fejlesztése helyett a következő előnyökkel jár:

  • Kevesebb fejlesztési idő áll rendelkezésre. Mivel a funkciók elérhetők az SQL Serverben, nem kell egyéni megoldást fejlesztenie.

  • Nincs szükség sémamódosításra. Nem kell oszlopokat hozzáadnia, eseményindítókat hozzáadnia, vagy melléktáblát létrehoznia, amelyekben nyomon követheti a törölt sorokat, illetve nem kell tárolnia a változáskövetési információkat, ha az oszlopok nem vehetők fel a felhasználói táblákba.

  • Van egy beépített tisztítási mechanizmus. A változáskövetés tisztítása automatikusan végrehajtódik a háttérben. Az oldaltáblákban tárolt adatok egyéni karbantartása nem szükséges.

  • A függvények a változási információk lekérésére szolgálnak.

  • A DML-műveletek alacsony többletterheléssel járnak. A szinkron változáskövetésnek mindig van némi terhelése. A változáskövetés használata azonban segíthet minimalizálni a többletterhelést. A többletterhelés gyakran kisebb lesz, mint az alternatív megoldások használata, különösen azok a megoldások, amelyek triggerek használatát igénylik.

  • A változáskövetés véglegesített tranzakciókon alapul. A módosítások sorrendje a tranzakció véglegesítési ideje alapján történik. Ez lehetővé teszi, hogy megbízható eredményeket lehessen elérni, ha hosszú ideig futó és átfedésben lévő tranzakciók vannak. Az időbélyeget értékeket használó egyéni megoldásokat úgy kell megtervezni, hogy kezelni tudják ezeket a forgatókönyveket.

  • A konfiguráláshoz és kezeléshez standard eszközök állnak rendelkezésre. Az SQL Server szabványos DDL-utasításokat, SQL Server Management Studiót, katalógusnézeteket és biztonsági engedélyeket biztosít.

Funkcióbeli különbségek a változásadatok rögzítése és a változáskövetés között

Az alábbi táblázat a változásadat-rögzítés és a változáskövetés funkcióbeli különbségeit sorolja fel. A változásadat-rögzítés nyomkövetési mechanizmusa magában foglalja a tranzakciónapló módosításainak aszinkron rögzítését, hogy a módosítások elérhetők legyenek a DML-művelet után. A változáskövetési mechanizmus magában foglalja a változások szinkron nyomon követését a DML-műveletekkel összhangban, így a változásadatok azonnal elérhetők lesznek.

Funkció Adatrögzítés módosítása Változáskövetés
Követett változtatások
DML-módosítások Igen Igen
Nyomon követett információk
Előzményadatok Igen Nem
Az oszlop módosult-e Igen Igen
DML-típus Igen Igen

Adatrögzítés módosítása

A módosítási adatrögzítés előzménymódosítási információkat biztosít a felhasználói táblákhoz a DML-módosítások és a módosított tényleges adatok rögzítésével. A módosításokat egy aszinkron folyamattal rögzítik, amely beolvassa a tranzakciónaplót, és kis hatással van a rendszerre.

Az alábbi ábrán látható módon a felhasználói táblákon végrehajtott módosítások a megfelelő változástáblákban lesznek rögzítve. Ezek a változástáblák történeti áttekintést nyújtanak a változások időbeli folyamatáról. Az SQL Server által biztosított Change Data Capture Functions függvények lehetővé teszik a változásadatok egyszerű és szisztematikus használatát.

diagram a változásadatok rögzítésének fogalmát mutatja be.

Biztonsági modell

Ez a szakasz a változásadat-rögzítés biztonsági modelljét ismerteti.

Konfigurálás és felügyelet

Az adatbázis adatrögzítésének engedélyezéséhez vagy letiltásához a sys.sp_cdc_enable_db vagy sys.sp_cdc_disable_db hívójának a rögzített kiszolgálói sysadmin szerepkör tagjának kell lennie. A változásadat-rögzítés táblaszintű engedélyezéséhez és letiltásához a sys.sp_cdc_enable_table és sys.sp_cdc_disable_table hívójának vagy a sysadmin szerepkör tagjának vagy az adatbázis-adatbázis db_owner szerepkör tagjának kell lennie.

A tárolt eljárások használata a változásadat-rögzítési feladatok felügyeletének támogatására a kiszolgáló sysadmin szerepkörre és a adatbázis db_owner szerepkör tagjaira korlátozódik.

Enumerálási és metaadat-lekérdezések módosítása

A rögzítési példányhoz társított változásadatokhoz való hozzáféréshez a felhasználónak hozzáférést kell biztosítani SELECT a társított forrástábla összes rögzített oszlopához. Ezenkívül ha a rögzítési példány létrehozásakor egy gating szerepkört ad meg, a hívónak is a megadott gating szerepkör tagjának kell lennie, és a változásadat-rögzítési sémának (cdc) hozzáféréssel kell rendelkeznie SELECT az adatrögzítési szerepkörhöz.

A metaadatok elérésére szolgáló egyéb általános adatrögzítési függvények a nyilvános szerepkörön keresztül minden adatbázis-felhasználó számára elérhetők lesznek, bár a visszaadott SELECT metaadatokhoz való hozzáférés általában az alapul szolgáló forrástáblákhoz való hozzáféréssel, valamint a meghatározott betöltési szerepkörök tagságával is elérhető lesz.

DDL-műveletek az adatrögzítést lehetővé tevő forrástáblák módosításához

Ha egy tábla engedélyezve van a változásadat-rögzítéshez, a DDL-műveleteket csak a rögzített kiszolgálói szerepkör egy tagja, sysadmin, a adatbázis-szerepkör db_ownertagja vagy a adatbázis-szerepkör tagja db_ddladminalkalmazhatja a táblára. Azok a felhasználók, akik kifejezetten engedélyezik a DDL-műveletek végrehajtását a táblán, 22914-et kapnak, ha megpróbálják ezeket a műveleteket.

Adattípussal kapcsolatos szempontok a változásadatok rögzítéséhez

A módosítási adatrögzítés minden alaposzloptípust támogat. Az alábbi táblázat több oszloptípus viselkedését és korlátozásait sorolja fel.

Oszlop típusa A változástáblákban rögzített módosítások Korlátozások
Ritka oszlopok Igen Oszlopkészlet használata esetén nem támogatja a módosítások rögzítését.
Számított oszlopok Nem A számított oszlopok módosításait nem követi nyomon a rendszer. Az oszlop a megfelelő típussal jelenik meg a változástáblában, de értéke NULLa következő lesz: .
XML Igen Az egyes XML-elemek módosításai nem lesznek nyomon követve.
Időbélyeg Igen A változástáblában szereplő adattípus binárissá lesz konvertálva.
BLOB-adattípusok Igen A BLOB oszlop előző képe csak akkor lesz tárolva, ha magát az oszlopot módosítja.

SQL Server-szolgáltatásintegráció

Ez a szakasz azt ismerteti, hogy az alábbi funkciók hogyan használják a módosítási adatrögzítést:

  • Adatbázis-tükrözés
  • Tranzakciós replikáció
  • Adatbázis visszaállítása vagy csatolása

Adatbázis-tükrözés

A módosítási adatrögzítéshez engedélyezett adatbázisok tükrözhetők. Annak érdekében, hogy a rögzítés és a törlés automatikusan megtörténjen a tükörben, kövesse az alábbi lépéseket:

  1. Győződjön meg arról, hogy az SQL Server Agent fut a tükörszerveren.

  2. Hozza létre a rögzítési és tisztítási feladatot a tükrön, miután az elsődleges rendszer a tükrözésre került áthelyezésre. A feladatok létrehozásához használja a tárolt eljárást sys.sp_cdc_add_job.

Az adatbázis-tükrözéssel kapcsolatos további információk: Database Mirroring (SQL Server).

Tranzakciós replikáció

A módosítási adatrögzítés és a tranzakciós replikáció együtt létezhet ugyanabban az adatbázisban, de a változástáblák sokasága eltérően lesz kezelve, ha mindkét funkció engedélyezve van. Az adatrögzítés és a tranzakciós replikáció módosítása mindig ugyanazt az eljárást használja, sp_replcmdsa tranzakciónapló módosításainak olvasásához. Amikor az adatváltozás rögzítés önmagában engedélyezett, egy SQL Server Agent-feladat meghívja sp_replcmds. Ha mindkét funkció engedélyezve van ugyanazon az adatbázisban, a Naplóolvasó ügynök meghívja sp_replcmds. Ez az ügynök feltölti a változástáblákat és a distribution adatbázistáblákat is. További információ: replikációs naplóolvasó ügynök.

Fontolja meg azt a forgatókönyvet, amelyben a változásadat-rögzítés engedélyezve van a AdventureWorks2025 adatbázison, és két tábla van engedélyezve a rögzítéshez. A változástáblák feltöltéséhez a rögzítési feladat meghívja a sp_replcmds-t. Az adatbázis engedélyezve van a tranzakciós replikációhoz, és létrejön egy kiadvány. Most létrejön a Naplóolvasó ügynök az adatbázishoz, és a rögzítési feladat törlődik. A Naplóolvasó ügynök továbbra is átvizsgálja a naplót az elmentett utolsó naplósorszámtól, amely a változástáblához tartozik. Ez biztosítja az adatkonzisztenciát a változástáblákban. Ha a tranzakciós replikáció le van tiltva ebben az adatbázisban, a naplóolvasó ügynök el lesz távolítva, és a rögzítési feladat újra létrejön.

Jegyzet

Ha a naplóolvasó ügynök a változásadat-rögzítéshez és a tranzakciós replikációhoz is használatos, a replikált módosítások először a distribution adatbázisba lesznek beírva. Ezután a rendszer a rögzített módosításokat a változástáblákba írja. Mindkét művelet együtt van elkönyvelve. Ha késés tapasztalható az distribution adatbázisba való írás során, a módosítások a változástáblákban való megjelenése előtt megfelelő késéssel fognak rendelkezni.

Változáskövetéshez engedélyezett adatbázis visszaállítása vagy csatolása

Az SQL Server az alábbi logikával állapítja meg, hogy az adatbázis visszaállítása vagy csatolása után a változásadat-rögzítés engedélyezve marad-e:

  • Ha egy adatbázist ugyanarra a kiszolgálóra állít vissza ugyanazzal az adatbázisnévvel, a módosítási adatrögzítés engedélyezve marad.

  • Ha egy adatbázist egy másik kiszolgálóra állít vissza, alapértelmezés szerint a módosítási adatrögzítés le van tiltva, és az összes kapcsolódó metaadat törlődik.

    A változásadatok rögzítésének megőrzéséhez használja a KEEP_CDC lehetőséget az adatbázis visszaállításakor. Erről a beállításról további információt a VISSZAÁLLÍTÁSi utasítások című témakörben talál.

  • Ha egy adatbázis leválasztva és ugyanahhoz a kiszolgálóhoz vagy egy másik kiszolgálóhoz van csatlakoztatva, az adatrögzítés továbbra is engedélyezve marad.

  • Ha egy adatbázist a Standard vagy a Nagyvállalati verziótól eltérő kiadások KEEP_CDC beállításával csatol vagy állít vissza, a művelet le lesz tiltva, mert a módosítási adatrögzítéshez SQL Server Standard vagy Enterprise kiadás szükséges. A 932-es számú hibaüzenet jelenik meg:

    SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

A sys.sp_cdc_disable_db használatával eltávolíthatja a módosítási adatrögzítést egy visszaállított vagy csatolt adatbázisból.

Változáskövetés

A változáskövetés rögzíti azt a tényt, hogy egy tábla sorai megváltoztak, de nem rögzíti a módosított adatokat. Ez lehetővé teszi az alkalmazások számára, hogy meghatározzák azokat a sorokat, amelyek megváltoztak, miközben a legújabb soradatok közvetlenül a felhasználói táblákból származnak. Ezért a változáskövetés korlátozottabb a változásadatok rögzítéséhez képest megválaszolható előzménykérdésekben. Azoknál az alkalmazásoknál azonban, amelyek nem igénylik az előzményadatokat, sokkal kisebb a tárterület terhelése, mert a módosított adatok nem lesznek rögzítve. A változások nyomon követésére szinkron nyomkövetési mechanizmust használunk. Ez úgy lett kialakítva, hogy minimális többletterheléssel rendelkezzen a DML-műveletekre.

Az alábbi ábra egy olyan szinkronizálási forgatókönyvet mutat be, amely a változáskövetés használatával előnyös lenne. A forgatókönyvben egy alkalmazásnak a következő információkra van szüksége: a tábla összes olyan sora, amely a tábla legutóbbi szinkronizálása óta módosult, és csak az aktuális soradatok. Mivel a módosítások nyomon követésére szinkron mechanizmust használnak, az alkalmazások kétirányú szinkronizálást végezhetnek, és megbízhatóan észlelhetik az esetleges ütközéseket.

diagram a változáskövetés fogalmát mutatja be.

Változáskövetés és szinkronizálási szolgáltatások ADO.NET

A sync services for ADO.NET lehetővé teszi az adatbázisok közötti szinkronizálást, és intuitív és rugalmas API-t biztosít, amellyel offline és együttműködési forgatókönyveket célzó alkalmazásokat hozhat létre. A sync services for ADO.NET egy API-t biztosít a módosítások szinkronizálásához, de valójában nem követi nyomon a módosításokat a kiszolgálón vagy a társadatbázisban. Létrehozhat egyéni változáskövetési rendszert, de ez általában jelentős összetettséggel és teljesítményterheléssel jár. A kiszolgálók vagy társadatbázisok változásainak nyomon követéséhez javasoljuk, hogy a változáskövetést használja az SQL Serverben, mert könnyen konfigurálható, és nagy teljesítményű nyomon követést biztosít.

Az ADO.NET változáskövetésével és szinkronizálási szolgáltatásaival kapcsolatos további információkért használja az alábbi hivatkozásokat:

Kapcsolódó tartalom

  • Last updated on