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

An approval step allows you to pause a release and perform a manual review of the database changes before the actual release takes place. This is a recommended practice before releasing changes to production environments.

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.
• A local Azure DevOps agent configured. See Deploy an agent on Windows.

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 process
6. Click Save

3. Create the target environment

An environment usually represents a stage in your development process.

For this guide, we'll create 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 create the environment:

1. Click on Environment 1 and then rename it to Production
2. In the Environments area, click Add and then New environment
3. Click Empty process.
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 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

5.1. Add the "Create Production Release" task

1. In the production environment, click 1 phase, 0 task.
2. Click the plus icon on the Agent phase
3. Find SQL Change Automation in the list of tasks and click the Add button
   
4. In Display name, enter Create Production Release
5. In Operation, click Create a database release artifact.
6. In Database package, click the ellipsis (...). Select the parent directory of your NuGet package from the build artifacts.
7. In Export path, type the path to a windows share accessible from your local agents. Please make a note of this location as it is required in step 5.3.

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. Click Save.

5.2. Add an approval task

An approval task allows you to pause a release within an environment, typically to perform a manual review of the database changes before the actual release takes place. 

1. In Deployment process, click the ellipsis (...) and then click Add agentless phase.
   
   
   
   
2. Add a Manual Intervention task. See Manual intervention documentation

5.3. Add the "Release to Production" task

1. Add an agent phase to your deployment process.
2. Click the plus icon on the Agent phase
3. Find SQL Change Automation in the list of tasks and click the Add button
4. In Operation, select Deploy database changes from a release artifact.
5. In Deployment Resources Path, enter the same path you entered for the Deployment Resources Path in the previous task.
6. 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.
   • Choose the Authentication Method for the production server.
7. Click Save.

6. Run a full release

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

1. Click Release and select Create Release.
2. Select the build version you want to release.
3. Click Create.
   You'll be prompted for approval when the process has created the release artifact. You may receive an email, or see a banner in Azure DevOps reading A manual intervention is pending for 'Production' environment. Resume or Reject. 
4. Review the Change report at the Deployment Resources Path you specified before.
   This shows:
   1. A report of the of changes that will be made (Reports/Changes.html)
   2. A report of any drift detected within the target database (Reports/Drift.html)
   3. The targeted deployment script (TargetedDeploymentScript.sql)
   4. The drift revert script (DriftRevertScript.sql)
5. Click Resume or Reject. 
6. Enter a suitable comment, then click Resume.
    

The deployment process will now continue and release to production.