.
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.
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é :
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 :
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▲
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.
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.
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.
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.