This example shows you how to automate the deployment of your database changes to a Preproduction environment before releasing to Production, using the SQL Change Automation cmdlets and Octopus Deploy.

Before you start

1. 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.
2. Install SQL Change Automation. Click here for more details.
3. On the same machine, install Octopus Deploy. Click here for more details.
4. Install an Octopus Tentacle and assign the db-server role. Click here for more details.
5. Create two environments - Preproduction and Production - and add the machine running the Tentacle agent to both.
6. Create an Octopus project called Widget Deployment. 
   For more details on managing projects, see Projects (Octopus Deploy documentation).
7. Set up your NuGet package feed by doing one of the following:
   1. • 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.

1. Set up project variables

Set variables for the parameter values used in the update script. 
Variables let you reuse scripts in other deployments, which you couldn't do if you used hardcoded values. For more information, see Variables (Octopus Deploy documentation).

In the project Variables tab, copy and paste the following variables. Replace variables surrounded by <angle brackets> with values specific to your environment:

2. Add the "Overwrite preproduction with production" step

The first step you'll add to your Widget Development Octopus project overwrites your preproduction database with production. 

Preproduction should be identical to production (or as close to identical as you can manage in your environment) so you can: 

• test exactly what'll happen when you deploy to production without the risks associated with updating a live database

• test server configuration and environmental settings

• detect bugs and prevent them going into production

There are several ways to make sure both environments are identical, including restoring from a backup of production. For more details, see Managing Test Data as a Database CI Component (Simple Talk article).

In this example, we're using the New-DatabaseRelease cmdlet to deploy the production schema to the preproduction database. Using the cmdlet in this way - with preproduction as the target and production as the source - will keep the example simple and still create the exact copy of production we need.

1. In the project Process tab, click Add step and select Run a PowerShell script.
2. In the Step name field, enter Overwrite preproduction with production.
3. In the Machine roles field, enter db-server and press Enter.
   This must match the role you assigned to the Tentacle.

4. In the Script Content field, copy and paste this script:

   Deploy Production to Preproduction

   # This step makes sure that the preproduction database and the production database
   # are identical, by deploying the production schema to the preproduction database.
   $preproductionDatabase = "Data Source=$DatabaseServer; `
                             Initial Catalog=$PreproductionDatabaseName; `
                             User ID=$SQLServerUsername;Password=$SQLServerPassword"
                        
   $productionDatabase = "Data Source=$DatabaseServer; `
                          Initial Catalog=$ProductionDatabaseName; `
                          User ID=$SQLServerUsername;Password=$SQLServerPassword"
                           
   $release = New-DatabaseReleaseArtifact -Target $preproductionDatabase -Source $productionDatabase
   Use-DatabaseReleaseArtifact $release -DeployTo $preproductionDatabase -SkipPreUpdateSchemaCheck -SkipPostUpdateSchemaCheck

    

5.  In the Environments field, enter Preproduction.

6. Click Save.

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

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

1. In the Widget Deployment project, on the Process tab, click Add step and select Deploy a NuGet package.
2. In the Step name field, enter Download and extract database package. 
3. In the Machine roles field, enter db-server and press Enter.
   This must match the role you assigned to the Tentacle.
4. 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.
5. In the NuGet package ID field, enter the name of the package without the version number. For example, if the package was called Widget.0.1.nupkg, you'd only enter Widget.
   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.
6. In the Environments field, select Preproduction.
7. Click Save.

4. Add the "Create database release artifacts" step

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

You'll specify two databases: Preproduction and Production (-Target $productionDatabase, $preproductionDatabase). This makes sure the Production and Preproduction database schemas are the same before creating the update script. If they're different, the deployment will fail at this point.

1. On the project Process tab, select Add step and select Run a PowerShell script.
2. In the Name field, enter Create database update resources.
3. In the Machine roles field, enter db-server and press Enter.
   This must match the role you assigned to the Tentacle.

4. In the Script Content field, copy and paste this script. It uses the project variables that you set up earlier:

   Make Database Release artifact

   # This step uses SQL Change Automation cmdlets to create a directory containing the
   # Database Release artifact: all the artifacts relating to the deployment.
     
   # Makes sure the directory we're about to create doesn't already exist.
   If (Test-Path $DatabaseReleaseArtifactDirectory) {
       rmdir $DatabaseReleaseArtifactDirectory -Recurse -Force
   }
    
   # Sets up connection string for the production database.
   $productionDatabase = "Data Source=$DatabaseServer; `
                          Initial Catalog=$ProductionDatabaseName; `
                          User ID=$SQLServerUsername;Password=$SQLServerPassword"
                        
   # Sets up connection string for the preproduction database.
   $preproductionDatabase = "Data Source=$DatabaseServer; `
                             Initial Catalog=$PreproductionDatabaseName; `
                             User ID=$SQLServerUsername;Password=$SQLServerPassword"   
                     
   # Creates the DatabaseReleaseArtifactDirectory directory.
   $release = New-DatabaseReleaseArtifact -Source "$PackageExtractDirectory\db\state" -Target $($productionDatabase, $preproductionDatabase) -Verbose
   Export-DatabaseReleaseArtifact $release -Path $DatabaseReleaseArtifactDirectory
    
   # Imports the changes report, deployment warnings, and update script
   # as Octopus artifacts, so you can review them in Octopus.
   New-OctopusArtifact "$DatabaseReleaseArtifactDirectory\Reports\Changes.html"
   New-OctopusArtifact "$DatabaseReleaseArtifactDirectory\Update.sql"

5. In the Environments field, enter Preproduction.
6. Click Save.

5. Add the "Review database release artifact" step

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

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

3. In the Instructions field, copy and paste this text:

   Please review the schema and static data changes, warnings and SQL change script in 'Changes.html'.

4. In the Environments field, select Preproduction. 

5. Click Save.

6. Add the "Run the update script" step

This step runs the update script once you've reviewed it.

1. On the project Process tab, click Add step and select Run a PowerShell script.
2. In the Name field, enter Run the update script.
3. In the Machine roles field, enter db-server and press Enter.
   This must match the role you assigned to the Tentacle.

4. In the Script Content field, copy and paste this script:

   # This step deploys the update we previously generated and wrote to disk.
   $targetDatabase = "Data Source=$DatabaseServer; `
                      Initial Catalog=$DatabaseName; `
                      User ID=$SQLServerUsername;Password=$SQLServerPassword"
                       
   Import-DatabaseReleaseArtifact $DatabaseReleaseArtifactDirectory | Use-DatabaseReleaseArtifact -DeployTo $targetDatabase

5. In the Environments field, select Production, and Preproduction.
6. Click Save.

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

7. Create a release and deploy

Deploy to Preproduction to validate the update script before you deploy to Production:

1. Click the Create release button.
   This page lets you add a release note if necessary. 
2. Click Save. 
3. From the Deploy drop-down menu, select Preproduction.
4. Click Deploy.
   The SQL Change Automation cmdlets check Preproduction matches the state of the Production database and then create the update script. The deployment pauses at Step 4 so you can review the database update resources:
   
5. Under Artifacts, click Changes.html. This opens a report you can use to check for warnings, look at the update script, and see what'll be added, removed or modified if you go ahead with deployment.
6. Close the report.
7. Click assign to me and, in Notes, enter a comment to say you've reviewed the database deployment resources. 
8. To continue deploying to Preproduction, click Proceed. 
   If deployment to Preproduction is successful, you can deploy to Production. 
9. From the Deploy to drop-down menu, select Production.
   Once deployment is successful, the Task progress page looks like this: