IAC for your SQL Server databases
18 Nov 2026
Should you use EFCore Migration Bundles or SQLPackage to remediate drift with your SQL Server database configuration? Maybe both!
Web application architecture tiers
First, let’s talk about the idea of multi-tier web application architectures.
It’s very common for product teams to serve web applications to visitors browsers’ using 3 separate tiers of infrastructure – one server hosting a database and responding over ODBC/JDBC/etc., one server hosting “backend” APIs and returning JSON to machines over HTTPS, and one server/CDN serving “frontend” UI code and returning HTML/CSS/JavaScript/etc. to visitors’ browsers over HTTPS.
Using a 3-tier design, instead of 2-tier design with a “full-stack” web monolith (or a 1-tier design like the 2000s-era Wordpress “LAMP stack”), where each tier can be version-numbered, deployed, released, and hosted separately from the other, helps keep the application codebases maintainable and performant – even for applications where the 3 tiers are tightly coupled to each other and likely will not need reuse elsewhere.
Often times, each codebase deployed to run on each of these 3 servers lives in its own source code version control repository (e.g. Git repository), even for applications where the 3 tiers are tightly coupled to each other and likely will not need reuse elsewhere. People often simply name the repositories so that they alphabetize near each other – e.g. HelloWorld-database, HelloWorld-web-api, and HelloWorld-web-ui.
Managing web server configuration
For the web server tiers that respond to HTTPS requests, deploying the code that they need to run is usually pretty straightforward – typically, under the hood, your CI/CD process is replace a whole folder’s worth of files on the web server and is perhaps running a command to restart an operating system process on that web server to make sure it looks for the fresh files.
Managing database management system configuration
But it’s not quite that simple when the operating system process is a whole relational database management system (“RDBMS”). You don’t just dump files & restart that.
Interactive reconfiguration
To reconfigure how it’s set up (that is, the table schemas, the privilege grants, etc.), more typically a database administrator (“DBA”) logs into the running process and run a bunch of commands written in DDL, DML, SQL, etc.
ORM reconfiguration
In the case of object-relational mapping (“ORM”), there’s some code (often stored in the same codebase as the backend web API tier’s code, if the database is tightly coupled to the API tier’s needs, because the point of it is to keep database schemas tightly coupled to the web server’s in-memory “object” data structures), that it’ll be someone’s job to set up web-backend-deploy CI/CD to run against the database server.
- (Note: Under the hood, the running ORM job is just logging into the running relational database management system and running a bunch of DDL, DML, SQL, etc. – ORM merely saves you, the web developer, from having to think about doing so.)
.NET EF Core migration bundles are a great example of running ORM to reconfigure a running database at backend-web-tier CI/CD deploy-time.
In other words, there might not actually be a HelloWorld-database repo, after all. There might simply be code in the HelloWorld-web-api repo responsible for running ORM against the database tier supporting the API tier.
More traditional IAC reconfiguration
But what can we still sprinkle in a bit more infrastructure as code (“IaC”) and save the DBAs some toil, with all the work they do constantly reconfiguring (including drift-remediating from desired setup – e.g. auto-revoking access nightly from inappropriate people who got accidentally manually granted DBA status at some point) the parts of the running RDBMS that the backend web API tier’s ORM couldn’t care less about?
Yes, we can!
Those DBAs might want to spin up a separate version-controlled codebase dedicated managing the database tier’s configuration (or the configuration of all databases at the enterprise), stored completely separately from whatever code the web developers are writing. That is, maybe it’s the DBAs who want to manage a repo called HelloWorld-database or, more likely, one called all-databases.
Microsoft Learn’s Secure, optimize, and deploy database solutions > Implement CI/CD by using SQL database projects > Implement CI/CD pipelines encourages DBAs to author files ending in the file extension .dacpac to describe how they wish the RDBMSes they manage were set up, and to have CI/CD pipelines run a command called sqlpackage /Action:Publish against those files.
- (Note: I’m sure that under the hood, the running
sqlpackagejob is also just logging into the running relational database management system and running a bunch of DDL, DML, SQL, etc. – and merely saves you, the DBA, from having to think about doing so.)
And I think that’s a great idea, too … just be caseful not to set up sqlpackage .dacpac automations that fight with the web developers’ ORM automations, if you’re both pointing your automations at the same database! 😆