Apprendre à déployer aisément votre base de données SQL Server sur plusieurs instances

En tant qu'administrateurs de bases de données (DBA), nombreux sommes-nous qui avons été au moins une fois confrontés aux défis du redéploiement de notre base de données sur plusieurs instances SQL Server. L'objectif de ce tutoriel est d'offrir une approche permettant aux DBA et développeurs SQL de faire évoluer leur application en maîtrisant toutes les contraintes liées au déploiement des mises à jour de base de données dans une architecture de base de données multi-instance comportant un serveur de Développement, de Test, de Préproduction ou Staging et de Production.

L'approche décrite dans le présent article permettra d'assurer non seulement une traçabilité du code dans la création, la modification et la suppression des objets de la base de données, mais aussi de garantir un déploiement rapide et efficient des mises à jour de la base de données.

Si vous avez des remarques concernant ce tutoriel, un espace de dialogue vous est proposé sur le forum.
3 commentaires Donner une note à l'article (4.5).

Article lu   fois.

L'auteur

Profil Pro

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Problème

Les architectures de base de données varient d'une entreprise à une autre. Cependant, dans la majorité des entreprises, l'architecture de base de données inclut un serveur de développement (DEV), un serveur TEST, un serveur de préproduction (STAGING) et enfin un serveur de production (PROD). Les mises à jour sont généralement déployées sur chacun des serveurs précités et dans le même ordre.

Serveur DEV

Permet aux développeurs d'effectuer des Tests unitaires de leur code.

Serveur TEST

Permet aux développeurs d'effectuer des tests d'interface.

Serveur STAGING

Il constitue l'exact miroir du serveur de production.

Serveur PROD ou LIVE

Il interagit directement avec les utilisateurs finaux de l'application.

Supposons que votre schéma de base de données dispose de 3 tables CLIENTS, PRODUCTS et SALES comportant respectivement les données des clients, les produits vendus par votre compagnie et les détails des ventes effectuées par votre département de ventes.

