Gérer les données historiques dans ses tables temporelles avec la stratégie de rétentionManage historical data in Temporal tables with retention policy

S’APPLIQUE À : ouiAzure SQL Database ouiAzure SQL Managed Instance APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

Par rapport aux tables normales, les tables temporelles peuvent augmenter la taille des bases de données, notamment si vous conservez les données historiques pendant plus longtemps.Temporal tables may increase database size more than regular tables, especially if you retain historical data for a longer period of time. Par conséquent, une stratégie de rétention des données d’historique est un aspect important de la planification et de la gestion du cycle de vie de chaque table temporelle.Hence, retention policy for historical data is an important aspect of planning and managing the lifecycle of every temporal table. Les tables temporelles dans Azure SQL Database et Azure SQL Managed Instance sont fournies avec un mécanisme de conservation des données destiné à vous aider à accomplir cette tâche.Temporal tables in Azure SQL Database and Azure SQL Managed Instance come with easy-to-use retention mechanism that helps you accomplish this task.

La rétention d’historique temporelle peut être configurée au niveau de la table individuelle, ce qui permet aux utilisateurs de créer des stratégies de vieillissement flexibles.Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices. L’application de la rétention temporelle est simple : un seul paramètre doit être défini durant la création de la table ou le changement de schéma.Applying temporal retention is simple: it requires only one parameter to be set during table creation or schema change.

Une fois que vous avez défini la stratégie de rétention, Azure SQL Database et Azure SQL Managed Instance commencent à vérifier régulièrement s’il existe des lignes de l’historique éligibles pour le nettoyage automatique des données.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. L’identification des lignes correspondantes et leur suppression de la table d’historique se produisent de façon transparente, dans la tâche en arrière-plan planifiée et exécutée par le système.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. La condition d’âge des lignes de la table d’historique est vérifiée en fonction de la colonne représentant la fin de la période SYSTEM_TIME.Age condition for the history table rows is checked based on the column representing end of SYSTEM_TIME period. Par exemple, si la période de rétention est définie à six mois, les lignes de table éligibles pour un nettoyage répondent à la condition suivante :If retention period, for example, is set to six months, table rows eligible for cleanup satisfy the following condition:

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

Dans l’exemple précédent, nous avons supposé que la colonne ValidTo correspond à la fin de la période SYSTEM_TIME.In the preceding example, we assumed that ValidTo column corresponds to the end of SYSTEM_TIME period.

Configuration d’une stratégie de rétentionHow to configure retention policy

Avant de configurer la stratégie de rétention d’une table temporelle, vérifiez si la rétention historique temporelle est activée au niveau de la base de données.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

L’indicateur de base de données is_temporal_history_retention_enabled a la valeur ON par défaut, mais les utilisateurs peuvent le changer à l’aide de l’instruction ALTER DATABASE.Database flag is_temporal_history_retention_enabled is set to ON by default, but users can change it with ALTER DATABASE statement. Il est automatiquement défini sur Désactivé après une opération de limite de restauration dans le temps.It is also automatically set to OFF after point in time restore operation. Si vous souhaitez activer le nettoyage de la rétention d’historique temporelle pour votre base de données, exécutez l’instruction suivante :To enable temporal history retention cleanup for your database, execute the following statement:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION  ON

Important

Vous pouvez configurer la durée de rétention pour les tables temporelles même si is_temporal_history_retention_enabled est DÉSACTIVÉ, mais le nettoyage automatique des lignes anciennes n’est pas déclenché dans ce cas.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.

La stratégie de rétention est configurée durant la création de la table quand vous spécifiez la valeur du paramètre HISTORY_RETENTION_PERIOD :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
     )
 );

Azure SQL Database et Azure SQL Managed Instance permettent de spécifier la période de rétention à l’aide de différentes unités de temps : DAYS, WEEKS, MONTHS et 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. Si HISTORY_RETENTION_PERIOD est omis, la rétention est censée être INFINITE.If HISTORY_RETENTION_PERIOD is omitted, INFINITE retention is assumed. Vous pouvez également utiliser explicitement le mot clé INFINITE.You can also use INFINITE keyword explicitly.

Dans certains scénarios, vous pouvez configurer la rétention après la création de la table, ou pour changer une valeur configurée.In some scenarios, you may want to configure retention after table creation, or to change previously configured value. Dans ce cas, utilisez l’instruction ALTER TABLE :In that case use ALTER TABLE statement:

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

Important

Le fait de définir SYSTEM_VERSIONING sur Désactivé ne préserve pas la valeur de période de rétention.Setting SYSTEM_VERSIONING to OFF does not preserve retention period value. La définition de SYSTEM_VERSIONING sur Activé sans spécifier HISTORY_RETENTION_PERIOD résulte de façon explicite en une période de rétention illimitée (INFINITE).Setting SYSTEM_VERSIONING to ON without HISTORY_RETENTION_PERIOD specified explicitly results in the INFINITE retention period.

Pour passer en revue l’état actuel de la stratégie de rétention, utilisez la requête suivante qui permet de joindre l’indicateur d’activation de rétention temporelle au niveau de la base de données aux périodes de rétention de tables individuelles :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

Suppression des lignes des âgesHow ages rows are deleted

Le processus de nettoyage dépend de la disposition de l’index de la table d’historique.The cleanup process depends on the index layout of the history table. Il est important de noter que seules les tables d’historique avec un index cluster (arbre B (B-tree) ou columnstore) peuvent avoir une stratégie de rétention limitée et configurée.It is important to notice that only history tables with a clustered index (B-tree or columnstore) can have finite retention policy configured. Une tâche en arrière-plan est créée afin d’effectuer le nettoyage des anciennes données pour toutes les tables temporelles dont la période de rétention est limitée.A background task is created to perform aged data cleanup for all temporal tables with finite retention period. La logique de nettoyage de l’index cluster rowstore (arbre B (B-tree)) supprime les anciennes lignes dans des blocs plus petits (jusqu’à 10 K), ce qui permet de réduire la pression sur le journal de la base de données et le sous-système d’E/S.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. Bien que la logique de nettoyage utilise l’index d’arbre B (B-tree) nécessaire, l’ordre des suppressions des lignes antérieures à la période de rétention ne peut pas être garanti.Although cleanup logic utilizes required B-tree index, order of deletions for the rows older than retention period cannot be firmly guaranteed. Vous ne devez donc pas accepter de dépendances relatives à l’ordre de nettoyage dans vos applications.Hence, do not take any dependency on the cleanup order in your applications.

La tâche de nettoyage pour le cluster columnstore supprime l’ensemble des groupes de lignes en une fois (ceux-ci contiennent généralement 1 million de lignes chacun), ce qui est très efficace, en particulier lorsque les données d’historique sont générées à un rythme élevé.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.

Rétention de columnstore en cluster

En raison de l’excellence de la compression des données et de l’efficacité du nettoyage de la rétention, l’index columnstore cluster est un choix idéal dans les scénarios où votre charge de travail génère rapidement une grande quantité de données d’historique.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. Ce modèle est généralement utilisé pour les charges de travail de traitement transactionnel intensives qui utilisent des tables temporelles à des fins de suivi des modifications et l’audit, d’analyse de tendances ou d’ingestion des données IoT.That pattern is typical for intensive transactional processing workloads that use temporal tables for change tracking and auditing, trend analysis, or IoT data ingestion.

Considérations relatives aux indexIndex considerations

La tâche de nettoyage des tables avec un index cluster rowstore requiert que l’index démarre avec la colonne correspondant à la fin de la période SYSTEM_TIME.The cleanup task for tables with rowstore clustered index requires index to start with the column corresponding the end of SYSTEM_TIME period. Si cet index n’existe pas, vous ne pouvez pas configurer de période de rétention limitée :If such index doesn't exist, you cannot configure a finite retention period:

Msg 13765, niveau 16, état 1

Échec de la définition d’une période de rétention limitée sur la table temporelle avec version gérée par le système « temporalstagetestdb.dbo.WebsiteUserInfo », car la table d’historique 'temporalstagetestdb.dbo.WebsiteUserInfoHistory » ne contient pas l’index cluster nécessaire. Vous devez créer un index cluster columnstore ou d’arbre B dans lequel la première colonne correspond à la fin de la période SYSTEM_TIME, sur la table d'historique.
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.

Il est important de noter que la table de l’historique par défaut déjà créée par Azure SQL Database et Azure SQL Managed Instance a un index cluster, ce qui est compatible avec la stratégie de rétention.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. Si vous essayez de supprimer cet index sur une table avec une période de rétention limitée, l’opération échoue avec l’erreur suivante :If you try to remove that index on a table with finite retention period, operation fails with the following error:

Msg 13766, niveau 16, état 1

Impossible de supprimer l’index cluster « WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory », car il est utilisé pour le nettoyage automatique des données anciennes. Pour supprimer cet index, attribuez la valeur INFINITE à HISTORY_RETENTION_PERIOD sur la table temporelle avec version gérée par le système correspondante.
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.

Le nettoyage de l’index cluster columnstore fonctionne de façon optimale si des lignes d’historique sont insérées dans l’ordre croissant (par la fin de la colonne de période), ce qui est toujours le cas lorsque la table d’historique est remplie exclusivement par le mécanisme SYSTEM_VERSIONIOING.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. Si les lignes de la table d’historique ne sont pas ordonnées par la fin de la colonne de période (ce qui peut arriver si vous avez migré des données d’historique existantes), vous devez recréer les index cluster columnstore sur les index d’arbre B rowstore correctement triés, pour optimiser les performances.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.

Évitez de reconstruire l’index cluster columnstore sur la table d’historique avec la période de rétention limitée, car cela risque de modifier l’ordre des groupes de lignes naturellement imposé par l’opération de système de version.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. Si vous devez reconstruire l’index cluster columnstore sur la table d’historique, faites-le en le recréant par-dessus l’index d’arbre B conforme, en conservant l’ordre des groupes de lignes nécessaire au nettoyage de données standard.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. La même approche convient si vous créez la table temporelle avec une table d’historique existante qui a un index de colonne cluster sans ordre de données garanti :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);

Lorsque la période de rétention limitée est configurée pour la table d’historique avec l’index cluster columnstore, vous ne pouvez pas créer d’index d’arbre B supplémentaire non cluster sur cette table :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])

Une tentative d’exécution de l’instruction ci-dessus échoue avec l’erreur suivante :An attempt to execute above statement fails with the following error:

Msg 13772, niveau 16, état 1

Impossible de créer un index non cluster sur la table d’historique temporelle « WebsiteUserInfoHistory » car celle-ci est définie avec une période de rétention limitée et un index cluster columnstore.
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.

Interrogation de tables comportant une stratégie de rétentionQuerying tables with retention policy

Toutes les requêtes effectuées sur une table temporelle filtrent automatiquement les lignes d’historique correspondant à la stratégie de rétention limitée, afin d’éviter des résultats imprévisibles et incohérents, étant donné que les anciennes lignes peuvent être supprimées par la tâche de nettoyage, à n’importe quel point dans le temps et dans un ordre arbitraire.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.

L’illustration suivante montre le plan de requête pour une requête simple :The following picture shows the query plan for a simple query:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Le plan de requête inclut un filtre supplémentaire appliqué à la fin de la colonne de période (ValidTo) dans l’opérateur Analyse d’index cluster sur la table d’historique (mise en surbrillance).The query plan includes additional filter applied to end of period column (ValidTo) in the Clustered Index Scan operator on the history table (highlighted). Cet exemple suppose que la période de rétention d’un MOIS a été définie sur la table WebsiteUserInfo.This example assumes that one MONTH retention period was set on WebsiteUserInfo table.

Filtre de requête de rétention

Cependant, si vous interrogez directement la table d’historique, vous pouvez voir des lignes antérieures à la période de rétention spécifiée, mais sans aucune garantie de bénéficier de résultats de requête reproductibles.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. L’illustration suivante montre le plan d’exécution de la requête sur la table d’historique sans appliquer de filtres supplémentaires :The following picture shows query execution plan for the query on the history table without additional filters applied:

Interrogation de l’historique sans filtre de rétention

Ne faites pas dépendre votre logique métier sur la lecture de la table d’historique au-delà de la période de rétention, car vous risquez d’obtenir des résultats incohérents ou inattendus.Do not rely your business logic on reading history table beyond retention period as you may get inconsistent or unexpected results. Nous vous recommandons d’utiliser des requêtes temporelles avec la clause FOR SYSTEM_TIME pour l’analyse des données dans les tables temporelles.We recommend that you use temporal queries with FOR SYSTEM_TIME clause for analyzing data in temporal tables.

Considérations relatives à la limite de restauration dans le tempsPoint in time restore considerations

Lorsque vous créez une base de données en restaurant une base de données existante à un point spécifique dans le temps, sa rétention temporelle est désactivée au niveau de la base de données.When you create new database by restoring existing database to a specific point in time, it has temporal retention disabled at the database level. L’indicateur is_temporal_history_retention_enabled est défini sur Désactivé.(is_temporal_history_retention_enabled flag set to OFF). Cette fonctionnalité vous permet d’examiner toutes les lignes d’historique lors de la restauration, sans craindre que les anciennes lignes soient supprimées avant qu’elles aient été interrogées.This functionality allows you to examine all historical rows upon restore, without worrying that aged rows are removed before you get to query them. Vous pouvez l’utiliser pour inspecter les données d’historique au-delà de la période de rétention configurée.You can use it to inspect historical data beyond configured retention period.

Supposons qu’une table temporelle a une période de rétention de un MOIS.Say that a temporal table has one MONTH retention period specified. Si votre base de données a été créée dans le niveau de service Premium, vous ne pouvez pas créer de copie de base de données avec l’état de la base de données jusqu’à 35 jours dans le passé.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. Cela vous permet d’analyser avec efficacité les lignes d’historique qui sont anciennes de moins de 65 jours en interrogeant la table d’historique directement.That effectively would allow you to analyze historical rows that are up to 65 days old by querying the history table directly.

Si vous souhaitez activer le nettoyage de la rétention temporelle, exécutez l’instruction Transact-SQL suivante après le point de restauration dans le temps :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

Étapes suivantesNext steps

Pour apprendre à utiliser les tables temporelles dans vos applications, consultez Prise en main des tables temporelles.To learn how to use temporal tables in your applications, check out Getting Started with Temporal Tables.

Visitez Channel 9 pour entendre le témoignage d’un client sur l’implémentation temporelle et regardez une démonstration temporelle en direct.Visit Channel 9 to hear a customer temporal implementation success story and watch a live temporal demonstration.

Pour plus d’informations sur les tables temporelles, consultez la rubrique Tables temporelles.For detailed information about temporal tables, review Temporal tables.