Rule Of Nerds
When you’re designing the back end of a database, people will have all sorts of ideas.
Normalization, partitioning, referential integrity, and, usually, trying to figure out what to do when you have more than one client.
If your application is user-focused (like Stack Overflow), you don’t have to struggle too much with the idea of isolation. But when your application is geared more towards supporting multiple entities that have one or more users, things change. Sort of like how Stack Overflow manages all the other Stack Network sites.
Were you to ask me which model I prefer, it would be every tenant getting their own database. Your other options are:
- Everyone all mixed in together like gen-pop
- Using separate schemas inside a single database
It’s hard to see any real upside to those propositions unless you’ve poorly chosen a cloud solution with limitations placed on number of databases. Yes, I’m looking at you.
The problems you run into with everyone mixed in together are just far too grand, especially if the concept of permissions comes into play within a tenant.
If a single user can belong to multiple tenants, forget it. You couldn’t get screwed harder if you bought a time share in Atlantis.
Embrace The Suck
Since we’ve discarded the lunchroom in Oz approach, let’s talk a little but about why the schema-per-tenant approach doesn’t work.
It doesn’t buy you anything from a maintenance perspective without dumping a whole lot of awful complexity in your lap. Worse, there’s no good tooling available to analyze anything in your database schema-by-schema.
Is your client in schema1138 unhappy? Okay, try to find out what’s going on with them in the plan cache or Query Store. I’ll wait. Look at how their indexes are used. I’ll be here. Wait stats? Blocking? Deadlocks? Hey, I’m comfy. Take your time.
Every client I have who started with this pattern has inevitably built a tool to move bigger tenants out to their own database to isolate them and figure out their problems, or move them to their own SQL Server instance. You will, too, if you choose… poorly.
While I hate SQL Server’s plan cache for finding performance problems, we are totally stuck with it as part of the query execution process. As much as I wish Microsoft would replace it with Query Store, they’re too busy trying to find ways to squeeze cloud customers for all they’re worth without the benefit of a lubricant to work on anything practically useful to SQL Server users.
So you end up with the same problem as you do with the database-per-tenant approach. Your plan cache has limitations on size and number of plans. When the same query executes from a different schema or database, a new entry in the plan cache is made.
This leads to a lot of plan duplication in there, and a lot of plans getting flushed out when those limitations are hit. The difference here is that it’s a whole lot easier to figure out what went raunchy for a specific database using Query Store than it is to figure out what’s gone sour in a specific schema in a specific database using… anything. Anything at all.
Perhaps thoughts and prayers are in order.
Rebalancing
I’ve run into far too many clients struggling to keep the lights on with the all-in-one approach, and the schema-per-tenant approach. Especially with the all-in-one approach, you end up with something like a TenantId column in every table, and in every index, sometimes even filtered down to specific problem-tenants. It’s kind of awful to watch.
This is true of many different areas, whether it’s scalability, security/compliance, performance, RPO and RTO, and of course costs.
Some readers may call out things like log backups, and Availability Groups. Sure, I get that! It might be hard to get a log backup job to run across a bunch of databases every RPO-minutes. It’s also trivial to put groups of databases into multiple log backup jobs so that you don’t have to worry about that.
For Availability Groups, I’ve seen more than a few cases where they were falling behind because there were hundreds of schemas in a database all making lots of tiny changes. Keeping lots of databases synchronized is certainly more responsibility, but no less in danger of things falling dismally behind.
Let’s say you have 400 databases. That may take way more worker threads to keep up, but at least they each get worker threads to do that business. A single database with 400 schema in it doesn’t magically get a bunch of additional worker threads to keep data moving, and it’s foolish to think otherwise.
When you start with the more sensible approach — database-per-tenant — you give yourself many more options. You can move databases to new server much more easily, and you can have different databases on different tiers of service way more easily. Think Standard vs. Enterprise Edition, SLAs, RPO, RTO, HA and DR, etc.
And of course, you can charge extra for those additional services. Don’t worry, after a decade in the cloud, everyone is used to getting their pockets shaken for every additional nicety.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
We’ve had some of those multi-tenant databases in the past. Overall, they worked reasonably well, but you could definitely hit issues if a “smaller” client executed a proc and got it into the plan cache before a “larger” client. Most times not an issue, but we ended up forcing an index hint to make the optimize use a suitable index instead of possibly choosing the “small query result” plan. Biggest challenges were making sure everything checked the Tenant ID to verify that the data belonged to the tenant – and that was everywhere in the code. After that, our biggest challenge was in some form of “self service” reporting because MS’ reporting tools really aren’t designed well for a multi-tenant database where all of the logins come from outside of the system. We ended up using a wrapper to SSRS to allow choosing parameters, but then passing over the hidden parameters (tenant/user/etc) as well to then schedule a report to run and have it ready to pull when done. It worked, but not as pretty as PowerBI or real-time interaction.
I can definitely see where trying to create a schema per tenant would cause issues and in retrospect, the idea of separate databases has a lot of merit. At the time, this was one of the options and I think it was spinning off of a much smaller startup. Of course, I know some in the SQL community have dealt with multi-tenant in the “one database per client” sense, but then had a company wanting to make it all work on one server. So thousands of databases in one SQL instance, which makes intellisense and SSMS go a little crazy. 🙂
I appreciate the analysis of some ways to handle this because it’s not really talked about too often, though I think MS now has a sample database/project for some multi-tenant ideas – the “Wingtips” app/database.
It’s definitely challenging deciding how to approach things.
I’ve had some clients do both database and then schema within database because at the time RDS only allowed ~30 databases per instance. For companies with thousands of small business clients, it would have been a nightmare to have hundreds or thousands of RDS instances with ~1mb databases.
Of course, as those restrictions eased or are dropped entirely, making new architectural changes becomes quite difficult because of how deeply ingrained the previous decisions are.
I worked with a company that wanted to be multi-tenanted. When I pitched the idea of a database per tenant, I got a TON of pushback from the developers. Years later, all the architects agreed with that decision. It was easier to troubleshoot the “bad” tenants. We could easily move the tenants around, decreasing the “noisy neighbor” problem. When we had 2 tenants responsible for 80% of our workload, we were able to scale those tenants up easily. The customer was willing to pay for the idea that they would be on their own “hardware.” I am a fan of that architecture.
Yep, that’s the same experience I’ve had. All the other solutions are ticking time bombs for performance and manageability.