Problème de Database Autodiagnostic

**samia004** · 07/07/2010, 16h36

Bonjour,
je suis une stagiaire, et on m'a demandé de :
"Mettre en place un « autodiagnostic » de l’état de santé des données de la base SQL Server.
Un ensemble de scripts sql vont être défini qui permettront de savoir à tout moment si la base n’est pas corrompu.
Ce jeu de script sera exécuté en automatique de manière régulière et une alerte sera levée au support"un groupe de développeurs" si une règle retourne une ligne (une erreur).

Exemple de règles :

- Pour un même client il n’existe pas 2 idEmpruntClient identiques.
- On peut vérifier que les date des échéances sont bien inclues dans les bornes de sa phase…
"
c'est ça ce que je dois faire, mais je ne sais comment faire

est ce que quelqu'un peut m'aider SVP.

Merci d'avance

Invité · 07/07/2010, 16h48

à mon humble avis, ce genre de cohérence de la bd devrait déjà être implanté avec des contraintes au lieu d'installer un processus de surveillance qui surveille ça.
Vaut-il mieux une porte fermée devant la maison ou un gardien qui vérifie que personne ne rentre ?

**mikedavem** · 07/07/2010, 17h03

Bonjour,

- Pour un même client il n’existe pas 2 idEmpruntClient identiques.
- On peut vérifier que les date des échéances sont bien inclues dans les bornes de sa phase…

Tout cela existe en natif. Vous pouvez l'implémenter en tant que contrainte unique pour votre premier besoin et contrainte de vérification pour votre second besoin.

Savez vous la raison pour laquelle ceci n'est pas implémenté de votre côté ?

++

**SQLpro** · 07/07/2010, 18h32

Parce que personne ne s'est soucié de modéliser proprement la base de données....

mais si vous voulez débosseler votre base (c'est le terme que l'on emploi en matière de qualitique) il faut créer pour chaque règle une requête qui, s'il y a une incohérence, va retourner au moins une ligne.
1) Mettre toutes ces requêtes dans une table des requêtes. (T_QUERY_QRY par exemple)
2) Planifier la vérification à l'aide de l'agent SQL
3) Mettre le résultat dans une table d'éxécution s'il y an as un (T_VERIFY_VRF par exemple)

Pour l'exécution, une proc stocke suffit....

Voici comment je modéliserait la chose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
CREATE SCHEMA S_DIAG
 
CREATE TABLE T_QUERY_QRY
(QRY_ID                 INT NOT NULL PRIMARY KEY IDENTITY,
 QRY_REGLE              VARCHAR(2048) NOT NULL,
 QRY_QUERY              VARCHAR(max))
 
CREATE TABLE T_VERIFY_VRF
(VRF_ID                 INT NOT NULL PRIMARY KEY IDENTITY,
 VRF_DATE               DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
 QRY_ID                 INT NOT NULL FOREIGN KEY REFERENCES T_QUERY_QRY (QRY_ID),
 VRF_RESULT             XML);

Et la procédure pour vérification :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
CREATE PROCEDURE S_DIAG.P_VERIFY
AS
 
-- variables de travail :
DECLARE @QRY_ID INT,                   -- id requête de vérification
        @QRY_QUERY VARCHAR(max),       -- texte SQL requête de vérification
        @SQL VARCHAR(max),             -- texte SQL de la requête d'exécution encapsulant la requête de vérification
        @DT  DATETIME;                 -- date heure d'exécution
 
DECLARE @T TABLE (C XML);              -- variable table intermédiaire de travail        
 
SET @DT = CURRENT_TIMESTAMP;           -- assignation date heure  
 
DECLARE C CURSOR                       -- déclaration curseur parcourant les règles
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR 
   SELECT QRY_ID, QRY_QUERY
   FROM   S_DIAG.T_QUERY_QRY;
 
OPEN C;                                -- ouverture curseur   
 
FETCH C INTO @QRY_ID, @QRY_QUERY;      -- lecture ligne courante dans le curseur
 
WHILE @@FETCH_STATUS = 0               -- boucle jusqu'à fin de ligne de table 
BEGIN
 
    DELETE FROM @T;                    -- vide table de travail
 
    SET @QRY_QUERY =                   -- construction dynamique de la requête à exécuter
        'SELECT CAST((SELECT * 
         FROM   (' + @QRY_QUERY + ') AS T
         FOR XML AUTO, ELEMENTS XSINIL, ROOT(''DIAGNOSTIC''), BINARY BASE64) AS XML) AS X'    
 
    INSERT INTO @T                     -- insertion résultat....
    EXEC (@QRY_QUERY);                 -- ... d'éxécution dynamique dans table de travail
 
    INSERT INTO S_DIAG.T_VERIFY_VRF    -- insertion du contenu de la table de travail dans la table de vérification
           (VRF_DATE, QRY_ID,  VRF_RESULT)
    SELECT @DT,       @QRY_ID, C
    FROM   @T
    WHERE  C IS NOT NULL;     
 
    FETCH C INTO @QRY_ID, @QRY_QUERY;  -- lecture ligne suivante dans le curseur
 
END;
 
CLOSE C;                               -- fermeture curseur 
 
DEALLOCATE C;                          -- désallocation curseur
 
SELECT VRF_DATE, QRY_REGLE, VRF_RESULT -- affichage des résultats
FROM   S_DIAG.T_VERIFY_VRF AS V
       INNER JOIN T_QUERY_QRY AS Q
             ON V.QRY_ID = Q.QRY_ID
WHERE  VRF_DATE = @DT
ORDER  BY Q.QRY_ID;

Pour tester cela, voici deux règles à insérer :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
INSERT INTO S_DIAG.T_QUERY_QRY 
VALUES ('Toutes les tables doivent avoir une clef', 
        'SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES EXCEPT SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = ''PRIMARY KEY''');
INSERT INTO S_DIAG.T_QUERY_QRY 
VALUES ('Rien', 
        'SELECT 1 AS N WHERE 2 = 3');