This example shows you how to deploy from a development database to a target database using the SQL Change Automation PowerShell cmdlets and Octopus Deploy. It uses SQL Server authentication.

1. Install Octopus Deploy and create a new environment

Install Octopus Deploy and in the Octopus web portal (typically accessed at localhost/) add a new environment called Production. 

An environment is a group of machines that you can deploy to at the same time. For more information, see Environments. 

In this example, we're only deploying to one machine, but we still need an environment before we can set up a target machine. 

2. Install an Octopus Tentacle

A Tentacle is an agent listening service that is required on all machines you deploy to.

1. Install a Tentacle on the same machine as the Octopus Server. This is also the machine where you will install SQL Change Automation.
   See Installing Tentacles 
2. In the Production environment, click Add deployment target new deployment target, set the Hostname as localhost and click Next. 
   
3. The Tentacle is on the same machine as the Octopus Server, so it doesn't need any further configuration to establish a connection.
4. Add a deployment target with the Display name of Prod01 and in the Roles box, type db-server and press enter:
   
   Roles are used by Octopus to define which machines to deploy to. For database deployments, the role is used to specify the machine the SQL Change Automation cmdlets are running. You'll reference the db-server role when you add a step to create the database deployment resources.
5. Click Save.

3. Install SQL Change Automation

You need to install SQL Change Automation on the machine running the Octopus Tentacle. For more information, see Installing.

4. Create a new Octopus Deploy project

Create a new Octopus project, accept the default setting and give it a name. See Projects (Octopus Deploy documentation).

The project consists of a series of steps which manage the process of deploying the schema:  

• step 1: the SQL Change Automation cmdlets compare the source and target database schemas to create the Database Update Resources. The Database Update Resources includes the SQL update script that'll be used to update the target database.
• step 2: Octopus pauses the deployment while the user reviews the update script and approves the changes. 
• step 3: the SQL Change Automation cmdlets runs the update script against the target database to deploy the required changes.

5. Set up the project variables

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).

 On the project's Variables tab, copy and paste the following variables. Variable values surrounded by <angle brackets> need to be replaced with values specific to your environment: 

If you need to specify a different server or different credentials for another target database, add duplicate variables and set the scope accordingly. 

6. Add the "Make database update resources" step

This step creates the deployment SQL script, and the other deployment resources.

1. On the project Process tab, select Add step and select Run a PowerShell script.
2. Enter these details:
   
   1. Step name: Make database update resources
   2. Machine roles: type db-server and press Enter
      This is the role you created when you installed an Octopus Tentacle. 

   3. Script: 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 target database.
      $developmentDatabase = "Data Source=$DevelopmentDatabaseServer; `
                              Initial Catalog=$DevelopmentDatabaseName; `
                              User ID=$DevelopmentSQLServerUsername;Password=$DevelopmentSQLServerPassword"
      $productionDatabase =  "Data Source=$ProductionDatabaseServer; `
                              Initial Catalog=$ProductionDatabaseName; `
                              User ID=$ProductionSQLServerUsername;Password=$ProductionSQLServerPassword"
      # Creates the DatabaseReleaseArtifactDirectory directory.
      New-DatabaseReleaseArtifact -Target $productionDatabase `
                                  -Source $developmentDatabase `
                                  -Verbose `
        | Export-DatabaseReleaseArtifact -Path $DatabaseReleaseArtifactDirectory
      # Creates 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"

      Click Save. 

7. Add the "Review database release artifact" step

This step tells Octopus to pause the deployment for you to review the Database Release artifact created in the previous step (the SQL update script and other resources). You can look at an HTML report to see what'll be added, removed or modified if you go ahead with deployment, and check for warnings. If you're not happy with the changes, you can stop the deployment at this point. 

1. On the project Process tab, click Add step and select Manual intervention required.
2. Enter these details in the fields:
   
   1. Step name: Review database update resources

   2. Instructions: Copy and paste this script:

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

      In the other fields, leave the default settings unchanged. 

   3. Click Save.

8. Add the "Run the update script" step

Run the script you reviewed in the previous step:

1. On the project Process tab, click Add step and select Run a PowerShell script. This is the deployment step.
   Enter these details in the fields:
   
   1. Step name: Run the update script
   2. Machine roles: type db-server and press Enter
      This is the role you created when you installed an Octopus Tentacle. 

   3. Script: Copy and paste this script:

      Run the update script

      # This step uses SQL Change Automation cmdlets to deploy the database update we previously generated and wrote to disk.
      $targetDatabase = "Data Source=$ProductionDatabaseServer; `
                         Initial Catalog=$ProductionDatabaseName; `
                         User ID=$ProductionSQLServerUsername;Password=$ProductionSQLServerPassword"
                          
      Import-DatabaseReleaseArtifact $DatabaseReleaseArtifactDirectory| Use-DatabaseReleaseArtifact -DeployTo $targetDatabase

      In the other fields, leave the default settings unchanged.

   4. Click Save.

What next?

You're ready to deploy a database update in Octopus. Deploying a database update is now the same process as deploying a normal update in Octopus Deploy.

If the deployment fails, click the Task log tab to check the details.

For instructions, see Create a release and deploy it.