At Redgate’s 2012 SQL in the City US tour, a question many people asked was: “How can I deploy x with Deployment Manager?” At the time, I was investigating Microsoft’s business intelligence stack for Redgate's SSAS Compare tool, and I began wondering if you could use Deployment Manager to deploy SSAS databases. This article will walk you through my solution – and hopefully inspire you to create your own deployment solutions.

Who might find this paper useful

• Deployment Manager users interested in extending Deployment Manager functionality

• Business intelligence developers deploying SSAS databases to several environments, and want to automate the process

Overview

To deploy a SSAS database with Deployment Manager, we'll:

1. Create a NuGet package containing the SSAS files and a PowerShell deployment script
2. Put the NuGet package on a NuGet server
3. Get Deployment Manager to pull the latest package from the NuGet server
4. Tell Deployment Manager to deliver the package along environment specific variables to a Deployment Manager Agent
5. The Deployment Manager Agent will unpack the package and run the PowerShell deployment script with the environment variables.

Technologies used

We’ll be using the following technologies:

• Deployment Manager
  A Redgate tool that manages your deployment process. Its main functionality is the deployment of web applications and their databases, but, as we’ll learn, it can be extended to deploy almost anything.

• SQL Server Analysis Services (SSAS) databases
  Part of Microsoft’s business intelligence stack. SSAS databases pull in data from multiple sources and process it, so users can easily query data, manipulate it in pivot tables, and draw conclusions from it.

• NuGet
  Microsoft’s open-source package manager for the .NET Framework. A NuGet package is essentially a zip file with some additional metadata. You may have used NuGet before to add libraries to Visual Studio projects, but we’ll be using them for a broader purpose.

• PowerShell
  Microsoft’s command line scripting language. Deployment Manager uses PowerShell to execute scripts on deployment. We’ll use it to extend Deployment Manager’s functionality.

The basics of deploying SSAS databases

When you build a SSAS database project, Visual Studio breaks it into four files (as described here). This is because some of these files describe the database and others describe the environment the database is being deployed to. For example, the schema described in the .asdatabase file is the same across all environments, but the connection string described in .deploymenttargets will be different across test and production environments.

The four parts are:

• .asdatabase 
  The database schema we’ll deploy. This doesn't vary between environments.

• .deploymenttargets
  The target database and server we’ll deploy to. In this example, this changes depending on where we’re deploying to.

• .deploymentoptions
  Additional deployment steps; for example, processing after deployment. In this example, we’ll use the same options for every deployment.

•  .configsettings
  The connection details for the SSAS database’s data sources. In this example, this changes depending on where we’re deploying to. The test database will not pull in data from our production data sources.

Making a NuGet package

We’re going to create a NuGet package that Deployment Manager will use to deploy our SSAS database schema.

The package will contain:

• the .asdatabase and .deploymentoptions files from the SSAS database project
• a copy of .configsettings for each environment we can deploy to. In this example, our environments are Test and Production
  
• .deploymenttargets is included only as a template. Its values will be overwritten by variables in Deployment Manager
• deploy.ps1. This is the script Deployment Manager uses to deploy the database. Among other things, it will choose which of the two .configsettings files to use in deployment, depending on where we’re deploying to
• import-Format-Xml.ps1 and ascmd.exe are both used by deploy.ps1 to complete the deployment.

The two .configsettings files

We’ll need two .configsettings files, because we’ll be deploying to two environments: a test database and a production database.

If we wanted to, instead of including the .configsettings file in the NuGet package, we could set the connection details inside Deployment Manager’s environment variables, as we will with the deployment targets. But for ease of maintenance, we’ll create two .configsettings files — one for the production database, and another for the test database — and include them both in the NuGet package.

When you build your project, Visual Studio produces a .configsettings file configured for your test environment. We’ll create a copy of this and configure it manually for our production environment.

Now we have two .configsettings files: one for the test environment, and one for the production environment.

The two .configsettings files have to describe the same objects. Whenever we make changes to the first .configsettings file, we have to manually edit the second .configsettings file.

Creating a .nuspec file

A .nuspec file describes a package metadata, and can tell you where to find the files that go in it. .nuspec files can be used to create NuGet packages.

Here's the .nuspec file we're using. We've named it SampleSsasDeployment.nuspec.

<?xml version="1.0"?>
<package >
	<metadata>
		<id>SampleSsasDeployment</id>
		<version>1.0.0</version>
		<authors>Redgate Software</authors>
		<owners>Redgate Software</owners>
		<description>An example SSAS package</description>
		<copyright>Copyright 2012</copyright>
	</metadata>
	<files>
		<!-- My project files -->
		<file src="..\bin\SSAS Compare Samples.asdatabase" />
		<file src="..\bin\SSAS Compare Samples.deploymentoptions" />
		<file src="..\bin\SSAS Compare Samples.deploymenttargets" />
		<!-- One copy of .configsettings for each environment (production and test) -->
		<file src="..\bin\production\SSAS Compare Samples.configsettings" target="production" />
		<file src="..\bin\test\SSAS Compare Samples.configsettings" target="test" />
		<!-- SSAS deployment files -->
		<file src="deploy.ps1" />
		<file src="Import-Format-Xml.ps1" />
		<file src="ascmd.exe" />
	</files>
