9 Jan 2015

Automating SQL Server Database projects Deployment

Within this post I wish to share the methods I used to automate the deployment of my SQL Server database projects.  I needed to automate the deployment of my database projects within the build process so I could integrate the execution of my unit tests. As mentioned in a previous post: Integrating TSQLT with Team Foundation Build Services continuous build.  I also wanted to automate the deployment into the development, test and production environment for the following reasons:
  • To empower the developers to do database deployment into the required environment and reducing the dependence on me.
  • To reduce the complexity of deploying the database projects.
  • To have an easy deployment process to document i.e. run this script.
  • In a way to document the deployment of the database projects.
Before I start I would like to highlight an invaluable project / solution file(s) that help with the automation.  These are "Profiles" which are used as configuration files for deployment.  They store settings like connection string, database name and other behavioural settings for DAC services (the framework used to deploy SQL Server database projects).  These files can be created when deploying from visual studio.  If the database solution, like mine was, is made of composite projects.  Then profiles, which can be shared between the SQL Server database projects, can ensure that the projects are deployed to the same SQL Server and database.

As mentioned before I needed to automate the deployment of my database project for the unit tests.  I couldn't use the MS-Test deployment functionality as I needed a post deployment script within a composite project to be executed.  I decided to use PowerShell to automate SQLPackage.exe for the following reasons:
  • It would be easier to manage the arguments required for SQLPackage
  • Easier to capture the output of SQLPackage into a log file.
The basic approach was to use Invoke-Expression on a dynamic created command string and redirect the output to file.  Then check the $LASTEXITCODE was 0 after the execution to ensure no errors had happened.  The command string was made up of hardcoded values and parameters passed into the script.  A demo version of this can be downloaded from the following link: http://1drv.ms/1Ks0qq0.

To deploy the successful builds the tool that was used within the company, at the time, was: TFSDeployer ( http://tfsdeployer.codeplex.com/ ).  Unfortunately I need to point out that this is not an active project, more information can be found here: http://tfsdeployer.codeplex.com/discussions/449767.  To get TFSDeployer to deploy the dacpac files, the build output for database projects, I needed to create a PowerShell script.  I wanted to find a way of using PowerShell without using an external process, SQLPackage or MSDeploy, and using $LASTEXITCODE to detect errors.
While searching for an alternative method I came across the DAC Services API (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacservices.aspx. The API supports .Net which allows interaction to all the functionality of the framework.  With this API I was able to create a reusable function to do the following:
  • Generate deployment report and script.
  • Optionally deploy the changes to target server and database.
  • Capture all the events into log files.
I also wrapped this function within a script which will take a parameter to load a DAC Services API library file.  A version of this script can be downloaded from the following link: http://1drv.ms/1Kc5fDP.  Also a demo script of using the DAC function within a PowerShell script can be downloaded from here: http://1drv.ms/1Kc5sad

The scripts can only be executed within PowerShell 3 or above as DAC service API library requires Microsoft .Net 4.0 or above.  Also the following SQL Server redistributable components are required:
further reading:


אורי דונר said...

Hi Steven,
Is it possible to create the publish profile from the command line, not from the GUI?

Steven Wright said...

There is not a command line tool to create a publish profile. However you could from create the a file yourself as it just and XML file. You could use a profile create from the GUI tool as a template file.