April 28, 2014 at 11:37 pm
Hi everyone
We're looking into setting up a SQL cluster for our app.
Our current setup is based on a very high number of small databases.
We are looking into setting up a cluster, but we are getting mixes opinions on whether to convert all our individual databases (about 5000) to one big individual database, or keeping our current setup.
Some say that it would be easier to manage one large database, and ensure high availability, while some say that managing a huge number of small databases would not be an issue. Any thoughts on this ?
We also want to make sure that should we scale and grow, and we get to 50000 databases, clustering etc. will still never cause issues.
Any thoughts?
Thanks
April 29, 2014 at 1:22 am
dexdev (4/28/2014)
Hi everyoneWe're looking into setting up a SQL cluster for our app.
Our current setup is based on a very high number of small databases.
We are looking into setting up a cluster, but we are getting mixes opinions on whether to convert all our individual databases (about 5000) to one big individual database, or keeping our current setup.
Some say that it would be easier to manage one large database, and ensure high availability, while some say that managing a huge number of small databases would not be an issue. Any thoughts on this ?
We also want to make sure that should we scale and grow, and we get to 50000 databases, clustering etc. will still never cause issues.
Any thoughts?
Thanks
5000 databases...well it is up to your requirement and kind of operation you are going to perform on these databases.Also instead of getting all in one better to group based on their usage, memory and operations.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
April 29, 2014 at 2:52 am
dexdev (4/28/2014)
We are looking into setting up a cluster, but we are getting mixes opinions on whether to convert all our individual databases (about 5000) to one big individual database, or keeping our current setup.
very mixed and confused i would have said, even if you cluster them you'll still have lots of small databases, they don't automatically become one database!
dexdev (4/28/2014)
Some say that it would be easier to manage one large database, and ensure high availability, while some say that managing a huge number of small databases would not be an issue. Any thoughts on this ?We also want to make sure that should we scale and grow, and we get to 50000 databases, clustering etc. will still never cause issues.
Any thoughts?
Thanks
It's obviously easier to manage a single instance even when clustered than it is to manage multiple instances. You need to distinguish the difference between an instance of SQL Server and a SQL Server database, don't get them confused with Oracle terminology\topology.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 29, 2014 at 6:36 am
First off, there's a max of 32,767 databases per instance, so you're not getting to 50,000 any time soon.
In general, if you cluster a SQL Server instance, you move all the databases there. Any problems you have with management of your 5,000 databases will just move there. Nothing special will be added or taken away in terms of management of these databases. You'll just have the added capabilities (and headaches) of the high availability cluster. The thing to think about is, how long does it take to start your server now? All those databases going through recovery will also have to go through recovery in a failover situation. That will be a limiting factor in the amount of downtime you get from the failover.
As to whether or not you should put 5000 databases into one database, I guess the question back at you is, why did you split it into 5000 to begin with? If that situation, whatever it is, is still in place, how will moving these things into a single database deal with that situation?
Also:
We also want to make sure that should we scale and grow, and we get to 50000 databases, clustering etc. will still never cause issues.
Never is a word no one can guarantee. Clustering (and any other technology) solves some very interesting problems AND introduces wonderful new ones. You're going to have issues with clustering. Guaranteed. But, does the solutions it provide outweigh any of these issues, well, that's something you'll have to determine. Do a search for standard clustering issues so you understand what choices you're making.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2014 at 10:10 am
Hi Grant
Thanks for the post, very helpful. I definetly didn't know about the 32K+ limit per instance. With this said, I guess we could install several instances, but that would probably complicate things like clustering even more, wouldn't it?
The initial reason we went with multiple dbs is simply because we built this new app from another one which was already using multiple dbs. So, we basically went with the same model for simplicity. In that other scenario, it made sense, because we were only working with a few databases (under 50), and we wanted each db to be isolated so we can treat each client seperatly, provide individual backups quickly, enhance security and things like that.. And, clustering was not needed.
In this scenario, the dbs are much smaller in size, but because we're targeting hopefully have 5000+ users, that would translate to 5000+ dbs, and that number would only grow as we grow our users base. That's why we're revisiting the whole idea while we still have a relatively low number of dbs right now, making the transition back to one db still possible.
Any thoughts?
April 29, 2014 at 10:28 am
I don't really understand the one-to-one mapping between users and databases. Please explain a bit more what the databases are used for and why you need one per user. Note that an alternative is to have one database with 5000 schemas, one per user but that seems nuts to me, even though it would make backup/restore and clustering much easier to manage.
April 29, 2014 at 10:38 am
Sorry, I said users, I should of said "suscribers". For each subscribers, we store their contacts, data, etc.. so that's why each one has his own db right now. And, by the way, not sure we would need different schemas, all the dbs are identical.
April 29, 2014 at 11:30 am
Just in terms of management, loads and loads of databases is a bit of a nightmare. But, from the standpoint of separation of customers, it's not a bad idea. Also, as means of performance enhancement, the concept of sharding the data is to split it into multiple storage locations (with multiple drives, etc.). So again, this isn't, on its face, a bad idea. It's primarily a question of function. Can you do what you need this way, or are you going to have to switch to different mechanisms at some point anyway.
Also, another thing to take into account with multiple instances on your server is that you then have to share the resources, cpu & memory & disk, among these instances, which creates additional management overhead. As the amount of databases and instances grows, you're probably going to be looking at going with different servers anyway.
BUT, this could all be premature optimization. How is the system behaving now? You're moving to high availability, but how's the performance? That's going to drive changes in what kind of system you set up. If you're not stressed yet, I sure wouldn't start making changes without a very thorough plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2014 at 1:52 pm
Here are a couple of things to consider:
1) Backup. As much fun as it will be to have that many databases, there will be an equal multiplier of database and log backups. Also, the backup job that has to issue thousands of backup commands will run longer than backing up the same amount of data in a single database. And you'll need to come up with some sort of restart strategy if the backup job dies in the middle.
2) Restore. But if you have the occasional need to restore a single subscribers data, it will be a whole lot easier with just a small database. If all of the data was lumped together, you'd have to restore somewhere else, and more over only the required data.
3) Look at storage replication instead of SQL Server backups, and see if they will mitigate 1 & 2.
4) Privacy. If there is any possibility of one anybody looking at data that is not their own, the separate database option looks better. This could be solved by having a separate schema for each subscriber, which may by slightly, bit not much more than that, less of a headache than all of those databases.
4) Clustering has no effect on the one vs. many database issue.
5) You will probably need to develop some sort of framework to manage all of the db's or schema's. Once you have that in place, it should also be possible to use multiple instances to keep the number of databases per instance manageable. It should also help manage the security issue in 4 above.
April 30, 2014 at 12:37 pm
Thanks to all for replying, this was very informative. Seems like the correct solution would be to go with one larger db. I'll continue to do some digging, but I do appreciate all the replies.
April 30, 2014 at 1:46 pm
dexdev (4/30/2014)
Seems like the correct solution would be to go with one larger db.
Clarify what you mean by one db, do you mean 1 instance?
The 2 terms have very different meanings
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 2, 2014 at 10:02 pm
You have a great idea here. Clustering gives you HA for the instance (memory structure, internal storage engine and background processes) but maintains a single point of data. As far as having many databases, that will pose a management struggle for the DBAs so consider consolidating them based on the various applications and / or app functions (if they are all in use by one app).
That way, you will have fewer but larger databases and you'll have to revisit your storage and maintenance strategy for them.
How many tables exist per database and what are the sizes of these databases? You might be able to consolidate but if not, I'll suggest using multiple instances to manage them (which means changes to the application connection string(s).
Please feel free to reach out for help more via email (patrick.oshioke@consultant.com).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply