Gérer les données historiques dans ses tables temporelles avec la stratégie de rétention

S’applique à :Base de données SQLAzure SQL Managed InstanceBase de données Azure SQL dans Fabric

Les tables temporelles peuvent augmenter la taille de la base de données plus que les tables régulières, en particulier si vous conservez des données historiques pendant une période plus longue. 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. 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.

La conservation de l’historique temporel peut être configurée au niveau de la table, ce qui permet aux utilisateurs de créer des stratégies de vieillissement flexibles. 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.

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. 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. 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. 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 :

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

Dans l’exemple précédent, nous avons supposé que cette ValidTo colonne correspond à la fin de SYSTEM_TIME la période.

Configuration d’une stratégie de rétention

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.

SELECT is_temporal_history_retention_enabled, [name]
FROM sys.databases;

L’indicateur is_temporal_history_retention_enabled de base de données est défini ON par défaut, mais les utilisateurs peuvent le modifier avec ALTER DATABASE l’instruction. Il est automatiquement défini sur Désactivé après une opération de limite de restauration dans le temps. Si vous souhaitez activer le nettoyage de la rétention d’historique temporelle pour votre base de données, exécutez l’instruction suivante :

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

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 :

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 vous permettent de spécifier la période de rétention à l’aide de différentes unités de temps : DAYS, , WEEKSMONTHS, et YEARS. Si HISTORY_RETENTION_PERIOD elle est omise, la rétention infinie est supposée. Vous pouvez également utiliser INFINITE le mot clé explicitement.

Dans certains scénarios, vous pouvez configurer la rétention après la création de la table ou modifier la valeur précédemment configurée. Dans ce cas, utilisez l'instruction ALTER TABLE :

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

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 :

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;

Comment les lignes âgées sont supprimées

Le processus de nettoyage dépend de la disposition de l’index de la table d’historique. 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. 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. 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. 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. Vous ne devez donc pas accepter de dépendances relatives à l’ordre de nettoyage dans vos 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é.

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. 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.

Index considerations

La tâche de nettoyage des tables avec un index groupé Rowstore nécessite que l’index commence par la colonne correspondant à la fin de la période SYSTEM_TIME. Si cet index n’existe pas, vous ne pouvez pas configurer de période de rétention limitée :

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 en 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.

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. 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 :

Msg 13766, niveau 16, état 1

Impossible de supprimer l’index en 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.

Le nettoyage sur l’index de stockage en colonnes groupé fonctionne de manière optimale si les lignes historiques sont insérées dans l’ordre croissant (classées par la colonne de fin de période), ce qui est toujours le cas lorsque la table d’historique est remplie exclusivement via le mécanisme SYSTEM_VERSIONIOING. Si les lignes de la table historique ne sont pas classées par colonne de fin de période (ce qui peut être le cas si vous avez migré des données historiques existantes), vous devez recréer l’index de stockage en colonnes groupées au-dessus de l’index Rowstore B-tree correctement classé afin d’obtenir des performances optimales.

Évitez de reconstruire l’index de stockage en colonnes groupées sur la table d’historique avec une période de conservation finie, car cela pourrait modifier l’ordre des groupes de lignes imposé naturellement par l’opération de versionnement du système. 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. 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 :

/*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 :

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Une tentative d’exécution de l’instruction ci-dessus échoue avec l’erreur suivante :

Msg 13772, niveau 16, état 1

Impossible de créer un index non cluster sur la table d’historique temporelle « WebsiteUserInfoHistory », car celle-ci a une période de rétention limitée et un index columnstore en cluster.

Requête de tables avec stratégie de conservation

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.

L’illustration suivante montre le plan de requête pour une requête simple :

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Le plan de requête inclut un filtre supplémentaire appliqué à la colonne 'fin de période' (ValidTo) dans l'opérateur de balayage d'index clusterisé sur la table d'historique (mise en surbrillance). Cet exemple suppose que la période de rétention d’un MOIS a été définie sur la table WebsiteUserInfo.

Toutefois, si vous interrogez directement la table de l’historique des requêtes, vous pouvez voir des lignes antérieures à la période de rétention spécifiée, mais sans aucune garantie pour les résultats de requête reproductibles. L’illustration suivante montre le plan d’exécution de la requête sur la table d’historique sans appliquer de filtres supplémentaires :

Ne comptez pas 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. Nous vous recommandons d’utiliser des requêtes temporelles avec FOR SYSTEM_TIME une clause pour analyser les données dans des tables temporelles.

Considérations relatives à la limite de restauration dans le temps

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. (indicateur is_temporal_history_retention_enableddéfini sur 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. Vous pouvez l’utiliser pour inspecter les données d’historique au-delà de la période de rétention configurée.

Supposons qu’une table temporelle ait une MONTH période de rétention spécifiée. 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é. 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.

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 :

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Related content

• Introduction aux tables temporelles
• Temporal tables