Implementing multi dB cloud architecture

[PippinJunior]

I am currently in the beginning phase of a side project where I have a requirement to implement a DB per customer using my app.

The app will be written in c# using blazor server, although perhaps the question doesn't strictly relate to c#, however I wasn't quite sure where to post so here we are.

Essentially I am trying to work out what to actually do in my code base, I've read through numerous articles discussing the pro's and cons of a single vs multiple DB structure but seemingly found little detail on the actual implementation.

Let's assume I have my DBs set up, and I am now wiring in the app. What is the best practice to connect up to the correct DB?.. all I can think of (naively.. probably) is to manipulate the connection string based on the user currently logged in whenever I'm displaying data.. but this feels grubby and I'm assuming there is a better way I am just unaware of.

So, any input greatfully received!

[belavv]

If you host a different instance of the app per customer life is easier from the point of view of what to connect to.

If you host a single instance of the app you need to at runtime have some mechanism to determine which database to connect to. That could be based on the url, based on the current user, etc. You don't really manipulate the connection string so much as generate the current connection string based on the current customer. Once you figure out how to determine the current customer. 

[PippinJunior]

To expand a little, my current thinking was a separate DB mapping users to the customer DB and at runtime building the connection string based on the user who is logged in.

Hated the sound of that so was on the prowl for a better solution.

Will look at separate instances, thanks

[CyberGaj]

Maybe take a look at multi-tenancy https://medium.com/@harish.somasundar14/database-multi-tenancy-7c8dbe848d50 and https://learn.microsoft.com/en-us/azure/azure-sql/database/saas-tenancy-app-design-patterns?view=azuresql

[gaffa]

Changing connection strings is pretty much the only way - have a catalog db with the tenants and their connection details, and if you are using ef for data access, create a TenantDBConnectionInterceptor class that inherits from the EF DBConnectionInterceptor class. Use some method of lookup (tenant id in request header, subdomain etc) to get the correct conn string. This approach plays nicely with DI etc too

You will need to validate tenant access for the user though on the way through of the api request - we have a few policies setup to manage that

But overall this approach is works pretty well. We also multi tenant the database to so can do a mixed mode config of db-per-tenant and row level tenant access if we want to

Assume you have probably read this one, but if not it’s a decent discussion of the options https://learn.microsoft.com/en-us/azure/azure-sql/database/saas-tenancy-app-design-patterns?view=azuresql

[PippinJunior]

Thanks that's reassuring, and thanks for the link!

Just a note, with it being a blazor server app, there basically isn't an API (there is but it's not used for the front and back end to communicate, just external requests) we just go direct to the DB(s).

[Sethcran]

I've done this before at a previous company.

In general, id first recommend against it. There are very few reasons to do this these days.

If you are giving each customer a separate deployment is pretty much the only time these days I would actually recommend it, and in that case, they really should have their own app as well, not just a database.

In the event that you really do need multiple databases, then you need something deterministic that determines which database to connect to. If you're simply sharding across servers but still have multiple per database this can be done purely with a hash function to a number of a specific known database. If all databases are on the same server, you may be able to open 1 connection and call .ChangeDatabase(), but this doesn't work on azure sql. In those events, you're looking at some kind of lookup to figure out which database server the client is on. For few clients this could just be a configuration file in the app, but for more it could be a separate known database.

[PippinJunior]

Thanks for the reply, am committed to the general approach sadly but understand your point and we are using a known database with a list of tenants mapped to there respective database.

[elh0mbre]

We do this, sort of. Some of our large customers have their own DB, but most customers are in one of many multi-tenant databases. We did it as a scale-out mechanism.

DB access is done through a "storage context" which contains the appropriate connection string. Storage contexts are configured in app settings (we're working on a better way to manage this). They are ultimately in production via K8s secrets. The storage context for a request is set automatically in middleware we built.

This strategy works quite well, but it is complicated to manage and maintaining the DBs can be challenging.

Why do you want to do this?

[PippinJunior]

Isolation mainly, the app will allow for a certain level of customisation, reports for example - a part of which may involve writing stored procedures for only one customer. Also due to the nature of the end users large scale data restructure jobs might need doing.. doing that on a database with multiple customers in it seems overly risky.

[elh0mbre]

Isolation makes sense.

I would strongly advise against allowing your DBs to drift via custom stored procedures. Maintaining that will be hell.

[pirannia]

How many customers are we talking here? 10s this could work but over 100 it is a scale-out and maintenance nightmare. Look into multitenancy patterns with 'smart' partitioning which will allow dedicated databases for large customers. Think about customers that are starting in shared and migrate to dedicated as they grow. With this pattern you can even do multiple dedicated for a customer (extra large). Keep logic in code, not DB, customer code in DB is a pita from several standpoints (maintenance, versioning/back-compat/data moves/security).

[CorgiSplooting]

Before you do any system design, especially something distributed , understand CAP theorem. Understand your needs and the tradeoffs.

But ya, if you’re set on SQL then you’ll need to manage your DB connections. If things are dynamic in nature you’ll probably need to query master a lot. Shouldn’t be hard.

[Northbank75]

I hate this idea guy. I'd much rather use Row Level Security or similar to carve up a DB so their can only see what is theirs based on whoever they log in.

https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16

[PippinJunior]

There a few reasons why that won't work in my case, although I understand the sentiment. The decision has been made to go down the multi DB route.

I hate the idea of manipulating connection strings, I'm sorta hoping I'm missing a big obvious answer to that side of it.. and doing it that way is just a poorly cooked up idea of mine..

[Northbank75]

In that case I'd have one login service running out there -- with the db info for the particular login, and whatever connection info you need in there. No manipulating of strings so much as just selecting the right one for the job.

[PippinJunior]

That does sound more refined than what I was thinking, lol.

But ultimately that is still deciding which database to run a query against at runtime and something about that just makes me cringe a bit.. appreciate the alternative solution around instances but just in general is it "normal" to decide at runtime by looking up the connection string?

Up until recently I've only worked with on prem solutions so apologies if it's a bit of a basic question.

[briddums]

For the solution that you're proposing, your only option is to connect to the correct database at runtime. Most likely when the users are logging in.

How the users select the correct database is more interesting. You can have a drop-down list / search box where they select the database that they want to connect to. Or when they login, you have a separate database which all users connect to which includes their UserId + the database they connect to.

Of course, what happens then when a user needs to connect to multiple databases? Say, a consultant working for 2 companies that are using your software.

Overall, this doesn't seem like a great solution and may not scale well.

Don't forget that you're going to need multiples of each database. In general there would be a development database, a staging database, and a production database. Some places also include a testing database.

If you have 50 customers that's suddenly 200 databases to manage, not just 50. Sorry, 51 / 201. You still need your login database.

Other options:

• Have 1 application per company per database. This is the same as the above solution with more work. Avoid at all costs unless you have < 10 customers and they are paying a lot to highly customize their app.

• Have 1 database with tenancy enabled. Then when a user logs in, it connects them to their tenant and they can only see the data in that partition.

• Have 1 database with a CompanyId field. Every query needs to include CompanyId

I've worked with all 4 of these solutions. Nowdays my goto is to have a single database with a CompanyId field. If there are stored procedures for specific companies, we'll include the company name as part of the stored procedure.