Automating Database Deployment - Part 2: Using a JSON Config File

Mark Wojciechowicz

In my last post, Automating Database Deployment – Part 1, I demonstrated how to use powershell to deploy database schema changes from a dacpac file (the output of an SSDT build).  This post will explore one way to make that process is more configurable.

Using a Configuration File

When we deploy to different environments (local, dev, qa, pro, etc.), we usually will have different settings per environment.  For example, we might have a different root directory where the database is located or where our working folder is stored.  The database might be on a different instance or use a different port.  Even if these differences do not exist, it is a good idea to make the configurable so that you can install your application anywhere and make it work, as would be the case for DR.

A deployment configuration file will contain these per-environment settings, which powershell can access during deployment.  In this example, we will store the filename of our publish profile and a list of directories we want create if they do not exist.  I prefer to use JSON for the format of this file because it is clearer to read than a CSV and there is native support for parsing it in POSH 3.0 and up. 


“JSON (JavaScript Object Notation) is a lightweight data-interchange format”, as will tell you.  JSON is structured in key –value pairs and the format is very simple, which is a good choice for a small configuration file.  Here is what our example file will look like:

                "Local" :
                                "RootFolder" : "C:\\",
                                "PublishProfile" : "Demo.Publish.xml"
                },"Dev" :
                                "RootFolder" : "C:\\Demo",
                                "PublishProfile" : "DemoTest.Publish.xml"
                },"Folders" : [
                                "ETL ",

Our whole configuration is contained in between moustaches :{.  Each environment name is followed by a colon and another set of moustaches:

"Local" :
                                "RootFolder" : "C:\\",
                                "PublishProfile" : "Demo.Publish.xml"
Each configuration is a key-value pair separated by a colon and followed by a comma within the moustaches:

"RootFolder" : "C:\\",

Our first configuration is the “RootFolder,” which we will use to create a directory structure.  Note that there are a few special characters in JSON that need to be escaped - in our case, the backslash needs to be escaped with another backslash. 
Last, we have an array of folders, which is contained by straight brackets:

"Folders" : [
                                "ETL ",

We will use this array to create each directory if it does not exist.  As we build out an automated deployment, we should capture each and every step to set up an environment.  In this way, we will never miss anything each time we deploy and we will easily be able to set up a new server. 
Our Powershell Script follows:


$installDir = Split-Path -Parent $MyInvocation.MyCommand.Path
Push-Location $installDir

cd "c:\temp\Build" 
$sourceFile = "$installDir\Demo.dacpac" 
$sqlPackage = "$env:PROGRAMFILES (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" #the location can vary by installation

#store contents of file in JSON object
$configs = (get-content .\Deploy_Config.txt) -join "" | ConvertFrom-Json
$profile = $configs.$Environment.PublishProfile

#deploy database
&  $sqlPackage /Profile:"$installDir\$profile" /SourceFile:$sourceFile /Action:Publish

#Create ETL directories
$root =  $configs.$Environment.RootFolder

    foreach ($f in $configs.Folders)
        $f = "$root$f"
        if(!(Test-Path $f))
            new-item -Path $f -ItemType directory


A few notes about this script:

·         We can call the script like this: & C:\build\Deployment_Script.ps1 -Environment "Local"
·         The parameter value that we pass in will use the settings for that environment.  
·         ConvertFrom-Json works just fine with normal  formatting in 3.0, but I found 4.0 was fussy and needed the carriage returns removed, thus -join””
·         Once the variable is loaded in the scripting environment, it will support intellisense as shown below.
So if we want to deploy our code to different environments, we can just zip up all these pieces: dacpac, publish profiles, config file and powershell script to execute it.  Then we drop that onto a server and execute the script with the appropriate parameter.  It is the same process for each environment, making the deployment process easy and consistent.

Labels: , ,