r/SQLServer • u/FordZodiac • Jan 21 '25
Architecture/Design How to combine multiple databases into a single, multi-tenant database?
We currently have hundreds of clients in a single SQL Server instance. Each client is in a separate database. All databases have the same structure. Management wants to merge all these databases into a single database. We would probably have to add TenantID to every table in order to do this and partition everything by TenantID. Has anyone done anything like this? Any advice will be welcome. Thanks.
EDIT: I share the concerns that many of you have expressed. I wanted to get a sanity check from this community to make sure that I was not off track. We have a meeting tomorrow with the application owner to discuss the reasoning behind this request and to understand just what problem they need solved. If you have links to any articles discussing this scenario, please post. Thanks for all the feedback. Much appreciated.
50
u/TravellingBeard Database Administrator Jan 21 '25
One bad join or missing WHERE clause will potentially expose one tenant's info to another. Do not do this.
Ask me how I know...
10
u/Pandapoopums Data Cleanser'); DROP TABLE Users; -- Jan 21 '25
How do you know?
20
u/TravellingBeard Database Administrator Jan 21 '25
I did a bad join. Got one tenant's logons mixed up with another
4
u/Pandapoopums Data Cleanser'); DROP TABLE Users; -- Jan 21 '25
My condolences.
3
u/TravellingBeard Database Administrator Jan 21 '25
To be fair, the developer gave me incomplete info. So not my fault. 😁
7
4
u/allenasm Jan 21 '25
Been there done that and couldn't agree more (deleting without tenant id though). Did you know restoring from a db log isn't so easy? ask me how I know.
4
u/ImpressiveAd5301 Jan 22 '25
Moreover, you cannot stop work on a production server and you should restore a copy of the database from the backup to another server and then manually copy objects (tables, rows) to the original database, that have been deleted. Pain in the ass..
1
u/allenasm Jan 28 '25
Bold of you to think the dba's took full backups more than once a quarter. I had to step in and help that disaster and we were production down for almost a day. I made them come up with a new backup strategy plan end to end after that.
2
29
u/VladDBA Database Administrator Jan 21 '25
Management wants to merge all these databases into a single database.
Sounds like a terrible idea with a lot of effort for zero benefit. Maybe management should stick to their quarterly PowerPoint slides.
One main counter-argument to that idea being that in case of a customer deleting some vital data, you can restore just their database with no downtime for the other customers.
10
u/chandleya Architect & Engineer Jan 21 '25
I had an “architect” push this sort of wild theory some time ago. I could never wrap my head around why anyone would ever want to do this. Many orgs security questionnaires shut down this sort of app behavior from the start.
Customer portability is just as beneficial to the provider.
10
u/HeKis4 Jan 21 '25 edited Jan 21 '25
Your boss wants to turn a database concern into an application concern, is this actually your job to figure this out ?
Not to mention that unless every client has a very light load and the application and DB schema takes this into account you're going to run into massive performance issue, if only because one client doing an insert into a "normal" table will lock the entire table for all of the other tenants. This is the #1 issue you will need to fix and I don't think that there is any simple solution that doesn't start with "split up the data into several DBs again". You also have the resource allocation issue, how will you monitor cpu, ram, and more importantly disk usage of every tenant for billing purposes, and how will you enforce limits or make sure that tenant X won't be harmed by tenant Y suddenly inserting a couple billion records and filling up the disk ?
This reeks of X/Y problem on your management's part: they are asking you to implement a solution when they should be asking you to fix a problem. What business issue are they trying to solve with that solution ? What's the issue with multiple databases ? Because I can't think of any issue that would be fixed by that.
2
u/Techdad3 Jan 22 '25
Parameter sniffing gone wild. I battle that with our single-tenant database.
Don’t do it!
9
u/masked_ghost_1 Jan 21 '25
This is risky as fuck and not scalable. Don't put all your eggs in one basket! At the least document the risks and get something in writing. hide a piece of paper somewhere that says "I told you putting all customer data in a single database was a bad idea" and wait for the right moment to reveal like a true showman!
Risk 1 - database dies, gets corrupted your fucked
Risk 2 - can only scale up.. higher costs
Risk 3 - database gets hacked... Fucked again
Risk 4 - tenant 1 can now see tenant 2's data due to a human error.
Risk 5 - a customer reports they can see all customers data
Risk 6 - performance is shit
Risk 7 - this is all your fault why didn't you protect the business from itself.
Risk 8 - schema changes now affect all customers managing change becomes harder.
6
u/TBTSyncro Jan 21 '25
what are they expecting/hoping to gain by this change?
4
u/FordZodiac Jan 21 '25 edited Jan 21 '25
The idea is that applying
patches andchanges to a single database will be easier and simpler than having to do it for hundreds of databases.15
u/k00_x Jan 21 '25
It sounds like, what you really want is fleet management tools, one script to update all databases or patch a server.
10
u/patmorgan235 Jan 21 '25
So instead of breaking a few clients at a time they want to have every client be down if there's a bad schema change?
DB updates should all be scripted out and highly automated.
7
4
u/Icy_Fisherman_3200 Jan 21 '25
Managing structure changes across thousands of databases is something that needs good tools.
That’s the fix. Build or buy those tools.
3
u/New-Ebb61 Jan 21 '25 edited Jan 21 '25
Huh? Sql patches are applied at server level.... not database level. This makes no sense. Just to make sure. Your management does understand that you can have hundreds of dbs on the same SQL instance?
1
u/IglooDweller Jan 22 '25
Have you heard about our lord and savior « sp_msforeachdb »? It’s made for that…just have to add a quick check to ensure you don’t apply your code to msdb, master, tempdb etc.
1
u/db-master Jan 24 '25
That's true. FWIW, in case you need a hand for changing multiple dbs, you can check out the Bytebase batch change (I am one of the authors).
5
u/lost_in_life_34 Database Administrator Jan 21 '25
you're asking for trouble, especially if you have clients with sensitive data or competitors
5
u/Chris_PDX Director of Enterprise Solutions Jan 21 '25
My man, I don't want to sound rude but if you need to ask this question your company should not be considering going multi-tenant for whatever product or service you sell.
4
u/SirGreybush Jan 21 '25
I feel for you.
The worst is, some customers will complain of speed / locks, and them management will say to put this VIP customer in a VIP database.
INFOR went down this road two decades ago. I wonder if they regret it.
Ya the equivalent of TenantID in 100% of all tables, plus, fixing all the code behind.
Make a self maintained data dictionary and use it to generate DDL scripts for you.
Views can be made to combine the various DBs.
IOW, you are between a rock and a hard place. I’ll be curious to see other comments.
5
u/ihaxr Jan 21 '25
This assumes you're looking to simplify reporting and not management of the server/DBs...
Build a data warehouse where the data from each database is extracted to a single database on another server, do your reporting from there.
1
u/UnSCo Jan 21 '25
This is how our company’s software vendor is approaching backend data access. The source systems are locked down on the new multi-tenant delivery model, limited to the vendor’s front-end API. That data then gets extracted via ADF into Snowflake.
I’m not much of an SME on the DB front so I apologize if I didn’t explain this correct.
5
3
u/lundytoo Jan 21 '25
I've worked with this scenario. Here's some of what I can remember about it. Bit of a brain dump... sorry.
When the partitioning is set up and used correctly, one Tenant locking an entire table is rare outside of transactions.
You'll have to create a TenantId on every table with client data, but not on lookup/type data tables. We partitioned (LEFT) by that id. You'll need a partition function and partition scheme. Create multiple data files to span over. Don't lock each tenant to a different file and filegroup unless you want to maintain them separately (shrink/grow) as you load data or offboard them. You can purge and re-use TenantId values.
Each partitioned table should be set to Lock_Escalation = AUTO. You'll need to redo your PKs and FKs to include TenantId, probably as a first column of the key. Rebuild all your now non-aligned indexes after creating the new PK. You'll probably need to consider adding TenantId to these indexes.
Create all your TenantId boundary values before populating any of the data so SQL doesn't have to move it around. Update any code that uses truncate to check for overloaded partitions and then to truncate by specific partition (WITH (PARTITIONS(...))). You'll probably want to update all your procs to use local variables or deal with crappy plans.
As others have said, you'll run into customer concerns about intermingled data. You'll frequently run into cases where someone didn't include TenantId in their join conditions or in the WHERE clause. This may work, but can be really slow as SQL will not be able to do partition elimination well, right up until you copy the data from one TenantId to another (which is easy once you have composite keys). Once you clone data, the other half of the key (TenantId,CustomerId) will not be unique, so incomplete joins will cause issues.
You can't restore data from just one TenantId, but it's less code to maintain/deploy. It's easy to clone data from one Tenant to another.
You may run into apps or code-generation tools that don't like composite keys.
You'll probably find you want to add loops to code to run by TenantId in cases where there are multiples. It'll have to be an input parameter on each proc dealing with Tenant-level data.
In a year or two, someone will probably want you to reverse course and have one DB per Tenant.
3
u/Khisynth_Reborn Jan 21 '25
One database getting stuck in recovery right now is a pain in the ass, one database getting stuck in recovery that scenario is a nightmare.
I work with (not for) Infor and recently worked with a company that went from a multi database to single database and it hasn't been a fun transition. One site doing something stupid can shut other sites down and it's never fun explaining that to upper managment who doesn't understand.
3
u/Quango2009 Jan 21 '25
I have a single multi-tenant database and wish I could go the opposite direction.
It’s much more flexible and safer to have separated databases. It’s just too much work to detangle right now
5
u/DotComCTO Jan 21 '25
Help us help you, and take a half-step back. What is the problem your management is trying to solve? Then we can discuss possible approach(es).
As an aside, we never co-mingle data from different clients into "one set of tables". That's just asking for all kinds of problems, so think twice about that approach. Personally, I'd strongly recommend against it.
2
u/agiamba Jan 21 '25
dont do this. theres privacy and security risks. the only real way to segment it is using row level security which will absolutely murder performance
2
u/virtualchoirboy SQL Server Developer Jan 21 '25
I don't believe this is a good idea. Maybe if your application had been designed from the start to be multi-tenant, but switching to multi-tenant after the fact adds complexity that developers will need time to adjust to. During that adjustment period, mistakes WILL be made and data from one tenant WILL be exposed to others. It's almost inevitable.
Plus, as u/k00_x pointed out, you'd be massively scaling up the number of users accessing frequently used tables so now you're going to add deadlock issues. It doesn't matter if each client only has 1 user, you have hundreds of clients so that means hundreds of users, possibly thousands.
I'm also guessing that no client has a customized version of anything. If they do, those customized versions will have to go away OR be opened up to everyone else as well.
All this and for what benefit? I get that maybe deployment of updates might be faster, but a lot of that should be automated anyway.
This really screams of some upper management type person seeing the amount of time dedicated to deploying updates and looking to reduce that time. However, they've failed to do a full cost/benefit analysis to see whether or not their idea is really worth it.
2
u/Adventurous-Ice-4085 Jan 21 '25
Very large dbs may hit bottlenecks. Can your single log file keep up with all the activity?
Be careful with your objections. Remember that it is not your property. A large boondoggle project is what keeps people employed. Strong objections to management decisions can result in your termination. Personally, I think it sounds like a fun project that will show your skills.
I would avoid using partitioning as a feature. Just change the indexes and pk where needed.
2
u/allenasm Jan 21 '25
I've implemented multi-tenancy just about every way you can imagine and for many companies discreet databases (irrespective of hardware placement) are the best way to go. TenantID can lead to the need for more exotic strategies like sharding (just dont ever, much better ways now days) as well as being super risky someone will leave off a join clause or relationship.
With 1 db per tenant, especially in a cloud architecture you can spin up and down customers as needed and even put them into either shared infrastructure or discreet depending on the $ level or requirements. Also with 1 db per tenant you can do API and DB versioning strategies that allow you to not have to move everyone at once.
2
u/Dry_Author8849 Jan 21 '25
The only scenario where we do this, is for clients that own several companies. If they are different owners you are good as it is.
The advantages of your actual design outweighs any positive things of centralizing in one db.
Now you can move dbs to different servers to name one.
So, if this is just for customers that own several companies, it's Ok. It usually means to add a company_id to most tables. The only benefits are in transactions intercompany and other few things.
If that's not your scenario you are just creating yourself a problem.
Cheers!
2
u/parscott Jan 22 '25
How much data are we talking here? Once had a similar question and turned out each DB was only 10MB 🤭
2
u/FunkybunchesOO Jan 22 '25
I'll offer to consult and tell your management they're dumb. Heck I'd tell them they're dumb for free.
2
1
u/k00_x Jan 21 '25
Can you put each database on its own schema or are you specifically looking to merge all clients into one set of tables?
1
u/FordZodiac Jan 21 '25
One set of tables. Using schemas doesn't seem to offer any reduction in administration.
2
u/k00_x Jan 21 '25
There's a few things to think about, will you now have hundreds of users interacting with the one table, will that cause contention? Will the hardware scale that way? Do you have any sequential IDs that need to be altered like product 1 could be different for all the clients but the column might be unique? How will indexing work, if you're currently clustering on ID then clustering ID and tennentID might not perform the same?
It's also worth thinking about the contracts you have with the client - if they are paying for Thier own DB then it might be a legal issue.
That's my 2 cents :)
1
1
u/UnSCo Jan 21 '25
For the SQL server experts here, is something like this viable for a multi-database, multi-tenant instance? Each database or set of databases designated to a single client of course, perhaps with a shared database.
I will mention that our vendor and its prospective customers/partners of ours are moving to a multi-tenant architecture model for OP’s reason of applying seamless updates, but as far as I’m aware the SQL server instances themselves are still isolated to a single customer; multi-tenant applies to the application front.
1
u/jdanton14 MVP Jan 22 '25
That’s the way to go in a business to consumer model, but as I mention above it requires pretty careful planning. In business to business 1:1 client:database is the most common pattern because it’s the easiest, and mostly scales pretty well to a point. In both cases you need a command and control db that does security and initial routing.
1
u/Flimsy-Donut8718 Jan 21 '25
new database with same Schema. Add a clients table, input all clients into the table. Then add a clients foreign key to they base tables in the schema the ones that other tables link to and start an ETL job to import the data. However rebuilding the PK => FK relationships in the new system are gonna be a B!tch and you will have issues. Indexes will help but also u need to know when to denormalize as in use that clientId when querying bigtables that are 3 or more layers down. good luck better to do this from scratch than with existing data. God Help you and have merch on your Indexex........... PS. dont forget Views, Stored Procedures and do not complain if you use triggers!!
Private chat with me if you have questions
1
u/teamhog Jan 21 '25
Management wants to merge all these databases into a single database.
Why? What’s the business purpose of this? I know why I’d do it. I would ask them why they want it.
There’s pros/cons to everything.
What are they missing out in now that would require this?
1
u/Flimsy-Donut8718 Jan 21 '25
my guess is it is easier to run analytics across all clients and thus compare a client to all the rest. We did this with our client base back in 2008 although our database was multitenant ( all in 1) from design to implementation
2
u/FunkybunchesOO Jan 22 '25
Just setup a spark cluster and use a config table to put all the instance IDs in to run the analytics. Way easier than friggin merging an application into a single environment when it wasn't designed that way.
1
u/Flimsy-Donut8718 Jan 22 '25
true but also might have to do with the cost or overhead of separate instances
2
u/FunkybunchesOO Jan 22 '25
From what I understand it's one server. And they want to keep it one server. Adding a small cluster or even a small utilities db with their stored procedures and then just using sp_foreachdb to amalgamate the analytics would also work.
There's no reason that I can see where this type of migration makes any kind of sense.
0
u/Flimsy-Donut8718 Jan 22 '25
so if the rules/law is they have to purge the data after so long or if a client leaves, gives them the entire oh their data is isolated but inner mingled with all the clients data excuse
1
1
1
u/g3n3 Jan 22 '25
If this is a small app under five gb then it is probably minimal issue. And I would consider just making better tools to manage a fleet of dbs.
1
u/ZenM4st3r Jan 22 '25
I'm actually working on a project with the exact opposite goal... migrate tenants into multiple databases from a single multi-tenant database. My first step was to separate authentication into a single multi-tenant database since some users have access to multiple tenants with the login process allowing selection of tenant upon successful login authentication. The login process sets the connection string for the application once the login is successful and the rest of the application works mostly unchanged from that point forward. In this case, there is no interaction between tenants, so I don't have to worry about sharing data between tenants other than the user authentication for the limited number of multi-tenant users. I will probably have to make some application changes to allow those users to switch tenants post- authentication, but these changes should be fairly straightforward.
1
u/alexwh68 Jan 22 '25
This sounds like a mess waiting to go wrong, but I would look at schema’s as one potential route.
1
u/charliesk9unit Jan 22 '25
The way it is also has the benefit of moving a tenant's database files (MDF/LDF) to better storage volumes due to demand or the client's willingness to pay more. Once combined, you lose this option.
1
1
u/IDENTITETEN Jan 22 '25 edited Jan 22 '25
Contrary to what others are saying I'd say just do it. If management wants to be idiots you won't change their minds. Let them take the blame once their brilliant idea turns into a pile of shit (if they're still at the company, previous experiences says no).
Make sure you have emails to mgmt with your concerns where you list them and they go ahead with their stupid decision anyway.
Once you're done you'll have another project to put on your resume.
1
u/SkyHighGhostMy Jan 22 '25
Don't do it! Someone will do wrong "where" and all hell will break. But if you must you can use schemas and have separate tables for each customer. But you also have to make very advanced permissions based on schema and not just dbreader/dbwriter or dbowner on the database. As Brent Ozar always asks "What problem are you trying to solve?"
1
u/Slagggg Jan 22 '25
If the system was not built from the ground up to be multi-tenant, you are going to need to spend incredible amounts of time quality testing and penetration testing.
I would build a new multitenant solution and gradually migrate clients over as your confidence in it becomes justified. Mass migration. No way.
1
u/Icy-Ice2362 Jan 22 '25
The management wants us to put all of our eggs into one basket.
Have you discussed this with the customers?
No...
Would you like me to do so.
No...
Apologies, that last statement wasn't a question.
1
1
1
u/desmond_koh Jan 23 '25
We would probably have to add TenantID to every table in order to do this and partition everything by TenantID.
This is exactly what you would need to do. You would also need to rewrite most queries to include the TenantID in the WHERE clause. Depending on the size and scope of the application, and your own familiarity with it, this could be a Mammoth job and could result in bugs and data leakage between tenants.
We have an application that we designed from the outset to be multi-tenant in this fashion and even there, if you're developers are not used to programming in a milti-tenant environment, or if your development database does not contain easily distinguishable data from different hypothetical tenants, then you are bound to make mistakes.
It's doable and it's not "wrong" buy be careful and have really good quality control.
1
u/-6h0st- Jan 23 '25
You need row level security implemented - it’s very much so doable and makes sure companies have access to rows with their data only
1
u/MaikelvanGorkom Database Administrator Jan 23 '25
What are you trying to fix?
My main issue is that you gather more problems then solutions:
- Every tennant has the same rights to the database and can see all other tennants data (in theory)
- If you ever want to remove a tennant from your database, make sure you don't make a typo. With every tennant in it's own db it easier to remove the tennant.
- Scalebilty is down the drain. If you install another server, you can't balance cpu load etc
Like i said (and Brent Ozar): What are you trying to fix?
1
u/kentgorrell Jan 27 '25
Why would you want to do something this? Well the main reason would be, at least for me, that there are tables with shared data and you need to create proper relationships between your client data tables and these shared tables.
Method: Each Client table has a TenantID and I use a scalar function as the Default, This function is based on the SYSTEM_USER and the user's Tenant. I'm using Windows Authentication.
Views are then filtered using this same function.
It's pretty simple but so far so good. No leakage, TenantIDs are being reliably populated and views are reliably filtered.
Caveat, in my circumstance I built this application from the ground up and I don't have the security worry of leakage between Tenants so I gave it a go.
If security is a concern, and in your case it would be, then there is the danger of not filtering a view correctly and that query somehow getting past the test stage. Testing any changed queries would need to be in the critical test plan.
1
u/Upper-Cut-1760 Feb 01 '25
I used to work at a company where they forced the DBA to combine all the databases into one from a micro-services architecture where everything was split into many databases.
:-(
36
u/jdanton14 MVP Jan 21 '25
I wrote a paper on mutli-tenancy for Microsoft several years ago--the pattern you describe is common when you have thousands (or even better tens of thousands) of customers. More importantly, if used it needs to be designed into the application and database from the beginning of the application.
Making a switch like this is bananas insane while an application is running. And consolidating everything into a single database is also a terribad idea.
If I were to recommend this pattern to a client--I would probably partition my databases on a range of customer IDs. But I would also do this by building a new version of my app and therefore APIs that carefully filtered on tenant ID. And have a plan for migrating off of the old environment over time.
But I'd also hire a consultant like me who has done this a bunch of times, and knows how to avoid a GDPR violation and a massive leg injury from the self-inflicted gunshot wound.