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.

Matching moments

Organizing a developer conference for 15,000 attendees
01:32 MIN

Organizing a developer conference for 15,000 attendees

Cat Herding with Lions and Tigers - Christian Heilmann

Scripting presentations and demos in VS Code
14:14 MIN

Scripting presentations and demos in VS Code

Devs vs. Marketers, COBOL and Copilot, Make Live Coding Easy and more - The Best of LIVE 2025 - Part 3

Improving the developer feedback loop with specialized tools
03:16 MIN

Improving the developer feedback loop with specialized tools

Developer Time Is Valuable - Use the Right Tools - Kilian Valkhof

How to build structure and culture without killing agility
05:12 MIN

How to build structure and culture without killing agility

From Data Keeper to Culture Shaper: The Evolution of HR Across Growth Stages

Using AI agents to modernize legacy COBOL systems
06:28 MIN

Using AI agents to modernize legacy COBOL systems

Devs vs. Marketers, COBOL and Copilot, Make Live Coding Easy and more - The Best of LIVE 2025 - Part 3

How AI is changing the freelance developer experience
09:10 MIN

How AI is changing the freelance developer experience

WeAreDevelopers LIVE – AI, Freelancing, Keeping Up with Tech and More

The value of progressive enhancement and semantic HTML
03:31 MIN

The value of progressive enhancement and semantic HTML

WeAreDevelopers LIVE – You Don’t Need JavaScript, Modern CSS and More

Selecting strategic partners and essential event tools
03:17 MIN

Selecting strategic partners and essential event tools

Cat Herding with Lions and Tigers - Christian Heilmann

Featured Partners

Related Videos

See all videos
Database DevOps with Containers39:15

Database DevOps with Containers

Rob Richardson

Enabling automated 1-click customer deployments with built-in quality and security44:40

Enabling automated 1-click customer deployments with built-in quality and security

Christoph Ruggenthaler

Developer Tools for Microsoft Azure25:41

Developer Tools for Microsoft Azure

Radu Vunvulea

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

Forget Developer Platforms, Think Developer Productivity!57:45

Forget Developer Platforms, Think Developer Productivity!

Robert Hoffmann & Christian Denich

Say goodbye to building boring APIs with Azure Data API Builder30:01

Say goodbye to building boring APIs with Azure Data API Builder

Sander ten Brinke

WeAreDevelopers LIVE - the weekly developer show with Chris Heilmann and Daniel Cranney59:27

WeAreDevelopers LIVE - the weekly developer show with Chris Heilmann and Daniel Cranney

Chris Heilmann, Daniel Cranney & Julia Kordick

Related Articles

View all articles
AG
Andre Braun, GitLab
Now is the time for industrialized software development
Now is the time for industrialized software development Recently, I received a letter from my car’s manufacturer alerting me to a recall. They had discovered a defective part and wanted to replace it. It was easily fixed, and I might have forgotten a...
Now is the time for industrialized software development

From learning to earning

Jobs that call for the skills explored in this talk.