Updating SQL Azure database using Visual Studio database project and Azure DevOps

Visual Studio database projects have been one of my important tools since Visual Studio 2010. Database projects were not easy to use with build servers ten years ago. Today things are different. It’s super easy to use database projects to update staging and live databases from Azure build and release pipelines. This blog post shows how to do it.

Azure pipelines with database update

Suppose we have solution with SQL Server database project and we want to use schema compare feature to update production database when new version of application is deployed. We don’t want to update database directly from Visual Studio but using Azure DevOps release pipelines. Here’s the pipeline I’m using in one of my projects. Blue blocks are steps in build pipeline and orange blocks are steps in release pipeline of Azure DevOps.

Azure DevOps build and release pipeline with database tasks

I don’t cover all steps of building Azure DevOps build and release pipelines as these topics are already covered thousands of times by different pages in internet.

Comparing schemas

SQL Server database projects make database schema versioned. We have history of database structure changes in Github or some other source code repository. It’s easy to take a look at history and see what changes and when were made to database. But there’s more.

If we change development database we can use schema compare feature of Visual Studio to compare changes between database and schema project.

Comparing dev database schema to database project

Same way we can compare schema project to production database and if needed we can update database with one button click. That’s nice but it’s manual work and doesn’t fit well into automated release topic.

How to build database project on Azure DevOps

We can build database projects on Azure DevOps. As a result we get DACPAC file with database structure. If everything is build using MSBuild then things should be easy. But I had a problematic situation – I can’t build database project together with other projects as all other projects use .NET Core 3.0 and .NET Standard 2.1. .NET Core tooling today doesn’t support build database projects. It doesn’t mean we cannot build database projects at all when .NET Core projects are involved.

On Azure DevOps I solved the problem by adding MSBuild task to build pipeline. .NET Core tooling skips database project and MSBuild task skips .NET Core projects. So, both types of projects are together and at same time separated.

I configured it to build only schema project.

Azure DevOps MSBuild task to build database project

Next thing I had to do was to copy resulting DACPAC file to build artifacts drop folder. I used File Copy task for this.

Azure DevOps copy files task

Although file location in drop folder is far from perfect, the DACPAC file is there and available to release pipeline.

Deploying changes to Azure SQL

I have release pipeline that is run manually. It updates database and deploys new versions of web applications to Azure App Service. Updating database is first step in the release pipeline. There’s special pipeline step by Microsoft to update Azure SQL database. Just add it to pipeline, fill configuration parameters and you are good to go.

AzureDev Ops database deployment task

Database update is done based on DACPAC file built in previous section. All we have to do is to organize DACPAC file to Azure SQL Database deployment step and magic happens automatically.

Wrapping up

Visual Studio database projects are one of my main tools since Visual Studio 2010. Over times tooling has changed and improved. Today we can use database projects also in Azure DevOps build and release pipelines to automatically update Azure SQL databases. Although most of new web applications are built on ASP.NET Core, I was still able to use MSBuild on Azure DevOps to make database project build independent from web applications. Deploying update to live database is matter of just one database deployment task that is easy to set up and configure.

References

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    5 thoughts on “Updating SQL Azure database using Visual Studio database project and Azure DevOps

    • Pingback:Dew Drop – March 10, 2020 (#3150) | Morning Dew

    • March 25, 2020 at 6:18 am
      Permalink

      This technology is called Sql Server Development Tools or SSDT. It works really well for on premise SQL and most of the time for Azure SQL DB. The only times it has a problem are when there are External Data Sources .

      Also you will want to create a SQL publish profile and make sure its included as an artifact so it can be used in release. This lets you avoid dropping things like indexes that you may have had added automatically by the Azure SQL performance auto tuning.

    • April 8, 2020 at 12:30 am
      Permalink

      I am trying through msbuild but not able to connect on Azure SQL with admin password as well. It’s working fine from VS. but when I do with MSBUild its giving error of deploy Deploy72004: login failed for admin.

    • April 8, 2020 at 3:54 am
      Permalink

      Try using sqlpackage.exe to deploy the dacpac produced by msbuild as a separate step. It can be difficult to pass some arguments into or through msbuild sometimes.

    • Pingback:Top Stories from the Microsoft DevOps Community – 2020.03.20 - Microsoft Today

    Leave a Reply

    Your email address will not be published. Required fields are marked *