October 11, 2010 at 1:38 am
Hi if any one knows ACtive-ACtive Clustering SQL server 2005 disadvantages.
Please help me out.
October 11, 2010 at 1:49 am
-- Gianluca Sartori
October 11, 2010 at 4:32 am
Essentially if you have two servers and want to leverage the most out of them you would use an active/active (if one fails the instance fails over to the second node and vice versa). Obviously this is good when everything is fine cause you get two servers, double the grunt and can share databases between the two.
Essentially what you will have are two active/pasive clusters (that fail to each other).
The situation gets better if you can have a third node which can be a shared passive notde for the two active nodes. BUT and heres the disadvantages: If you have a shared passive for both active nodes, what happens if they both failover to the passive? Can it handle the load? Remember to be part of a Windows cluster the hardware must be identicle, so following failover your active instances will be sharing 1 node so will have less the 50% of the resources they had.
We are in a fortunate position where investment for infrastructure has never been an issue. We have two active/passive clusters (2 seperate nodes per cluster) and a thrid node (per cluster) at SCF for database mirror failover.
Back to your questions: As i see it the disavantages with a/a are that it is not a high availability solutiion r ather a high performance solution. What happens in the case of a failure? What is acceptable to the business? Only you or your boss can answer these questions.
For my money, i'd rather have half the grunt but now i am protected! A/P all the way.
Hope this helps.
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 12, 2010 at 5:53 am
Microsoft pushes Clustering as the next best thing to sliced bread. In my experience we have had more downtime with clusters than with a regular server. Clusters are very touchy and the smallest things can make them fail over. 100% CPU usage for a certain length of time can cause them to fail over... We have two active/passive clusters here and three active/active clusters here. Installing SQL 2008 in a clustered environment is a royal pain. I had so many problems just getting it installed I had to call support.
October 12, 2010 at 6:08 am
Markus (10/12/2010)
Microsoft pushes Clustering as the next best thing to sliced bread. In my experience we have had more downtime with clusters than with a regular server. Clusters are very touchy and the smallest things can make them fail over. 100% CPU usage for a certain length of time can cause them to fail over... We have two active/passive clusters here and three active/active clusters here. Installing SQL 2008 in a clustered environment is a royal pain. I had so many problems just getting it installed I had to call support.
Sorry to hear that. I'm afraid to say that your clusters have obviously not been configured or tuned correctly. Ours are bomb-proof!
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 12, 2010 at 6:50 am
I have to second the previous comment. We've been running clusters (Sql Server and Oracle) for years and the only time these clusters fail over is when we manually fail them over for patches. They have been solid.
For a few of our clusters, I run Active/Active and as said previously, you have to account for the scenario where both instances are running on the same node. Essentially you want to be sure to spec the servers properly. For example, we set max server memory for each instance to about 45% of the total memory.
Side Note:
There was a post here some time ago where someone dynamically configured the memory on failover/startup so they could use up to 90% of the memory when the sql instances were running on their separate nodes.
October 12, 2010 at 7:07 am
In response to Markus's comment - we have many clusters, ranging from 2-node A/A clusters to a 6-node N+1 (N Active / 1 Passive) cluster for our SQL instances, and they all work very well, and are stable, the biggest issues we have are hardware related, not clustering. And, if your machines are running at very high CPU loads, your servers are probably underpowered, or you have runaway processes.
In response to Schadenfreude-Mei's comments (1) to be part of a Windows cluster the hardware must be identicle, No, the hardware doesnt have to identical across the cluster, it just has to be certified (for Win 2003 or earlier), or pass validation for Win 2008. (2) disavantages with a/a are that it is not a high availability solutiion r ather a high performance solution A/A clusters are still High-Availability, just with a extra risk that the remaining node may not be able to handle the load without degraded performance; to be a High-Performance solution, it would require a Load-Balancing cluster, not a Failover cluster.
The biggest issue you should watch for is if all nodes except 1 failed, could the remaining node handle all the instances. Benchmark the load (CPU / memory / IO), if you can, to get a good indication of the resulting load.
October 12, 2010 at 8:20 am
A/A clusters are a good way to maximize hardware investment. One thing I advise my clients is to have a monitoring agent of some flavor trigger on a failover and automatically adjust the sql server max memory on BOTH systems to an appropriate value that totals to the amount an indivitual machine should take on the given hardware. This will avoid memory contention problems. I also advise being cautious with Lock Pages in Memory setting on these servers for same reason.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 12, 2010 at 9:42 am
Markus (10/12/2010)
Microsoft pushes Clustering as the next best thing to sliced bread. In my experience we have had more downtime with clusters than with a regular server. Clusters are very touchy and the smallest things can make them fail over. 100% CPU usage for a certain length of time can cause them to fail over... We have two active/passive clusters here and three active/active clusters here.
I work with 3 different clusters at present and they all perform without issue, have you verified your cluster configurations?
Markus (10/12/2010)
Installing SQL 2008 in a clustered environment is a royal pain. I had so many problems just getting it installed I had to call support.
it's fine if you slipstream the SP1 during install time or create a merged drop installer
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 12, 2010 at 9:19 pm
TheSQLGuru (10/12/2010)
A/A clusters are a good way to maximize hardware investment. One thing I advise my clients is to have a monitoring agent of some flavor trigger on a failover and automatically adjust the sql server max memory on BOTH systems to an appropriate value that totals to the amount an indivitual machine should take on the given hardware. This will avoid memory contention problems. I also advise being cautious with Lock Pages in Memory setting on these servers for same reason.
Kevin, what type of monitoring agents are these that will automatically adjust the sql server max memory?
October 13, 2010 at 2:13 am
jamesn 89076 (10/12/2010)
TheSQLGuru (10/12/2010)
A/A clusters are a good way to maximize hardware investment. One thing I advise my clients is to have a monitoring agent of some flavor trigger on a failover and automatically adjust the sql server max memory on BOTH systems to an appropriate value that totals to the amount an indivitual machine should take on the given hardware. This will avoid memory contention problems. I also advise being cautious with Lock Pages in Memory setting on these servers for same reason.Kevin, what type of monitoring agents are these that will automatically adjust the sql server max memory?
You could do it with a scheduled task using a watcher (for the failover event) and then run a.bat file with a sqlcmd command. Simples. 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
February 11, 2011 at 11:41 pm
I have a scenario here, what if we want to implement 3 active & 1 passive clustering, our active servers will be running more than one instances just becoz of security & better administration (like if we need to restart one instance it should not affect others). for simplicity, consider that we have 42 DBs all equally
divided on 3 active server. we will be using SAN. now my queries are:
1) is there any thing wrong in my understanding? like this is not possible or not possible this way
2) how many instances & DBs our passive server have? as any if the active server fails it should handling the request
3) will the recommended solution be different of we go for SQL server 2008 r2?
thanks
February 12, 2011 at 9:00 am
That should be OK. Restarting or failing over one instance from one server to another won't effect the others. Keep in mind you will need enough CPU and Memory to run all three instances on one physical server in a just in case moment.
February 14, 2011 at 1:59 am
I agree with Markus.
It wouldnt be my first choice to run 3 instances on one node, but the concept is fine.
As Markus says make sure you have enough CPU and Memory etc... Remeber that with 3 instances for 42 db's you will be wasting a fair amount of Mem and CPU not to mention relying on the on OS.
Adam.
Adam Zacks-------------------------------------------Be Nice, Or Leave
February 14, 2011 at 8:17 am
Do you mean that 42 DBs will be less for 3 servers? i would prefer 3 a and 2 p but here we have cost constraints
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply