Tag: SSDT

Continuous Deployment Of Databases : Part 2 – SqlPackage.exe

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:$constr

Scenario 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_scriptfile

For 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:$reportoutputfilepath

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

Francois Delport
Latest posts by Francois Delport (see all)

Continuous Deployment Of Databases : Part 1

This is part 1 in my series on continuous deployment of databases. In this post I’ll be covering the logic behind the different deployment options.

Part 2 covers SqlPackage.exe.
Part 3 covers ReadyRoll.
Part 4 covers FlyWay.
VSTS Sql Server Database Deployment Task can be found here.

Continuous deployment of databases is a lot more complicated than applications so before we can look at continuous deployment of databases we have to look at managing database changes first. I’m going to give a high level overview of the problem space and methods to deal with database changes before looking at practical examples of continuous deployment of databases in the next post.

The Problem Space
A few of the challenges we face with database changes are:

  • Rolling back: Rolling back files is easy, just restore the previous version from source control, with databases you have to explicitly back them up before hand and restore them if needed and it can take a while for large databases and no one can work while you are restoring.
  • Down Time: It is easy to swap servers in and out of your load balancer to update them but it is not so simple with a database, even in a cluster. It is not impossible with databases just more work, there are solutions like sharding for instance but that is a post on its own.
  • State: Your application files do not contain state, you can recreate them easily while your database contains precious business data. You have to make changes to the structure and existing data without loosing anything. This leads to some teams hand crafting the change scripts for each release which is error prone.
  • Control: In large organisations DBAs are often the only people allowed to make changes to a database and they scrutinise the change scripts provided by developers before implementing them manually. You can’t have automatic migrations or automatically generated scripts executing against the database.
  • Drift: It is not unheard of that databases in production do not match the version developers are using the create the change scripts, especially with applications that are installed at the client premises.

Keeping Track Of Database Changes
The most important step to keep track of your database is adding it to source control, whether you are using specialised database tools or not. This will enable tracking the changes made to your database at specific points in time. Even just having the database create scripts committed each time a change is made will be better than nothing. If you are using the state/declarative method it will also help to solve conflicting changes, for instance two developers renaming the same column in a table at the same time will lead to a merge conflict in your source control system. You can also branch the database just like you do with application code to fix a serious production bug and then later merge the changes back into trunk.

Approaches To Implement Database Changes
Currently there are two ways to handle database changes, state/declarative and migrations, each one with its own strengths and weaknesses:

State/Declarative Method:
The state method works by comparing the target database to a source database and then generating a script to bring the target database in sync with the source database. This is best accomplished using a database schema comparison tool.
Pro: Any number of changes made in any sequence by team members will be included since the current state of the source database in source control is the source of truth not the steps taken to reach that state. Works great for databases during the development phase when you can tolerate dropping and recreating tables or columns and large teams making multiple changes at the same time. Less error prone since the scripts are generated by a tool instead of hand crafting them.
Cons: Cannot handle data migrations since the tool works according to the current state, it has no way of knowing how that state was achieved. For example if a new column was created by manipulating data from two existing columns.

Migrations Method:
This method applies a sequence of changes to a database to migrate it to the new state. With this method the migration scripts are the source of truth, the scripts are created by hand (there are exceptions see the comparison of tools later in the post) and keeping the correct sequence is very important.
Pro: Handles data migrations very well since developers can include any data manipulation required in the scripts.
Cons: Most of the time the scripts will be created by hand which is cumbersome and error prone. You have to take all the change scripts created during development and scrutinise them to weed out conflicting changes and you have to keep the correct sequence. For example developer A renames a column in table X and checks in the alter script, later developer B also renames the same column to something different without getting the changes from developer A and checks in his alter script. When you execute all the migrations the change from developer B will fail since the column exists under the new name. It is also cumbersome for all developers to keep their development databases in sync with the changes.

As you can see there is no clear cut solution to the problem and most tools will fix some of the problems but not all, for example Sql Server Data Tools will handle the state based method very well and Entity Framework Code First will handle migrations very well. Some tools like ReadyRoll try to combine both by generating a sequentially numbered migration script based on the current state of your database when you check-in a change.

Francois Delport
Latest posts by Francois Delport (see all)