Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

one db versus multipl dbs Expand / Collapse
Author
Message
Posted Monday, April 28, 2014 11:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 10:29 AM
Points: 5, Visits: 16
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
Post #1565757
Posted Tuesday, April 29, 2014 1:22 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 11:45 PM
Points: 562, Visits: 887
dexdev (4/28/2014)
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


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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1565780
Posted Tuesday, April 29, 2014 2:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:56 PM
Points: 6,365, Visits: 13,695
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"
Post #1565806
Posted Tuesday, April 29, 2014 6:36 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 15,739, Visits: 28,147
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1565888
Posted Tuesday, April 29, 2014 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 10:29 AM
Points: 5, Visits: 16
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?
Post #1566034
Posted Tuesday, April 29, 2014 10:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
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.
Post #1566051
Posted Tuesday, April 29, 2014 10:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 10:29 AM
Points: 5, Visits: 16
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.
Post #1566055
Posted Tuesday, April 29, 2014 11:30 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 15,739, Visits: 28,147
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1566068
Posted Tuesday, April 29, 2014 1:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 226, Visits: 2,132
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.

Post #1566121
Posted Wednesday, April 30, 2014 12:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 10:29 AM
Points: 5, Visits: 16
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.
Post #1566552
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse