SQl Server 2005 Clustering

Question

SQl Server 2005 Clustering

control

SSCommitted

Points: 1630
More actions
May 13, 2009 at 7:52 am

#204294

Guys,
I am planning to setup a window clustering environment for SQL Server 2005. A coworker is perferActive Active, and I prefer Active, Passive.
Can somebpdy tells me the benefit of having Acitive Active Clustering?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

The benefit is that you don't have a passive node just sitting there waiting for a failure. The downside is you don't have a passive node just sitting there waiting for a failure. As long as the server hardware can support both instances in the event of a failover there shouldn't be a problem.

FelixG SSCrazy Points: 2550 More actions · Answer 2

The advantage of the active/active configuration is that you can save on hardware resources (provided that each of the servers in the cluster can handle the workload in the event of a failure).

Since an Active/Passive configuration essentially results in a machine sitting idle waiting for a failure to occur, some DBAs would prefer to put that idle machine to use in an Active/Active configuration. The important thing to note here is that if a failover event occurs, the remaining server will need to service the requests of both SQL Server instances simultaneously - so properly sizing them in the beginning is necessary.

happycat59 One Orange Chip Points: 29387 More actions · Answer 3

In addition to what FelixG said - with an active/active cluster, when a node fails, it is potentially quicker for the (one) services that were on the failed node to start up on the remaining node (give or take the load on the remaining node, of course). I say this because, instead of 2 cluster resources (which I assume would be two or more instances of SQL Server) trying to start simultaneously, you only have one trying to start.

There is a licensing advantage if you use an active/passive cluster. If one node is "normally" has no active resources running on it, you do not need to separately licence the software on that node.

Andy Hogg SSCarpal Tunnel Points: 4585 More actions · Answer 4

Both have their merits.

If you do opt for A-A though you should configure SQL Server on each instance to only use a maximum of half the available memory (minus whatever you leave aside for the OS).

If you allow SQL Server to just take what it wants, then you may find that when disaster strikes and your instance fails over to the other node, there is no memory left for it since the existing instance on that node has taken it all.

For example if you have 14GB in each node, leave 2GB free for the OS and allow SQL Server a maximum of 6GB.

That way if a failover occurs, you have a further 6 GB in the bank for the second instance when it needs to come online.

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 5

I believe (could be wrong) at least 2-3gig (based on boot.ini) is set aside for the os in the kernel. So, you can still take full advantage of your memory even in an A/A cluster by using the "Min Server Memory" setting. This setting is what guarantees SQL Server what memory it will get. When the cluster fails over and there are two instances on the same node, the Instances will steal memory from each other but not more than the required minimum memory.

So, as an example in the 14gig scenario, a min server memory of 4-6gig and a max of 12gig would work just fine. that is, if you don't mind the SQL's stealing memory back and forth from each other.

Tim White

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

2 Tim 3:16 (5/18/2009)
I believe (could be wrong) at least 2-3gig (based on boot.ini) is set aside for the os in the kernel.

The VAS split is specific to 32bit servers which have 4GB of VAS that is generally split 50/50 into user mode and kernel mode VAS. You can adjust this by using the /USERVA or /3GB switch in boot.ini which changes the split to provide more memory to the user mode VAS taking it away from the kernel VAS. This can and usually is problemattic for servers that have > 8GB of memory, and the /PAE switch should be used along with AWE, not the /USERVA or /3GB switches.

So, you can still take full advantage of your memory even in an A/A cluster by using the "Min Server Memory" setting. This setting is what guarantees SQL Server what memory it will get. When the cluster fails over and there are two instances on the same node, the Instances will steal memory from each other but not more than the required minimum memory.
So, as an example in the 14gig scenario, a min server memory of 4-6gig and a max of 12gig would work just fine. that is, if you don't mind the SQL's stealing memory back and forth from each other.

This is a bad configuration suggestion because it causes memory set trimming which will lead to paging on the SQL Server, which is often a non-recoverable problem. The previously mentioned 6GB max server memory is the correct way of configuring this. One could set Lock Pages in Memory in Enterprise Edition to control the paging issue, but then the instance that has committed the memory won't release it for the other one, so you are back where you started.

Andy Hogg SSCarpal Tunnel Points: 4585 More actions · Answer 7

Thank you for the eloquent explanation Jonathan, that's exactly what I was trying to get at.

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 8

Thanks for clarifying Jonathan. I was trying to just keep the discussion simple on the kernel and I believe what I simply called "Stealing" is the non-technical term for "memory set trimming which leads to paging".

So, help me out a little please;

First, I'm not sure what you mean by "non-recoverable problem"? what non-recoverable problem can occur? thanks.

Also, (assuming the fail-over of the node is temporary), doesn't the stealing, paging, trimming just bring the memory for the SQL instance down to what you suggested as a permanent setting anyway and then allow it to use more when the nodes are separated?

For lock page in memory, I know what the books say, but a few months back I tested this and thought the two sql instances still stole memory from the other when a fail-over occurred. I'll have to go back and review that.

Thanks for the insight and always appreciate the input on this blog.

Tim White

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

I meant to include that 64bit Servers don't have the same VAS limitations as 32bit servers because they have 8TB of VAS, so for those servers, setting a max server memory setting is extremely important or paging will occur. This is the case regardless of whether the SQL Server has 1 or 16 instances, you have to set max server memory for 64 bit SQL Servers.