Que se passerait-il si une clé primaire était modifiée ou si les autorisations d’une procédure stockée étaient mises à jour sans que vous en soyez conscient ? Le suivi de SQL Server uniquement avec la capture de données de modification (CDC) ne vous suffit pas pour rester au courant des modifications apportées à vos bases de données critiques. En effet, sans un suivi des modifications SQL Server fiable et efficace, vous ne pouvez tout simplement pas contrôler ce qui se passe sur vos serveurs SQL.
Suivi des modifications natif dans SQL Server
SQL Server utilise un mécanisme de suivi synchrone, ce qui signifie que les modifications sont enregistrées une fois les modifications apportées au Langage de manipulation des données (DML) validées. Les fonctionnalités de suivi des modifications de SQL Server offertes par les outils natifs fournissent certaines fonctions de suivi des modifications ; par exemple, vous pouvez utiliser le suivi des modifications pour voir qui a supprimé quelles tables ou quelles lignes ont été modifiées. Mais vous devrez écrire un ensemble de requêtes dans Transact-SQL pour obtenir des informations de suivi des modifications plus détaillées, telles que des détails sur les données modifiées, les colonnes modifiées ou les tables créées par l’utilisateur – et cette tâche peut facilement dévorer votre temps précieux. En plus de cela, l’activation du suivi des modifications peut rapidement remplir votre serveur, vous devez donc constamment effectuer un nettoyage pour éviter de manquer d’espace disque, ce qui pourrait nuire aux performances et à la disponibilité.
Pour utiliser le suivi des modifications de SQL Server, vous devez activer le suivi des modifications sur deux niveaux imbriqués : suivi des modifications de base de données et suivi des modifications de table.
Activation du suivi des modifications pour une base de données
Pour que le suivi des modifications fonctionne, votre base de données doit avoir au moins un niveau de compatibilité SQL Server 2005. Vous pouvez déterminer le numéro de version de la base de données de votre serveur en exécutant la requête « SELECT@@VERSION « . Vous devez activer le suivi des modifications pour chaque base de données individuellement.
Pour activer le suivi des modifications au niveau de la base de données, vous pouvez utiliser SQL Server Management Studio ou exécuter la requête T-SQL suivante:
ALTER DATABASE Accounting2020
SET CHANGE_TRACKING=ON
(CHANGE_RETENTION=3 JOURS, AUTO_CLEANUP=ON)
Une fois le suivi des modifications activé, vous pouvez définir une période de rétention différente en modifiant les paramètres CHANGE_RETENTION et AUTO_CLEANUP.
Activation du suivi des modifications pour une table
Comme pour les bases de données, le suivi des modifications de table nécessite d’activer le suivi sur chaque table séparément. Encore une fois, vous pouvez le faire soit dans SQL Server Management Studio, soit avec une requête comme celle-ci:
MODIFIER les contrats DE TABLE
ACTIVER CHANGE_TRACKING
AVEC (TRACK_COLUMNS_UPDATED=ON)
Une fois le suivi des modifications activé pour une table, des informations sur toutes les lignes modifiées par des instructions SQL courantes (SELECT, INSERT, UPDATE, DELETE, etc.) est stocké en interne.
Le paramètre TRACK_COLUMNS_UPDATED est utilisé pour activer ou désactiver le suivi des modifications pour les colonnes. S’il est activé, SQL Server stockera des informations supplémentaires sur les colonnes mises à jour. Cependant, étant donné que le suivi des informations de colonne ajoute à la surcharge de stockage, cette option est désactivée par défaut et il est recommandé de la garder désactivée pour toutes les applications qui ne nécessitent pas de données historiques.
Suivi des modifications simple et efficace avec Netwrix Auditor
Contrairement aux outils de suivi des modifications natifs qui vous obligent à écrire des requêtes et à parcourir manuellement les données, Netwrix Auditor pour SQL Server fournit des tableaux de bord et des rapports prédéfinis qui vous permettent de repérer facilement les modifications et de voir tous les détails critiques. Par exemple, vous pouvez voir qu’une colonne qui a été supprimée ou qu’un rôle d’utilisateur a été modifié, ainsi que les valeurs avant et après de chaque modification. La recherche interactive vous aide à enquêter rapidement sur les modifications suspectes afin d’éviter l’indisponibilité de la base de données et d’autres perturbations.
Netwrix Auditor permet également de surveiller facilement les ressources clés, telles que les serveurs avec le plus de modifications et les utilisateurs apportant le plus de modifications. Les abonnements aux rapports vous tiennent automatiquement à jour selon le calendrier que vous choisissez. Et il n’est plus nécessaire de s’inquiéter constamment du nettoyage, car le stockage à deux niveaux de Netwrix conserve vos données de modification SQL Server consolidées de manière rentable pendant plus de 10 ans, tout en garantissant un accès rapide et sécurisé à tout moment. Mieux encore, Netwrix Auditor utilise un mécanisme d’audit non intrusif, de sorte qu’il ne dégradera jamais les performances de la base de données.
