This example shows you how to automate the deployment of your database changes to a preproduction environment before releasing to a production environment, using SQL Release and Atlassian Bamboo.

Overview

Your preproduction environment should contain a database that can be used for testing deployments. Your production environment should contain the live database you want to release to.

You'll use a Bamboo deployment project to deploy a database to preproduction and then to production. The stages are:

1. Update the preproduction database so that its schema is identical to the production database.
2. Create a deployment script to update the preproduction database.
3. Apply the deployment script to the preproduction database.
4. Review the deployment resources to check the preproduction database has been updated correctly.
5. Promote the release to production by applying the same deployment script to production. Before doing this, SQL Release checks the production schema has not drifted since the script was generated.

Before you start

1. Install Bamboo

   Bamboo Cloud

   If you're using Bamboo Cloud with remote agents, you should still be able to use these instructions, with minor modifications. If you're using Bamboo Cloud with elastic agents, you'll need to install the DLM Automation Suite on each elastic agent.

2. Install the DLM Automation Suite, which also installs SQL Release, on the machine running the Bamboo agent. For more information, see Installing (DLM Automation Suite documentation).

1. Set up the SQL CI Build

In Bamboo, create a build job that builds your database to a NuGet package. This can be done using command-line tasks, or using the SQL CI Build Plugin.

The deployment project will need access to the build job's output, so you'll need to create an artifact definition for the NuGet package. To do this:

• Go to the build job.
• From the Artifact tab, click Create Definition.
   
• Enter a name, such as DbPackage, and use the copy pattern *.nupkg.

2. Create a deployment project

1. Create a deployment project, and link it to the build plan.
2. Add the preproduction environment. Name this Preproduction.
3. Add the production environment. Name this Production.

Notice that Bamboo automatically adds two tasks to the build plan: Clean working directory task and Aritfact download. Artifact download downloads the NuGet package from the SQL CI build, ready for use by the deployment.

3. Add the preproduction variables

1. Go to your deployment project.
2. In the preproduction environment, click Variables and add these variables:

If your preproduction and production databases are on different servers, add more variables and add these into the script in the next section.

4. Add the "Overwrite preproduction with production" task

The first task you'll add to your deployment project overwrites the preproduction database with production.

Preproduction should be identical to production (or as close to identical as possible) so you can:

• test exactly what'll happen when you deploy to production, without running 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, you'll use the New-DlmDatabaseRelease cmdlet to deploy the production schema to the preproduction database.

1. In your deployment project, in the preproduction environment, click Edit tasks.
2. Click Add Task, and select Script.
3. In Task Description, enter Overwrite preproduction with production.
4. Select the Run as Powershell script checkbox.

5. In the Script field, copy and paste the script below. This uses the variables that we set up in the previous step:

   $ErrorActionPreference = "Stop"
   # 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=${bamboo.DatabaseServer}; `
   Initial Catalog=${bamboo.PreproductionDatabaseName}; `
   User ID=${bamboo.SQLServerUsername};Password=${bamboo.SQLServerPassword}" 
   
   $productionDatabase = "Data Source=${bamboo.DatabaseServer}; `
   Initial Catalog=${bamboo.ProductionDatabaseName}; `
   User ID=${bamboo.SQLServerUsername};Password=${bamboo.SQLServerPassword}" 
   
   $release = New-DlmDatabaseRelease -Target $preproductionDatabase -Source $productionDatabase
   Use-DlmDatabaseRelease $release -DeployTo $preproductionDatabase -SkipPreUpdateSchemaCheck –SkipPostUpdateSchemaCheck

6. Click Save. 

5. Add the "Create database update resources" task

1. In your deployment project, in the preproduction environment, click Edit tasks.
2. Click Add Task, and select Script.
3. In the Task Description, enter Create database update resources.
4. Select the Run as Powershell script checkbox.

5. In the Script field, copy and paste the script below:

   # This step uses SQL Release to create a directory containing the
   # Database Update Resources: all the artifacts relating to the deployment. 
   
   $databaseUpdateResourcesDirectory = "d:\DatabaseUpdateResources\${bamboo.deploy.project}\${bamboo.deploy.version}\"
   
   # Makes sure the directory we're about to create doesn't already exist.
   If (Test-Path $$databaseUpdateResourcesDirectory) {
   rmdir $$databaseUpdateResourcesDirectory -Recurse -Force
   }
   
   # Sets up connection string for the production database.
   $productionDatabase = "Data Source=${bamboo.DatabaseServer}; `
   Initial Catalog=${bamboo.ProductionDatabaseName}; `
   User ID=${bamboo.SQLServerUsername};Password=${bamboo.SQLServerPassword}"
   
   # Sets up connection string for the preproduction database.
   $preproductionDatabase = "Data Source=${bamboo.DatabaseServer}; `
   Initial Catalog=${bamboo.PreProductionDatabaseName}; `
   User ID=${bamboo.SQLServerUsername};Password=${bamboo.SQLServerPassword}"
   
   # Creates the DatabaseUpdateResources directory.
   $release = New-DlmDatabaseRelease -Source WidgetDev.1.0.nupkg -Target $($productionDatabase, $preproductionDatabase) -Verbose
   Export-DlmDatabaseRelease $release -Path $databaseUpdateResourcesDirectory 

6. In line 22 on the script, replace WidgetDev.1.0.nupkg with the name of your NuGet package. You may need to use an environment variable in the name, such as WidgetDev.1.${bamboo.buildNumber}.nupkg.
7. Click Save.

6. Add the "Run the update script" task

This task runs the update script created in the previous task on the preproduction database.

1. In your deployment project, in the preproduction environment, click Edit tasks.
2. Click Add Task, and select Script.
3. In the Task Description, enter Run the update script.
4. Select the Run as Powershell script checkbox.

5. In the Script field, copy and paste the script below. It uses variables that we will set up in the next section:

   $ErrorActionPreference = "Stop" 
   $databaseUpdateResourcesDirectory = "d:\DatabaseUpdateResources\${bamboo.deploy.project}\${bamboo.deploy.version}\"
   
   # This step deploys the update we previously generated and wrote to disk.
   $targetDatabase = "Data Source=${bamboo.DatabaseServer}; `
   Initial Catalog=${bamboo.PreProductionDatabaseName}; `
   User ID=${bamboo.SQLServerUsername};Password=${bamboo.SQLServerPassword}" 
   Import-DlmDatabaseRelease $databaseUpdateResourcesDirectory | Use-DlmDatabaseRelease -DeployTo $targetDatabase 

6. Click Save.

Your deployment project should now look like this:
 

7. Add the production variables

In the production environment, click Variables and add these variables. These are the details of your production database environment:

8. Add the "Run the update script" task to the production environment

This task applies the same update script previously applied to preproduction to production.

1. In your deployment project, in the preproduction environment, click Edit tasks.
2. Click Add Task, and select Script.
3. In the Task Description, enter Run the update script.
4. Select the Run as Powershell script checkbox.

5. In the Script field, copy and paste the script below: 

   $ErrorActionPreference = "Stop" 
   $databaseUpdateResourcesDirectory = "d:\DatabaseUpdateResources\${bamboo.deploy.project}\${bamboo.deploy.version}\" 
   
   # This step deploys the update we previously generated and wrote to disk.
   $targetDatabase = "Data Source=${bamboo.DatabaseServer}; `
                      Initial Catalog=${bamboo.DatabaseName}; `
                      User ID=${bamboo.SQLServerUsername};Password=${bamboo.SQLServerPassword}"
                        
   Import-DlmDatabaseRelease $DatabaseUpdateResourcesDirectory | Use-DlmDatabaseRelease -DeployTo $targetDatabase

6. Click Save.

You've finished adding the tasks for your deployment project. You can now try running the deployment.

9. Run a deployment

1. Go to your deployment project and click Deploy > Preproduction.
2. Choose Create new release from build result, and select the Build result you want to deploy.
3. Click Start Deployment.
4. Watch the build complete. If it fails, view the deployment log. The most common causes of errors are mistyped variables.
5. Review the deployment resources in D:\DatabaseUpdateResources\[Project Name]\[Release number]. This includes:
   • The update script, Update.sql.
   • \Reports\Changes.html. This shows the changes that the script will make. Check the Warnings tab for potentially dangerous changes such as column drops.
6. To start the deployment to production, click Deploy > PROMOTE TO Production.
7. Click Start Deployment.