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.
Octopus Deploy Step Template
To get up and running with Octopus Deploy even more quickly we've created a ReadyRoll step template you can drop into your process. Instead of having to use variables, you can use our step template user interface to enter your database connection details. The template is available from the Octopus Deploy Library.
See Tutorial: Octopus Deploy Step Template for more detail on getting set up.
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:
Performance tip: Once you have configured your database project for Octopus packaging, feel free to un-check the Octopus Deploy-compatible NuGet package output option in project settings. This will result in much faster Visual Studio builds (don't worry, the package will still be generated during Continuous Integration).
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)
∙ 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)
∙ TargetServer = [Prod SQL Server Instance] (optional)
∙ ShadowServer = [Test SQL Server Instance] (optional)
Optional system properties
Specify the TargetServer and ShadowServer properties if you would like to include a preview of your deployment within Octopus:
- TargetServer: Target instance of SQL Server to generate the preview against. Typically this will be the SQL Server instance in your Production environment. Read (db_datareader membership) and VIEW DEFINITION permission is required within the target database(s).
- ShadowServer: An instance of SQL Server where ReadyRoll may create a temporary copy of your database based on the project sources. Typically this will be an instance of SQL Server in a Development environment. The schema within this database is compared back to that of the TargetServer in order to determine what schema changes are pending deployment, if any. Sysadmin permissions are required on this server.
For more information, see Octopus deployment preview.
Before you can build ReadyRoll projects, you'll need to do one of the following:
- install ReadyRoll on your build agent (server), or
- install or include the ReadyRoll.MSBuild NuGet package in your solution
For more information, see Installing on your build agents.
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.
By default, Windows Authentication is used to connect to your database server. This means that the account that the OctopusDeploy Tentacle service is configured to run-as needs to have access to your target SQL Server instances.
If you would prefer to use SQL Server Authentication instead, add the following variables within your Octopus project:
- UseWindowsAuth: False
- DatabaseUserName: (username)
- DatabasePassword: (password)
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. 220.127.116.11.
Then add a new migration script to the project and specify the variable name in SQLCMD format:
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:
- 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)
- Install the SQL Native Client 2012 (x86/x64) and SQL Command Line Utilities 2012 (x86/x64) on the Tentacle server.
- Install the OctopusDeploy Tentacle on a server that can communicate with the target SQL Server instances.
- 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.
- On the project Process page, update the package step with the new role name.
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).
However it is possible for certain types of Deploy-Change migrations to be rolled-back, providing this option has been enabled in your ReadyRoll database project settings. This applies to any kinds of "replaceable" objects in your database, such as stored procedures, triggers and views. It does not apply to objects that hold a persistent state, such as tables. In this sense, any database rollback operation performed within Octopus can only be considered a partial rollback.
To perform a partial rollback in Octopus, simply re-deploy a prior release to the target environment. During deployment, the package will detect which Deploy-Change migrations are out-of-sync and incrementally deploy them.
For more information about how to setup Deploy-Change migrations in your project, see Programmable Objects.