Configuration et gestion du suivi des modifications
Cette rubrique explique comment activer, désactiver et gérer le suivi des modifications. Elle explique également comment configurer la sécurité et déterminer l'impact de l'utilisation du suivi des modifications sur le stockage et les performances.
Activation du suivi des modifications pour une base de données
Avant de pouvoir utiliser le suivi des modifications, vous devez l'activer au niveau de la base de données. L'exemple suivant indique comment activer le suivi des modifications en utilisant ALTER DATABASE :
ALTER DATABASE AdventureWorks2008R2
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
Vous pouvez également activer le suivi des modifications dans SQL Server Management Studio en utilisant la boîte de dialogue Propriétés de la base de données (page Suivi des modifications).
Vous pouvez spécifier les options CHANGE_RETENTION et AUTO_CLEANUP lorsque vous activez le suivi des modifications et vous pouvez modifier leurs valeurs à tout moment une fois que le suivi des modifications a été activé.
La valeur de la rétention des modifications indique la période pendant laquelle les informations de suivi des modifications sont conservées. À l'issue de cette période, les informations de suivi des modifications sont supprimées. Lorsque vous affectez cette valeur, vous devez considérer la fréquence à laquelle les applications sont synchronisées avec les tables incluses dans la base de données. En effet, la période de rétention spécifiée doit être supérieure ou égale à la période maximale entre deux synchronisations. Si une application obtient des modifications sur des intervalles plus longs, les résultats retournés risquent d'être incorrects, parce qu'une partie des informations de modification aura probablement été supprimée. Pour éviter d'obtenir des résultats incorrects, une application peut utiliser la fonction système CHANGE_TRACKING_MIN_VALID_VERSION pour déterminer si l'intervalle entre synchronisations a été trop long.
Vous pouvez utiliser l'option AUTO_CLEANUP pour activer ou désactiver la tâche de nettoyage qui permet de supprimer les informations de suivi des modifications anciennes. Ce paramètre peut s'avérer utile lorsqu'il existe un problème temporaire qui empêche les applications de se synchroniser et que le processus de suppression des informations de suivi des modifications antérieures à la période de rétention doit être suspendu jusqu'à ce que le problème soit résolu.
Pour toute base de données qui utilise le suivi des modifications, ayez conscience de ce qui suit :
Pour utiliser le suivi des modifications, le niveau de compatibilité de la base de données doit être défini à 90 ou plus. Si une base de données présente un niveau de compatibilité inférieur à 90, vous pouvez configurer le suivi des modifications. Dans ce cas, toutefois, la fonction CHANGETABLE, utilisée pour obtenir des informations de suivi des modifications, retourne une erreur.
L'utilisation de l'isolement d'instantané constitue le moyen le plus simple de garantir la cohérence de toutes les informations de suivi des modifications. C'est pourquoi nous recommandons fortement d'affecter la valeur ON à cet isolement d'instantané pour la base de données. Pour plus d'informations, consultez Utilisation du suivi des modifications.
Activation du suivi des modifications pour une table
Le suivi des modifications doit être activé pour chaque table que vous souhaitez suivre. Lorsque le suivi des modifications est activé, les informations correspondantes sont conservées pour toutes les lignes de la table qui sont affectées par une opération DML.
L'exemple suivant indique comment activer le suivi des modifications pour une table en utilisant ALTER TABLE :
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
Vous pouvez également activer le suivi des modifications pour une table dans SQL Server Management Studio en utilisant la boîte de dialogue Propriétés de la table (page Suivi des modifications).
Lorsque l'option TRACK_COLUMNS_UPDATED a la valeur ON, le Moteur de base de données SQL Server stocke des informations supplémentaires sur les colonnes qui ont été mises à jour dans la table de suivi des modifications interne. Le suivi des colonnes peut permettre à une application de synchroniser uniquement les colonnes mises à jour. Il permet ainsi d'améliorer l'efficacité et les performances. Toutefois, parce que la conservation des informations de suivi des colonnes ajoute à la charge mémoire de stockage, cette option a la valeur OFF par défaut.
Désactivation du suivi des modifications
Vous devez d'abord désactiver le suivi des modifications pour toutes les tables qui en font l'objet avant d'affecter la valeur OFF au suivi des modifications pour la base de données. Pour déterminer les tables dont le suivi des modifications est activé pour une base de données, utilisez l'affichage catalogue sys.change_tracking_tables.
L'exemple suivant indique comment désactiver le suivi des modifications pour une table en utilisant ALTER TABLE :
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING;
Quand aucune table n'effectue un suivi des modifications dans une base de données, vous pouvez désactiver le suivi des modifications pour cette base de données. L'exemple suivant indique comment désactiver le suivi des modifications pour une base de données en utilisant ALTER DATABASE :
ALTER DATABASE AdventureWorks2008R2
SET CHANGE_TRACKING = OFF;
Gestion du suivi des modifications
Les sections suivantes répertorient les affichages catalogue, les autorisations et les paramètres qui relèvent de la gestion du suivi des modifications.
Affichages catalogue
Pour déterminer quelles tables et bases de données font l'objet d'un suivi des modifications, vous pouvez utiliser les affichages catalogue suivants :
En outre, l'affichage catalogue sys.internal_tables répertorie les tables internes créées lorsque le suivi des modifications est activé pour une table utilisateur.
Sécurité
Pour accéder aux informations de suivi des modifications à l'aide des fonctions de suivi des modifications, le principal doit posséder les autorisations suivantes :
Autorisation SELECT sur au moins les colonnes de clés primaires de la table faisant l'objet d'un suivi des modifications pour la table interrogée.
Autorisation VIEW CHANGE TRACKING sur la table pour laquelle les modifications sont obtenues. L'autorisation VIEW CHANGE TRACKING est obligatoire pour les raisons suivantes :
Les enregistrements de suivi des modifications incluent des informations sur les lignes supprimées et plus particulièrement sur les valeurs de clés primaires des lignes qui ont été supprimées. Un principal pourrait avoir reçu, après la suppression de certaines données sensibles, une autorisation SELECT pour une table faisant l'objet d'un suivi des modifications. Dans ce cas, vous ne souhaiteriez pas que ce principal soit en mesure d'accéder aux informations supprimées à l'aide du suivi des modifications.
Les informations de suivi des modifications peuvent stocker des informations sur les colonnes qui ont été modifiées par des opérations de mise à jour. Un principal pourrait se voir refuser l'autorisation d'accéder à une colonne qui contient des informations sensibles. Toutefois, étant donné que les informations de suivi des modifications sont disponibles, un principal peut déterminer qu'une valeur de colonne a été mise à jour, mais le principal ne peut pas déterminer la valeur de la colonne.
Présentation de la charge de traitement liée au suivi des modifications
Lorsque le suivi des modifications est activé pour une table, certaines opérations d'administration sont affectées. Le tableau suivant répertorie les opérations et les effets à considérer.
Opération |
Lorsque le suivi des modifications est activé |
|---|---|
DROP TABLE |
Toutes les informations de suivi des modifications pour la table supprimée sont supprimées. |
ALTER TABLE DROP CONSTRAINT |
Toute tentative de supprimer la contrainte PRIMARY KEY échoue. Le suivi des modifications doit être désactivé avant de supprimer une contrainte PRIMARY KEY. |
ALTER TABLE DROP COLUMN |
Si une colonne supprimée fait partie de la clé primaire, la suppression de la colonne n'est pas autorisée, indépendamment du suivi des modifications. Si la colonne supprimée ne fait pas partie de la clé primaire, la suppression de la colonne réussit. Toutefois, il est préférable de bien comprendre au préalable l'effet sur toutes les applications qui synchronisent ces données. Si le suivi des modifications de colonnes est activé pour la table, la colonne supprimée peut quand même être retournée dans le cadre des informations de suivi des modifications. L'application est chargée de gérer la colonne supprimée. |
ALTER TABLE ADD COLUMN |
Si une nouvelle colonne est ajoutée à la table faisant l'objet d'un suivi des modifications, cet ajout ne fait pas l'objet d'un suivi des modifications. Seules sont suivies les mises à jour et les modifications apportées à la nouvelle colonne. |
ALTER TABLE ALTER COLUMN |
Les modifications des types de données dans les colonnes de clés non primaires ne font pas l'objet d'un suivi. |
ALTER TABLE SWITCH |
Le basculement de partition échoue si le suivi des modifications est activé pour l'une des tables ou les deux. |
DROP INDEX ou ALTER INDEX DISABLE |
L'index qui applique la clé primaire ne peut pas être supprimé ni désactivé. |
TRUNCATE TABLE |
La troncature d'une table peut être effectuée sur une table pour laquelle le suivi des modifications est activé. Toutefois, les lignes supprimées par l'opération ne sont pas suivies, et la version valide minimale est mise à jour. Lorsqu'une application vérifie sa version, le contrôle indique que la version est trop ancienne et qu'une réinitialisation est requise. Cela revient au même qu'une désactivation du suivi des modifications, suivie d'une nouvelle activation pour la table. |
L'utilisation du suivi des modifications ajoute à la charge de traitement des opérations DML en raison des informations stockées dans le cadre de l'opération.
Effets sur les opérations DML
Le suivi des modifications a été optimisé afin de réduire la sollicitation des ressources système que les opérations DML engendrent. Les diminutions de performances incrémentielles associées à l'utilisation du suivi des modifications sur une table sont semblables aux charges de traitement générées lorsqu'un index est créé pour une table et doit être géré.
Pour chaque ligne modifiée par une opération DML, une ligne est ajoutée à la table de suivi des modifications interne. L'effet produit en fonction de l'opération DML dépend de différents facteurs, tels que les suivants :
le nombre de colonnes de clés primaires ;
la quantité de données modifiées dans la ligne de la table utilisateur ;
le nombre d'opérations effectuées dans une transaction.
L'isolement d'instantané, s'il est utilisé, produit également un effet sur les performances de toutes les opérations DML, que le suivi des modifications soit activé ou non.
Effets sur le stockage
Les données de suivi des modifications sont stockées dans les types suivants de tables internes :
Table des modifications interne
Il existe une seule table des modifications interne pour chaque table utilisateur où le suivi des modifications est activé.
Table des transactions interne
Il existe une seule table des transactions interne pour la base de données.
Ces tables internes affectent les besoins de stockage des manières suivantes :
Pour chaque modification apportée à chaque ligne dans la table utilisateur, une ligne est ajoutée à la table des modifications interne. Cette ligne a une faible charge fixe, plus une charge variable égale à la taille des colonnes de clés primaires. La ligne peut contenir des informations de contexte facultatives définies par une application. En outre, si le suivi des colonnes est activé, chaque colonne modifiée requiert 4 octets dans la table de suivi.
Pour chaque transaction validée, une ligne est ajoutée à une table des transactions interne.
Comme avec d'autres tables internes, vous pouvez déterminer l'espace utilisé pour les tables de suivi des modifications en utilisant la procédure stockée de sp_spaceused. Vous pouvez obtenir les noms des tables internes en utilisant l'affichage catalogue sys.internal_tables, comme l'illustre l'exemple suivant :
sp_spaceused 'sys.change_tracking_309576141';
sp_spaceused 'sys.syscommittab';
Voir aussi