This is part 2 in my series on continuous deployment of databases. In this post I’m going to have a quick look at SqlPackage.exe and using it in continuous database deployment.
Part 1 covers the logic behind the deployment options.
Part 3 covers ReadyRoll.
Part 4 covers FlyWay.
Update: The VSTS Sql Server Database Deployment Task using SqlPackage.exe has since been released, you can read more about in this post. If you are using SqlPackage.exe outside VSTS the information in this post will still be relevant.
SqlPackage.exe is a free command line tool from Microsoft that you can use against SQL Server and Azure SQL Server to compare, update, import, export databases to name but a few scenarios. This post is only scratching the surface, the official documentation is here, please read it to get a better understanding of the tool.
SqlPackage.exe is a state based database tool, if you are not familiar with the term please read part 1 of the series here for an explanation. For continuous deployments I will be using a dacpac file as the source state for my database, read more on dacpac files here. The easiest way to generate dacpac files is using Sql Server Data Tools. SSDT is integrated with Visual Studio, when you build the SSDT project the output will be a dacpac file. This also fits in nicely with adding your database to source control and building the proper version of your database along with your other application components. You can read more about SSDT here.
Making Backups
As everyone knows you always make a backup of your database before deploying changes. You can instruct SqlPackage.exe to backup your database by passing the /p:BackupDatabaseBeforeChanges parameter. I assume it will create a backup using the default settings and location of your server but I prefer to rather do it myself. Here is a sample PowerShell script that will backup a database using the SQLPS module and SQL Server Management Objects SMO. Side Note: keep your PowerShell scripts in source control instead of lumping large blocks of script into text boxes in your build or deployment tool.
Now for the fun part, I’m going to explore 3 scenarios that I came across so far and I guess most other developers will as well.
Scenario 1: Automatically update your target database
If you are deploying to a development database or have an extremely informal (but not recommended) production environment, you can use SqlPackage.exe to update your target database by comparing it to your dacpac file and applying the changes on the fly. The syntax for this is:
/Action:Publish /SourceFile:$dacpac_path /TargetConnectionString:$constrScenario 2: Generate a script with the changes that will be applied
The more likely choice for production environments would be generating a script that your DBA can scrutinise before applying it to the production database. The syntax for this would be:
/Action:Script /SourceFile:$dacpac_path} ` /TargetConnectionString:$constr /OutputPath:$path_to_scriptfileFor the actual deployment you have a few options, you can pull in the generated script as an artifact of the build or email it to the DBA or drop it in a file location etc and they can apply it manually using their preferred procedure. Alternatively you can still keep the process semi automated and inside your release management tool by adding a deployment step that requires approval from the DBA, this step will use the generated script artifact to deploy the changes to a database.
Scenario 3: Generate a difference report between database
This is usually a post deployment task that will form part of your post deployment testing or sanity check to confirm the database is in the correct state. The SqlPackage.exe tool should always do its job properly but database are precious pets and everyone wants to make 100% sure everything is correct. The syntax is:
/Action:DeployReport /SourceFile:$dacpac-path ` /TargetConnectionString:$constr /OutputPath:$reportoutputfilepathI combined all the samples into one PowerShell script here.
Populating lookup tables
In your SSDT project you can add pre and post deployment scripts. I use the post deployment scripts with the SQL Merge statement to populate lookup tables, full example from MSDN here. There is a way to add the lookup table(s) as dependent objects but last I read the tables couldn’t contain foreign key relations to tables outside the dependent dacpac.
VSTS SQL Deployment Task
At the time of writing this blog post the official SQL Deployment task for VSTS was in preview and should be released very soon but in the mean time I used these examples scripts in VSTS and they worked so if you are in hurry they will do the job.
