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

Automating deployments and leveraging managed services
03:39 MIN

Automating deployments and leveraging managed services

Cloud-nativeApplications- What’s the buzz about

Understanding the unique challenges of database DevOps
04:09 MIN

Understanding the unique challenges of database DevOps

Database DevOps with Containers

A DBA's journey to running SQL Server on Kubernetes
03:02 MIN

A DBA's journey to running SQL Server on Kubernetes

Adjusting Pod Eviction Timings in Kubernetes

Using the Modern Data Stack and DBT for transformations
05:00 MIN

Using the Modern Data Stack and DBT for transformations

Modern Data Architectures need Software Engineering

Applying software engineering practices to data pipelines
03:47 MIN

Applying software engineering practices to data pipelines

Enjoying SQL data pipelines with dbt

Key takeaways for building event-driven systems on Azure
04:04 MIN

Key takeaways for building event-driven systems on Azure

Implementing an Event Sourcing strategy on Azure

Applying a Git-like workflow to database schema changes
04:11 MIN

Applying a Git-like workflow to database schema changes

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

Merging data engineering and DevOps for scalability
01:55 MIN

Merging data engineering and DevOps for scalability

Software Engineering Social Connection: Yubo’s lean approach to scaling an 80M-user infrastructure

Featured Partners

Related Videos

See all videos
Database DevOps with Containers
39:15

Database DevOps with Containers

Rob Richardson

Enjoying SQL data pipelines with dbt
26:53

Enjoying SQL data pipelines with dbt

Matthias Niehoff

Develop enterprise-ready applications for Microsoft Teams with Azure resources on modern web technologies
43: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 hand
48: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'ts
51:13

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

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

Implementing continuous delivery in a data processing pipeline
34:00

Implementing continuous delivery in a data processing pipeline

Álvaro Martín Lozano

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

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

Christoph Ruggenthaler

Related Articles

View all articles
Christina Schaireiter
Why Attend a Developer Event?
Modern software engineering moves too fast for documentation alone. Attending a world-class event is about shifting from tactical execution to strategic leadership. Skill Diversification: Break out of your specific tech stack to see how the industry...
Why Attend a Developer Event?

From learning to earning

Jobs that call for the skills explored in this talk.