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

Agah Gnango Michel Landry

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.
Commentez .

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

- Auteur : Agah Gnango Michel Landry

- Parution : 26 juin 2017

- Public visé :

- Licence : 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.

Sommaire

- I. Problème

- II. Structure de la solution

- II-A. Runner_EnvironmentName.BAT

- II-B. Dispatcher.DAT

- II-C. DatabaseScript.SQL

- II-D. Le Fichier LOG

- III. Fichiers script SQL

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

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

- III-C. Autres fichiers SQL

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

- IV. Conclusion

- V. Remerciements

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

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

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

1. ECHO OFF

3. :SETVAR FILEDATE

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

6. set Hour=%time: =0%

8. Set SERVER=%1

9. Set SERVERNAME=%SERVER:~1,-1%

10. Set SERVERNAME=%SERVERNAME:\=_%

12. 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

14. 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

1. Print 'Exécution en cours du script DatabaseScript.SQL sur Instance = ' + '$(ServerName)' + ' et Base de donnée = ' + '$(DatabaseName)'

2. Go

3. /**************************************************************************************************

4. ** Fichier:     DatabaseScript.SQL

5. ** Nom:         Script de réinitialisation de la base de données | Release 1.2.0 du 05-30-2017

6. ** Description: Recréer tous les objets de la base de données et réinjecter les données existantes

7. ** Auteur:     Gnango Michel Landry AGAH / DBA

8. ** Date:     05/21/2017 09:56 PM

9. ***************************************************************************************************

10. ** Historique des modifications

11. ***************************************************************************************************

12. ** PR        Date            Auteur        Description

13. ** --        --------    -------        ------------------------------------

14. ** 1        11-21-2016      Gnango A.    Initial Script

15. ** 2        01-11-2017    Gnango A.    Create tables script from line 27 to

16. ***************************************************************************************************/

18. --     Sélection de la base de données à utiliser pour le script en cours

19. Use $(DatabaseName);

20. Go

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

24. :r .\Tables\Clients.SQL

25. :r .\Tables\Products.SQL

26. :r .\Tables\Sales.SQL

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

30. :r .\Views\vwClients.SQL

31. :r .\Views\vwProducts.SQL

32. :r .\Views\vwSales.SQL

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

36. :r .\StoredProcedures\spRevenuePerYear.SQL

38. Print '    '

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

40. 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

1. PRINT 'Exécution du script Products.SQL'

2. GO

3. /**************************************************************************************************

4. ** File:        Products.SQL

5. ** Name:        Script Table Product

6. ** Description:        Script de création de l'object Table Products

7. ** Author:        Gnango Michel Landry AGAH / DBA

8. ** Date:        11/22/2016 11:08 AM

9. ***************************************************************************************************

10. ** Historique des modifications apportées au script

11. ***************************************************************************************************

12. ** PR        Date            Auteur        Description

13. ** --        --------    -------        ------------------------------------

14. ** 1        11-21-2016      Gnango A.    Initial Script

15. ** 2        05-21-2017    Gnango A.    Suppression du champ color

16. ***************************************************************************************************/

18. If Not Exists(

19. Select *

20. From sysobjects

21. Where name='Products' and xtype='U')

22. Create Table Products

23. (

24. Pro_Identification_PK    Int    Identity(1,1)    not null,

25. Pro_Name        nvarchar(100)    not null,

26. Pro_Price        float        not null,

27. Pro_Color        nvarchar(50)    not null

28. Constraint [PK_Products] Primary key clustered ([Pro_Identification_PK] Asc)

29. )

30. GO

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

34. IF EXISTS (    SELECT    1 FROM

35. INFORMATION_SCHEMA.COLUMNS

36. WHERE TABLE_SCHEMA = 'dbo'

37. AND TABLE_NAME = 'Products'

38. AND COLUMN_NAME = 'Pro_Color')

39. BEGIN

40. SELECT     Pro_Identification_PK

