This tutorial takes you through each stage of setting up a database release pipeline using Visual Studio Team Services (VSTS) and the VSTS SQL Release extension.

Before you start, you must have already set up a SQL CI build in VSTS that creates a NuGet package from your source-controlled database and publishes it as an artifact. See Using the VSTS SQL CI extension.

1. Install the SQL Release extension

1. In VSTS, in the top-right, click the "shopping-bag" icon.

2. Go to Visual Studio Team Services extensions.

3. In the Build and Release area, click see more.

4. Click the Redgate SQL Release item.

5. Click Install.

6. Choose the account you want to install to, and click Confirm.

7. Click Close.

2. Create a release definition

A VSTS release definition is similar to a build definition. It defines your environments, release processes, variables and other options.

1. In VSTS, open your project.
   In this tutorial, we'll be releasing the database, WidgetShop. This is the same database used in the tutorial Using the VSTS SQL CI extension.
2. In the top menu, click RELEASE.
3. Click the  icon to create a new build definition.
4. Click Empty. This creates an empty definition. Click OK.
5. In the yellow bar, enter a name for your build definition. Click Save.

3. Create the preproduction and production environments

An environment usually represents a stage in your development process. In VSTS, for each environment, you need to set up approval steps, variables, and a set of tasks that define the release process to that environment.

For the WidgetShop database, we'll create two environments: preproduction and production. The production environment is your live hardware and software. The preproduction (or "staging") environment is used for testing and should closely match the production environment.

Although it's not covered in this tutorial, releasing to these environments is also likely to involve releasing a corresponding application or website.

To create the two environments:

1. Click Default Environment, and rename this Preproduction.
2. Click Add environments.
3. Click Empty to start with an empty definition. Click OK.
4. Name this environment Production.

4. Link to a build definition

A release definition is often linked to a build definition. The build definition is used to release the artifacts published by a particular build. In this case, we want to release the NuGet package that was published by our WidgetShop build definition.

1. Click Link to a build definition.
2. In Type, select Build.
3. In Project, select WidgetShop.
4. In Source, select WidgetShop.
5. Click Link.

5. Build the deployment process

There are many ways to set up a deployment process, depending on your requirements.
In this tutorial, we'll use the following method:

1. Overwrite the preproduction schema with the production schema
2. Create deployment resources
   These include the update script that will be applied to production in the final step.
3. Deploy to preproduction
   This uses the same update script that was created in the previous step.
4. Review and approve
   This allows you to examine the deployment resources and the preproduction database before proceeding.
5. Deploy to production 

To set up this process, we'll add three tasks to the preproduction environment and one task to the production environment.

5.1. Add the "Overwrite preproduction with production" task

1. In the Preproduction environment, click Add Tasks.
2. In the Deploy section, find Redgate SQL Release in the list of tasks and click the Add button to the right of this.
3. Click Close.
4. Click the  icon and rename the task Overwrite preproduction with production.
5. In Operation, click Deploy a database to a database.
6. In Source SQL Server instance, enter the name of your production server.
7. In Source database name, enter the name of your production database.

8. Select the Authentication Method of the production server.

9. If you select SQL Server Authentication, you also need to give your SQL Server username and password.

   Use a variable to hide your password

   1. In Password, enter $(MyPassword).
   2. If you want to store the password globally, click Configuration.
   3. If you want to store the password by environment, click the ellipsis on the relevant environment and then click Configure variables.
   4. Click Add variable.
   5. In Name, enter MyPassword.
   6. In Value, enter your password.
   7. Click theicon to hide the password.
   8. Click OK.
10. In the Target database section, enter the following details:
    • In Target SQL Server instance, enter the name of your preproduction server.
    • In Target database name, enter the name of your preproduction database.

    • Select the Authentication Method for the production server.

11. Click Save.

5.2. Add the "Create database release" task

1. In the Preproduction environment, click Add Tasks.
2. In the Deploy section, find Redgate SQL Release in the list of tasks and click the Add button to the right of this.
3. Click Close.
4. Click the  icon and rename the task Create database release.
5. In Operation, select Create a database release.
6. In Database package, select the NuGet package you want to deploy. For example:
   $(System.DefaultWorkingDirectory)\WidgetShop\WidgetShop\WidgetShop.2.2.205.nupkg
7. Because the name of this package will change for each build you deploy, you need to replace the build number, which is 205 in the example above, with $(BUILD_BUILDNUMBER).
8. In Export Path, enter the path to where you want to put the deployment resources. It's a good idea to include the release name. For example:
   D:\DeploymentResources\$(RELEASE_DEFINITIONNAME)\$(RELEASE_RELEASENAME)
9. In the Target database section, enter the following details:
   • In Target SQL Server instance, enter the name of your production server.
   • In Target database name, enter the name of your production database.
   • Choose the Authentication Method for the production server.
10. Click Save.

5.3. Add the "Deploy to Preproduction" task

1. In the Preproduction environment, click Add Tasks.
2. In the list of tasks, in the Deploy section, find Redgate SQL Release and click Add next to this.
3. Click Close.
4. Click the  icon and rename the task Deploy to Preproduction.
5. In Operation, select Deploy a database release from deployment resources.
6. In Deployment Resources Path, enter the same path you entered for the Export Path in the previous task. For example:
   D:\DeploymentResources\$(RELEASE_DEFINITIONNAME)\$(RELEASE_RELEASENAME)
7. In the Target database section, enter the following details:
   • In Target SQL Server instance, enter the name of your preproduction server.
   • In Target database name, enter the name of your preproduction database name.
   • Choose the Authentication Method for the production server.
8. Click Save.

5.4. Add the "Deploy to Production" task

1. In the Production environment, click Add Tasks.
2. In the Deploy section, find Redgate SQL Release in the list of tasks and click the Add button to the right of this.
   
3. Click Close.
4. Click the  icon and rename the task Deploy to Production.
5. In Operation, select Deploy a database release from deployment resources.
6. In Deployment Resources Path, enter the same path you entered for the Deployment Resources Path in the previous task. For example:
   D:\DeploymentResources\$(RELEASE_DEFINITIONNAME)\$(RELEASE_RELEASENAME)
7. In Target database section, enter the following details:
   • In Target SQL Server instance, enter the name of your production server.
   • In Target database name, enter the name of your production database name. For our examples, we've used WidgetProd.
   • Choose the Authentication Method for the production server.
8. Click Save.

6. Select an agent

In VSTS, you can either use a hosted agent or an on-site agent. Hosted agents are suitable for deploying to Azure. However, they can't deploy to an on-site SQL Server, unless it's accessible to the internet. Hosted agents also make the deployment resources directory inaccessible. Because of this, we recommend you use an on-site agent. Click here for more information about installing and setting up an on-site VSTS agent. 

To set the agent used by a release definition:

1. Go to your release definition.
2. Click the ellipsis next to an environment. For this release, you'll need to do this for both the preproduction and production environments.
3. Click Agent options.
4. In Default queue, select the queue associated with your agent.
5. Click OK.

7. Run a release without a review step

You're now ready to try out the release process.

1. Click Release and select Create Release.
2. Select the build version you want to release.
3. In the Environments section, select the Production environment. This means the release process will deploy to both preproduction and production.
   
4. Click Create.

The release will begin. To watch it progress:

1. click ReleaseRelease-XXXhas been queued.
2. click Logs.

The release process will normally take about 30 seconds. However, it may take longer the first time you run it because the agent needs to download the SQL Release extension.

If the completion is successful, all the steps will be ticked, as in the example below.

If you see an error, the first diagnostic step is to review the logs. Common causes of error are an incorrect password or a non-existent directory.

8. Add an "Approval" step  

At the moment, the release process automatically deploys to production after deploying to preproduction. We want to add an "Approval" step that pauses the process after deploying to preproduction so you can review the Change report in the deployment resources and check the preproduction database.

1. Go to the WidgetShop release definition.
2. Click Edit.
3. Click the ellipsis next to the Preproduction environment.
4. Click Assign Approvers.
5. In Post-deployment approver, deselect Automated and enter the VSTS user or group who should approve the release.
6. Click OK.
7. Click Save.

9. Run a full release

You're now ready to run another release, and check the approval step works:

1. Click Release and select Create Release.
2. Select the build version you want to release.
3. In the Environments section, select the Production environment.
4. Click Create.
   You'll be prompted for approval when the process has released to preproduction. You may receive an email, or see a banner in VSTS reading A post-deployment approval is pending for 'Preproduction' environment. Approve or Reject. 
5. Review the Change report at:
   D:\DeploymentResources\$(RELEASE_DEFINITIONNAME)\$(RELEASE_RELEASENAME) \Reports\Changes.html.
   This shows:
   1. A list of changes that will be made
   2. A list of warnings
   3. The update script (also available in the parent folder, in Update.sql)
6. You can also review the preproduction database before deciding whether to continue deploying to production.
7. Click Approve or Reject. 
8. Enter a suitable comment, then click Approve.
    

The deployment process will now continue and release to production.