Le schéma de la base de données est le suivant :
CLIENTS (CLI_IDENTIFICATION_PK, CLI_NAME, CLI_PHONE),
PRODUCTS (PRO_IDENTIFICATION_PK, PRO_NAME, PRO_PRICE ET PRO_COLOR)
SALES (SAL_IDENTIFICATION_PK, SAL_DATE, SAL_QTY, SAL_AMOUNT, SAL_DATE,
PRO_IDENTIFICATION_FK, CLI_IDENTIFICATION_FK

Vous disposez d'une équipe de développeurs qui travaille actuellement sur une nouvelle mise à jour de l'application, qui nécessite quelques changements au niveau du schéma de données. Le champ PRO_COLOR devra être supprimé de la table PRODUCTS. Un nouveau champ CLI_EMAIL sera ajouté à la table CLIENTS et un autre nouveau champ SAL_TAX à la table SALES.

En tant que développeur SQL/DBA, vous souhaitez implémenter les changements requis tout en conservant l'intégrité de la base de données sur toutes les instances SQL Server de votre architecture, spécialement en Préproduction et en Production. La dernière sauvegarde de la base de données de production devra servir de base pour l'application des mises à jour.

Une traçabilité des modifications apportées au schéma de base de données est à considérer. Le même script de mise à jour devra s'appliquer à la base sur les serveurs DEV, TEST, PREPROD et PROD. Aussi, pour des raisons qui lui sont propres, l'équipe de développeurs souhaite qu'après déploiement des mises à jour sur les serveurs DEV et TEST, toutes les données de transactions soient supprimées. En d'autres termes, ces deux instances ne doivent comporter que les données des tables CLIENTS et PRODUITS. Toutes les données de VENTES doivent être supprimées. Les développeurs estiment que cela leur permet de tester les nouvelles fonctionnalités plus aisément.

Autre chose, les données existant dans l'environnent de DEV et TEST peuvent différer de l'environnent de STAGING et PRODUCTION, qui elles devront être identiques.

Un peu compliqué tout ça, n'est-ce pas ? Une simple sauvegarde/restauration de la base de production ne saurait résoudre le problème. Souvenez-vous que pendant que les développeurs travaillent sur la prochaine mise à jour, le serveur de production demeure actif et continue de générer des milliers de nouveaux enregistrements.

Je vous propose de vous accompagner dans l'implémentation d'une telle solution. Cet article se subdivise en 2 parties dont la première se focalisera sur l'architecture de la solution en elle-même. Elle décrira l'organisation des fichiers du script de mise à jour de la base de données. On utilisera SQLCMD (un utilitaire qui permet d'exécuter du TSQL, des procédures systeme ou des fichiers scripts en utilisant les lignes de commandes) pour automatiser cette mise à jour dans plusieurs environnements. La seconde partie de l'article portera sur le contenu de chaque script faisant partie de la solution globale.

II. Structure de la solution

Pour rappel, votre système d'information comporte un serveur de développement (DEV), un serveur TEST, un serveur de Préproduction (STAGING) et enfin un serveur de Production (PROD). Il vous est demandé d'utiliser un unique package de mise à jour de la base de données applicable sur tous les environnements précités. L'objectif est donc de mettre en place une solution qui prendra pour paramètres le nom de l'instance SQL Server et le nom de la base de données et qui procédera à la mise a jour de la base de données.

Pour y arriver, nous allons utiliser deux types de scripts. Le premier comportera essentiellement du Transact-SQL et le second type des SQLCMD.

Comme vous pouvez le constater sur la figure 1 nous allons écrire un bon nombre de scripts intégrant du T-SQL ou du SQLCMD ou les deux à la fois. Tout fichier à extension SQL ne comportera que du script T-SQL, à l'exception du fichier DatabaseScript.SQL qui lui inclura aussi du SQLCMD.

Le fichier Runner_EnvironementName.BAT est le seul fichier qui sera exécuté pour déclencher la mise à jour de la base de données pour une instance donnée. Par exemple, le Runner du server STAGING sera Runner_STAGING.BAT. Ensuite vient le dispatcher qui lui va récupérer les paramètres fournis par le fichier Runner et les transmettre au fichier DatabaseScript.SQL, qui à son tour ordonnancera l'exécution des fichiers SQL engageant la création ou la modification des objets de la base de données.

Un fichier log sera généré et permettra d'auditer l'exécution de la mise à jour.

II-A. Runner_EnvironmentName.BAT

Je l'appelle affectueusement le « Runner ». Il s'agit du fichier que devra exécuter le DBA pour amorcer la mise à jour de la base de données sur une instance SQL Server donnée. Par exemple, le nom exact du fichier Runner sera Runner_DEV.DAT pour lancer le script de mise à jour de la base de données sur le serveur DEV, ou Runner_PROD.DAT pour lancer le script de mise à jour de la base de données sur le serveur PRODUCTION. Son contenu est simple et comporte uniquement les paramètres d'exécution du script de base de données DatabaseScript.SQL qui lui seront transmis via un Dispatcher.

Contenu du Fichier Runner_EnvironmentName.BAT
Sélectionnez
1.
Dispatcher.DAT "SERVERDB" Database_DEV

Dispatcher.DAT représente le fichier qui passera les paramètres nom de l'instance et nom de la base de données au fichier script DatabaseScript.SQL. SERVERDB représente le nom de l'instance SQL Server abritant la base de données à mettre à jour. Il est suivi de Database_DEV qui lui est le nom de la base de données à mettre à jour.

S'il s'agissait de la base de données Database_PREPROD sur une instance appelée SERVERDB_PREPROD, le nom du fichier aurait été Runner_PREPROD.DAT et l'unique commande dans le fichier aurait été :

Autre exemple de contenu du fichier Runner en Préproduction
Sélectionnez
1.
Dispatcher.DAT "SERVERDB_PREPROD" Database_PREPROD

II-B. Dispatcher.DAT

Le fichier Dispatcher.DAT est le fichier qui, utilisant les deux paramètres fournis par le Runner, va établir la connexion à la base de données et exécuter le script DatabaseScript.SQL. Contrairement au fichier Runner, il existe un et un seul dispatcher pour toute la solution et son contenu est le suivant :

Contenu du fichier Dispatcher.DAT
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
ECHO OFF

:SETVAR FILEDATE
for /F "tokens=2-4 delims=/ " %%i in ('date /t') do set FILEDATE=%%k%%i%%j

set Hour=%time: =0%

Set SERVER=%1
Set SERVERNAME=%SERVER:~1,-1%
Set SERVERNAME=%SERVERNAME:\=_%

SQLCMD -S %1 -E -d %2 -i.\DatabaseScript.SQL -v ServerName= %1 DatabaseName= %2 -o .\Log\%FILEDATE%_%Hour:~0,2%_%time:~3,2%_%time:~6,2%_Database_Update_Log_%username%_On_Server_[%SERVERNAME%]_Database_[%2].TXT 

PAUSE

:SETVAR est une commande utilisée pour déclarer une variable locale. Dans le cas d'espèce, il s'agit de la variable FILEDATE.
-S au sein de la commande SQLCMD permet de définir le serveur,
-E pour indiquer au système qu'il s'agit d'une « Trusted connexion »
-i pour indiquer que le script devra exécuter un input SQL file. Le Input SQL file dans notre exemple est .\DatabaseScript.SQL.
-v liste les variables à passer au script SQL pour son exécution. Nous avons 2 variables, le nom du serveur ServerName (instance SQL Server) et le nom de la base de données DatabaseName.
-o spécifie le fichier output qui recueillera les résultats d'exécution du script SQL contenu dans le fichier DatabaseScript.SQL.

II-C. DatabaseScript.SQL

Contenu du fichier DatabaseScript.SQL
Sélectionnez
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.
Print 'Exécution en cours du script DatabaseScript.SQL sur Instance = ' + '$(ServerName)' + ' et Base de donnée = ' + '$(DatabaseName)'
Go
/**************************************************************************************************
** Fichier:     DatabaseScript.SQL  
** Nom:         Script de réinitialisation de la base de données | Release 1.2.0 du 05-30-2017
** Description: Recréer tous les objets de la base de données et réinjecter les données existantes 
** Auteur:     Gnango Michel Landry AGAH / DBA
** Date:     05/21/2017 09:56 PM
***************************************************************************************************
** Historique des modifications
***************************************************************************************************
** PR        Date            Auteur        Description 
** --        --------    -------        ------------------------------------
** 1        11-21-2016      Gnango A.    Initial Script
** 2        01-11-2017    Gnango A.    Create tables script from line 27 to 
***************************************************************************************************/

--     Sélection de la base de données à utiliser pour le script en cours
    Use $(DatabaseName);
    Go

--     Exécution des scripts de création des objets tables

    :r .\Tables\Clients.SQL
    :r .\Tables\Products.SQL
    :r .\Tables\Sales.SQL

--     Exécution des scripts de création des objets view

    :r .\Views\vwClients.SQL
    :r .\Views\vwProducts.SQL
    :r .\Views\vwSales.SQL

--     Exécution des scripts de création des objets Stored Procedure

    :r .\StoredProcedures\spRevenuePerYear.SQL

Print '    '
Print 'Exécution terminée du script DatabaseScript.SQL sur la base de données = ' + '$(DatabaseName)'
Go

La structure du fichier DatabaseScript.SQL comporte autant de sections que de type d'objets disponibles dans le schéma de la base de données à manipuler. Chaque objet dispose d'un script SQL d'initialisation qui est exécuté en utilisant la commande :r.

Évidemment, si votre base de données utilise des fonctions, vous devez ajouter une section « Exécution des scripts de création des objets Fonctions », ainsi que pour les objets de type SQL agent job, Service Broker, Message Queue.

Chaque fichier SQL est enregistré dans le répertoire correspondant au type d'objet de base de données auquel il appartient. Dans le répertoire de la solution, sur la racine (root) sera créé un répertoire pour chaque type d'objet manipulé par la base de données.

II-D. Le Fichier LOG

Le fichier LOG est un fichier qui est généré et édité lors de l'exécution des fichiers script SQL. C'est le fichier d'audit de l'exécution du script de mise à jour. Il comporte les éventuels messages d'erreur générés lors de l'exécution des scripts. Nous y reviendrons dans la seconde partie de cet article.

III. Fichiers script SQL

Dans cette partie, nous allons parcourir ensemble les différents scripts de création ou de modification des objets de la base de données. Nous discuterons aussi des meilleures pratiques en la matière.

III-A. Fichier de création ou modification d'objet Table

Tous les fichiers de création ou modification des objets de type table devront être sauvegardés dans le répertoire Tables. L'exemple ci-dessous est celui du fichier Products.SQL.

Contenu du fichier Produts.SQL
Sélectionnez
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.
PRINT 'Exécution du script Products.SQL'
GO
/**************************************************************************************************
** File:        Products.SQL  
** Name:        Script Table Product
** Description:        Script de création de l'object Table Products
** Author:        Gnango Michel Landry AGAH / DBA
** Date:        11/22/2016 11:08 AM
***************************************************************************************************
** Historique des modifications apportées au script
***************************************************************************************************
** PR        Date            Auteur        Description 
** --        --------    -------        ------------------------------------
** 1        11-21-2016      Gnango A.    Initial Script
** 2        05-21-2017    Gnango A.    Suppression du champ color
***************************************************************************************************/

If Not Exists(
    Select * 
    From sysobjects 
    Where name='Products' and xtype='U')
        Create Table Products
        (
            Pro_Identification_PK    Int    Identity(1,1)    not null,
            Pro_Name        nvarchar(100)    not null,
            Pro_Price        float        not null,
            Pro_Color        nvarchar(50)    not null
            Constraint [PK_Products] Primary key clustered ([Pro_Identification_PK] Asc)
        )
GO

-- Suppression du champ Pro_Color by Gnango A. on 05-21-2017

IF EXISTS (    SELECT    1 FROM
        INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = 'dbo'
            AND TABLE_NAME = 'Products'
            AND COLUMN_NAME = 'Pro_Color')
BEGIN
    SELECT     Pro_Identification_PK
        ,Pro_Color
    INTO    Bkp_Pro_Color
    FROM    Products

    ALTER TABLE Products
              DROP COLUMN Pro_Color
END
GO

PRINT 'Exécution du script Products.SQL terminée'
GO

Je ne vais pas m'attarder sur la DDL CREATE mais plutôt sur l'agencement du code. D'abord, l'entête du script devra fournir le maximum d'information sur l'objet de base de données que le présent script a pour fonction de créer ou de modifier. On doit savoir qui a créé le fichier, qui y a travaillé et à quelle date.

En ce qui concerne le script en lui-même, il procède d'abord à une vérification de l'existence de l'objet à créer dans la base de données afin d'éviter d'écraser les objets existant déjà.

Toutes les modifications apportées au schéma sont bien documentées comme vous pouvez le constater. Je reviendrai sur le pourquoi de la sauvegarde de l'ensemble des données de PRO_COLOR dans une table avant de la supprimer de la table Products.

Cette méthode permet donc d'éviter d'engager la création d'objets qui existent déjà dans la base de données. Elle force la modification de la structure des objets de la base de données surtout en environnement de production, où les risques de perdre des données sont plus grands. Enfin, elle facilite par la même occasion un rollback (restauration) sans stress. Chaque script de création ou modification de table de la base de données devra ressembler à celui qui est présenté ci-dessus.

III-B. Fichier de création d'objets Views

Tous les fichiers de création et modification d'objets de type Views seront sauvegardés dans le répertoire views.

L'exemple ci-dessous est celui du fichier vwProducts.SQL.

Dans cet exemple, nous avons utilisé une approche un peu différente de celle adoptée pour la création et la modification des objets de type table : vérifier que l'objet existe, le supprimer s'il existe, puis le recréer.

Comme vous pouvez le constater, compte tenu de la nature de l'objet view en lui-même, il n'est nullement nécessaire d'utiliser un ALTER VIEW. Cependant, nous avons lié la vue au schéma de données en utilisant SCHEMABINDING de façon à empêcher toute modification de la structure de la table PRODUCTS sans au préalable en modifier la vue.

Contenu du fichier vwProducts.SQL
Sélectionnez
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.
PRINT 'Execution du script vwProduts.SQL'
GO

/**************************************************************************************************
** File:        vwProducts.SQL  
** Name:        Script view Products
** Description:        Script de création de l'object view vwProducts
** Author:        Gnango Michel Landry AGAH / DBA
** Date:        11/23/2016 09:08 AM
***************************************************************************************************
** Historique des modifications apportées au script
***************************************************************************************************
** PR        Date            Auteur        Description 
** --        --------    -------        ------------------------------------
** 1        11-23-2016      Gnango A.    Initial Script
** 2        05-20-2017     Gnango A.         Suppression de la column Pro_Color de la vue
***************************************************************************************************/

IF  EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS 
             WHERE TABLE_SCHEMA = 'dbo' 
             AND TABLE_NAME = 'vwProducts')
DROP VIEW [dbo].[vwProducts]    
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwProducts] WITH SCHEMABINDING
AS
SELECT     Pro_Identification_PK
    ,Pro_Name
    ,Pro_Price
      FROM dbo.Products

