Verwalten von Verlaufsdaten in temporalen Tabellen mit einer AufbewahrungsrichtlinieManage historical data in Temporal tables with retention policy

GILT FÜR: Azure SQL-Datenbank Azure SQL Managed Instance

Temporale Tabellen können die Größe einer Datenbank ggf. mehr erhöhen als reguläre Tabellen. Dies gilt besonders, wenn Sie Verlaufsdaten über längere Zeiträume aufbewahren.Temporal tables may increase database size more than regular tables, especially if you retain historical data for a longer period of time. Daher stellt eine Aufbewahrungsrichtlinie für Verlaufsdaten einen wichtigen Aspekt der Planung und Verwaltung des Lebenszyklus jeder temporalen Tabelle dar.Hence, retention policy for historical data is an important aspect of planning and managing the lifecycle of every temporal table. Temporale Tabellen in Azure SQL-Datenbank und auf verwalteten Azure SQL-Instanzen verfügen über einen benutzerfreundlichen Aufbewahrungsmechanismus, der Sie bei dieser Aufgabe unterstützt.Temporal tables in Azure SQL Database and Azure SQL Managed Instance come with easy-to-use retention mechanism that helps you accomplish this task.

Die Beibehaltung temporaler Verlaufsdaten kann auf den einzelnen Tabellenebenen konfiguriert werden, sodass Benutzer flexible Ablaufrichtlinien erstellen können.Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices. Das Anwenden der temporalen Beibehaltung ist einfach: Sie erfordert nur einen Parameter, der bei der Tabellenerstellung oder einer Schemaänderung festgelegt werden muss.Applying temporal retention is simple: it requires only one parameter to be set during table creation or schema change.

Nachdem Sie Beibehaltungsrichtlinien festgelegt haben, wird von Azure SQL-Datenbank und der verwalteten Azure SQL-Instanz in regelmäßigen Abständen überprüft, ob Zeilen mit Verlaufsdaten vorhanden sind, die für die automatische Datenbereinigung infrage kommen.After you define retention policy, Azure SQL Database and Azure SQL Managed Instance starts checking regularly if there are historical rows that are eligible for automatic data cleanup. Die Ermittlung übereinstimmender Zeilen und ihre Entfernung aus der Verlaufstabelle erfolgen transparent mithilfe eines vom System geplanten und ausgeführten Hintergrundtasks.Identification of matching rows and their removal from the history table occur transparently, in the background task that is scheduled and run by the system. Die Ablaufbedingungen für die Zeilen der Verlaufstabelle werden basierend auf der Spalte überprüft, die das Ende des SYSTEM_TIME-Zeitraums repräsentiert.Age condition for the history table rows is checked based on the column representing end of SYSTEM_TIME period. Wenn die Beibehaltungsdauer beispielsweise auf sechs Monate festgelegt ist, erfüllen die für die Bereinigung infrage kommenden Zeilen folgende Bedingung:If retention period, for example, is set to six months, table rows eligible for cleanup satisfy the following condition:

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

Im vorherigen Beispiel wird angenommen, dass die Spalte ValidTo dem Ende des SYSTEM_TIME-Zeitraums entspricht.In the preceding example, we assumed that ValidTo column corresponds to the end of SYSTEM_TIME period.

Konfigurieren der AufbewahrungsrichtlinieHow to configure retention policy

Bevor Sie die Aufbewahrungsrichtlinie für eine temporale Tabelle konfigurieren, überprüfen Sie zunächst, ob die temporale Verlaufsdatenaufbewahrung auf Datenbankebene aktiviert ist.Before you configure retention policy for a temporal table, check first whether temporal historical retention is enabled at the database level.

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

Das Datenbankflag Is_temporal_history_retention_enabled ist standardmäßig auf „ON“ festgelegt, aber Benutzer können dies mit der Anweisung ALTER DATABASE ändern.Database flag is_temporal_history_retention_enabled is set to ON by default, but users can change it with ALTER DATABASE statement. Nach dem Vorgang Point-in-Time-Wiederherstellung wird es außerdem automatisch auf OFF gesetzt.It is also automatically set to OFF after point in time restore operation. Um die Bereinigung der Beibehaltung temporaler Verlaufsdaten in Ihrer Datenbank zu aktivieren, führen Sie folgende Anweisung aus:To enable temporal history retention cleanup for your database, execute the following statement:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION  ON

