Shared Database with Dedicated Model

RVBU

I understand that git is rather pointless in the shared model.  However, we are using DevOps and will get some value out of the auditing available with being able to link commits to work items and stories.  Are there any glaring issues of using the dedicated model on a shared database?

AlexYates

That would probably work fine. Using the shared model is probably the quickest way to making some short-term progress. However, in my experience, the longer you use the shared model the more likely you are to reinforce monolithic/big-ball-of-mud databases that act as a single point of failure for a zillion dependent services. In the long-term this will lead to bigger challenges and nastier failures.

I'm not saying that you should move to dedicated databases immediately. Maybe shared model is a good first step. However, in response to a few of your challenges I can offer the following suggestions.

3rd-party frontend apps
in an ideal world, each app should manage it's own data. That's obviously a big change that won't happen overnight. Perhaps you'll never truly separate everything, but you can decide to stop exacerbating issues by avoiding this architecture for new stuff wherever possible and by looking for some of the services or groups of services that could be separated relatively easily.

Circular dependencies
The cloning tools I mentioned above are actually amazing at solving a bunch of these issues. They allow you to deploy one or more databases, even if they're missing dependencies, in a couple of seconds. This is a great enabler for the disposable dedicated environment workflow, either for dev or test purposes.

Of course, this doesn't solve all your challenges, but it's a big help. :-)

AlexYates

It sounds like you understand the issues so I won't bore you with that. Obviously branching and shared databases is just complicated/broken.

Assuming you use a trunk-based dev approach, it *should* work fine. Although Redgate will obviously say it isn't officially supported so don't blame them etc... Which is fair.

I recommend you read (and understand) this before going ahead with it:
https://documentation.red-gate.com/soc5/reference-information/how-sql-source-control-works-behind-the-scenes

You'll often get into a funky state where all developers are commiting to their local git repos, so they'll likely be seeimg everyone's changes in the commit tab. There is likely to be lots of duplications and merge stuff but that *should* for the most part sort itself out. You'll be asking git to do a lot of unneccessary merging, which should be fine, but git has a reputation for being awkward sometimes. There will probs be a lot of "No-Ops" (see link above) but they *should* cancel each other out.

So yeah... It *should* work. Lot's of people do do this. It's ugly, but it sorta works. Your mileage may vary. Be prepared to fix stuff when it doesn't work.

What I'd like to understand more is why.

I hear the desire to link commits to work items and stories, but wouldn't it make more sense to achieve that through branching and pull requests using dedicated databases? If you did it that way git would be actively helping you, rather than just complicating things. You'd be able to get the best out of both TFS, Git and Redgate.

If you are concerned about data and the storage, admin or data privacy challenges associated with managing many dedicated databases, you should check out Redgate SQL Provision. It's ace.

https://www.red-gate.com/products/dba/sql-provision/

RVBU

Thanks for your thoughtful reply.  It is very helpful.  Would it be easier to just used the shared model and a secondary git client to push commits you think?

As for your question on why.  Most of our database development is done in support of 3rd party frontend applications.  The dev/test environments on those applications are configured to the shared databases.  I think it would be a lot of work, if even possible, to reconfigure those applications to be able to point to a local copy of the database.  There are also a lot of dependencies on support software that are hard coded to the shared databases.  On top of that, there are a ton of circular references in stored procedures/views over dozens of databases which make it a little difficult to recreate the environment on a local machine.