GO

PRINT 'Execution du script vwProducts.SQL terminée.'

III-C. Autres fichiers SQL

Comme annoncé au début de cet article, chaque fichier SQL créé sera sauvegardé dans le répertoire correspondant au type d'objet de base de données auquel il appartient. Nous n'allons pas décrire le script de la procédure stockée spRevenuePerYear.SQL mais sachez tout simplement que comme le script de création et modification des views, il devra s'assurer que la procédure stockée existe, la supprimer, puis la recréer.

III-D. Annuler les modifications apportées à la base de données (Rollback Script)

Le Rollback script a pour mission de restaurer la base de données en l'état où elle était avant l'application du script de mise à jour.

Prenons le cas, du champ PRO_COLOR qui a été supprimé de la table PRODUCTS. Supposons que plusieurs heures après avoir déployé la mise à jour de la base de données en production, les développeurs décèlent un nouveau problème et décident d'annuler toutes les modifications appliquées au schéma de données par la dernière mise à jour.

L'application étant critique, des milliers de transactions ont déjà été validées. Il vous est alors demandé de restaurer la structure de la base de données, de façon à permettre à l'ancienne version de la base en question de continuer à fonctionner sans perdre les données récemment acquises.

C'est à ce moment-là que le script de rollback entre en jeu. Il est essentiellement composé de T-SQL, servant à restaurer les objets modifiés par la récente mise à jour.

