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.
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.
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.
Next thing I had to do was to copy resulting DACPAC file to build artifacts drop folder. I used File Copy task for this.
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.
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
- Visual Studio database projects (Gunnar Peipman)
- Using Visual Studio database projects in real life (Gunnar Peipman)
View Comments (3)
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.
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.
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.