This tutorial takes you through each stage of setting up a database release pipeline using Azure DevOps and the SQL Change Automation Release extension.

1. Prerequisites

Make sure you have the following:

• A SQL Change Automation build in Azure DevOps that creates a NuGet package from your source-controlled project and publishes it as an artifact. See Using the Azure DevOps Build extension.
• SQL Change Automation: Release Azure DevOps plugin installed.

2. Create a release definition

An Azure DevOps release definition is similar to a build definition. It defines your environments, release processes, variables and other options.

1. In your Azure DevOps account, go to your Projects page
   In this tutorial, we'll be releasing the database, AdventureWorks. This is the same database used in the tutorial Using the Azure DevOps Build extension.
2. In the top menu, click Build and Release.
3. Click the Releases tab
4. Click New definition
   
   
5. Click Empty pipeline at the top of the Select a template page.

3. Create the target environments

An environment usually represents a stage in your development process.

For this guide, we'll use one environment: production.

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

To set up the environment:

• Click on Environment 1 and then rename it to 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 Add artifact
2. In Source type select Build
3. In Project, select your project
4. In Source (Build definition), select your build definition
5. In Default version, select Latest
6. Click Add

5. Build the deployment process

1. In the production environment, click 1 phase, 0 task.
2. On the left of the screen you should see the phases of your build. In Phase 1, click the plus icon to add a task.
3. Find SQL Change Automation in the list of tasks and click the Add button
   
4. Click the SQL Change Automation task that has been added as part of phase 1.
5. In Display name, enter Deploy database changes
6. In Operation, select Deploy database changes from a build artifact.
7. In Package path, click the ellipsis (...). Select the parent directory of your nuget package from the build artifacts.

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

   • Select the Authentication Method for the production server. 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.
9. SQLCMD variables can be specified in the Variables tab. For example, you may wish to assign the ReleaseVersion variable to $(Release.ReleaseName).

10. Click Save.

6. Run a full release

You're now ready to run a release:

1. Click Release in the top right-hand corner and select Create Release.
2. Click Create to queue the release. 

The deployment process will now continue and release to production.

7. Review the release

1. Click on Release-<number> to see a summary of the release.
2. Click on the Logs tab menu to see the log output of the SQL Change Automation release step.