Pour répondre à cette problématique, il y a plusieurs approches :

• Gestion manuelle

L’utilisateur crée les scripts spécifiques pour créer ou mettre à jour chaque base. Source d’erreurs et perte de temps… bref, solution peu recommandable.

• Migration de version en version

On définit des versions de la base (version 1, version 2, … ) et les scripts permettant de passer de la version N à N+1. Si la base est en v2 et que l’on souhaite la migrer en v4, il faut appliquer le script v2->v3 puis v3->v4. C’est par exemple la solution choisie par Entity Framework Migrations.

• Desired State

On définit le contenu de base de données cible et l’outil se débrouille pour passer la base existante à la version cible (et ce quel que soit l’existant). C’est la solution retenue par CodeFluent Entities avec le PivotRunner ou par l’équipe SQL Server avec le Data-Tier Application (solution décrite dans la suite de l’article). On peut également citer SQL Compare de RedGate (solution payante).

Dans la suite de l’article nous allons voir comment fonctionne la solution fournie par SQL Server, à savoir les packages DAC (DAC package ou DACPAC).

dacpac à la rescousse

Un fichier DacPac est une archive zip contenant principalement un fichier XML. Ce fichier contient toutes les informations du schéma de la base de données : la liste des tables, des colonnes, des procédures stockées, des triggers, … Tous les objets ou presque sont pris en charge. Vous trouverez plus de détails sur MSDN : DAC Support For SQL Server Objects and Versions

On a donc un fichier qui, à lui seul, permet de décrire le contenu de la base de données. Cela peut être très pratique lorsque l’on doit échanger le schéma de la BDD avec quelqu’un d’autre (par exemple entre un développeur et un DBA).

Finalement ce fichier peut être déployé sur un serveur SQL pour créer une nouvelle base de données ou mettre à jour une base existante. Cette procédure peut se faire via SQL Server Management Studio ou en ligne de commandes via l’outil SqlPackage.exe. Dans le cas d’une migration, un script de migration est généré puis exécuté. On peut donc valider que le script correspond à ce qui est attendu. Pour certaines migrations il y a des warning pour indiquer par exemple qu’il peut potentiellement y avoir une perte de données (data-loss).

Utilisation

Création du fichier dacpac

Il y a plusieurs façons de créer un dacpac :

• SQL Server Management Studio

• SqlPackage. exe

SqlPackage est l’utilitaire en ligne de commandes permettant de manipuler les dacpac. Pour créer un dacpac à partir d’une base de données existante on peut utiliser la commande extract :

sqlpackage.exe "/action:extract"
               "/TargetFile:Sample.Database.dacpac"
               "/SourceConnectionString:Server=(localdb)\MSSQLLocalDB;Database=Sample.Database;Trusted_Connection=True;"

• Projet Visual Studio

Ce type de projet est intégré à Visual Studio et contient des scripts de création d’objets (tables, triggers, etc. ). Lors de la compilation de ce type de projet un fichier dacpac est généré. Je reviendrai sur ce type de projet dans le prochain article.

Déploiement du fichier dacpac

Pour le déploiement d’un fichier dacpac on retrouve bien évidemment les mêmes outils, mais également WebDeploy. Dans le cas de ce dernier, cela permet de déployer un site et sa base de données en une seule opération. Vous trouverez toutes les informations sur le site de IIS : http://www.iis.net/learn/publish/using-web-deploy/dbdacfx-provider-for-incremental-database-publishing

Le déploiement peut se faire en utilisant l’interface graphique ou la ligne de commandes :

• Management Studio

• SqlPackage. exe

Générer le script de déploiement :

sqlpackage.exe "/action:Script" 
               "/SourceFile:Sample.Database.dacpac" 
               "/TargetConnectionString:Server=(localdb)\MSSQLLocalDB;Database=Sample.Database;Trusted_Connection=True;"
               "/outputpath:PublishScript.sql"

Générer le script de déploiement et l’exécuter :

sqlpackage.exe "/action:Publish" 
               "/SourceFile:Sample.Database.dacpac" 
               "/TargetConnectionString:Server=(localdb)\MSSQLLocalDB;Database=Sample.Database2;Trusted_Connection=True;"

De nombreuses options permettent de personnaliser les opérations réalisées lors de la création du script de déploiement (utile principalement pour les mises à jour) :

Voici un extrait de script de migration. Dans ce script la table Product est créée, la table Customer dont le schéma a changé (modification de l’incrément de la colonne identité) est mise à jour sans perte de données en utilisant une table temporaire, et les procédures stockées associées aux tables modifiées sont rafraichies. On peut donc voir que la migration est capable de modifications avancées.

-- Exemple de script de migration
PRINT N'Creating [dbo].[Product]...';
GO
CREATE TABLE [dbo].[Product] (
    [Id]          INT           NOT NULL,
    [DisplayName] NVARCHAR (50) NOT NULL,
    [Price]       MONEY         NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
PRINT N'Refreshing [dbo].[Customer_LoadAll]...';
GO
EXECUTE sp_refreshsqlmodule N'[dbo].[Customer_LoadAll]';
GO
PRINT N'Starting rebuilding table [dbo].[Customer]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_Customer] (
    [Id]              INT           NOT NULL,
    [FirstName]       NVARCHAR (50) NOT NULL,
    [LastName]        NVARCHAR (50) NOT NULL,
    [Identity_column] INT           IDENTITY (1, 2) NOT NULL,
    [DateOfBirth]     DATE          NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[Customer])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Customer] ON;
        INSERT INTO [dbo].[tmp_ms_xx_Customer] ([Id], [FirstName], [LastName], [Identity_column], [DateOfBirth])
        SELECT   [Id],
                 [FirstName],
                 [LastName],
                 [Identity_column],
                 [DateOfBirth]
        FROM     [dbo].[Customer]
        ORDER BY [Id] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Customer] OFF;
    END
DROP TABLE [dbo].[Customer];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_Customer]', N'Customer';
COMMIT TRANSACTION;

Vérifier le déploiement

L’outil SqlPackage fournit une fonctionnalité permettant de comparer le schéma d’une base de données avec l’état lors du déploiement du dacpac et de générer un rapport (drift report).

sqlpackage.exe "/action:DriftReport" 
               "/TargetConnectionString:Server=(localdb)\MSSQLLocalDB;Database=Sample.Database;Trusted_Connection=True;" 
               "/outputpath:Report.xml"

Le rapport généré est un fichier XML. Par exemple suite à la modification d’une colonne d’une table, le fichier est le suivant :

<DriftReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DriftReport/2012/02">
  <Additions />
  <Removals />
  <Modifications>
    <Object Name="[DateOfBirth]" Parent="[dbo].[Customer]" Type="SqlSimpleColumn" />
    <Object Name="[Customer]" Parent="[dbo]" Type="SqlTable" />
  </Modifications>
</DriftReport>

Conclusion

Les DACPAC facilitent fortement la gestion du cycle de vie de la base de données en permettant de créer ou mettre à jour une base de données à l’aide d’un unique fichier servant de référentiel. Je vous conseille fortement d’y regarder de plus près, à moins que vous n’ayez déjà un autre outil répondant à ce besoin.

Note pour les développeurs : SqlPackage. exe (et les DLL associées) est une application .NET. Vous pouvez donc tout à fait l’utiliser dans vos applications si vous en avez besoin. Les fichiers sont surement déjà sur votre disque C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB