Before you start

1. Create a NuGet package containing a SQL Change Automation project.
   If you use SQL Change Automation for continuous integration, your build server can automatically build a package on every commit. See an example of how to do this using TeamCity.
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 an environment called Production, and add the machine running the Tentacle agent to it.
6. 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.
   2. configure your build server to push packages to the Octopus built-in repository. For more details, see Using the built-in repository.

1. Create a new Octopus Deploy project

1. In Octopus Deploy, click Projects and All.
2. Click Add project.
3. In the Name field, enter AdventureWorks Deployment.
4. Click Save.

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

In the AdventureWorks Deployment project, on the project Variables tab, add the following variables.
Variables surrounded by <angle brackets> need to be replaced with a real value:

If you leave the SQLServerUsername and SQLServerPassword values blank, Windows authentication will be used to connect to the target database.

Click Save.

You'll now add a series of deployment process steps to your Octopus Deploy project. 

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 AdventureWorks 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 AdventureWorks.0.1.nupkg, you'd only enter AdventureWorks.
   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 Production.
   If you leave this blank, the step will be accessible to all environments.
7. In Configure Features, enable the Custom Deployment Scripts feature and click Save.

8. In Custom deployment scripts, select the Deployment Script field and paste in the following script. This script uses the project variables that you set up earlier:

   Create 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
   }
   $packageFilePath = $OctopusParameters["Octopus.Tentacle.CurrentDeployment.PackageFilePath"]
   # Sets up connection string for the target database.
   $targetDatabase = "Data Source=$DatabaseServer; `
                      Initial Catalog=$DatabaseName; `
                      User ID=$SQLServerUsername; `
                      Password=$SQLServerPassword"
   # Creates the DatabaseReleaseArtifactDirectory directory.
   New-DatabaseReleaseArtifact -Target $targetDatabase `
                               -Source $packageFilePath  `
                               -Verbose `
     | Export-DatabaseReleaseArtifact -Path $DatabaseReleaseArtifactDirectory
   # Exposes 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\Reports\Drift.html"
   New-OctopusArtifact "$DatabaseReleaseArtifactDirectory\TargetedDeploymentScript.sql"
   New-OctopusArtifact "$DatabaseReleaseArtifactDirectory\DriftRevertScript.sql"
   
   

9. Click Save.

4.  Add the "Review database release artifacts" 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 Production.
   If you leave this blank, the step will be accessible to all environments. 

5. Click Save.

5. Add the "Run the update script" step

This is the step that deploys the update to your target database.

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

4. In the Script field, paste in the following 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=$DatabaseServer; `
                      Initial Catalog=$DatabaseName; `
                      User ID=$SQLServerUsername;Password=$SQLServerPassword"
                       
   Import-DatabaseReleaseArtifact $DatabaseReleaseArtifactDirectory | Use-DatabaseReleaseArtifact -DeployTo $targetDatabase

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

6. Click Save. 

6. Create a release 

Now all the steps are set up, you can create a release: 

1. In the AdventureWorks Deployment project, on the Process tab, click Create release.
   This page lets you add an optional release note. 
2. Click Save. 
3. Click Deploy to Production (or if there's more than one environment, click Deploy and select Production).
4. Click Deploy Now.
   As the deployment process runs, Octopus Deploy shows the task progress list. The deployment pauses so you can review the database deployment resources. 
5. 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. 
6. In Octopus Deploy, click assign to me and, in Notes, enter a comment to say you've reviewed the database deployment resources.
7. If you're happy with the report, click Proceed.
   When the deployment is complete, the Task progress page looks like this:
   

You've now completed the deployment of the database package.

What next?

If you have multiple database environments, such as a Preproduction and Production, you can use the SQL Change Automation cmdlets and Octopus Deploy to automate the deployment changes to your Preproduction environment before releasing to Production. See Deploy to multiple environments using Octopus Deploy and PowerShell.