</package>  

Here's how I've structured my project:

To create the NuGet package from the .nuspec file, in the Windows command line we type:

nuGet.Exe Pack SampleSsasDeployment.nuspec -Version A.B.C.D -NoPackageAnalysis

This creates the file SampleSsasDeployment.A.B.C.D.nupkg. This is the package Deployment Manager will deploy.

NuGet package server

This is where we’ll keep the package, so Deployment Manager can retrieve it and deploy it when it needs to.

How we’ll use Deployment Manager

We need to set up a Deployment Manager project.

1. In Settings > Package Repositories, add the Package Repository containing SampleSsasDeployment.A.B.C.D.nupkg
2. Create a Test and Production Environment.

3. Add a machine to each.

   Each machine must have SQL Server Analysis Services installed.
4. Create a new project named SSAS Sample
5. Add a deployment step and enter the nuget package ID SampleSsasDeployment.
6. Define the following variables:

Deploying

We’re ready to create a release and deploy it.

Deployment Agent

The Agent does the following things:

1. Receives the package and the specified environment variables from Deployment Manager
2. Extracts the contents of the package into a temporary directory
3. Configures a PowerShell environment runtime with the environment variables
4. Executes deploy.PS1

deploy.PS1

This is the PowerShell script that does the deployment work. The NuGet package is sent to the Agent, where it's unzipped and deploy.ps1 is executed.

The first part of the deploy script checks the required variables have been set:

@(
    "asDatabase",
    "asServer",
    "asDeployExe",
    "asConfigSettings"
) |
ForEach-Object { 
    if (!(test-path variable:\$_))
    {
        Write-Error "Variable '$_' does not exist. Please set this variable from the Deployment Manager portal." 
        Exit 1
    }
}

Now we get the PowerShell script to edit the XML files with the following variables:

#User Deployment Manager variables to update the deploymenttargets file
$deploymentTargets = Get-Item *.deploymenttargets
$deploymentTargetsXml = [xml] (Get-Content $deploymentTargets)
$deploymentTargetsXml.DeploymentTarget.Server = $asServer
$deploymentTargetsXml.DeploymentTarget.Database = $asDatabase
 
#If optional asConnectionString variable is set use it, otherwise remove ConnectionString from deploymenttargets
if ((test-path variable:\asConnectionString)) {
    $deploymentTargetsXml.DeploymentTarget.ConnectionString = $asConnectionString
}
else {
    $connectionStringNode = $deploymentTargetsXml.DeploymentTarget.SelectSingleNode("ConnectionString")
    if(!($connectionStringNode -eq $NULL))     {
        $deploymentTargetsXml.DeploymentTarget.RemoveChild($connectionStringNode)
    }
}
#Save deploymenttargets file
$deploymentTargetsXml.Save($deploymentTargets.FullName)
 
#Copy .configsettings file from subdirectory to root
$configSettings = Get-Item "$asConfigSettings/*.configsettings"
Move-Item $configSettings .

Now we’ll use two different command line tools to do the SSAS deployment:

• Microsoft.AnalysisServices.Deployment.exe(MASD.exe)

This is installed with SQL Server Analysis Services. Because it’s a large .exe file, and it’s installed with SSAS anyway, we’ll provide a path to it in the variables rather than include it in the package.

• ascmd.exe

This is part of the Microsoft SSAS samples, and relies on DLLs installed with SSAS. Because it’s a very small file, we’ll include it in the package. You can download it here.

We could use MASD.exe on its own, but it doesn't give us any feedback when it executes the script. ascmd.exe returns the server’s full response, so we’ll use MASD.exe to create the XMLA script and ascmd.exe to execute it.

#Use Microsoft.AnalysisServices.Deployment.exe to write optimised deployment script
$xmlaDeployScript = "deployScript.xmla"
$deployScriptResults = "scriptResults.xml"
$deployScriptTrace = "scriptTrace.txt"
&$asDeployExe /o:$xmlaDeployScript /s | Write-Output
 
#Use ascmd.exe to run the script (ascmd.exe gives better feedback than Deployment.exe)
$ascmdExe = Get-Item "ascmd.exe"
&$ascmdExe -S "$asServer" -i "$xmlaDeployScript" -o "$deployScriptResults" -T "$deployScriptTrace" | Write-Output
$ascmdExitCode = $LASTEXITCODE

MASD.exe creates the XMLA script by combining the four XML files and comparing them to the target database. Then ascmd.exe gives us the results from the server so we can check the deployment has worked.

ascmd.exe's exit code is returned to Deployment Manager to indicate success or failure of the deployment:

#Write out the server response to the console
. .\Import-Format-Xml.ps1
Format-XML ([xml](Get-Content "$deployScriptResults")) -indent 4
 
Exit $ascmdExitCode

How did you get on?

Hopefully, after reading this guide, you can now deploy SSAS databases with Deployment Manager.

If you had any problems on the way, or have suggestions about how we could make this guide better, email the Deployment Manager support team at DMSupport@red-gate.com.