Vérifiez les modifications apportées à une table SQL Server?
Comment puis-je surveiller une base de données SQL Server pour les modifications apportées à une table sans utiliser de déclencheurs ou modifier la structure de la base de données de quelque façon que ce soit? Mon environnement de programmation préféré est . Net {[5] } et C#.
J'aimerais pouvoir supporter n'importe quel SQL Server 2000 SP4 ou plus récent. Mon application est une visualisation de données boulonnée pour le produit d'une autre entreprise. Notre base de clients est dans les milliers, donc je ne veux pas avoir à mettre dans les exigences que nous modifions le tiers table du vendeur à chaque installation.
Par "changements à une table" {[13] } je veux dire des changements aux données de la table, pas des changements à la structure de la table.
En fin de compte, je voudrais que le changement déclenche un événement dans mon application, au lieu d'avoir à vérifier les changements à un intervalle.
Le meilleur plan d'action compte tenu de mes besoins (Pas de déclencheurs ou de modification de schéma, SQL Server 2000 et 2005) semble être d'utiliser la fonction BINARY_CHECKSUM dans T-SQL. La façon dont je prévois de la mise en œuvre est la suivante:
Toutes les X secondes exécutez la requête suivante:
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM sample_table
WITH (NOLOCK);
Et comparez cela à la valeur stockée. Si la valeur a changé, parcourez la table ligne par ligne en utilisant la requête:
SELECT row_id, BINARY_CHECKSUM(*)
FROM sample_table
WITH (NOLOCK);
Et comparez les sommes de contrôle renvoyées aux valeurs stockées.
8 réponses
Jetez un oeil à la commande CHECKSUM:
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM sample_table WITH (NOLOCK);
Qui retournera le même nombre chaque fois qu'il est exécuté tant que le contenu de la table n'a pas changé. Voir mon post à ce sujet pour plus d'informations:
Voici comment je l'ai utilisé pour reconstruire les dépendances du cache lorsque les tables ont changé:
ASP.NET 1.1 dépendance au cache de base de données (sans déclencheurs)
malheureusement, la somme de contrôle ne fonctionne pas toujours correctement pour détecter les modifications. Ce n'est qu'une somme de contrôle primitive et aucun calcul CRC. Par conséquent, vous ne pouvez pas l'utiliser pour détecter tous les changements, par exemple les changements symétriques entraînent la même somme de contrôle!
Par exemple, la solution avec CHECKSUM_AGG(BINARY_CHECKSUM(*)) fournit toujours 0 pour les 3 tables avec un contenu différent!
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM
(
SELECT 1 as numA, 1 as numB
UNION ALL
SELECT 1 as numA, 1 as numB
) q
-- delivers 0!
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM
(
SELECT 1 as numA, 2 as numB
UNION ALL
SELECT 1 as numA, 2 as numB
) q
-- delivers 0!
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM
(
SELECT 0 as numA, 0 as numB
UNION ALL
SELECT 0 as numA, 0 as numB
) q
-- delivers 0!
Pourquoi ne voulez-vous pas utiliser des déclencheurs? Ils sont une bonne chose si vous les utilisez correctement. Si vous les utilisez comme un moyen d'appliquer l'intégrité référentielle qui est quand ils vont de bon à mauvais. Mais si vous les utilisez pour la surveillance, ils ne sont pas vraiment considérés comme tabous.
À quelle fréquence devez-vous vérifier les modifications et quelle est la taille (en termes de taille de ligne) des tables dans la base de données? Si vous utilisez la méthode CHECKSUM_AGG(BINARY_CHECKSUM(*)) suggérée par John, elle analysera chaque ligne de la table spécifiée. L'indice NOLOCK aide, mais sur une grande base de données, vous frappez toujours chaque ligne. Vous devrez également stocker la somme de contrôle pour chaque ligne afin que vous disiez que l'on a changé.
Avez-vous envisagé d'y aller sous un angle différent? Si vous ne souhaitez pas modifier le schéma ajouter des déclencheurs, (ce qui est logique, ce n'est pas votre base de données), Avez-vous envisagé de travailler avec le fournisseur d'applications qui fait la base de données?
Ils pourraient implémenter une API qui fournit un mécanisme pour notifier les applications accessoires que les données ont changé. Cela pourrait être aussi simple que d'écrire dans une table de notification qui répertorie quelle table et quelle ligne ont été modifiées. Cela pourrait être implémenté via des déclencheurs ou du code d'application. De votre côté, ti n'a pas d'importance, votre seule préoccupation serait scanner le tableau de notification sur une base périodique. Le résultat de performance sur la base de données serait beaucoup moins que d'analyser chaque ligne pour les modifications.
Le plus dur serait de convaincre le fournisseur de l'application d'implémenter cette fonctionnalité. Comme cela peut être entièrement géré via SQL via des déclencheurs, vous pouvez effectuer la majeure partie du travail en écrivant et en testant les Déclencheurs, puis en apportant le code au fournisseur d'applications. En faisant en sorte que le fournisseur prenne en charge les déclencheurs, il empêche la situation où votre ajout d'un déclencheur remplace par inadvertance un déclencheur fourni par le fournisseur.
Avoir un travail DTS (ou un travail démarré par un service windows) qui s'exécute à un intervalle donné. Chaque fois qu'il est exécuté, il obtient des informations sur la table donnée en utilisant les tables system INFORMATION_SCHEMA et enregistre ces données dans le référentiel de données. Comparez les données renvoyées concernant la structure de la table avec les données renvoyées la fois précédente. Si c'est différent, alors vous savez que la structure a changé.
Exemple de requête pour renvoyer des informations parmi les colonnes de la table ABC (idéalement énumérer uniquement les colonnes de la table INFORMATION_SCHEMA que vous voulez, au lieu d'utiliser * select * * comme je le fais ici):
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'ABC'
Vous surveillez différentes colonnes et vues INFORMATION_SCHEMA en fonction de la définition exacte de "modifications apportées à une table".
Malheureusement, je ne pense pas qu'il existe un moyen propre de le faire dans SQL2000. Si vous limitez vos besoins à SQL Server 2005 (et versions ultérieures), vous êtes en affaires. Vous pouvez utiliser la classe SQLDependency dans System.Data.SqlClient. Voir notifications de requête dans SQL Server (ADO.NET).
Wild guess here: si vous ne voulez pas modifier les tables du tiers, pouvez-vous créer une vue, puis mettre un déclencheur sur cette vue?
Vérifiez la dernière date de validation. Chaque base de données a un historique du moment où chaque validation est faite. Je crois que c'est une norme de conformité à L'acide.