Contenu du fichier Rollback_1_2_0.SQL
Sélectionnez
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.
57.
58.
59.
60.
61.
62.
63.
PRINT 'Execution du Rollback Script du release 1.2.0 Rollback_1_2_0.SQL'

/**************************************************************************************************
** File:        Rollback.SQL  
** Name:        Script de Rollback release 1.2.0 du 30-05-2017
** Desciption:        Ce script va restaurer les objects mis à jour par le Release 1.2.0
** Author:        Gnango Michel Landry AGAH / DBA
** Date:        11/23/2016 09:08 PM
***************************************************************************************************
** Historique des modifications apportées au script
***************************************************************************************************
** PR        Date            Auteur        Description 
** --        --------    -------        ------------------------------------
** 1        05-21-2017      Gnango A.    Initial Script
***************************************************************************************************/

-- Restauration de la table Products | Rétablissement du champ Pro_Color 
--
-- Restauration de la table Products | Rétablissement du champ Pro_Color 
--
IF NOT EXISTS (    SELECT    1 FROM
        INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = 'dbo'
            AND TABLE_NAME = 'Products'
            AND COLUMN_NAME = 'Pro_Color')
BEGIN
    ALTER TABLE Products
              ADD Pro_Color nvarchar(50)

    UPDATE    Products 
        SET     Products.Pro_Color=backupTable.Pro_Color
        FROM     Products
        INNER JOIN     bkp_Pro_Color backupTable
            ON     backupTable.Pro_Identification_PK=Products.Pro_Identification_PK

