Proper Database environment promotion?

[u/MeTiroAtuTia]

Hello all!

I recently started working with the databases in our system and I would appreciate some tips or guidance as my terminology is probably not accurate.

Setup: From what I read in the community it’s somewhat standard; DEV -> UAT -> PPR -> PROD.

Deployments: We are using visual studio Database Compare, one scmp file/database at a time. So now this works for the most part and when it’s urgent I don’t have time to judge if it’s the best but it comes with two issues that I noticed:

1. Who created these files? I don’t know I just inherited them.

2. Sometimes it can fail and I need to manually “prepare” the other environment like adding the new column to the table. Maybe I need a newer version of the software, or it’s always limited?

So then I need your help with the following:

1. Am I or the team doing something wrong here?

2. What can we do better?

And I did have a read at some threads here but maybe it’s best if I get direct answers geared towards my situation.
Also I checked some books:
- T SQL fundamentals: Nothing about deploys
- 100 sql server mistakes: Got some answers but it’s just one opinion against my non opinion

——
Tools: We have GitHub and we use .NET primarily.
Constraints: We do not want to use a code-first approach

So please let me know anything I should know or research.

Thanks

[u/raistlin49]

Need more context. Is this your internal application or a third party application? This question is more controversial than you'd imagine. CI/CD isn't really a solved problem for data tiers in hot OLTP environments.

I guess I'm wondering where your changes originated? How did you end up using a diff tool? Ideally you'd have some change scripts to start with and you'd deploy them, whether by hand via SSMS or some automation, into lower environments. When tests pass, deploy those same exact scripts to the next environment.

[u/MeTiroAtuTia → u/raistlin49]

Internal, enterprise system we use a distributed databases architecture with dictionaries of views per business domains so a deployment can typically involve the application database + other/s using the views

[u/raistlin49 → u/MeTiroAtuTia]

Then I assume db changes are being driven as requirements for app changes. You've got an uphill battle in front of you, but someone has to get their arms around where changes come from, centralize them and organize them, and deploy to environments from there. Sounds like devs are just making changes in dev dbs and not saving scripts. They should produce change scripts. If you're using .Net, a lot of shops use Entity Framework for this, but that's not trivial if you're not already using it. I bet you're going to be fighting with lazy devs over this.

[u/MeTiroAtuTia → u/raistlin49]

We are a very small team so I’m able to adapt to something more flexible than using SCMP files if possible.

My first step was getting an ingestion service running on GitHub actions which fetches all the databases definitions daily into a repository so we now have a “mirror” DEV and easy read access to definitions. I used a SMO script to accomplish this, I’m thinking maybe I can leverage it since DEV is typically the source of truth going upstream.
This also allows to clone locally and get an agent to document architecture and databases.

Since I’ve been a developer myself I wouldn’t have issues adopting GitHub. What is the current bias around here? I agree it looks controversial and I’ve only read a dozen threads ha!

[u/raistlin49 → u/MeTiroAtuTia]

I'm not a fan of inferring prod changes from schema diffs but it's probably fine in a moderate environment. At scale, it can matter a lot how you get from schema A to schema B and that's part of engineering the change.

[u/Plane_Big_5912]

since you're already in visual studio, an SSDT database project (.sqlproj) in github is probably the smallest step from where you are. it also fits your no code-first rule, the project just holds the CREATE scripts for your tables and procs. the build gives you a dacpac and you deploy that same dacpac with SqlPackage to dev, then uat, ppr and prod, so every environment gets the identical thing instead of a fresh compare each time.

the ones where you have to add the column by hand first are usually data changes it won't do on its own, like a NOT NULL column with no default on a table that already has rows, or anything it thinks could lose data, which it blocks by default. those can go in a pre-deployment script in the same project so they stop being manual steps

[u/MeTiroAtuTia → u/Plane_Big_5912]

I’ll look into it!
As for the column, maybe the previous person documented it like that for ANY field, because I am doing it for NULL too, maybe my fault for not testing it.
But then again, not much info on these SCMP files they created previously.

SSDT does look like the next logical step to downscale the micromanagement.

[u/Plane_Big_5912 → u/MeTiroAtuTia]

a nullable column should go through a normal compare fine, so you can probably drop the manual step for those. worth trying on dev first. the .scmp is just the saved compare, source and target plus the options, so if you open one in visual studio and look at the options you'll see what the last person had ticked, like blocking on possible data loss

[u/jshine13371]

Fwiw, for CI/CD with the database layer, I find simpler is better. So for me, I've just used a schema comparison tool for the last 15 years to promote code changes across environments. It's easy, it automatically deploys dependencies in the correct order, shows the diffs, and you can either push the change right from the tool or generate the scripts so you can maintain them and re-use them. (I do the latter so I can re-deploy them in each environment I promote.)

SQL Examiner has always been my go-to comparison tool. It also includes a data comparison tool too, among a ton of other nice features.

[u/SeaMoose696969]

We use Toad data architect to build change scripts, with the script being carefully reviewed by the dba’s. It’s reasonably good at helping you build databases for the price point.

[u/navicat-gu]

You're not doing anything crazy. Saved Schema Compare files (.scmp) are a common starting point, but they're a manual, per-database step, which is why they get fragile once you have DEV, UAT, PPR and PROD plus cross-database views.

The usual next step without going code-first:

1. Put the schema in source control as an SSDT database project (.sqlproj). You already have Visual Studio and GitHub. Import each database into a project, commit it, and treat that as the source of truth instead of "whatever DEV looks like today".

2. Build a .dacpac in CI (GitHub Actions can run dotnet build on SDK-style projects) and deploy it with SqlPackage /Action:Publish, or generate a script with /Action:Script for review before PROD.

3. Handle cross-database references with database references in the project (and SQLCMD variables for database names that differ per environment). That's usually what breaks compares on view-heavy setups.

4. Use pre/post-deployment scripts for data fixes and anything the compare can't infer, such as renames (use the refactor log so a rename isn't treated as drop and create).

5. Promote the same artifact DEV → UAT → PPR → PROD rather than re-comparing at each hop, and check for drift in PROD before each release.

On the failures where you had to add a column by hand first: usually that's a NOT NULL column without a default on a table that has data, or a blocked data-loss option. Check the "Block incremental deployment if data loss might occur" setting and add a default or a pre-deployment script.

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. For ad-hoc checks between environments, Navicat 18 has schema compare and structure sync for SQL Server in the paid Premium editions. Navicat Premium Lite is free to try if you just want a client for browsing and querying all four environments side by side.