Support Center

Using Octopus to deploy your SQL Server databases

Last Updated: May 01, 2014 09:14PM AEST

This article has moved to a new home. For up-to-date documentation please continue reading at http://doc.ready-roll.com/display/RRSQLDOC/Octopus+Deploy


Both Octopus and ReadyRoll support the build once, deploy many times tenet of Continuous Delivery: the practice of ensuring that the same build artifacts (binaries, scripts, etc) used in Dev testing are carried all the way through to Production.

ReadyRoll supports this practice by following the imperative database change pattern: migration scripts are fleshed-out in Dev and incrementally deployed through Test and Production.

 


 

This makes integration between ReadyRoll and Octopus super easy: as part of the project build process, ReadyRoll creates a type of NuGet Package-based file that can be readily consumed by Octopus and progressively deployed through all of your lifecycle environments.
 

Just follow a few simple rules for authoring and maintaining your change scripts and you will be a step closer to achieving database deployment nirvana: a repeatable, reliable release process for all of your SQL Server projects.


 

Getting Started

 

This is how it works. First enable artifact packaging within your ReadyRoll SQL project settings:


Then click Add/Edit NuSpec File to add a package metadata file to the project:


Add version details within the Assembly Information, accessible from the SQLCLR tab:

 

Now switch to the Release configuration and Build the solution to test the new settings. ReadyRoll will use OctoPack to create an Octopus-compatible NuGet package:


Opening up the contents of the output package will reveal a handful of deployment assets:
 


 

 

 

Configure Build in TeamCity

 

To build Octopus NuPkg files for your project, specify the following settings in a new build configuration:
 

Artifact path: db/AdventureWorks/bin/Release
Runner type: MSBuild
Build file path: db/AdventureWorks/AdventureWorks.sln
MSBuild platform: x86 (32bit)**
MSBuild version: .NET Framework 4.0
MSBuild tools version: 4.0
System Properties:
∙ Configuration = Release
∙ RunOctoPack = True
∙ TargetServer = [Prod SQL Server Instance] (optional)
∙ ShadowServer = [Test SQL Server Instance] (optional)

 

Specify the last two properties if you would like to include a preview of your deployment within Octopus.

 

** If your build agent is running a x64 version of Windows, you will need to specify the x86 version of the MSBuild executable in your build configuration: C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe

 

Note that there are a couple of dependencies that you will need to install on your build server; check out our guide on setting up your project for Continuous Integration for more information.

After performing a build, the package will be ready to serve via a NuGet feed

 

 

 

Configure Octopus Project

 

Now add a new step to your deployment project to consume the package from your NuGet feed:


Provide values for the DatabaseName and DatabaseServer variables for each of your target environments as appropriate (Note: Specifying either of these variables in ReadyRoll 1.7.2 is optional). For example, here's how you might set up your Octopus variables if your Staging and Production environments share the same SQL Server instance:

** The NuPkg file will use Windows Authentication to connect to your database server by default; if you would prefer to use SQL Server Authentication instead, specify the DatabaseUserName & DatabasePassword variables as part of your project. 

 

 

 

Create Octopus Release

 

To test out the deployment, click Create release. If you enabled deployment previews in your build configuration, the release contents should give you a list of pending changes:

Deployment preview in Octopus

 

Click Deploy to [Environment]. As this is the initial deploy to the Staging environment, the database will be created from scratch and all project migrations applied:


In subsequent deployments, the package will only deploy any new migrations that have been added to the project (if any).


When you're ready to deploy onto other environments, including Production, click Promote to... [Environment]. The exact same set of migrations will execute against your upstream environment, giving you a predictable deployment outcome every time!


You can also have TeamCity create the release in Octopus and deploy it to your target environment for you. For more information, see this guide for integrating TeamCity with Octopus



 

Octopus to SQLCMD Variable Mapping


ReadyRoll supports automated mapping of Octopus variables to SQLCMD variables. This makes it incredibly easy to consume values from your existing pool of environment-specific variables, or to make use of the system variables provided by Octopus itself.


For example, say you are using Octopus (version 1.x) and you want to store the version number of the deployment package in the target database. To do this, firstly add the $(OctopusPackageVersion) variable to the ReadyRoll project and give it a Default value, eg. 1.0.0.0.



Then include a reference to the variable in a Deploy-Once script. When you deploy locally it will output the Default value, however when you deploy via Octopus the value will be substituted with the deployment package version:

Screenshot - 6_12_2012 , 10_25_19 PM

** Please note that the names of system variables has changed between Octopus 1.x and 2.x.
Additionally, if you are using Octopus 2.x, you will need to remove the "." characters from the name of the variable when adding it to your project. For example, if you want to use the Octopus.Release.Number variable, add a SQLCMD variable called $(OctopusReleaseNumber) to your ReadyRoll database project.


Mapping isn't just limited to built-in Octopus variables; simply add your variables to your ReadyRoll project to map your custom variables as well.

Continue reading about how to work with SQLCMD variables in your ReadyRoll project.


 

Deploying Multiple Databases


To deploy multiple databases in your Octopus Project, simply add a Step for each database in your solution.

If your solution has one or more databases with interdependencies, such as cross-database joins, you will also need to add Octopus variables for each of the database references contained within your ReadyRoll projects.

To avoid repetition in your variable values, you can use variable substitutions in Octopus, e.g.


Update 25-Nov-2013: If you're using ReadyRoll 1.7.2 or later, the database names will be passed to your Octopus Deploy project automatically (using the SQLCMD variable defaults). You only need to add them as above if you want to override the values.

Note: The names of the Octopus variables should match those of the SQLCMD variables that underlie your database references (the variable name you specify when adding the database reference to your project). 

 

Further reading

readyroll.support@red-gate.com
http://assets2.desk.com/
false
readyrollsql
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete