Monday, 16 September 2013

DevOps | Supporting your platform

I am the build and release manager for an online gaming platform, and much of my time is spent supporting and improving the development and testing infrastructure; reducing friction, removing obstacles, straightening processes and generally improving our teams productivity.

Obvious activities include adapting and optimizing build & deployment processes, provisioning new environments. Less visible and less obvious is the maintenance and house-keeping that's required to keep things ticking along on a day to day basis.

Example of this are:
  • IIS meta-base corruption
  • Bespoke deployments
  • Corrupt message queues
  • Stopped windows services
  • Invalid HTTP routing
  • Firewall exceptions
  • Crashed application pools
  • AppFabric corruption
  • Missing message queues
The list is long, and without automation would seriously impede the development and I.T. teams.

In an ideal world, we could engineer-out these repetitive issues, but diverting resources towards C.I. is often as hard as getting water to flow up hill. The ancient Greeks of course solved this problem, but it did involve a giant screw, and I seem to have mislaid mine. With resource allocation beyond my control, the next best thing I can do to ease the pressure is put things right as quickly as possible.

Powershell 'maintenance' module

Requests for support are constant an unrelenting, but some days they can be more numerous than others. For common problems, i have an automated fix, and I've embedded these automated fixes into a collective package known to me as the "Maintenance" module.

This is a standard Powershell Module that is "Platform Aware". The module is able to resolve the run-time configuration of any given instance of the platform, and therefore knows where to find specific wesbites, endpoints, services and hosts. This provides the foundation for the automated fixes that the module provides:
  • Add and remove test users
  • Flush cache hosts
    • Traversing all cache nodes in a cluster
  • Repair MSMQ queues
    • Journalling messages
    • Clearing down queues
  • Reset firewall rules
  • Reset firewall routing
  • Reset application request routes
  • Restart windows services
  • Enable/disable diagnostic operations
  • Enable/disable the scheduled tasks 
    • Noting which were already disabled
  • Gracefully re-start the platform
    • Specific services, in a specific order
  • Warm-up the platform by visiting specific sites and end-points
    • Crawling site maps
    • Crawling message queue handlers
  • Clear-out logs

The general theme throughout is the maintenance and repair of any given instance.

Using the module

The module is available on all the development computers.

Import-Module DevMaintenance

With the module now connected to a given instance, complex multi-step operations can be initiated with simple commands.


Identifies all the app fabric cache hosts in given instance, and visits each one in turn, clearing out the existing caches and removing them from the cluster. Once the cluster is effectively torn-down, each node is rebuilt and re-added to the cluster.


Import-Module DistributedCacheAdministration
Import-Module DistributedCacheConfiguration

# On the cache cluster host

Use-CacheCluster -Provider "XML" -ConnectionString "c:\AppFabric\AppFabricCacheShare"

Get-Cache | % {
Write-Host " Removing $($_.CacheName)" -fore cyan
Remove-Cache $_.CacheName



# Per host found


Unregister-CacheHost -Provider "XML" -ConnectionString "\\$($\AppFabricCache" # -EA SilentlyContinue

Remove-CacheCluster -Provider "XML" -ConnectionString "\\$($\AppFabricCache" -force

# Back on the the cache cluster host

Remove-CacheCluster -Provider "XML" -ConnectionString "\\$($\AppFabricCache" -force

Reset-RequestRouting & Reset-TCPForwarding

A command that is frequently invoked, as developers (legitimately) alter the application request routes on their environments. However, the next developer that approaches the instance get's very confused when messages go missing. Easiest, quickest and simplest thing to do, invoke this command, and everything is reset back to exactly how it should be.

netsh advfirewall firewall delete rule name="Platform infrastructure - $($"  | Out-Null

netsh advfirewall firewall add rule name="Platform infrastructure - $($" dir=in protocol=$($service.protocol) localport=$($servicePorts) action=allow profile=domain  | Out-Null


The platform depends upon numerous windows services, but from time to time they failed to respond in a timely fashion and Windows terminates them. Any given instance of the platform can span multiple hosts, and visiting each in turn and resetting the services is a time consuming process.

This command can complete that onerous task in under a minute, visiting every service host in a parallel operation and performing the necessary actions.

Get-Service | Where-Object {$ -match "Suffix.*"} | Restart-Service 


The platform has a very specific start-up sequence, which if not adhered too, can be detrimental to the overall function. During system patching and reboots, the incumbent platform must be restarted before development can resume.


These actions can be invoked individually, or they can be pipeline into a series of actions that flow from one to the next.

Further more, they can be applied to one instance of the platform, or as many as I need.

Get-Platform | ? {$_.Types -eq "Development"} | Attach-Platform | Reset-Cache | Reset-TCP | Reset-Services | Publish-ETL | Reset-Hosts | Restart-Platform

A very handy facility to have, especially when an un-noticed bug made it back into the main trunk and out on to every development instance.

An entire legion of environments and hosts can be repaired invisibly and more importantly, without fuss.

The powershell pipeline makes it easy to chain together as many actions as I need, and I can add more and more as the platform evolves.

I need this module, I have over 30 deployed instances of our platform to support that span over 50 hardware nodes. And at the rate we are expanding, these numbers are likely to grow.

Continuous Integration with Database Projects (SQL Server)


In addition to the daily-grind of DBA activities, our DBAs also participate in the software production activities of the platform team. 

Continuous integration patterns

The business of creating data schemas and alike is left to the Developers with oversight and governance from the DBAs. The eventual deployment to production is handled exclusively by the DBAs with assistance from the developers.

Most new code features and fixes will involve modifications to the DB schema, the reference data and the gold-data sets. Without automation, the DBA team would probably need to provide a dedicated resource assisting the development teams with deployments to dev and test environments.

However, Microsoft do provide some very sophisticated tools for deployment and patching databases, and this repetitive and resource intensive task can be entirely automated an exploited in a CI process.

Software prerequisites

Visual Studio 2008 and 2010 support the "Database Project (.dbproj) which are compatible with SQL 2008. Database Projects will not be supported by SQL 2012, so you will have to move sooner or later.

Visual Studio 2010 and 2012 support the newer Data-Tier Applications (DacPac), which is supported by SQL 2008 R2 or better.

Compatibility issues

Database Project (.dbproj)

The Database Project (.dbproj) are not supported by SQL 2012 or newer.

Data-Tier Applications (DacPac)

The Data-Tier applications (DACPAC) arrived some time ago, supported by SQL 2008 R2 or newer.


MSBuild handles both types of database project in exactly the same way, and neither are too dissimilar to building a regular code assembly.

There are some subtle command line differences from the usual library builds.


$msbuild = @{
performanceParameters = "/nologo", "/noconsolelogger", "/p:WarningLevel=0", "/clp:ErrorsOnly", "/m:1"

loggingParameters = "/l:FileLogger,Microsoft.Build.Engine;logfile=c:\log.txt"

packageParameters = ,"/property:outdir=$($data.output)/","/p:configuration=release"

targets = "/t:rebuild"


cmd /c "C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe" `
$msbuild.performanceParameters `
$msbuild.packageParameters `
$msbuild.loggingParameters `
$msbuild.targets `


Whilst MSBuild is invoked in identical fashion irrespective, the output is entirely different between the two projects.

Database Project (.dbproj) - Last choice

The database project (dbproj) emits a manifest file, which effectively defines the intended DB schema. Using VsDbCmd the manifest is compared to a real instance of the database, and a diff-SQL script is generated.

The idea is then to run the generate diff-sql against the same target SQL instance, however I have found it to contain a serious amount of detritus that typically renders the actual target database unusable. To work around this some fairly hideous post-processing is performed to remove all the destructive actions. 

To date, I have not heard of any reports that the post-processed file has done anything that was unexpected, but I still do not have the confidence to use this on our Live systems. 

Database projects are compatible out of the box with Visual Studio.

Data-Tier Applications (DacPac) -1st Choice

However, things have got better. The Data-Tier applications (DACPAC) arrived some time ago, supported since SQL 2008 R2.

The DacPac's produced by the .SqlProj are quite a bit easier to deploy, and do not involve any post processing. With DacPac's you are very much "syncing" the compiled schema with the database instance. With a little bedding down, we may trust DacPacs with our production databases.

If you want to use the DacPac's, you just need to get the appropriate version of the Sql Server Data Tools first.

TFS and Build Agents

The database projects can be compiled by having the desired version of Visual Studio installed on your build agents. 

In order to work with the new Data-Tier applications (DacPac), you'll need the correct version of Sql Server Data Tools on all machines involved with building.

Packaging and Archiving

MSBuild has parameters which allow you to specify a custom output location. With this parameter, we can ensure our database output is correctly archived after the build has completed.

Deployment of Database Projects (.dbproj)

As I might have previously hinted, things have got better, but I had to support these projects once-upon-a-time, and maybe you still have too. So, here's a script example that covers the following actions:
  1. Generating the Sql-Diff file
  2. Post-Processing the SQL file to remove the destructive modifications
  3. Patching the SQL instance
function GenerateSqlScripts
Write-Debug "****************************"
$debugPreference = $debugMode;
$start = Get-Date

if (Test-Path $dbManifestFile) 
$drop = $false;

$VsDbCmd = @()
$VsDbCmd += "/dd:-"  #Note the dd:- means DO NOT RUN AT SERVER
$VsDbCmd += "/a:deploy"
$VsDbCmd += "/manifest:$dbManifestFile"
$VsDbCmd += "/cs:$sqlConnectionString"
$VsDbCmd += "/p:TreatVerificationErrorsAsWarnings=True " #This prevents failure on replicated tables
$VsDbCmd += "/p:DeployDatabaseProperties=True " #This prevents file resizing from being viewed as something to undo
$VsDbCmd += "/p:IgnoreColumnOrder=True " #This prevents table create/copy/drop/rename for the sake of columns being in the same order
$VsDbCmd += "/p:GenerateDropsIfNotInProject=True " #Drop objects not present(Dangerous)
$VsDbCmd += "/p:AlwaysCreateNewDatabase=False " #Don't drop the database first
$VsDbCmd += "/p:IgnoreLoginSids=False "
$VsDbCmd += "/p:IgnorePermissions=False "
$VsDbCmd += "/p:IgnoreRoleMembership=False "
$VsDbCmd += "/DeploymentScriptFile:$dbOutputFile"
$cmd = Join-Path $workingDirectory "..\BuildTools\VSDBCMD\VSDBCMD.exe"
$output = & $cmd $VsDbCmd
$output | Out-File "$"

# This is the repugnant Post-Process part
# I did only what needed to be done, this is not pretty.
# -----------------------------------------------------

(Get-Content $dbOutputFile) -inotmatch "(:on error exit|:setvar|REVOKE CONNECT|EXECUTE sp_droprolemember|DROP ROLE|DROP USER|AS \[dbo\])" -replace "USE \[\$\(DatabaseName\)\]","USE [$dbName]" |  Out-File $sqlDeploymentFile

$sql = Get-Content "$sqlDeploymentFile" | out-string

$keywords = @("USE [$dbName]")
$index = $sql.length;

foreach ($keyword in $keywords)
$foundAt =  $sql.ToLower().IndexOf($keyword.ToLower())
if ($foundAt -lt $index -and ($foundAt -gt 0))
$index = $foundAt
$sqlScript = $sql | % {$_.substring($index)} 
$sqlScript = "
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N'$dbName')
"  + $sqlScript 
$sqlScript | Out-File $sqlDeploymentFile
Write-Debug "Manifest file was not found!"

GenerateSqlScripts $ManifestFile $RawSqlOutputFile $ParsedFile $DbName $ConnectionString

$ParsedSql = Get-Content $ParsedFile | Out-String

Invoke-Sqlcmd -Query $ParsedSql -ServerInstance $server -Database $database -U $username -P $password 

Deployment of the Data-Tier Application (DacPac)

The terminology with the DacPacs is "Sync", and syncing the compiled dacpac manifest is a much simpler, much safer process.

function SyncDatabase

Write-Debug "****************************"

$debugPreference = $debugMode;
$start = Get-Date

if (Test-Path $dbManifestFile) 
#There is a deployment manifest file, so let's process it.
Write-Debug "Manifest file was found!"

& "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:publish /sf:"$dbManifestFile"  /tcs:"$sqlConnectionString" /p:IncludeCompositeObjects=true /p:BlockOnPossibleDataLoss=false | Out-File "$dbManifestFile.txt"


Continuous Integration and Microsoft Business Intelligence (Analysis Services)


In addition to the daily-grind of DBA activities, our DBAs also participate in the software production activities of the platform team. 

One of their biggest contributions is in the arena of data warehousing and ETL. 

Continuous integration patterns

Each new feature or fix can be impacted by, or upon, the data-warehousing systems. Therefore, just like our development teams isolate their code-changes using branches, the same must be true of the data warehousing.

Sometimes, the developers and DBAs will collaborate together in the same branch where its mutually beneficial to do so. Sometimes, the DBA's will take their own branches for general improvements not tied a specific feature in the code base.

It is vital that the code and data be packaged together during the build process, to maintain synchronicity and compatibility. Therefore, when I am building the code projects and producing output packages, I also need to include the co-dependent databases (.dbproj) and data-warehouses (.dwproj) at the same time.

Without this synchronicity, precious DBA time is sapped by developers and testers experiencing Cube and Warehousing issues within their respective environments. Deployments to production also become way more complex as the DBAs must attempt to match the correct versions of code and data.

Software prerequisites

The DBA's use similar tooling to the developers. Our DBA's use the Business Intelligence Development Studio, which appears to be Visual Studio with a few extra bits and pieces.

Crucially however, BIDS includes the necessary tooling and command line tools for building the Analysis Services (.dwproj) projects. 

You'll find the BIDS installer is part of your SQL server deployment CD or ISO.

You can't use MSBuild

Normally, MSBuild takes care of the work of finding the appropriate compiler, and all we need to do is provide the right set of command line parameters. Not so with Analysis Services, we must make a call directly to Visual Studio via the Command Line.

You may have found this post because you were looking for "How do I build .dwproj with MSBuild". Well, you can't, but there is an entirely satisfactory work around.

Visual Studio (BIDS edition) Command Line

For whatever reason, Analysis Services projects can only be built using the Visual Studio command line. So, here is my Powershell script for invoking this.


$vsroot = "$env:ProgramFiles (x86)\Microsoft Visual Studio 9.0"
$devenv = "$vsroot\Common7\IDE\"

$devenv = @{
fileParameters = $solutionFile, $projectFile
actionParameters = ,"/rebuild","development"
loggingParameters = ,"/out","$($data.log)"


cmd /c $devenv $devenv.fileParameters `
$devenv.actionParameters `

$devenv.loggingParameters | Out-Null.

TFS and Build Agents

All of the above applies to your Build Agents. If you want to build .dwproj, you need to have the appropriate version of BIDS on your build agents too.


Unlike MSBuild, this visual studio command line doesn't permit specifying a custom output location. The output is just deposited in the projects default output folder.

Packaging is therefore your responsibility I'm afraid. 


Deployment is extremely simple, and involves a single command line call to the Analysis Services tooling.

You need to have Analysis Services installed on your SQL server for this to work, but with that in place, generating the XLNA and deploying to your database server is a single command line action. 

Here is my powershell for deploying (or patching!) your data warehouse.
{note: this script has to be run on the Analysis Server where the Analysis Services and its tools can be found}

$analysisservices = "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\microsoft.analysisservices.deployment.exe"

$manifestfile = "C:\temp\Project.asDatabase"

cmd /c $analysisservices $manifestfile /s

Blissfully simple.

ETL deployment

The ETL deployment is auto-generated from SQL management studio. Right-Click the SQL Agent job and "Script as create to new window".

This SQL script can be stored in a file, and invoked by Powershell (Invoke-SqlCMD) whenever you need it.

Finito. GL HF.