Partager via

Gérer le suivi des modifications (SQL Server)

Cette rubrique explique comment gérer le suivi des modifications. Il décrit également comment configurer la sécurité et déterminer les effets sur le stockage et les performances lorsque le suivi des modifications est utilisé.

Gestion du suivi des modifications

Les sections suivantes répertorient les vues de catalogue, les autorisations et les paramètres pertinents pour la gestion du suivi des modifications.

Vues du catalogue

Pour déterminer les tables et bases de données activées pour le suivi des modifications, vous pouvez utiliser les vues de catalogue suivantes :

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 disposer des autorisations suivantes :

  • Autorisation SELECT sur au moins les colonnes clés primaires de la table de suivi des modifications vers la table interrogée.

  • Autorisation VIEW CHANGE TRACKING sur la table pour laquelle les modifications sont obtenues. L’autorisation VIEW CHANGE TRACKING est requise pour les raisons suivantes :

    • Les enregistrements de suivi des modifications incluent des informations sur les lignes qui ont été supprimées, en particulier les valeurs de clé primaire des lignes qui ont été supprimées. Un principal peut avoir reçu l’autorisation SELECT pour une table de suivi des modifications après la suppression de certaines données sensibles. Dans ce cas, vous ne souhaitez pas que ce principal puisse accéder à ces 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 les opérations de mise à jour. Il pourrait être refusé à un principal l'autorisation d'accéder à une colonne contenant 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.

Comprendre la surcharge de 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 que vous devez prendre en compte.

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 Une tentative de suppression de la contrainte PRIMARY KEY échoue. Le suivi des modifications doit être désactivé avant qu’une contrainte PRIMARY KEY puisse être supprimée.
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, quel que soit le suivi des modifications.

Si la colonne en cours de suppression ne fait pas partie de la clé primaire, la suppression de la colonne réussit. Toutefois, l’effet sur toute application qui synchronise ces données doit d’abord être comprise. Si le suivi des modifications de colonne est activé pour la table, la colonne supprimée peut toujours être retournée dans le cadre des informations de suivi des modifications. Il incombe à l’application de gérer la colonne supprimée.
ALTER TABLE ADD COLUMN Si une nouvelle colonne est ajoutée à la table de suivi des modifications, l’ajout de la colonne n’est pas suivi. Seules les mises à jour et les modifications apportées à la nouvelle colonne sont suivies.
ALTER TABLE ALTER COLUMN Les modifications de type de données d’une colonne clé non primaire ne sont pas suivies.
MODIFIER LA TABLE PAR COMMUTATION Le basculement d’une partition échoue si l’une ou l’autre des tables a activé le suivi des modifications.
DROP INDEX ou ALTER INDEX DISABLE L’index qui applique la clé primaire ne peut pas être supprimé ou désactivé.
TRONQUER LA TABLE La troncation d’une table peut être effectuée sur une table sur laquelle le suivi des modifications est activé. Toutefois, les lignes supprimées par l’opération ne sont pas suivies et la version minimale valide est mise à jour. Lorsqu’une application vérifie sa version, la vérification indique que la version est trop ancienne et qu’une réinitialisation est requise. Il s’agit de la même chose que lorsque le suivi des modifications est désactivé, puis réactivé pour la table.

L’utilisation du suivi des modifications ajoute une surcharge aux opérations DML en raison des informations de suivi des modifications stockées dans le cadre de l’opération.

Effets sur DML

Le suivi des modifications a été optimisé pour réduire la surcharge de performances sur les opérations DML. La surcharge de performances incrémentielle associée à l’utilisation du suivi des modifications sur une table est similaire à la surcharge engendrée lors de la création d’un index pour une table et doit être conservée.

Pour chaque ligne modifiée par une opération DML, une ligne est ajoutée à la table de suivi des modifications interne. L’effet de cette opération par rapport à l’opération DML dépend de différents facteurs, tels que les suivants :

  • Nombre de colonnes clés primaires

  • Quantité de données en cours de modification dans la ligne de la table utilisateur

  • Nombre d’opérations effectuées dans une transaction

L’isolation d’instantané, si elle est utilisée, a é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 de tables internes suivantes :

  • Table de modifications internes

    Il existe une table de modifications interne pour chaque table utilisateur sur laquelle le suivi des modifications est activé.

  • Table des transactions internes

    Il existe une table de transactions interne pour la base de données.

Ces tables internes affectent les exigences de stockage de la manière suivante :

  • Pour chaque modification apportée à chaque ligne de la table utilisateur, une ligne est ajoutée à la table de modifications interne. Cette ligne a une petite surcharge fixe plus une surcharge variable égale à la taille des colonnes clés primaires. La ligne peut contenir des informations de contexte facultatives définies par une application. Et, si le suivi des colonnes est activé, chaque colonne modifiée nécessite 4 octets dans la table de suivi.

  • Pour chaque transaction validée, une ligne est ajoutée à une table de transactions interne.

Comme pour d’autres tables internes, vous pouvez déterminer l’espace utilisé pour les tables de suivi des modifications à l’aide de la procédure stockée sp_spaceused . Les noms des tables internes peuvent être obtenus à l’aide de l’affichage catalogue sys.internal_tables , comme illustré dans l’exemple suivant.

sp_spaceused 'sys.change_tracking_309576141'  
sp_spaceused 'sys.syscommittab'

Voir aussi

Suivre les modifications de données (SQL Server)
MODIFIER LA TABLE (Transact-SQL)
Propriétés de la base de données (page ChangeTracking)
Options SET d’ALTER DATABASE (Transact-SQL)
sys.change_tracking_databases (Transact-SQL)
sys.change_tracking_tables (Transact-SQL)
Suivre les modifications de données (SQL Server)
À propos du suivi des modifications (SQL Server)
Utiliser les données modifiées (SQL Server)

  • Last updated on