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

A következőkre vonatkozik:Azure SQL DatabaseAzure SQL Managed InstanceSQL-adatbázis a Fabricben

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 korfeltételét a rendszer az időszak végét SYSTEM_TIME jelző oszlop alapján ellenőrzi. 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 az 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-jelző is_temporal_history_retention_enabled alapértelmezés szerint be van állítva ON , de a felhasználók utasítással ALTER DATABASE módosíthatják. Emellett automatikusan kikapcsolt állapotra van állítva 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

Important

Az időbeli táblák megőrzését akkor is konfigurálhatja, ha is_temporal_history_retention_enabled igen OFF, 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. A kulcsszót explicit módon is használhatja INFINITE .

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 a ALTER TABLE utasítást:

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

Important

A SYSTEM_VERSIONING kikapcsolása nem őrzi meg a megőrzési időszak értékét. Ha a SYSTEM_VERSIONING nincs kifejezetten megadva, a ON beállítása HISTORY_RETENTION_PERIOD eredményezi a megőrzési INFINITE időszakot.

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 hagyatkozzon az alkalmazásaiban a törlési sorrendre.

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.

Index considerations

A törlési feladat során a sorokat tároló, klaszterezett indexet tartalmazó táblák esetén az indexnek az SYSTEM_TIME időszak végével megfelelő oszloppal kell kezdődnie. Ha az index nem létezik, nem konfigurálhat véges megőrzési időszakot:

Üzenet 13765, Szint 16, Állapot 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 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' fürtözött index nem távolítható el, mert az elavult adatok automatikus törlésére van használatban. 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ő sorok nincsenek az időszaki oszlop szerint rendezve (ami előfordulhat, ha meglévő előzményadatokat migrált), az optimális teljesítmény érdekében újra kell létrehozni a B-fa alapú rendezett soros index tetején a fürtözött oszlopos indexet.

Kerülje el a fürtözött oszlopcentrikus index újraépítését az előzménytáblán, amelynek megőrzési ideje korlátozott, mert ez megváltoztathatja a rendszerverziózás által természetesen kialakult sorcsoportsorrendet. Ha újra kell építenie az oszlopalapú indexet az előzménytáblán, ezt úgy teheti meg, hogy újra létrehozza egy kompatibilis B-fa indexre, megőrizve a rendszeres adattisztításhoz szükséges sorcsoportok sorrendet. 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:

Üzenet 13772, Szint 16, Állapot 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áblán lévő Fürtözött indexvizsgálat operátorban (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 támaszkodjon az üzleti logikára az előzménytáblák megőrzési időn túli olvasására, mert inkonzisztens vagy váratlan eredményeket kaphat. Javasoljuk, hogy időleges lekérdezéseket használjon záradékkal FOR SYSTEM_TIME az adatok időbeli táblákban való elemzéséhez.

Az időponthoz kötött visszaállítás szempontjai

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 a jelölő értéke : OFF). 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 a temporális táblákban egy MONTH megőrzési időtartam 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é időalapú adatmegőrzés tisztítását, futtassa a következő Transact-SQL utasítást az időbeli visszaállítás után.

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Related content

• A temporális táblák használatának első lépései
• Temporal tables