Wichtig

Sie können die Aufbewahrungsdauer für temporale Tabellen selbst dann konfigurieren, wenn is_temporal_history_retention_enabled auf „OFF“ festgelegt ist. In diesem Fall wird jedoch keine automatische Bereinigung veralteter Zeilen ausgelöst.You can configure retention for temporal tables even if is_temporal_history_retention_enabled is OFF, but automatic cleanup for aged rows is not triggered in that case.

Die Beibehaltungsrichtlinien werden während der Tabellenerstellung konfiguriert, indem ein Wert für den Parameter HISTORY_RETENTION_PERIOD angegeben wird:Retention policy is configured during table creation by specifying value for the HISTORY_RETENTION_PERIOD parameter:

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
     )
 );

Bei Azure SQL-Datenbank und verwalteten Azure SQL-Instanzen können Sie den Aufbewahrungszeitraum mit unterschiedlichen Zeiteinheiten angeben: DAYS, WEEKS, MONTHS und YEARS.Azure SQL Database and Azure SQL Managed Instance allow you to specify retention period by using different time units: DAYS, WEEKS, MONTHS, and YEARS. Wenn HISTORY_RETENTION_PERIOD weggelassen wird, wird von einer unbegrenzten (INFINITE) Beibehaltung ausgegangen.If HISTORY_RETENTION_PERIOD is omitted, INFINITE retention is assumed. Sie können das Schlüsselwort INFINITE auch explizit verwenden.You can also use INFINITE keyword explicitly.

In manchen Szenarios sollten Sie die Beibehaltung erst nach der Tabellenerstellung konfigurieren oder den zuvor konfigurierten Wert ändern.In some scenarios, you may want to configure retention after table creation, or to change previously configured value. Verwenden Sie für diesen Fall die Anweisung ALTER TABLE:In that case use ALTER TABLE statement:

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

Wichtig

Wenn Sie SYSTEM_VERSIONING auf OFF setzen, bleibt der Wert für die Beibehaltungsdauer nicht erhalten.Setting SYSTEM_VERSIONING to OFF does not preserve retention period value. Wenn Sie SYSTEM_VERSIONING auf ON setzen, ohne explizit HISTORY_RETENTION_PERIOD anzugeben, führt dies zu einer unbegrenzten Beibehaltungsdauer (INFINITE).Setting SYSTEM_VERSIONING to ON without HISTORY_RETENTION_PERIOD specified explicitly results in the INFINITE retention period.

Zum Prüfen des aktuellen Status der Beibehaltungsrichtlinie verwenden Sie die folgende Abfrage, die das Flag für die Aktivierung der temporalen Beibehaltung auf Datenbankebene mit den Beibehaltungszeiträumen für die einzelnen Tabellen verknüpft:To review current state of the retention policy, use the following query that joins temporal retention enablement flag at the database level with retention periods for individual tables:

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

Löschen von veralteten ZeilenHow ages rows are deleted

Der Bereinigungsprozess hängt vom Indexlayout der Verlaufstabelle ab.The cleanup process depends on the index layout of the history table. Zu beachten ist, dass eine Richtlinie für die begrenzte Beibehaltung nur für Verlaufstabellen mit einem gruppierten Index (B-Struktur oder Columnstore) konfiguriert werden können.It is important to notice that only history tables with a clustered index (B-tree or columnstore) can have finite retention policy configured. Es wird ein Hintergrundtask erstellt, um die Bereinigung veralteter Daten für alle temporalen Tabellen mit begrenztem Beibehaltungszeitraum auszuführen.A background task is created to perform aged data cleanup for all temporal tables with finite retention period. Die Bereinigungslogik für den gruppierten Index für Rowstore (B-Struktur) löscht veraltete Zeilen in kleineren Blöcken (max. 10.000), was die Belastung des Datenbankprotokolls und des E/A-Subsystems minimiert.Cleanup logic for the rowstore (B-tree) clustered index deletes aged row in smaller chunks (up to 10K) minimizing pressure on database log and IO subsystem. Die Bereinigungslogik verwendet zwar den benötigten B-Strukturindex, jedoch kann die Löschreihenfolge der Zeilen, die den Beibehaltungszeitraum übersteigen, nicht sicher garantiert werden.Although cleanup logic utilizes required B-tree index, order of deletions for the rows older than retention period cannot be firmly guaranteed. Verwenden Sie in Ihren Anwendungen daher keine Abhängigkeit von der Bereinigungsreihenfolge.Hence, do not take any dependency on the cleanup order in your applications.

Der Bereinigungstask für den gruppierten Columnstore entfernt ganze Zeilengruppen gleichzeitig (die üblicherweise jeweils 1 Mio. Zeilen enthalten). Dies ist sehr effizient, vor allem dann, wenn mit einer hohen Geschwindigkeit Verlaufsdaten generiert werden.The cleanup task for the clustered columnstore removes entire row groups at once (typically contain 1 million of rows each), which is very efficient, especially when historical data is generated at a high pace.

Beibehaltung des gruppierten Columnstore

Die hervorragende Datenkompression und die effiziente Beibehaltungsbereinigung machen den gruppierten Columnstore-Index zur perfekten Lösung für Szenarios, bei denen Ihre Workload in kürzester Zeit eine große Menge an Verlaufsdaten generiert.Excellent data compression and efficient retention cleanup makes clustered columnstore index a perfect choice for scenarios when your workload rapidly generates high amount of historical data. Dieses Muster ist typisch für intensive Transaktionsverarbeitungs-Workloads, die temporale Tabellen verwenden, um die Änderungsnachverfolgung und -überwachung, Trendanalysen oder die Erfassung von IoT-Daten durchzuführen.That pattern is typical for intensive transactional processing workloads that use temporal tables for change tracking and auditing, trend analysis, or IoT data ingestion.

Überlegungen bezüglich des IndexIndex considerations

Beim Bereinigungstask für Tabellen mit gruppiertem Rowstore-Index muss der Index mit der Spalte beginnen, die dem Ende des SYSTEM_TIME-Zeitraums entspricht.The cleanup task for tables with rowstore clustered index requires index to start with the column corresponding the end of SYSTEM_TIME period. Wenn kein solcher Index vorhanden ist, können Sie keine begrenzte Beibehaltungsdauer konfigurieren:If such index doesn't exist, you cannot configure a finite retention period:

Meldg. 13765, Ebene 16, Status 1

Setting finite retention period failed on system-versioned temporal table 'temporalstagetestdb.dbo.WebsiteUserInfo' because the history table 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' does not contain required clustered index. (Festlegen einer unbegrenzten Beibehaltungsdauer bei der temporalen Tabelle mit Systemversionsverwaltung 'temporalstagetestdb.dbo.WebsiteUserInfo' fehlgeschlagen, da die Verlaufstabelle 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' nicht den erforderlichen gruppierten Index enthält.) Sie könnten nun für die Verlaufstabelle einen gruppierten Columnstore- oder B-Strukturindex erstellen, der mit der Spalte beginnt, die mit dem Ende des SYSTEM_TIME-Zeitraums entspricht.
Msg 13765, Level 16, State 1

Setting finite retention period failed on system-versioned temporal table 'temporalstagetestdb.dbo.WebsiteUserInfo' because the history table 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' does not contain required clustered index. Consider creating a clustered columnstore or B-tree index starting with the column that matches end of SYSTEM_TIME period, on the history table.

Hierbei ist zu beachten, dass die von Azure SQL-Datenbank und der verwalteten Azure SQL-Instanz erstellte Standardverlaufstabelle bereits einen gruppierten Index enthält, der mit der Aufbewahrungsrichtlinie konform ist.It is important to notice that the default history table created by Azure SQL Database and Azure SQL Managed Instance already has clustered index, which is compliant for retention policy. Wenn Sie versuchen, diesen Index für eine Tabelle mit begrenzte Beibehaltungsdauer zu entfernen, schlägt der Vorgang mit folgender Fehlermeldung fehl:If you try to remove that index on a table with finite retention period, operation fails with the following error:

Meldg. 13766, Ebene 16, Status 1

Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned temporal table if you need to drop this index.
(Der gruppierte Index "WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory" kann nicht entfernt werden, da er für die automatische Bereinigung veralteter Daten verwendet wird. Legen Sie ggf. "HISTORY_RETENTION_PERIOD" für die zugehörige temporale Tabelle mit Systemversionsverwaltung auf "INFINITE" fest, wenn Sie diesen Index entfernen müssen.)
Msg 13766, Level 16, State 1

Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned temporal table if you need to drop this index.

Die Bereinigung des gruppierten Columnstore-Index funktioniert optimal, wenn Zeilen mit Verlaufsdaten in aufsteigender Reihenfolge (geordnet nach der Periodenende-Spalte) eingefügt werden, was immer dann der Fall ist, wenn die Verlaufstabelle ausschließlich mithilfe des Mechanismus SYSTEM_VERSIONING gefüllt wird.Cleanup on the clustered columnstore index works optimally if historical rows are inserted in the ascending order (ordered by the end of period column), which is always the case when the history table is populated exclusively by the SYSTEM_VERSIONIOING mechanism. Wenn Zeilen in der Verlaufstabelle nicht nach der Periodenende-Spalte sortiert sind (was beim Migrieren vorhandener Verlaufsdaten der Fall sein kann), sollten Sie den gruppierten Columnstore-Index auf Basis des ordnungsgemäß sortierten B-Struktur-Rowstore-Index neu erstellen, um eine optimale Leistung zu erzielen.If rows in the history table are not ordered by end of period column (which may be the case if you migrated existing historical data), you should re-create clustered columnstore index on top of B-tree rowstore index that is properly ordered, to achieve optimal performance.

Vermeiden Sie eine Neuerstellung des gruppierten Columnstore-Index für die Verlaufstabelle mit begrenzter Beibehaltungsdauer, da hierdurch die Reihenfolge der Zeilengruppen verändert werden kann, die durch die Systemversionsverwaltung automatisch auferlegt wurde.Avoid rebuilding clustered columnstore index on the history table with the finite retention period, because it may change ordering in the row groups naturally imposed by the system-versioning operation. Wenn Sie den gruppierten Columnstore-Index für die Verlaufstabelle neu erstellen müssen, erstellen Sie ihn hierzu auf Basis des kompatiblen B-Strukturindex neu. So behalten Sie die Reihenfolge der Zeilengruppen bei, die für eine regelmäßige Datenbereinigung erforderlich ist.If you need to rebuild clustered columnstore index on the history table, do that by re-creating it on top of compliant B-tree index, preserving ordering in the rowgroups necessary for regular data cleanup. Derselbe Ansatz sollte gewählt werden, wenn Sie eine temporale Tabelle mit einer vorhandenen Verlaufstabelle erstellen, die über einen gruppierten Spaltenindex ohne garantierte Datenreihenfolge verfügt:The same approach should be taken if you create temporal table with existing history table that has clustered column index without guaranteed data order:

/*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);

Wenn eine begrenzte Beibehaltungsdauer für die Verlaufstabelle mit dem gruppierten Columnstore-Index konfiguriert ist, können Sie keine zusätzlichen ungruppierten B-Strukturindizes für diese Tabelle erstellen:When finite retention period is configured for the history table with the clustered columnstore index, you cannot create additional non-clustered B-tree indexes on that table:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Der Versuch, die obige Anweisung auszuführen, ist nicht erfolgreich, und es wird folgende Fehlermeldung ausgegeben:An attempt to execute above statement fails with the following error:

Msg 13772, Level 16, State 1

Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined. (Nicht-gruppierter Index kann für temporale Verlaufstabelle 'WebsiteUserInfoHistory' nicht erstellt werden, da sie eine begrenzte Beibehaltungsdauer hat und ein gruppierter Columnstore-Index definiert wurde.)
Msg 13772, Level 16, State 1

Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.

Abfragen von Tabellen mit AufbewahrungsrichtlinieQuerying tables with retention policy

Alle Abfragen für die temporale Tabelle filtern automatisch Zeilen mit Verlaufsdaten aus, die der begrenzten Aufbewahrungsrichtlinie entsprechen, um unvorhersehbare und inkonsistente Ergebnisse zu vermeiden, da veraltete Zeilen jederzeit und in beliebiger Reihenfolge durch den Bereinigungstask gelöscht werden können.All queries on the temporal table automatically filter out historical rows matching finite retention policy, to avoid unpredictable and inconsistent results, since aged rows can be deleted by the cleanup task, at any point in time and in arbitrary order.

Das folgende Bild zeigt den Abfrageplan für eine einfache Abfrage:The following picture shows the query plan for a simple query:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Der Abfrageplan enthält einen zusätzlichen Filter, der auf die Spalte mit dem Zeitraumende (ValidTo) im Operator „Clustered Index Scan“ für die Verlaufstabelle angewendet wird (siehe Hervorhebung).The query plan includes additional filter applied to end of period column (ValidTo) in the Clustered Index Scan operator on the history table (highlighted). In diesem Beispiel wird davon ausgegangen, dass in der Tabelle „WebsiteUserInfo“ eine Beibehaltungsdauer von einem MONAT festgelegt wurde.This example assumes that one MONTH retention period was set on WebsiteUserInfo table.

Aufbewahrungsabfragefilter

Wenn Sie allerdings die Verlaufstabelle direkt abfragen, sehen Sie möglicherweise Zeilen, die älter sind als die angegebene Beibehaltungsdauer – allerdings ohne Garantie für wiederholbare Abfrageergebnisse.However, if you query history table directly, you may see rows that are older than specified retention period, but without any guarantee for repeatable query results. Das folgende Bild zeigt den Abfrageausführungsplan für die Abfrage der Verlaufstabelle, ohne dass zusätzliche Filter angewandt werden:The following picture shows query execution plan for the query on the history table without additional filters applied:

Abfragen des Verlaufs ohne Beibehaltungsfilter

Basieren Sie Ihre Geschäftslogik nicht auf das Lesen der Verlaufstabelle nach der Beibehaltungsdauer, da Sie inkonsistente oder unerwartete Ergebnisse erhalten könnten.Do not rely your business logic on reading history table beyond retention period as you may get inconsistent or unexpected results. Es empfiehlt sich, temporale Abfragen mit der Klausel „FOR SYSTEM_TIME“ zu verwenden, um Daten in temporalen Tabellen zu analysieren.We recommend that you use temporal queries with FOR SYSTEM_TIME clause for analyzing data in temporal tables.

Überlegungen zur Point-in-Time-WiederherstellungPoint in time restore considerations

Wenn Sie eine neue Datenbank erstellen, indem Sie eine vorhandene Datenbank zu einem bestimmten Zeitpunkt wiederherstellen, ist die temporale Beibehaltungsdauer auf Datenbankebene deaktiviert.When you create new database by restoring existing database to a specific point in time, it has temporal retention disabled at the database level. (Das Flag is_temporal_history_retention_enabled ist auf OFF gesetzt.)(is_temporal_history_retention_enabled flag set to OFF). Mit dieser Funktion können Sie bei der Wiederherstellung alle Zeilen mit Verlaufsdaten untersuchen, ohne dass veraltete Zeilen entfernt werden, bevor Sie diese abfragen können.This functionality allows you to examine all historical rows upon restore, without worrying that aged rows are removed before you get to query them. Sie können diese Funktion verwenden, um Verlaufsdaten außerhalb der konfigurierten Beibehaltungsdauer zu überprüfen.You can use it to inspect historical data beyond configured retention period.

Ein Beispiel: Für eine temporale Tabelle wurde eine Beibehaltungsdauer von einem MONAT angegeben.Say that a temporal table has one MONTH retention period specified. Wenn Ihre Datenbank im Premium-Tarif erstellt wurde, könnten Sie eine Datenbankkopie mit dem Status der Datenbank vor 35 Tagen erstellen.If your database was created in Premium Service tier, you would be able to create database copy with the database state up to 35 days back in the past. So könnten Sie effektiv Zeilen mit Verlaufsdaten analysieren, die bis zu 65 Tage alt sind, indem Sie die Verlaufstabelle direkt abfragen.That effectively would allow you to analyze historical rows that are up to 65 days old by querying the history table directly.

Wenn Sie die temporale Aufbewahrungsbereinigung aktivieren möchten, führen Sie die folgende Transact-SQL-Anweisung nach der Point-in-Time-Wiederherstellung aus:If you want to activate temporal retention cleanup, run the following Transact-SQL statement after point in time restore:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION  ON

Nächste SchritteNext steps

Informationen zur Verwendung temporaler Tabellen in Ihren Anwendungen finden Sie unter Erste Schritte mit temporalen Tabellen.To learn how to use temporal tables in your applications, check out Getting Started with Temporal Tables.

Auf Channel 9 können Sie sich einen Kundenerfahrungsbericht zur Temporal-Implementierung anhören und eine Temporal-Live-Demo ansehen.Visit Channel 9 to hear a customer temporal implementation success story and watch a live temporal demonstration.

Ausführliche Informationen zu temporalen Tabellen finden Sie unter Temporale Tabellen.For detailed information about temporal tables, review Temporal tables.