When it comes to deploying SQL Server databases, Octopus Deploy and ReadyRoll make a great team.

Use ReadyRoll to carefully prepare your migrations – column additions, stored procedure changes, SQLCLR assemblies, or static data and then propagate those changes through your environments using Octopus with total consistency and minimal fuss.

ReadyRoll makes deploying with Octopus easy through it’s out-of-the-box support for OctoPack; a tool that produces standardized packages that can be consumed by Octopus Deploy.

During deployment of ReadyRoll database packages, Octopus can take care of:

• Creating the database if it doesn’t exist
• Deploying of any pending migrations with automatic transaction handling
• Rolling-back to earlier versions of procs/views/functions (if an old package is redeployed)
• Running of any pre/post-deployment actions (eg. db backup or bulk data insertions)

In your SQL migration scripts, you can make use of any of system or user-defined variables from Octopus, allowing for environment-specific settings to be consumed during deployment.

Packaging your database project

After using Visual Studio to create your ReadyRoll database project and import your database (if pre-existing), open the project properties.

On the Project Settings tab, you’ll notice an option to enable Octopus packaging for your project:

Choose Deploy to the default instance on the Tentacle option if you intend to install a Tentacle on each of your target SQL Server hosts (more about local vs remote deployments later in this article).

In effect this option sets the DatabaseServer variable to (local), however if your SQL Server instance has a specific name, you can optionally add a value for the DatabaseServer variable on the Octopus Deploy tab:

Switch back to the Project Settings tab and click Add/Edit NuSpec File. This will add a package metadata file to the project:

(Optional step) If you intend to use the Assembly Info patcher in TeamCity, you will need to add assembly information to your project. To do so, switch to the the SQLCLR tab, and click Assembly Information to generate an AssemblyInfo.cs file:

Now switch to the Release configuration and Build the solution to test packaging of your database project:

Opening up the generated package using NuGet Package Explorer will reveal a handful of deployment assets: 

Automating your solution build

You can choose any Continuous Integration server to automate the build of your ReadyRoll database project, but the Octopus plug-in for TeamCity makes integrating the packaging and deployment of your application and database components incredibly easy.

Add the following build step to a new or existing build configuration/plan:

Runner type: MSBuild
Build file path: (solution or project file path)
MSBuild platform: x86 (32-bit)
MSBuild version: .NET Framework 4.0 (or later)
MSBuild tools version: 4.0 (or later)
System Properties:
∙ RunOctoPack = True (only needed if not using the TeamCity Octopus plugin)
∙ OctoPackPackageVersion = e.g. %build.number% (only needed if not using the TeamCity Octopus plugin)
∙ OctoPackTargetsPath = path to the OctoPack.targets file, specify it only if you want to use a different version of the OctoPack than the one bundled into the ReadyRoll (optional)
∙ TargetServer = [Prod SQL Server Instance] (optional, see below)
∙ ShadowServer = [Test SQL Server Instance] (optional, see below)

As part of your build configuration, you will need to ensure that the NuPkg artifacts (produced during build) are made available to Octopus via a NuGet feed. Read more about how to integrate TeamCity with Octopus.

Packaging with TFS Build

Please see the Octopus documentation if you would like to build and package your solution with Team Foundation Server.

Setting up package deployment

The packaging steps above should have resulted in your database being packaged and served up to your chosen NuGet feed.

Within Octopus Deploy, add a new step to your deployment process, specifying the NuGet package ID from your NuSpec file:

In this example, we're assuming that you've installed a Tentacle (deployment agent) onto each of your target SQL Server machines, enabling a local database deployment. More about local vs remote database deployments later.

Specifying the Database Server/Name (optional)  

By default, ReadyRoll will deploy to the default SQL Server instance on the Tentacle, using the database name specified in your .sqlproj file. However this can be overridden by adding the following variables to your Octopus project:

• DatabaseServer: The target SQL Server server/instance
• DatabaseName: The name of the target database

Scope the variables appropriately to provide a different server name or database name for each target environment. You may want to do this if, for example, you want to deploy from a central Tentacle (more on this below), or if you need to deploy multiple copies of a database to the same SQL instance.

Creating a release

From the Project Overview page, click Create Release and select the appropriate package version.

If you enabled deployment previews in your build configuration, the release contents should give you a list of pending changes:

Click Deploy this Release and select an environment to deploy to. If the database doesn't exist on the target SQL Server instance, it will be created and then all migrations will be executed.

If you switch to the Task Log, you’ll see the output from your database deployment:

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.

Using Octopus variables in your database deployment

During deployment, ReadyRoll will automatically map your Octopus variables to SQLCMD variables. This makes it easy to consume values from your Octopus project's variables, such as environment-specific settings, or to make use of the system variables provided by Octopus itself.

For example, say you want to use the Octopus release number in a migration script. To do so, firstly add the $(OctopusReleaseNumber) variable to the ReadyRoll project and give it a Default value, eg. 1.0.0.0. 

Then add a new migration script to the project and specify the variable name in SQLCMD format:

-- <Migration ID="05a44c70-fd8b-4f1f-9d50-5441daa0db58" />
GO
PRINT 'We''re deploying release $(OctopusReleaseNumber)';
GO

When you deploy on your own machine, it will use the Default value provided in the SQLCMD variables tab. However when you deploy via Octopus, the value will be substituted with the current release number:

Mapping isn't limited to just the built-in Octopus variables: to use any of your own project variables (or any variable libraries that have been shared with your Octopus project), simply add the variable names to the SQLCMD variables tab within the database project in Visual Studio.

The scope that you specify in Octopus for each of your variables (e.g. whether the value applies to Development & Staging, or just Production) will be applied in the same way as a website or Windows service deployment. 

Local vs Remote Tentacle deployments

In this tutorial, we have configured the project to deploy to a local instance of SQL Server. This requires that a Tentacle is installed on each of your SQL Server host servers. For example, if you have 2 SQL Server machines in each your environments (Dev,Test & Prod), you would need to install the agent on 6 servers.

To reduce maintenance of your deployment server infrastructure, you may prefer to deploy from a single "central" Tentacle instead, assuming your network layout permits it. Follows these steps to perform a remote deployment from a single deployment agent:

1. In your project variables, add a variable called DatabaseServer and scope it appropriately for each of your target environments and/or deployment step(s) (see Specifying the Database Server/Name, above)
2. Install the SQL Native Client 2012 (x86/x64) and SQL Command Line Utilities 2012 (x86/x64) on the Tentacle server.
3. Install the OctopusDeploy Tentacle on a server that can communicate with the target SQL Server instances.
4. Add the machine on the Octopus Environments page, and under Deployment, specify all environments. Assign a new role name to the machine, e.g. remote-sql-deployment. 
5. On the project Process page, update the package step with the new role name.

Rollback support

Once a migration is executed against a given database instance, and the transaction has been committed, the operations performed within that migration cannot be undone (save for the restoration of a backup taken prior to its deployment). Read more about handling rollbacks