Deploying DB changes using SSDT via Azure DevOps

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
    Azure DevOps - Create new Git repo
  2. Create SSDT project using a template in Visual Studio
    Add > New Project > SQL Server Database Project> Click Ok
    SSDT Project in VS
  3. Right click and select “properties”. Choose “Microsoft Azure SQL Database” from the Target platform drop down. Save and close.Microsoft Azure SQL Database
  4. Right click on project again and select “Import”, select database typeImport Database
  5. Choose database connection and connect with your local database that you want to import. Click start.Import DatabaseImport Database
  6. Push database files to repository branch…Push Database
  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.Database Schema Compare
  8. Choose source as local database and target as current project compare both and update the project and build the project.Build the SSDT Project
  9. Build the project once and then create a publish profile with settings as below.Publish Profile
    Publish Profile SettingsPublish Profile SettingsSave 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.Commit on GIT
  11. The DACPAC file will then be used in the Azure DevOps release pipeline for deployment on the Azure SQL database.DACPAC fileDACPAC settingsSQL DACPAC package

 

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