END
GO


-- Restauration de la vue vwProducts

IF  EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS 
             WHERE TABLE_SCHEMA = 'dbo' 
             AND TABLE_NAME = 'vwProducts')
DROP VIEW [dbo].[vwProducts]    
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwProducts] WITH SCHEMABINDING
AS
SELECT     Pro_Identification_PK
    ,Pro_Name
    ,Pro_Price
        ,Pro_Color
      FROM dbo.Products

GO

PRINT 'Exécution du Rollback Script du release 1.2.0 Rollback_1_2_0.SQL terminée.'

Comme vous pouvez le constater, le script de rollback est aussi important que le script de déploiement de la mise à jour de la base de données. Bien que son exécution permette de restaurer la base en l'état avant déploiement, il garantit qu'aucune des données de production ne sera perdue en cas d'échec du déploiement.

Vous comprenez maintenant pourquoi nous avons sauvegardé les données de PRO_COLOR existant dans la table PRODUCTS avant le déploiement de la mise à jour.

Je n'ai pas intégré le script du Rollback dans l'arborescence de la solution, mais il appartient au DBA de définir un répertoire de rollback ayant la même structure que le répertoire de déploiement ayant un Runner, un Dispatcher et évidemment un Rollback Script.

IV. Conclusion

Avoir un contrôle complet (total control) de l'architecture des bases de données de l'entreprise est la mission première de tout DBA. Nous avons essayé à travers cet article de partager notre approche de déploiement de mise à jour de base de données dans un environnement multi-instance et espérons que cela vous aura été utile.

Aussi, aimerais-je souligner que l'objet de cet article n'était pas d'étaler simplement des lignes de code T-SQL, mais surtout d'insister sur l'approche elle-même. Dans un scénario beaucoup plus réaliste, vous auriez eu à gérer des utilisateurs et des rôles utilisés dans chaque instance de base de données, des SQL agent jobs, des objets services broker, des triggers et même des scripts de données pour les éventuelles tables de correspondances (lookup tables).

V. Remerciements

Je tiens à remercier à Maxy35 pour la relecture orthographique.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2017 Agah Gnango Michel Landry. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.