5

We have several C# applications using a shared monolithic database (on-premise SQL server) using Stored Procedures as an exclusive interface. The C# code repositories include only Stored Procedures that are relevant to them, which means a single database is split into multiple repositories.

We are currently using a shared DEV environment restored from production (after some modification to sensitive data, etc.)

I want to move to our version control being a single source of truth and put the whole database into one repository so I can generate empty DB for each developer (that would allow me to unit test, etc.)

But that would create problems for developers: they would have to keep a branch of their app and branch of a database in sync and coordinate extensively.

How to solve this without moving the apps back into monolith as well?

Improve this question
4
  • What are the "repositories" in your first paragraph?
    – simbo1905
    Commented May 9, 2020 at 12:32
  • The normal repositories. The ones you clone when downloading them locally.
    – Zikato
    Commented May 9, 2020 at 12:41
  • so these are sourcecode repositories of the applications that are made up of stored procs?
    – simbo1905
    Commented May 9, 2020 at 13:16
  • No, the applications are C# code, but they access a shared database. But they only include stored procedures that are relevant to them.
    – Zikato
    Commented May 9, 2020 at 15:02

2 Answers 2

Reset to default
8

I personally made the unwise choice of cramming five different apps into a single database about 20 years ago. I then had to deal with the repercussions of that. The bad news is, this is really hard. The good news is, modern tools make it easiser, but not easy.

So, first, absolutely get a single source of truth. You're using SQL Server, so there are a bunch of really good tools that will help you maintain your database in source control. One is provided by Microsoft. It's called SQL Server Data Tools. It only works inside Visual Studio. However, it's a solid tool. If you have an MSDN license to run VS, it's complete free. There are also 3rd party tools available for a cost. They're generally much more sophisticated (Microsoft doesn't give SSDT much love). I work for one of the vendors of such tools, Redgate Software.

Once you're on the path of maintaining the code in source control, the easy part is over. Now you have to figure out how to integrate the teams. Here's what we did. Every team got it's own branch of code (we did this in the old TFS, which sucked, you can use some variant of Git which will be much easier, not easy, just easier). This also means that each team had to have it's own copy of the database (which it sounds like what you're going for anyway). In fact, each developer got their own database and then we had an integration testing database for each team. So the team worked on it's own, independent of the other teams.

Next, we created a master integration database. This is where the teams came together. We had a main branch for the code and teams would have to merge their code semi-regularly into this branch and then we could run automated tests to see if any team had broken the code or structures of other teams.

Then, you have to build a release process that ensures that any one team can't overwrite or supersede the others. This means that teams have to regularly merge from the main branch, changes from the other teams.

It requires quite a bit of discipline and some training to get this all set up. However, it's doable. I'd also suggest implementing a flow control software like Jenkins, Octopus, AWS DevOps or Azure DevOps to assist with automating all of it.

I've worked on tons and tons of documentation around these types of processes. Full disclosure, a lot of the info at that link includes our tooling. However, the processes are what you're looking for and you can find good information there. This is a free ebook on these processes I helped to write that's very product agnostic. Hopefully between these resources you get enough to help out.

Improve this answer
3
  • 2
    Hello Grant and thank you for a thorough reply. I'll look into your resources as well. You are actually one of the people who influenced me to start on the DevOps path. My root cause is actually a culture, but I need to be equipped with the best knowledge to convince others and find a common solution.
    – Zikato
    Commented May 6, 2020 at 11:52
  • The cultural shift is hard. No lie. However, the payoffs are huge. But, if you can only do one thing and get the database into source control, and that's all you get done, it still has a payoff. Change tracking and the ability to undo changes is a win, regardless of if you get all the rest of the functionality automated or not.
    – Grant Fritchey
    Commented May 6, 2020 at 18:47
  • I saw a two hundred million dollar write-off of trying to cram multiple teams into a single database and mandating store-procs to access it. i think that the microservices crazy is largely driven by organisations finding that the idea that somehow using stored procs provides some level of abstraction to change the tables doesn't work in practice at scale.
    – simbo1905
    Commented May 9, 2020 at 13:19
2

Disclaimer: I'm not a RDBMS DBA and I have zero experience with MSSQL. Everything below is a hypothesis.

Each app should have it's own stored procedures in migrations, and shared procedures should be in their own repo with a separate migration list. This adds a necessary isolation and the ability to use benefits of common ones with optional toggling on per-app basis if there are breaking changes, plus a separate repository is an easy way to maintain them.

Improve this answer
3
  • My problem is I want to recreate an empty database from version control. How to do that if it's split into multiple repos? Which repo should contain the DDL to create tables when they are shared. How to keep the repos in sync with the correct version of DB - especially when branching.
    – Zikato
    Commented May 6, 2020 at 10:17
  • You can replace "stored procedures" with "data" and it will almost resolve your problem because you'll need more steps to be able to roll back app-specific changes and integrate them in shared procedures, the process would be add app-specific columns/tables and stop using dependent shared procedures in that app → propose the changes in shared data → alter shared data and procedures. In microservice approach each service has it's own database and none of them share original data (e.g. service for accounting should store names in Users table while auth service doesn't need them).
    – chupasaurus
    Commented May 6, 2020 at 11:04
  • you can use git submodules to "mount" the application repos into folders within the main repo. git-scm.com/docs/git-submodule many teams try not to use submodules as with a well-factored architecture you don't need them. when you have the architecture you describe then it might be a good solution.
    – simbo1905
    Commented May 9, 2020 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.