December 30, 2010 at 1:29 pm
Thanks.
December 30, 2010 at 2:07 pm
Thanks everyone. These 100 dbs are identical schema wise. We do not have a DR so we will be implementing 2 node active-active cluster for local HA.
December 30, 2010 at 2:40 pm
sqldba_icon (12/30/2010)
Thanks everyone. These 100 dbs are identical schema wise. We do not have a DR so we will be implementing 2 node active-active cluster for local HA.
May I ask why you're going to have 100 identical dbs instead of 1 db with logical divisions?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 30, 2010 at 6:22 pm
One of the many reasons is each db is specific to one client lets say i have 100 clients and i have 100 tables in one db then if this db goes corrupted or something it will effect all 100 clients apart from that changing the schema is changing entire app which is not possible;-). Thanks for your feeback
December 31, 2010 at 8:54 am
sqldba_icon (12/29/2010)
Thanks Gail.I should have mentioned this before. These dbs are not heavily transactional. Lets say i have 64bit sql 2008 with OS 2008 64 bit on a 64 gb Dell server with 4 quadcore's , how many dbs and instances you think is suitable?Thanks
400dbs at 500mb each = 200000mb, so they won't all fit in memory. Now it is simply an exercise of how hard you hit the databases, with how many users, and especially how fast your disk subsystem can feed not-in-buffer data to the server. That is quite a beefy system CPU wise so unless you have REALLY bad IO you should be fine.
As a separate point, I have a client that got up to 7400 databases on a single server that was less than half as beefy as yours. And it was really bad schema and especially code. It took some significant Guruness to get performance up to an acceptable level, but it did work acceptably. I am continually AMAZED at what SQL Server can do and how well it does despite what I have seen clients do to/with it!!
I do highly recommend you get yourself a performance tuning professional to work with you some to ensure your system can scale properly and to help find and mitigate any bottlenecks your app may present (I am sure there will be some unless you have very low-end needs).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 1, 2011 at 10:42 am
Thanks
January 2, 2011 at 12:04 pm
The only reason for multiple instances is if you need to prioritise resources among databases, or for security reasons, or for more tempDBs.
Or for multiple test environments - we have 6 production machines and 1 test machine with 6 instances.
January 3, 2011 at 8:32 am
sqldba_icon (12/30/2010)
One of the many reasons is each db is specific to one client lets say i have 100 clients and i have 100 tables in one db then if this db goes corrupted or something it will effect all 100 clients apart from that changing the schema is changing entire app which is not possible;-). Thanks for your feeback
Ah. I know this scenario. I used to work for a software solutions company that hosted several different companies on their servers. That's a good reason to keep the data in separate databases.
You need to pinpoint your 10 most active clients. They should be the first you move. Then keep an eye on their dbs as you add other databases from most active to least active.
January 3, 2011 at 9:01 am
That was exactly what i was about to do. Thanks
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply