Előzményadatok kezelése időbeli táblákban adatmegőrzési szabályzattal

A következőre vonatkozik: Azure SQL DatabaseFelügyelt Azure SQL-példány

A temporális táblák növelhetik az adatbázis méretét a normál tábláknál, különösen akkor, ha hosszabb ideig őrzi meg az előzményadatokat. Ezért az előzményadatok adatmegőrzési szabályzata fontos szempont minden időbeli tábla életciklusának tervezésében és kezelésében. Az Azure SQL Database-ben és az Azure SQL Managed Instance-ben található temporális táblák könnyen használható megőrzési mechanizmussal rendelkeznek, amely segít a feladat végrehajtásában.

Az időelőzmények megőrzése egyéni táblaszinten konfigurálható, így a felhasználók rugalmas öregedési szabályzatokat hozhatnak létre. Az időbeli megőrzés alkalmazása egyszerű: csak egy paramétert kell beállítani a tábla létrehozása vagy séma módosítása során.

A megőrzési szabályzat meghatározása után az Azure SQL Database és az Azure SQL Managed Instance rendszeresen ellenőrzi, hogy vannak-e olyan előzménysorok, amelyek jogosultak az automatikus adattisztításra. Az egyező sorok azonosítása és eltávolítása az előzménytáblából transzparens módon történik a rendszer által ütemezett és futtatott háttérfeladatban. Az előzménytábla sorainak életkori feltétele a SYSTEM_TIME időszak végét jelző oszlop alapján van bejelölve. Ha például a megőrzési idő hat hónapra van beállítva, a törlésre jogosult táblasorok megfelelnek a következő feltételnek:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

Az előző példában feltételeztük, hogy a ValidTo oszlop SYSTEM_TIME időszak végének felel meg.

Adatmegőrzési szabályzat konfigurálása

Mielőtt konfigurálja az időalapú táblák adatmegőrzési szabályzatát, először ellenőrizze, hogy engedélyezve van-e az időbeli előzménymegőrzés az adatbázis szintjén.

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

Az adatbázis-jelölő is_temporal_history_retention_enabled alapértelmezés szerint BE értékre van állítva, de a felhasználók az ALTER DATABASE utasítással módosíthatják. Emellett automatikusan KI értékre van állítva az időponthoz kötött visszaállítási művelet után. Az időelőzmények adatmegőrzési törlésének engedélyezéséhez hajtsa végre a következő utasítást:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Fontos

Az időbeli táblák megőrzését akkor is konfigurálhatja, ha is_temporal_history_retention_enabled ki van kapcsolva, de az elöregedett sorok automatikus törlése ebben az esetben nem aktiválódik.

A megőrzési szabályzat a tábla létrehozása során van konfigurálva a HISTORY_RETENTION_PERIOD paraméter értékének megadásával:

