Discussion :

[fraeez]

Bonjour à tous,

Je m'attèle à la rédaction d'un petit essai sur la gestion de la configuration des bases de données dans le cadre d'un projet JAVA multibranche. L'objectif de mon écrit sera de définir les bonnes pratiques pour pouvoir efficacement gérer les différentes livraisons SQL dans les différents environnements de développement.
Cet écrit est motivé par une expérience de stage dans une entreprise où la gestion des livraisons SQL était assez chaotique.

Contrairement à JAVA par exemple, qui propose de nombreux outils permettant d'effectuer facilement les livraisons de différentes versions et de merger les différentes branches, c'est moins evident au niveau base de données. En effet, entre les petits scripts passés rapidement par les développeurs lors de la correction d'une anomalie et oubliés lors de la livraison et les différentes versions des tables de configuration, il n'est pas toujours simple de s'y retrouver et de gérer efficacement les grosses livraisons de production.

Je recherche actuellement des témoignages et des documents sur le sujet. Comment est géré la gestion de configuration dans le cadre de vos projets? Avec vous un guide de bonne pratique? Connaissez vous des livres traitant du sujet?

[SQLpro]

les outils de modélisation de données comme power AMC de Sybase permettent de gérer ce genre de chose.

Également l'outil de base de MS SQL Server (SQL Server management Studio) permet de gérer des projets et s’intègre dans team foundation server :
http://fr.wikipedia.org/wiki/Team_Foundation_Server

Nativement, SQL Server permet de créer plusieurs versions des mêmes routines.

Exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE PROCEDURE P1;1
AS
   SELECT 'Il est : ' + CONVERT(CHAR(8), GETDATE(), 108);
GO
 
EXEC P1
 
CREATE PROCEDURE P1;2
AS
   SELECT 'Il est exactement : ' + CONVERT(CHAR(8), GETDATE(), 108);
GO

Néanmoins c'est toujours la P1 qui est exécuté à défaut.
En combinant ceci avec un déclencheur DDL on peut empiler l'ancienne procédure en version 2...

Certaines bases de données permettent de créer des déclencheurs DDL (Oracle SQL Server) et donc de tracer la création, altération, suppression des objets...
Il ne suffit plus que d'une vue pour connaître toutes les versions avec leur code de touts les objets de toutes les bases...

Exemple avec MS SQL Server :
1) création d'une table de suivie des modifs dans la base msdb dédié à l'administration globale du serveur

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
USE msdb
GO
 
CREATE TABLE S_DDL.T_SUIVI_DDL
(DDL_ID             bigint IDENTITY PRIMARY KEY, 
 DDL_DH             datetime2(7)  DEFAULT GETDATE(),
 DDL_APPLICATION    nvarchar(128) DEFAULT APP_NAME(),
 DDL_HOST           nvarchar(128) DEFAULT HOST_NAME(),
 DDL_CONNEXION      nvarchar(128) DEFAULT SYSTEM_USER,
 DDL_USER           nvarchar(128) DEFAULT USER,
 DDL_DATA           XML NOT NULL);
GO

2) création d'un trigger alimentant cette table pour chaque opération DDL de tous niveau

Code :

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

1
2
3
4
5
6
7
8
9
10
USE master;
GO
 
CREATE TRIGGER E_DDL_ALL
ON ALL SERVER
FOR DDL_EVENTS
AS
   INSERT INTO msdb.S_DDL.T_SUIVI_DDL (DDL_DATA)
   SELECT EVENTDATA();
GO

3) test de création d'une procédure avec plusieurs version dans une base quelconque :

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
USE DB_TEST;
GO
 
CREATE PROCEDURE P1
AS
   SELECT 'Il est : ' + CONVERT(CHAR(8), GETDATE(), 108);
GO
 
ALTER PROCEDURE P1
AS
   SELECT 'Il est exactement : ' + CONVERT(CHAR(8), GETDATE(), 108);
GO
 
ALTER PROCEDURE P1
AS
   SELECT 'Il était exactement : ' + CONVERT(CHAR(8), GETDATE(), 108);
GO

4) visualisation de la table de suivi :

Code :

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

SELECT * FROM msdb.S_DDL.T_SUIVI_DDL

Code :

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

1
2
3
4
5
DDL_ID DDL_DH                 DDL_APPLICATION      DDL_HOST    DDL_CONNEXION    DDL_USER    DDL_DATA
------ ---------------------- -------------------- ----------- ---------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1      2014-03-09 17:36:41.16 Microsoft SQL Serv   HPZFRED     HPZFRED\FB       dbo         <EVENT_INSTANCE><EventType>CREATE_PROCEDURE</EventType><PostTime>2014-03-09T17:36:41.137</PostTime><SPID>54</SPID><ServerName>HPZFRED\SQL2008R2</ServerName><LoginName>HPZFRED\FB</LoginName><UserName>dbo</UserName><DatabaseName>master</DatabaseName><SchemaN
2      2014-03-09 17:36:41.21 Microsoft SQL Serv   HPZFRED     HPZFRED\FB       dbo         <EVENT_INSTANCE><EventType>ALTER_PROCEDURE</EventType><PostTime>2014-03-09T17:36:41.210</PostTime><SPID>54</SPID><ServerName>HPZFRED\SQL2008R2</ServerName><LoginName>HPZFRED\FB</LoginName><UserName>dbo</UserName><DatabaseName>master</DatabaseName><SchemaNa
3      2014-03-09 17:36:41.24 Microsoft SQL Serv   HPZFRED     HPZFRED\FB       dbo         <EVENT_INSTANCE><EventType>ALTER_PROCEDURE</EventType><PostTime>2014-03-09T17:36:41.243</PostTime><SPID>54</SPID><ServerName>HPZFRED\SQL2008R2</ServerName><LoginName>HPZFRED\FB</LoginName><UserName>dbo</UserName><DatabaseName>master</DatabaseName><SchemaNa

5) Comme le XML n'est pas très bandant, extrayons les infos qui nous intéressent, sous forme atomique :

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
WITH 
T0 AS
(
SELECT DDL_ID, DDL_DH, DDL_APPLICATION, DDL_HOST, DDL_CONNEXION, DDL_USER,
       Txml.DataXml.value('(./EventType[1])', 'sysname') AS EVENT_TYPE,
       Txml.DataXml.value('(./DatabaseName[1])', 'sysname') AS DATABASE_NAME,
       Txml.DataXml.value('(./SchemaName[1])', 'sysname') AS SCHEMA_NAME,
       Txml.DataXml.value('(./ObjectName[1])', 'sysname') AS OBJECT_NAME,
       Txml.DataXml.value('(./ObjectType[1])', 'sysname') AS OBJECT_TYPE,
       Txml.DataXml.value('(./PostTime[1])', 'DATETIME2') AS CREATE_DATETIME,
       Txml.DataXml.value('(./TSQLCommand[1]/CommandText[1])', 'NVARCHAR(max)') AS SQL_COMMANDE
FROM   msdb.S_DDL.T_SUIVI_DDL AS E
       CROSS APPLY E.DDL_DATA.nodes('/EVENT_INSTANCE') AS Txml (DataXml)
),
T1 AS
(
SELECT DATABASE_NAME, SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE,
       CASE 
          WHEN EVENT_TYPE LIKE 'CREATE?_%' ESCAPE '?' THEN 1
          WHEN EVENT_TYPE LIKE 'ALTER?_%'  ESCAPE '?' THEN 2
          WHEN EVENT_TYPE LIKE 'DROP?_%'  ESCAPE '?' THEN 3 
          ELSE NULL
       END AS ORDER_CODE, CREATE_DATETIME, SQL_COMMANDE,
       DDL_ID, DDL_DH, DDL_APPLICATION, DDL_HOST, DDL_CONNEXION, DDL_USER  
FROM   T0)
SELECT DATABASE_NAME, OBJECT_TYPE, SCHEMA_NAME, OBJECT_NAME, CREATE_DATETIME, 
       DENSE_RANK() OVER(ORDER BY DATABASE_NAME, OBJECT_TYPE, SCHEMA_NAME, OBJECT_NAME, ORDER_CODE, CREATE_DATETIME) 
          AS OBJECT_VERSION,
       SQL_COMMANDE, DDL_ID, DDL_DH, DDL_APPLICATION, DDL_HOST, DDL_CONNEXION, DDL_USER
FROM T1;

Bien entendu ceci devrait être une vue !

Dans la colonne OBJECT_VERSION, vous avez maintenant le n° de version calculé.... pour tout objet de toute nature et avec le code de création correspondant !

A lire :
http://blog.developpez.com/sqlpro/p1...serveur-ms-sql


A +

[fraeez]

Merci SQLPro pour ces précisions.

C'est un outil que je ne connaisait pas et qui n'est pas utilisé dans mon entreprise. Je vais me pencher la dessus. Merci.

Certain d'entres vous aurait-ils des retours d'experiences concernant la gestion de configuration BDD sans l'utilisation d' outils de modélisation de données comme power AMC ?