When The Client Has 1100 Databases What Is The Best Solution?
When the client has 1100 databases what’s the best solution? In a previous article: MySQL Remote DBA Consultant helped the client to scale up, We discussed a situation where a client had a MySQL server that had 1,100 databases and was using it with a SaaS application. We do not want you to think that having separate databases per client is always a bad idea. There are some real advantages with using separate databases for each client, so we want to go over those also so that you can determine the best way to design your database and structure for your SaaS app.
When there are too many databases, it is going put a stress on the server?
A couple ground rules first. These suggestions pretty much apply to SQL Server and MySQL/MariaDB and probably other database products also. Having 1,100 databases with each database having 450 tables in each database is going to put a real stress on a server, regardless of what database product is being used. If each database contained less than 100 tables, it would have been significantly less work for the database server to do its job. If there had been some temporary tables used, that might also have helped.
What’s the perfect answer for the right solution?
For a remote database administrator, there is no one perfect answer, but here are the general guidelines that we try to stick with. Again, sometimes there are reasons to adjust this, but this will generally lead you in the right direction.
Having a separate database could be an advantage in some cases:
Having a separate database for each client allows you to be sure that no client can lock/block/deadlock any other clients data. This can be a big plus. When re-indexing for example, only that client's table is potentially being locked and re-indexing will be faster since it is only working on that client’s table, not a much larger table that has all the client’s data in it. It also is much easier to move or delete a client's database as it is already separate.
For a single database strategy, MySQL Consultants need to update each one separately or automate the process:
There can be disadvantages also. For example, if you want to add or change a column in a table, or add a new table, you cannot just do it in one place, you must do it in every clients' database. Of course, there are tools that make that much easier. We can highly recommend SQL Delta and Navicat, but there are many others tools that would also help. Keeping the database schema (structure) in sync with every other client’s database is much easier when you have a nice tool to use.
It could be easier to deal with one database at the time but will require more maintenance work:
If you find that you need to eventually move some clients to another database server to split the load, it is much easier if each client has its own database, and if a client were to go away and need to be removed, you simply need to remove the one database, much easier than developing a script or process to remove all the records for the client out of each table in one big database. In general, our SQL Consultants deal a lot with clients that have issues with SaaS application. In today’s world it is quite popular to provide access to an application as a service.
We do help with MySQL/MariaDB and provide Remote DBA services that covers MS SQL Server as well. In general, these calls for companies with SaaS Apps are urgent and requires immediate intervention helping them right after the initial call.
What do we do? A Single database for all clients? Or just one database per client? Which way is Best?
Now it might seem like we are changing our mind and saying that a separate database per client is the way to go. We are not, there are valid reasons for keeping all the data in one big database, and reasons for separating them, it just depends on the circumstance and what architecture is best for your application.
Conclusions for SQL Consultants: Decision Making Depends On The Scenario For The SaaS Owner:
Since we do MS Server and also MySQL/MariaDB, our remote DBA consultants have seen both solutions work, and both solutions fail, and it is not impossible to change from one to the other if needed in the future. Just pick the best one now and have some fun.
Check out our Remote DBA services:
For more info about Finding MySQL Consulting Experts at Your Convenience and MS SQL consulting services that we provide please call us: 732-536-4765 or send us an email via our Contact Us page.