Automating Database Deployment - Part 1

Mark Wojciechowicz

Recently, I participated in a DR event to recover a data warehouse.  The configuration of the environment was not terribly complex, but it took some time to find and adjust settings to get the ETL running again.  What occurred to me is that, if you have an automated deployment process, DR will be a breeze.  And, if you are doing DR test runs, you probably deserve automated deployment.  This post will be 1 of 2, in which I will describe an approach to dealing with database changes.

How do we detect differences in the database schema and create a change script?

There are a bunch of tools out there which support schema comparison, but I prefer SSDT.  This will allow us to perform a schema comparison and create a SQLCMD file, which can be executed against the target database to implement the changes.  Please note that the script generated is not idempotent.  If a new table needs to be created, it will create that table without checking to see if it already exists.  While the output script cannot be executed twice in a row without error, the process of publishing or schema comparison can.  So it is the both the mechanism to compare schemas and implement those changes in a script which makes the entire process idempotent.

Getting set up for automation

We can manually publish changes to a database in SSDT by right clicking on the project and selecting Publish.  This is a good starting point, but instead of publishing, we will create a publish profile.  Note that in the pop-up menu there are a few options about profiles on the bottom left corner:

When you are done configuring the profile, click "Save As," rather than "Create Profile."  The difference between the two, is that "Save As" lets you specify the name and location of the file.  The latter just drops the file in the root of your project and names it automatically.  We can use this feature to create environment specific profiles.  For demo purposes, I will create a second profile with a different database name.

Advanced Profile Settings

The advanced settings button can be used to modify the settings properties for the profile.  This has a few import options to consider per environment:

The end result of saving the profile is an xml document that specifies these settings, including the values for SQLCMD variables which will usually vary by environment:

Scripting deployment

Once we have the publish profiles created, we can script the deployment process in powershell using the sqlpackage.exe utility.  Note that by clicking on Publish or double clicking a profile, the project will be built.  Building the project creates a dacpac file in the bin folder of the project.  The dacpac stores the schema definition which can be used for comparison to a target database.  I have copied the dacpac and publish profiles to staging folder in c:\temp\build.  The deployment script looks like this in powershell:

  1. $buildFolder = "c:\temp\Build"  
  2. $sourceFile = "$BuildFolder\Demo.dacpac"  
  3. $sqlPackage = "$env:PROGRAMFILES (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" #the location can vary by installation  
  5. &  $sqlPackage /Profile:"$buildFolder\Demo.Publish.xml" /SourceFile:$sourceFile /Action:Publish   
  6. &  $sqlPackage /Profile:"$buildFolder\DemoTest.Publish.xml" /SourceFile:$sourceFile /Action:Publish   
In the next post, we will make the above script configurable using a JSON configuration file.

Labels: , ,