The core problem was automating deployments across nearly 400 highly customized SQL Server databases on different environments and servers.
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.
The pipeline uses MSBuild for building artifacts and WinRM for deployment, with libraries and task groups for reusable, configurable steps.
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.
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.
You can extend the deployment process by writing custom C# code using additional deployment contributors to modify SQLPackage behavior, such as ignoring specific objects.
The Q&A covers choosing cloud providers, scenarios where dacpacs are not recommended, the use of PowerShell scripts, and handling relational constraints during deployments.
39:15Rob Richardson
26:53Matthias Niehoff
43:26Markus Möller
48:05Johannes Nicolai & Lilli Seyther-Besecke
56:19Pawel Piwosz
51:13Thomas Fuchs, Waleed Arshad & Frank Dornberger & Idir Ouhab Meskine:
34:00Álvaro Martín Lozano
46:06Dominik Kress
.gif?w=240&auto=compress,format)
Jobs that call for the skills explored in this talk.
Rocken AG