Mark Wojciechowicz
2014-04-29
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
“JSON (JavaScript Object Notation) is a lightweight
data-interchange format”, as
json.org 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
",
"ETL\\Drop",
"ETL\\Archive"]
}
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
",
"ETL\Drop",
"ETL\Archive"]
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:
param([string]$Environment)
$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: Continuous Integration, JSON, SSDT