In this tutorial, you'll deploy a database from a NuGet package, using the SQL Change Automation step templates for Octopus Deploy.

Prerequisites

• Create a NuGet package containing a Redgate database scripts folder.
  If you use SQL Change Automation for continuous integration, your build server can automatically build a package on every commit. See examples of how to do this using TeamCity or Visual Studio Team Services.
• An Octopus Deploy installation
• An Octopus Tentacle configured with the database role.
• An existing environment called Production, with the machine running the Tentacle agent to it.
• A existing Octopus Deploy project

1. Install the SQL Change Automation Octopus Step Templates

• Install the Redgate - Create Database Release step template
• Install the Redgate - Deploy from Database Release step template

2. Add the "Download and extract database package" step

This step picks up the NuGet package of the database schema you're going to deploy.

• Set up your NuGet package feed by doing one of the following:
  • Register your existing external NuGet package feed with Octopus. For more details, see Adding external package feeds.
  • Configure your build server to push packages to the Octopus built-in repository. For more details, see Using the built-in repository.
• In your project, on the Process tab, click Add step and select Deploy a package.
  • In the Step name field, enter Download and extract database package. 
  • In the Execution Plan field, enter database and press Enter. This must match the role you assigned to the Tentacle.
  • In the NuGet feed field, select either the name of the external feed you registered when you set up your NuGet feed, or the Octopus Server (built-in) repository.
  • In the NuGet package ID field, enter the name of the package without the version number. When the package is generated, NuGet package manager automatically adds a number. If we included it here, Octopus would only deploy the package that matched that name and version number. By removing the number, we're telling Octopus to always look for the latest package with that name.
  • In the Environments field, select Run only for specific environments and then select Production.
    If you leave this blank, the step will be accessible to all environments. 
• Click Save.

3. Add the "Create database release" step

This step creates the database deployment resources, including the Update.sql script.

• On the project Process tab, click Add step and select Redgate - Create Database Release.
• In the Execution Plan field, enter database and press Enter. This must match the role you assigned to the Tentacle.
• In the Exportpath field, enter the path the database deployment resources will be exported to.
  This path will later be used in the "Deploy from Database Release" step. It must be accessible to all tentacles used in database deployment steps.
• In the Database package step field, select Download and extract database package.
• In the TargetSQL Server instance field, enter the fully qualified SQL Server instance for the database you're deploying to.
• In the Target database name field, enter the name of the database you're deploying to.
• In the Username (optional) and Password (optional) fields, enter the SQL Server username and password used to connect to the target database.
  If you leave these blank, Windows authentication will be used to connect to the target database.
• In the Environments field, select Production.
  If you leave this blank, the step will be accessible to all environments. 
• (Optional) Under SQL Change Automation version, select Specific and provide the specific version of SQL Change Automation that will be used. By default, the latest version is used.
• Click Save.

4.  Add the "Review database deployment resources" step

This step pauses deployment to let you review the database deployment resources, including the Changes.html report, before allowing deployment to go ahead.

• On the project Process tab, click Add step and select Manual intervention required.
• In the Step name field, enter Review database deployment resources.

• Enter the Instructions field, copy and paste this text

  Please review the database changes, warnings and SQL change script in 'Changes.html'.

• In the Environments field, select Production.
  If you leave this blank, the step will be accessible to all environments. 

• Click  Save.

5. Add the "Deploy from database release" step

This step uses the database deployment resources to deploy the database changes.

• On the project Process tab, click Add step and select Redgate - Deploy from Database Release.
• In the Execution Plan field, enter database and press Enter.
  This must match the role you assigned to the Tentacle.
• In the Export path  field, enter the path the database deployment resources will be exported to.
  This must match the export path you entered in 3. Add the 'Create database release' step. 
• In the Database package step field, select Download and extract database package.
• In the Target SQL Server instance field, enter the fully qualified SQL Server instance for the database you're deploying to.
• In the Target database name field, enter the name of the database you're deploying to.
• In the Username (optional) and Password (optional) fields, enter the SQL Server username and password used to connect to the target database.
  If you leave these blank, Windows authentication will be used to connect to the target database.
• In the Environments field, select Production.
  If you leave this blank, the step will be accessible to all environments. 
• Click Save.

You've finished setting up the project steps.The Process tab should look like this.

6. Create a release

• On the project page, click Create Release. This page lets you add an optional release note. 
• Click Save. 
• Click Deploy to Production (or if there's more than one environment, click Deploy and select Production).
• Click Deploy. As the deployment process runs, Octopus Deploy shows the task progress list. The deployment pauses so you can review the database deployment resources.
• Click Changes.html to download the Change report.
  Use the report to review the update script, warnings, and details of what'll be added, removed or modified if you go ahead with deployment. 
• In Octopus Deploy, click assign to me and, in Notes, enter a comment to say you've reviewed the database deployment resources.
• If you're happy with the report, click Proceed.

What now?

Other SQL Change Automation step templates are available on the Octopus Deploy Library website:

• Redgate - Deploy from Database deploys the schema of a source database to a target database without a review step.
• Redgate - Deploy from Package deploys a NuGet package containing a database project to a target database without a review step.