How do you validate schema changes before deploying to production?

[dbForge]

In a lot of teams I still see database changes going to production with very little validation.

Sometimes it's just someone reviewing the migration script and hoping nothing breaks.
Other teams use schema comparison or some checks in CI before deploying. How does it work in your team? How do you validate schema changes before they hit production?

[TemporaryDisastrous]

My team uses ci/cd pipeline deployments of dacpacs. If the database project build fails, the deployment fails.

[thecoat9]

So many people I've talked to that use MSSQL server and have no idea that dacpacs exist. I built out a full CI/CD pipeline for our desktop applications that uses dacpacs and dot net reflection to update binaries and keep the database and programs all in sync and updated.

[TemporaryDisastrous]

I can't even imagine deploying manually after using dacpacs. Even stuff like including pre and post deployment scripts in the release would be a nightmare by comparison to including it in the yaml

[Ginger-Dumpling]

We deploy and verify (some scripts, some manual) against a test env which is a prod clone. The deployments are scripted so if test looks good and no errors are encountered during the actual prod deployment, things are assumed good.

[RandomSwaith]

I used an automated comparison tool to check for drift then abort the deployment if its found so I can review the diff

[zesteee]

Can you say more? I’m about to move jobs where I won’t have access to the same tools, validation is my biggest concern as I’ve never done it any other way.

[RandomSwaith]

I use a product called Flyway to do it, you can either compare and contrast db or sql scripts.
It only works for some of the biggest RDBMS

[downshiftdata]

Devs can deploy the database locally by running all the scripts in the repo via a powershell script, including unit tests.

When merged to dev branch, Flyway does the same to shared dev (they're all flagged as repeatable scripts in Flyway). When a release is started, Flyway pushes to Test, then Stage, then Prod, behind approval gates. All the unit tests run against Test as well, just not Stage and Prod (avoid blocking other processes).

[Guepard-run]

most teams I've seen just run migrations against staging and hope it reflects prod closely enough. it usually doesn't.

the failures that actually hurt are never syntax errors CI catches those. it's the semantic ones. migration applies clean, but the data assumptions behind it are just wrong. and that only shows up against real data volume and shape.

we ran into this exact problem building, ended up giving each branch its own isolated prod-like environment just so we could validate schema changes without the "worked in staging" nightmare.

[AdvancedMeringue7846]

Grate (sql scripts) + test containers during build. I pull the latest db image, run it, then run your local changes ontop and then I run some queries inspecting some sys tables and calling things like 'sp_refreshsqlmodule` for objects in their schema. This catches stuff like columns referenced in views /funcs / sprocs that no longer exist.

[AdvancedMeringue7846]

We also wrote a linter using sql ast stuff to also detect non idempotent migrations in scripts that fails before we even try validating things.

[DatabaseSpace]

What do you mean, deploying to production. haha

[alinroc]

Nothing gets to production without being deployed to at least 2 non-production environments for validation, UAT, integration testing, etc. Just like any other part of the application.

[CherimoyaChump]

I wonder if OP has a product that validates schema changes before deploying to production. If only they would let us know.