August 9, 2011 at 6:20 am
I'm just curious what others are doing with their clusters. My boss is wanting each major application database to have its own instance in the cluster which means 10 instances across the cluster (5 across each active node with the potential for one node to have to host all 10.... ahhhhh......).
His sole reason for this is patching as my original plan was 1 instance per node (2 in total), but he's saying what happens if App A is in the same instance as App B and App A requires a SP/CU to fix an issue but App A hasn't been validated again we're stuffed so he said we need to split them all into separate instances.
I wondered what the rest of you do in situations like this with SPs, one instance per node, multiple instances per node, etc, etc.
Appreciate some feedback 🙂
August 9, 2011 at 10:29 am
Hi Philip.
One of the big uses of instances to to isolate SQL server installs for administrative, versioning or patching considerations. It can be a big headache to administer but there may be business decisions as to why this is to be used. Are you running SQL2000, 2005 and 2008 on the save servers? Instances will allow you to do this.
One of the big wins regarding Active/Active/Passive clusters is that you can save the cost of a second passive server and its licenses. So that you don't overload the Active nodes, you may want to install an instance so it only failover between one active node and the passive node. You may get to a point where you have 10 instances running on the passive node so size the min memory settings accordingly 😎
One thing to remember regarding clustered instances and patching - make sure that you maintain the same level of patching for a given instance on all the nodes that instance is allowed to run on.
The last paragraph may be a bit confusing at first glance but it is not that bad in real life.
Hope this helps.
Regards, Mark.
August 9, 2011 at 10:57 am
My concerns with having 10 potential clusters on a single node is the resources that will be required to make it work.
How do you plan to configure your disk resources for starters? Each instance must have unique disk resources. Furthermore, those disk resources should be designated a unique drive letter (or possibly mount point). Should all instances failover to the single node, you would likely need >=20 drives attached to that instance (depending on how your disks are allocated between log, data and system).
Next you need to consider sizing of those disks and need to ensure that appropriate disk space is available for 10 well configured instances.
I think 10 instances is too many for a single AAP cluster - but that is my opinion.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 9, 2011 at 4:22 pm
Thanks for your thoughts guys, very much appreciated.
It's all SQL 2008 R2 across the whole cluster, and there is talk about having another 3 node cluster and splitting the critical application databases across the two clusters, so that seems to be in the works as well.
One thing I did wonder was, if you get into this many instances, what's the general best approach with MSDTC. Having an MSDTC for each SQL Resource Group seems a bit over the top from my point of view. I thought about the aspect of having MSDTC in its own resource group on each active node and pointing those instances that "home" to a particular node to the related MSDTC instance on that node. Anything generally wrong in doing that?
August 10, 2011 at 6:16 am
there's nothing to stop you having a 5 node cluster with 4 active. The theory is that the "passive" node should support all "active" nodes, but reality is that this situation is unlikely. You may struggle with drive letters ( as previously suggested )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 10, 2011 at 6:32 am
I have done up to A/A/A/A/A/P/P, though bost common is A/A/P
They save a significan amount of money versus just A/P.
And if you look at it statistically, it's unlikely that multiple nodes will be down and need to be rebuilt.
I am 100% in favor of using large clustered systems
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
August 10, 2011 at 6:34 am
How do you guys handle fail-over on big clusters like that out of interest in terms of what fails over where?
August 10, 2011 at 6:37 am
We designate preferred owners
Everything is preferred to be owned by ist's active node, the secondary owners are passive nodes. We try to keep the heaviest db's form being on the same node.
CPU is not an issue, on no node do we average about 20%. The issue is memory, you may have to make some on the fly ajustments is a failover (or worse 2 failovers) happen.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
August 10, 2011 at 2:47 pm
10 named instances, one for each application database seems a bit over the top. That's a fair bit of admin you would be adding for each application. I'd recommend some logical grouping of applications based on fucntionality or business unit and try to keep the numbers down to 3 or 4 intances.
Ask him if he's going to put each application on it's own physical or virtual server in case one app is certified for the Windows SP or CU when another one isn't. If not, then why do it with the database?
There is a slight risk that you may have one app that is cerified for an SP or CU where another isn't, but it is very slight and I can't see it justifying the work. That is unless you know some of your application vendors are very tardy when it comes to certifying their apps.
It should also be rare that you need to apply a CU. Microsoft only recommend you apply these if you experience the spasific issue it fixes, and I've seldom seen a server needing a CU that couldn't wait for the next SP.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 11, 2011 at 6:17 am
We are starting a new cluster with Act/Act/Act/Passive and I cannot find a whitepaper for more than two nodes. Any advice?
Ed Watson aka SQLGator
Microsoft SQL Server MVP
Follow me on Twitter!
Go Gators!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply