Sebastian Wolff

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.

Automated MS SQL Server database deployments with 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.

Related jobs
Jobs that call for the skills explored in this talk.

Featured Partners

Related Videos

See all videos
Database DevOps with Containers39:15

Database DevOps with Containers

Rob Richardson

Enjoying SQL data pipelines with dbt26:53

Enjoying SQL data pipelines with dbt

Matthias Niehoff

Develop enterprise-ready applications for Microsoft Teams with Azure resources on modern web technologies43:26

Develop enterprise-ready applications for Microsoft Teams with Azure resources on modern web technologies

Markus Möller

Branch your database like your code: How schema changes and pull requests go hand in hand48:05

Branch your database like your code: How schema changes and pull requests go hand in hand

Johannes Nicolai & Lilli Seyther-Besecke

Plan CI/CD on the Enterprise level!56:19

Plan CI/CD on the Enterprise level!

Pawel Piwosz

Demystifying DevOps—Pros, cons, dos & don'ts51:13

Demystifying DevOps—Pros, cons, dos & don'ts

Thomas Fuchs, Waleed Arshad & Frank Dornberger & Idir Ouhab Meskine:

Implementing continuous delivery in a data processing pipeline34:00

Implementing continuous delivery in a data processing pipeline

Álvaro Martín Lozano

CD2CF - Continuous Deployment to Cloud Foundry46:06

CD2CF - Continuous Deployment to Cloud Foundry

Dominik Kress

From learning to earning

Jobs that call for the skills explored in this talk.

DevOps .NET


Municipality of Valencia, Spain

.NET
DevOps
Agile Methodologies