Automated MS SQL Server database deployments with dacpacs and Azure DevOps

Manually scripting updates for 400 databases was impossible. So we started treating our schema as code using dacpacs and Azure DevOps.

#1about 2 minutes

The challenge of managing hundreds of distributed SQL databases

The core problem was automating deployments across nearly 400 highly customized SQL Server databases on different environments and servers.

#2about 3 minutes

Solution design using dacpacs and Azure DevOps pipelines

The solution involves extracting a database schema into a dacpac, managing it as code in a Visual Studio project, and deploying it via Azure DevOps.

#3about 3 minutes

Implementing the deployment pipeline in Azure DevOps

The pipeline uses MSBuild for building artifacts and WinRM for deployment, with libraries and task groups for reusable, configurable steps.

#4about 12 minutes

How dacpac deployments work with SQLPackage.exe

A dacpac deployment uses SQLPackage.exe to compare the dacpac model with the target database, guided by a publish.xml profile, to generate and apply an update script.

#5about 7 minutes

Handling limitations like multi-environment support

The lack of built-in multi-environment support for objects like users and logins is managed using pre- and post-deployment scripts with SQLCMD variables.

#6about 2 minutes

Advanced customization with deployment contributors

You can extend the deployment process by writing custom C# code using additional deployment contributors to modify SQLPackage behavior, such as ignoring specific objects.

#7about 8 minutes

Q&A on cloud services, dacpac use cases, and PowerShell

The Q&A covers choosing cloud providers, scenarios where dacpacs are not recommended, the use of PowerShell scripts, and handling relational constraints during deployments.