Deploying DB changes using SSDT via Azure DevOps

This post explains how a developer can make Database changes with SSDT and automate the implementation of those changes on the Azure SQL database via Azure DevOps.

The main objective of this approach is to:

1. Deny developers direct access to Azure SQL database
2. Use TFS to maintain database versions as we do changes.
3. Ensure a robust deployment procedure as a single mistake introduced through manual deployment can lead to a chaotic situation.

Step by step Implementation:

1. Create a repository on Azure GIT

2. Create SSDT project using a template in Visual Studio
Add > New Project > SQL Server Database Project> Click Ok

3. Right click and select “properties”. Choose “Microsoft Azure SQL Database” from the Target platform drop down. Save and close.

4. Right click on project again and select “Import”, select database type

5. Choose database connection and connect with your local database that you want to import. Click start.

6. Push database files to repository branch…

7. If there is any change made in local database by developer go to respective SSDT project right click on project in solution select schema compare option.

8. Choose source as local database and target as current project compare both and update the project and build the project.

9. Build the project once and then create a publish profile with settings as below.

Save the profile and build the project again.

10. Then commit and push the changes on git repository. As the Azure DevOps build pipeline initiates, it will build the database solution and create a DACPAC as an artifact.

11. The DACPAC file will then be used in the Azure DevOps release pipeline for deployment on the Azure SQL database.

Challenge Faced:

Once while deploying the Database changes via Azure DevOps, the pipeline failed with the error “index already exists”. This was because the indexes had been already added to the database manually.

To solve this, drop statements can be defined in a pre-deployment script to drop the already created indexes and then Dacpac will create them again without giving error.

Sample drop statement for dropping index in a pre-deployment script:

DROP INDEX IF EXISTS [nci_wi_AspNetUsers_FAD9A4DCB2C2226FB45CF38DA1713AD0] ON [dbo].[AspNetUsers]

Important Notes:

1. An important fact to take note of is the pre and post-deployment scripts added to the Azure DevOps pipeline will run every time in the context of the database deployed to, regardless of environment. Therefore, they must be re-runnable and idempotent, as they are executed as part of every deployment. For example, any object creation statements must be executed inside object existence checks.
2. Dacpac implementation only handles the schema level changes.
3. Create post/pre deployment scripts if you wish to insert, update data on the Azure SQL database, but know that scripts will run every time.
4. Avoid making changes manually to the server database.

This brings us to the end of this post on SSDT and Azure DevOps. But that’s not all from me. Another post to follow soon will be on “Webjob creation via Azure DevOps”.

Originally published at https://www.synsoftglobal.com/blog/deploying-db-changes-using-ssdt-via-azure-devops