0

I currently use MySQL as the database solution for my product; however, recent problems have prompted me to research a migration to SQL Server.

I use a database-per-customer design and discovered today that SQL Server has a 32k database limit where MySQL doesn't have a hard limit.

Given I expect to exceed this limit, I'm wondering what the best approach to dealing with this limit is?

Obviously adding more server nodes is an option, but with clustering setup for failover, each added node would require adding multiple Window Server instances.

It looks like you can run up to 50 instances (Named Instances) of SQL Server on a single Windows server, so having multiple instances seems to be a solution as opposed to having to add more nodes.

Are there any other solutions to this limit?

Thanks.

1
  • The quickest change would be to use a schema per client rather than a database per client. This can then use one database. You are going to run into general performance issues due to the way that the connections need handling so you may want to further transform and have a single schema and use something like row level security (aka virtual private database) to prevent client A seeing client B data, it would take more of a redesign but it has better potential. Jan 23, 2023 at 15:41

2 Answers 2

2

You'll hit many other issues before you have an issue with the 32k database limit, just with the required threads for background tasks, connections, and a paltry 1 request for 10% of all databases you're looking at a very large server.

I'd advise against trying to do this, and base the number of tenants on actual resource usage rather than a finite hard limit which will not be hit in real life.

4
  • I run on MySQL now with about 10k databases and it's not a problem and resource usage is pretty low. Connection pooling is done at the application level so the number of connections being managed would be same regardless of whether there is 1 database or 10k.
    – mwarble
    Jan 20, 2023 at 20:21
  • @mwarble MySQL on a Windows server or Linux?...if the latter, presumably things use less resources overall because of the platform, aside from 10k databases being a significant difference from 32k. As much as I love SQL Server, I wouldn't doubt MySQL may require less resources to operate as well, generally speaking (though I don't know for sure). But as with most database things, it's hard to tell what'll actually happen in your specific scenario, because of all the variables, until you test it yourself. From a management perspective alone I would consider multiple servers or a cloud solution.
    – J.D.
    Jan 21, 2023 at 0:17
  • @mwarble Also, you have a separate database for each client, but a single application that manages the database connections of all of the clients, and will prevent some clients from being able to connect to their own database concurrently while other connections are in use?
    – J.D.
    Jan 21, 2023 at 0:19
  • Connection pooling does help, but not with concurrent requests, all will require at a minimum, 1 thread in the engine. This doesn't even cover the issues you're going to have with updates/upgrades, HA/DR if needed, recovery (after patching, for example), etc. We don't know what issues you're hitting with MySQL but each RDBMS has their trade offs. Jan 21, 2023 at 15:13
1

, but with clustering setup for failover, each added node would require adding multiple Window Server instances.

With clustering you can add active nodes to a single cluster without increasing the number of passive nodes. You cluster can start of as a 1+1 cluster (one active, one passive), and grow to a 2+1, 3+1, 4+1 etc. As with the databases well before you hit any technical limit of how many nodes you can put in a cluster you will want to start having multiple clusters.

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.