November 10, 2008 at 3:13 pm
I'm a DBA in a software as a service company who maintains more then 70 instances of SQL Server 2005. For our main application we have 2 production clusters, a 3 node 32 bit cluster (12 instances) and a 3 node 64bit cluster (hopefully 9 instances) which we are currently migrating to.
As a software as a service company we give each customer their own database on the cluster. We have found that SQL Server 2005 isn't tailored to this unique architecture and I was wondering if anyone else is in charge of a similiar environment. I'd like to talk to others and possibly start a thread documenting the pain points & best practices when dealing with this sort of architecture as I have not encountered alot of useful documentation on the internet yet. So anyone reading this who is in a similiar situation please respond!
Things we have learned...
When 32 bit SQL Server 2005 instances are filled up with more then 500 db's a piece they have a hard time managing the buffer pool.
When you try to use built in data maintenance jobs expect them to never finish. You need to write them to use multiple threads because performing the same action sequentially across all of the DBs on an instance takes forever.
Backups at night will fill your sql server error logs quickly with the successful backup messages. Use traceflag /t3226 in your configuration manager
Node failover takes longer as it needs to perform dbcc checkdb across all of the databases on the instance.
Lots of 3rd party tools will freeze up when you attempt to do any sort of work on the instance. Usually it freezes when enumerating the databases. Even SSMS will take forever to perform some actions.
more to come if anyone else is interested..
November 10, 2008 at 4:03 pm
That is a lot to discuss but not really any questions. If the question is "Can SQL Server handle this type of environment?" the answer is yes but as always, it depends on a lot, configuration of the hardware, disk subsystem, and of course the application layer. The move you mention to 64 Bit will help a lot as I am sure you aware.
As to the other points it would be good to get some details so that more indepth discussions / questions can be worked through.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 10, 2008 at 8:24 pm
I work with a similar environment albeit not at the same scale, and I'd certainly be interested in anything on this topic - I'm sure I'll hit a lot of the issues you have seen at some point.
A couple of questions for abair34:
Do you use any failover technologies other than clustering? Did these change as the number of databases grew?
Do you have to support a 24x7 environment or are you able to balance the load by timezone?
November 11, 2008 at 7:12 am
Thanks for the reply,
We are in the middle of moving to a new storage system to hopefully allow us to do block level replication to our DR location. We have had quite a hard time finding any solutions which will allow us to do any mirroring, as alot of technology was not written to quiesce 3000+ databases at once. Thats why we've ended up looking at the block level solutions. I'm not a fan of filter drivers but it seems like a necessary evil at this time.
We have a 24x7 global application but we do create our new user databases across different instances based on their timezone. This allows us to run maintenance jobs at times where it will least impact the customer. It also allows us to roll out updates to our application when users are not calling customer service because we disabled the system during the middle of their working day.
Has anyone else found a mirroring solution which works in an architecture like this? How do others in this scenario roll out schema changes when the application updates?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply