1

My application is centered around self-contianed "workspaces". For many really good reasons (everything from management to security), we have always had a one-database-per-workspace architecture. Each database has identical schema, stored procedures, triggers, etc. There is a "database of databases" that coordinates all of this. Works great.

The problem: scalability. It was recently proposed that a customer might want to have 100,000 workspaces. Obviously this is a non-starter for one SQL instance. Plus, each workspace might be rather small, but there'd also be a very wide size distribution - the biggest workspace could be 100x the size of the median. The top 1% of workspaces could easily constitute 90+% of the rows across all workspaces.

I'm looking for options for rearchitecting things to support this scenario, and here are some things I've considered and the issues I see with each.

  • Keep the multi-database architecture but spread across multiple SQL instances. The problem is cost (both administrative and infrastructure). If we stick to a limit of 1,000 DBs on each instance, that's still 100 instances, spread across who knows how many actual VMs. But since so many of the workspaces will be small (much smaller than our current average), the revenue won't nearly scale accordingly. So I think this is probably out of the question and I'm focusing now on single-database architectures.

  • Every workspace shares the same tables, indexed by workspace ID. So every table would need a new workspace ID column and every query needs to add the workspace condition in the WHERE clause (or more likely every real table is wrapped in an inline table-valued function that takes the WorkspaceID; anyway...) The primary key of every table would also have to be redefined to include the workspace ID since not every PK now is globally unique. Programming-wise this is all fine, but even with proper indexing and perfect query design (and no, not all our queries are perfect - the dreaded row scan still happens on occasion) is there any conceivable way this could perform as well - for everyone - as separate databases? More specifically can we guarantee that small projects won't suffer from the presence of big projects which could be taking up 100x more rows than the small ones? And what specific steps would need to be taken, whether it be the type of index to use or how to write queries to guarantee that the optimizer always narrows things down by workspace ID before it does literally anything else?

  • Partitioning - from what I've read, this doesn't help with query performance, and it appears MS recommends limiting tables or indexes to 1000 partitions so this also won't help.

  • Create the same set of tables but with a new schema for each workspace. I thought of this because there are no limits to the number of tables a database can have other than the overall 2G object limit. But I haven't explored this idea much. I'm wondering if there would be performance concerns with 100,000 schemas and millions of tables, views, stored procs, etc.

With all that, here is the specific question - What specific features of SQL Server, and/or general strategies, including but not limited to things I've considered, would be most useful for maintaining a large number of self-contained data sets with identical schemas in a single giant database? To reiterate, maintaining performance as close as possible to a multi-database architecture is of top priority.

And needless to say, if any part of my assessment above seems incorrect or misguided I'd be glad to be corrected. Many thanks.

12
  • How many workspaces does a customer typically have? The multi-tenancy database model usually revolves around 1 database per customer. Schemas and sometimes Row-Level Security are ways to control access and improve management within a single database. I'd be inclined to have this customer put all workspaces in the same database anyway. Maybe explaining more on what workspaces are and how they're used in your application may help conceptualize a better way to manage this scale for this particular customer.
    – J.D.
    Aug 18, 2023 at 3:22
  • Welcome to the DBA.SE community. SQL Server can scale to up to TBs of data and still perform quite well. It's sometimes a question of How much hardware do I want to invest into the solution. Providing some details (via edit) on the size of records, tables, database might result in a recommendation. However, even providing these details might not result in a good recommendation. Currently there just isn't enough to go with. This question might be closed as opinion-based or Tip of the iceberg (requires consultant).
    – John K. N.
    Aug 18, 2023 at 9:52
  • @JD yes as I mentioned I'm looking at 1 database architecture at this point and looking for help identifying what features of SQL Server would be relevant to this change. As for how the workspaces are used, an analogy would be a movie production studio where each workspace is a movie. They are completely self-contained and logically isolated. Users are assigned to each workspace individually. If not for the specified as well as practical database count limits with SQL Server this would remain a multi-database architecture indisputably. But I need to get around that limit somehow. Aug 18, 2023 at 11:42
  • @PeterMoore Unfortunately I think your analogy might've made this more confusing, as a database modeled for movie production studios, the Movies would all be in a single table. If a single studio has its own database, and they wanted to make 100,000 or even 1 billion movies, it would be no problem and make sense to put that all in a single Movies table. I have a feeling workspaces don't operate exactly the same way and don't belong in a single table - but maybe I'm wrong? You'd probably be better off describing some details in actuality of what they are as opposed to an analogy.
    – J.D.
    Aug 18, 2023 at 12:39
  • @jd there are dozens of tables in each workspace. Each workspace contains every detail about the project from payroll to staffing to work product to revenue. The point is that each database (in the multi-arch) contains everything about the project relevant to the application and is completely unrelated to every other project. It is absolutely a fact that a multi-database setup is more sensical but I have to work around SQL Server's limitations. I'm sorry but I'm not comfortable going into more detail about the specific application, but I do think the problem has been clearly stated. Aug 18, 2023 at 14:56

2 Answers 2

1

More specifically can we guarantee that small projects won't suffer from the presence of big projects which could be taking up 100x more rows than the small ones? And what specific steps would need to be taken, whether it be the type of index to use or how to write queries to guarantee that the optimizer always narrows things down by workspace ID before it does literally anything else?

It's not all-or-nothing. You can retain your multi-database architecture while enabling multiple projects to share a database. Then you only store multiple workspaces in a database for smaller workspaces.

The normal indexing approach is to add the WorkspaceID as the leading column of all primary keys, which will physically co-locate the rows for a particular Workspace.

You'll need a procedure to delete a Workspace from a database. Then to split a database, just restore a new copy of it, and delete Workspaces from each one.

5
  • HI David, thank you for this. So I understand clearly, are you saying that if I ensure the WorkspaceID is always the leading column of all primary keys, and assuming I use a simple "WorkspaceID = xxx" in every WHERE clause, then queries for Workspace A with 100 rows, vs. Workspace B with 1,000,000 rows, will each respectively perform comparably to them being in separate databases? And will this be true of transactions (tempdb, etc.) as well? And finally I wouldn't need to do anything such as partitioning, filegroups, or any other imaginable tactic to realize this benefit? Aug 20, 2023 at 15:40
  • No. Not quite. Adding WorkspaceID=xxx as a literal, not a parameter will enable separate query plans per workspace, but the statistics will not be tracked per Workspace, so you may still get bad plans. So it's best to separate large Workspaces into their own database. Aug 20, 2023 at 15:55
  • Gotcha that's what I thought. :/ If I split them out into tiers like you suggest (and index properly), would the small projects all in the one DB at least each have query performance comparable to that of individual databases, assuming a relatively even distribution? Many thanks, this is exactly the kind of input I was hoping for. Aug 20, 2023 at 18:00
  • Should be similar, but you should definitely monitor performance. Query Store is your friend: learn.microsoft.com/en-us/sql/relational-databases/performance/… Aug 20, 2023 at 18:02
  • Awesome. Well by far this has been the most helpful suggestion and info I've come across, so thanks again. Aug 20, 2023 at 21:03
0

Keep the multi-database architecture but spread across multiple SQL instances. The problem is cost (both administrative and infrastructure). If we stick to a limit of 1,000 DBs on each instance, that's still 100 instances, spread across who knows how many actual VMs. But since so many of the workspaces will be small (much smaller than our current average), the revenue won't nearly scale accordingly. So I think this is probably out of the question and I'm focusing now on single-database architectures.

Given the current architecture, this is the obvious way to scale this. You would need to find someway to balance the load. Maybe you could have up to 25,000 small workspace databases on some servers but not more than 20 large workspace databases on other servers. There is an administrative cost for this but the infrastructure costs should be about the same as SQL Server is licensed per core and the number of cores, as well as memory and storage, should be about the same even if spread out over a number of servers.

Any of the other solutions will reduce the isolation of the data which might be a problem.

2
  • Thank you @aardvark. I agree separate databases has many benefits, including security, as I lead with in my question. However as I also mentioned the revenue is not necessarily going to scale proportionally to the database count, which, put more simply, means I think this might get too expensive to administer. Aug 20, 2023 at 15:46
  • What I could see working is having two project tiers, as suggested by David Browne above - so you have to specify when you create the project (and then perhaps pay a different rate) - and then putting the small tier projects in one DB and the large ones in separate DBs. But I don't see an economically viable way around keeping at least the small projects in a single DB. Even 25,000 databases is far too much for a single SQL instance according to every source I've found for that question. Aug 20, 2023 at 17:58

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.