CREATE TABLE dbo.WebsiteUserInfo
(  
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [UserName] nvarchar(100) NOT NULL
  , [PagesVisited] int NOT NULL
  , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH
 (
     SYSTEM_VERSIONING = ON
     (
        HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
     )
 );

Az Azure SQL Database és a felügyelt Azure SQL-példány lehetővé teszi a megőrzési időtartam megadását különböző időegységek használatával: DAYS, WEEKS, MONTHS és YEARS. Ha HISTORY_RETENTION_PERIOD nincs megadva, a függvény végtelen megőrzést feltételez. Az INFINITE kulcsszót explicit módon is használhatja.

Bizonyos esetekben érdemes lehet a tábla létrehozása után konfigurálni a megőrzést, vagy módosítani a korábban konfigurált értéket. Ebben az esetben használja az ALTER TABLE utasítást:

ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Fontos

A SYSTEM_VERSIONING KI értékre állítása nem őrzi meg a megőrzési időszak értékét. Ha SYSTEM_VERSIONING be van kapcsolva, HISTORY_RETENTION_PERIOD megadása explicit módon az INFINITE megőrzési időt eredményezi.

A megőrzési szabályzat aktuális állapotának áttekintéséhez használja az alábbi lekérdezést, amely az adatbázis szintjén összekapcsolja az ideiglenes adatmegőrzés engedélyezésének jelzőt az egyes táblák megőrzési időszakaival:

SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name as TemporalTableName,  SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name as HistoryTableName,T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1  
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2

Az elöregedett sorok törlése

A törlési folyamat az előzménytábla indexelrendezésétől függ. Fontos megfigyelni, hogy csak a fürtözött indexet (B-fa vagy oszloptár) tartalmazó előzménytáblák rendelkezhetnek véges adatmegőrzési szabályzattal. Létrejön egy háttérfeladat, amely a véges megőrzési időszakkal rendelkező összes időbeli tábla elavult adattisztítását hajtja végre. A sortárba (B-fa) csoportosított index tisztítási logikája kisebb adattömbökben (legfeljebb 10 000) törli az elöregedett sorokat, így minimalizálva az adatbázisnaplóra és az IO-alrendszerre nehezedő nyomást. Bár a törlési logika a szükséges B-fa indexet használja, a megőrzési időnél régebbi sorok törlési sorrendje nem garantálható szilárdan. Ezért ne függjön az alkalmazásokban a törlési sorrendhez.

A fürtözött oszloptár törlési feladata egyszerre távolítja el a teljes sorcsoportokat (általában 1 millió sort tartalmaz), ami nagyon hatékony, különösen akkor, ha az előzményadatok nagy ütemben jönnek létre.

A kiváló adattömörítés és a hatékony adatmegőrzési karbantartás tökéletes választássá teszi a fürtözött oszlopcentrikus indexet olyan helyzetekben, amikor a számítási feladat gyorsan nagy mennyiségű előzményadatot hoz létre. Ez a minta olyan intenzív tranzakciófeldolgozási számítási feladatokra jellemző, amelyek időbeli táblákat használnak a változáskövetéshez és -naplózáshoz, a trendelemzéshez vagy az IoT-adatbetöltéshez.

Indexelési szempontok

A sorcentrikus fürtözött indexet tartalmazó táblák törlési feladatához az indexnek a SYSTEM_TIME időszak végének megfelelő oszlopmal kell kezdődnie. Ha az index nem létezik, nem konfigurálhat véges megőrzési időszakot:

Msg 13765, Level 16, State 1

A véges megőrzési időszak beállítása nem sikerült a rendszerverziójú temporális táblán (temporalstagetestdb.dbo.WebsiteUserInfo), mert a "temporalstagetestdb.dbo.WebsiteUserInfoHistory" előzménytábla nem tartalmaz szükséges fürtözött indexet. Érdemes lehet fürtözött oszlopcentrikus vagy B-fa indexet létrehozni az előzménytáblában az SYSTEM_TIME időszak végének megfelelő oszloptól kezdve.

Fontos megfigyelni, hogy az Azure SQL Database és az Azure SQL Managed Instance által létrehozott alapértelmezett előzménytábla már tartalmaz fürtözött indexet, amely megfelel a megőrzési szabályzatnak. Ha az indexet egy véges megőrzési időtartamú táblán próbálja eltávolítani, a művelet a következő hibával meghiúsul:

Msg 13766, Level 16, State 1

A fürtözött index (WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory) nem hajtható végre, mert az elavult adatok automatikus törléséhez használatos. Ha el szeretné dobni ezt az indexet, fontolja meg a HISTORY_RETENTION_PERIOD VÉGTELEN értékre állítását a megfelelő rendszerverziójú időtáblán.

A fürtözött oszlopcentrikus index tisztítása optimálisan működik, ha az előzménysorok növekvő sorrendbe vannak beszúrva (az időszak vége oszlop szerint rendezve), ami mindig akkor fordul elő, ha az előzménytáblát kizárólag a SYSTEM_VERSIONIOING mechanizmus tölti ki. Ha az előzménytáblában lévő sorokat nem az időszak vége szerint rendezi (ez lehet a helyzet, ha meglévő előzményadatokat migrált), az optimális teljesítmény elérése érdekében újra létre kell hoznia a fürtözött oszlopcentrikus indexet a B-fa soráruház-index tetején, amely megfelelően van rendezve.

Kerülje a fürtözött oszlopcentrikus index újraépítését az előzménytáblán a véges megőrzési időszakkal, mert az megváltoztathatja a rendszerverziósítási művelet által természetesen előírt sorcsoportok sorrendjét. Ha újra kell építenie a fürtözött oszlopcentrikus indexet az előzménytáblán, ezt úgy teheti meg, hogy újra létrehozza a megfelelő B-fa index fölé, megőrizve a rendszeres adattisztításhoz szükséges sorcsoportok sorrendjét. Ugyanezt a módszert kell alkalmazni, ha olyan ideiglenes táblát hoz létre, amely olyan meglévő előzménytáblával rendelkezik, amely garantált adatsorrend nélkül csoportosított oszlopindexet tartalmaz:

/*Create B-tree ordered by the end of period column*/
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory (ValidTo)
WITH (DROP_EXISTING = ON);
GO
/*Re-create clustered columnstore index*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

Ha véges megőrzési időtartam van konfigurálva a fürtözött oszlopcentrikus indexmel rendelkező előzménytáblához, nem hozhat létre további nem fürtözött B-fa indexeket a táblán:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

A fenti utasítás végrehajtásának kísérlete a következő hibával meghiúsul:

Msg 13772, Level 16, State 1

Nem fürtözött index nem hozható létre a "WebsiteUserInfoHistory" időelőzménytáblán, mivel véges megőrzési időszak és fürtözött oszlopcentrikus index van meghatározva.

Táblák lekérdezése adatmegőrzési szabályzattal

A temporális táblában lévő összes lekérdezés automatikusan kiszűri a véges adatmegőrzési szabályzatnak megfelelő előzménysorokat, hogy elkerülje a kiszámíthatatlan és inkonzisztens eredményeket, mivel az elöregedett sorokat a törlési feladat bármikor, tetszőleges sorrendben törölheti.

Az alábbi képen egy egyszerű lekérdezés lekérdezési terve látható:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

A lekérdezési terv további szűrőt is tartalmaz, amely az előzménytábla Fürtözött indexvizsgálat operátorában (kiemelve) az időszak vége oszlopra (ValidTo) van alkalmazva. Ez a példa feltételezi, hogy a WebsiteUserInfo táblában egy HÓNAP megőrzési időtartam van beállítva.

Ha azonban közvetlenül kérdezi le az előzménytáblát, előfordulhat, hogy a megadott megőrzési időnél régebbi sorok jelennek meg, de nem garantálják az ismétlődő lekérdezési eredményeket. Az alábbi képen a lekérdezés végrehajtási terve látható az előzménytáblában további szűrők alkalmazása nélkül:

Ne használja az üzleti logikát az előzménytáblák olvasására a megőrzési időszakon túl, mert inkonzisztens vagy váratlan eredményeket kaphat. Javasoljuk, hogy ideiglenes lekérdezéseket használjon a FOR SYSTEM_TIME záradékkal az adatok időbeli táblákban való elemzéséhez.

Időponthoz kötött visszaállítási szempontok

Amikor új adatbázist hoz létre a meglévő adatbázis adott időpontra való visszaállításával, az adatbázis szintjén az időbeli megőrzés le van tiltva. (is_temporal_history_retention_enabled kikapcsolva értékre van állítva). Ez a funkció lehetővé teszi az összes előzménysor vizsgálatát a visszaállításkor, anélkül, hogy aggódnia kellene, hogy az elavult sorok el lesznek távolítva, mielőtt lekérdezi őket. Használatával a konfigurált megőrzési időszakon túl is megvizsgálhatja az előzményadatokat.

Tegyük fel, hogy egy temporális táblában egy HÓNAP megőrzési időszak van megadva. Ha az adatbázis prémium szolgáltatási szinten lett létrehozva, akkor akár 35 nappal korábban is létrehozhat adatbázis-másolatot az adatbázis állapotával. Ez hatékonyan lehetővé tenné a legfeljebb 65 napos előzménysorok elemzését az előzménytábla közvetlen lekérdezésével.

Ha aktiválni szeretné az időbeli megőrzési karbantartást, futtassa a következő Transact-SQL utasítást az időponthoz kötött visszaállítás után:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

További lépések

Ha szeretné megtudni, hogyan használhatja az időbeli táblákat az alkalmazásokban, tekintse meg a Temporális táblák használatának első lépéseit.

A temporális táblákról részletes információt a Temporális táblákban talál.