Source Control and Database Per Tenant

CMTietgen

We have a website that uses a database per tenant (almost 200 in total) and 99% of the database objects between all tenants are the same, but some tenants have custom stored procedures.

What would be the best practice to version our databases?

Should we create a single database and put all objects in this database and make changes and commits against it? How would we handle the custom SPs for tenants? Would/should they be kept in their own branch?

AlexYates

Firstly, I'd try to avoid that architecture. It doesn't scale well.

However, if you are stuck with it, I would include all the sprocs in DEV/source control and I'd ensure there was some sort of naming convention that allows you to filter them all out at deployment time. Hence, all the standard tenants get the basic image without any custom sprocs.

Then I would add a post deploy script that does an (if exists) drop and redeploy on the specific sprocs depending on the tenant. If you have a sensible naming convention you might be able to make that post deploy relatively straightforward.

We had a similar problem with one of our customers. You can watch/read about it here:
http://dlmconsultants.com/greentube/

CMTietgen

Unfortunately, we're locked into a database per tenant. It came about mainly due to the fact that developed the application about 15 years ago and didn't really know any better, but also due to having a certain level of data isolation. It also made on-boarding new customers quicker at the time.

Script deployment is a pain now, though, since it they have to be deployed on 200+ customers.

I'll take a look at your video and see what you guys did, but if I can ask, were you using the database per tenant and did you switch up to something else?

AlexYates

They had 6 production data centres that were *mostly* in sync. We developed a solution to include all the differences in source control but in the end it was very complicated and easier for them to standardise to a single golden version.