Sql-server – How to we manage cross-database dependencies across environments

2. Databases on a remote server only accessible to production

... every environment should be identical (with the exception of configurations such as credentials and connection strings)...

With regards to Linked Servers, this one is actually not so difficult. You just need to classify them correctly: they themselves are a component of the system and should be identical across environments, BUT their definition is a configuration that is often different between environments. Meaning, what needs to be consistent across environments is the fact that the Linked Server exists, and its name. This way any and all references to the Linked Server(s) can be identical across environments.

For non-Production environments, you just need to set up an Instance to be a dummy target of the Linked Server so that validations made against it when code is compiled will succeed. This can be achieved with SQL Server Express if need be. You don't need any data in this Instance, just the Schema that is explicitly referenced in your code. You can share one of these dummy targets between environments, but you should probably have two of them: one for the current structure, and one for changes being made in those remote resources that you will need to test against before they go live (assuming that you are given notice of upcoming changes).

And in case it is not obvious, these dummy targets are shared resources, regardless of whether devs are developing against private, local Instances on their workstations or against a shared Instance.

The Schema for the dummy targets is not mixed in with your app / DB code. It is either considered configuration or as a component of your build process / CI process.

1. Databases on the same server

(quoted out of order) While bootstrapping developer environments, we must take care to create these in a particular order in order to successfully apply DDL and DML-scripts, lest we face dependency errors... it is causing so much headache that some of our developers have simply given up on local developer environments and do all of their development in a shared database.

I understand the idealism behind developing against private DBs to prevent external interference, but in practice I am not sure how necessary it is. I worked in an environment that had easily 20 DBs for 7 - 10 products, over 1000 tables, over 3000 stored procedures, etc. We had easily 7 product teams. Some of the DBs were dedicated (i.e. isolated) to a particular product, and some where shared across multiple products. And there were other complexities that I am leaving out, but the point is that it was a more complex environment than what you are describing. We had shared DBs in all environments: dev, QA, staging, etc. In the 6 years I worked there I can think of maybe a handful of times where there was cross-team interference. And when there was, we just discussed the situation with each other and came up with a way to not impede each other. Sometimes we rearranged the order in which we worked on particular stories / tasks so get the dependency done sooner, or if that was not possible, we might stub something out / make a partial change for something that would be done soon but didn't have time to get to right then. I don't remember there being a time when a team couldn't proceed at all (and if I am forgetting, then it was still quite rare). If the dependency was that large of a factor for the story / task, then we usually discussed it before planning the sprint so that we would know if our story could even proceed, or if it had to be punted to the next sprint while the other team made their changes.

All of that to say, getting this to work on dev workstations for private development sandboxes is a bit of work and it might not be worth the investment of time in getting this working. Especially because it ain't just boot-strapping that you need to handle. If it was merely an issue of initial setup, then I would still recommend restoring the most recent backup of the shared dev Instance (because you will still have one of those anyway, right, for when code is checked in? just like there should be a shared app server that gets deployments from all teams to make sure that everything compiles correctly before migrating up to QA). I don't see any benefit in running SQL scripts as that still leaves the private Instance with no data. And how much time do you want each dev spending on adding rows to tables so that they can test their change? Not only is that a waste of time, but it is highly error prone as we all know that they won't go through the app (and hence the app logic), but will simply do direct inserts or use SSMS to edit the table(s) to add rows. And they will add the bare minimum number of rows with only ideal conditions. So you will end up getting more instances of code that doesn't functionally work against real data, along with the dev saying, "well, it works on my box".

But it's not just a matter of initial population of structure (and hopefully data). You still need to push down committed changes from others. And that might even be more likely to fail now since another team might have completed their project that either breaks when being pushed down to the dev workstation, or changes from a dev workstation can't merge into the shared DB because the environment changed. In a shared Instance, these interdependencies are found and accounted for much earlier in dev cycle.

All of that to say, if your developers are not currently blocking each other semi-frequently, then I recommend sticking with your current setup of a shared Instance.

... These are databases with separate repositories, deployment pipelines, ...

Separate repositories, libraries, etc is not a problem. But separate deployments could certainly complicate things, the degree to which depends on what direction(s) the dependencies are in. Do dependencies ever go both ways between projects, or always from one project/DB to another?

I do not put much faith in fully-automated database deployments. Checking in code and having a process blindly take it all and do something with it works fine for app code since app code is just that: code. You take the committed / checked-in files, compile them, and you have your finished product (typically). If there are dependencies between libraries then they are handled by building the projects in the correct order.

But databases, by their very nature, are more than just stateless code: they also contain state. And so I don't see how one gets around custom deployment scripts (outside of singular databases and/or not much data). I know some folks are able to use automated migration scripts, but I have never worked in an environment that could. So as much as I strongly advocate for keeping all code in source control, I would never consider generating deployment scripts from it.

Due to our complex structure (and the number of servers / DBs we were deploying to — same schema deployed to anywhere from 4 up to 20+ instances, depending on the product, and multiple DBs per each Instance — we had a custom deployment system. It was multi-threaded and quite nice, actually (mostly using nAnt and CruiseControl, I believe). Our process was to have a separate release script folder (per each release), in the repository (separate from Trunk) that the build system processed, triggered by checkins. We had folders for each project, and within each project folder, we had folders for Schema (Tables, Views, and Triggers) and Data, Functions, Stored Procedures, etc. When we updated stored procedures and functions (i.e. code) we checked the object script into both our team branch and to the appropriate release folder. All release scripts were required to be idempotent (i.e. re-runnable). The build process processed the folders in the same order each time, and ran the Schema and Data folder before the "code" folders. For Schema and Data changes, we checked the object script (basic CREATE statement) into the team branch, but a custom migration script into the release script folder. This allowed us to properly handle transactions, dependencies, etc. Also, per each release script folder, all scripts were prefixed with a 3-digit order number: 001 - date - this change.sql, 002 - date - shortDescription.sql, etc. This way the release was processed in the same way each time it ran, and it ran a couple of hundred times over the course of the 3-week sprint. If we were working in a DB that was a shared resource and we had a lot of changes to make, we would simply "reserve" the number of slots we needed by checking in empty .sql scripts with the first and last order numbers we needed. Everyone always did an update before committing anything, so we always knew what the next available order number was. Worst case scenario, we renamed a script to adjust the order number if two people checked in the same number at roughly the same time (but again, this was quite infrequent, even with 10 of us constantly checking stuff in).

One tricky thing was managing the Linked Servers. We had one DB were all Linked Servers existed (the rule was that they would only ever be in that one DB). And, to make it even better, we were using Replication and had to ensure that schema changes were made at the subscriber before data changes were made. To make this work, we had two release script folders for this one DB: one of them was the first release script folder to process, the other was the last release script folder to process. Schema changes went into the "first" folder so that the changes were in place for other DBs with dependencies to it, and data changes went in the "last" folder since by this time, the publisher and subscribers had their schemas updated.

Hopefully this gives you some direction and ideas for handling these complexities of dependencies and Linked Servers, etc.

Good luck.