41. ,Pro_Color

42. INTO    Bkp_Pro_Color

43. FROM    Products

45. ALTER TABLE Products

46. DROP COLUMN Pro_Color

47. END

48. GO

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

51. 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

1. PRINT 'Execution du script vwProduts.SQL'

2. GO

4. /**************************************************************************************************

5. ** File:        vwProducts.SQL

6. ** Name:        Script view Products

7. ** Description:        Script de création de l'object view vwProducts

8. ** Author:        Gnango Michel Landry AGAH / DBA

9. ** Date:        11/23/2016 09:08 AM

10. ***************************************************************************************************

11. ** Historique des modifications apportées au script

12. ***************************************************************************************************

13. ** PR        Date            Auteur        Description

14. ** --        --------    -------        ------------------------------------

15. ** 1        11-23-2016      Gnango A.    Initial Script

16. ** 2        05-20-2017     Gnango A.         Suppression de la column Pro_Color de la vue

17. ***************************************************************************************************/

19. IF  EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS

20. WHERE TABLE_SCHEMA = 'dbo'

21. AND TABLE_NAME = 'vwProducts')

22. DROP VIEW [dbo].[vwProducts]

23. GO

25. SET ANSI_NULLS ON

26. GO

27. SET QUOTED_IDENTIFIER ON

28. GO

30. CREATE VIEW [dbo].[vwProducts] WITH SCHEMABINDING

31. AS

32. SELECT     Pro_Identification_PK

33. ,Pro_Name

34. ,Pro_Price

35. FROM dbo.Products

37. GO

39. 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

1. PRINT 'Execution du Rollback Script du release 1.2.0 Rollback_1_2_0.SQL'

3. /**************************************************************************************************

4. ** File:        Rollback.SQL

5. ** Name:        Script de Rollback release 1.2.0 du 30-05-2017

6. ** Desciption:        Ce script va restaurer les objects mis à jour par le Release 1.2.0

7. ** Author:        Gnango Michel Landry AGAH / DBA

8. ** Date:        11/23/2016 09:08 PM

9. ***************************************************************************************************

10. ** Historique des modifications apportées au script

11. ***************************************************************************************************

12. ** PR        Date            Auteur        Description

13. ** --        --------    -------        ------------------------------------

14. ** 1        05-21-2017      Gnango A.    Initial Script

15. ***************************************************************************************************/

17. -- Restauration de la table Products | Rétablissement du champ Pro_Color

18. --

19. -- Restauration de la table Products | Rétablissement du champ Pro_Color

20. --

21. IF NOT EXISTS (    SELECT    1 FROM

22. INFORMATION_SCHEMA.COLUMNS

23. WHERE TABLE_SCHEMA = 'dbo'

24. AND TABLE_NAME = 'Products'

25. AND COLUMN_NAME = 'Pro_Color')

26. BEGIN

27. ALTER TABLE Products

28. ADD Pro_Color nvarchar(50)

30. UPDATE    Products

31. SET     Products.Pro_Color=backupTable.Pro_Color

32. FROM     Products

33. INNER JOIN     bkp_Pro_Color backupTable

34. ON     backupTable.Pro_Identification_PK=Products.Pro_Identification_PK

36. END

37. GO

40. -- Restauration de la vue vwProducts

42. IF  EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS

43. WHERE TABLE_SCHEMA = 'dbo'

44. AND TABLE_NAME = 'vwProducts')

45. DROP VIEW [dbo].[vwProducts]

46. GO

48. SET ANSI_NULLS ON

49. GO

50. SET QUOTED_IDENTIFIER ON

51. GO

53. CREATE VIEW [dbo].[vwProducts] WITH SCHEMABINDING

54. AS

55. SELECT     Pro_Identification_PK

56. ,Pro_Name

57. ,Pro_Price

58. ,Pro_Color

59. FROM dbo.Products

61. GO

63. 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.