Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

ACtive-ACtive Clustering SQL server 2005 Expand / Collapse
Author
Message
Posted Wednesday, October 13, 2010 2:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:03 AM
Points: 1,106, Visits: 1,334
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
Post #1003409
Posted Friday, February 11, 2011 11:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 7, 2011 6:27 AM
Points: 6, Visits: 33
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
Post #1062940
Posted Saturday, February 12, 2011 9:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:16 AM
Points: 1,270, Visits: 2,784
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.


Post #1062985
Posted Monday, February 14, 2011 1:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:03 AM
Points: 1,106, Visits: 1,334
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
Post #1063360
Posted Monday, February 14, 2011 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 7, 2011 6:27 AM
Points: 6, Visits: 33
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
Post #1063560
Posted Monday, February 14, 2011 8:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:03 AM
Points: 1,106, Visits: 1,334
mohsinabdul (2/14/2011)
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


Sorry dont under stand your response.
If you have 3 instances, you have 3 lots of system db's, 3 lots of sqlsvr.exe memory usage, 3 lots of clr buffer cache memory (and all the other memory buffers which use mem outside the core .exe)...

What I am saying is running 42 db's on 1 instance, uses less resources (mem and cpu) then 42 db's on 3 instances on the same box!


Adam Zacks

-------------------------------------------

Be Nice, Or Leave
Post #1063591
Posted Thursday, June 2, 2011 11:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 3, 2011 5:28 AM
Points: 1, Visits: 1
We have recently converted an Active/Passive cluster to an Active/Active cluster (SQL2005). I understand that if one of the nodes fails, the remaining node will have to carry all of the load. I'd like to run each of the Active nodes at better than 45% utilization. But, I'm concerned about the consequences of this if one of the nodes fails. For instance, if I plan for each node to run at 60% utilization, logically, a failure would result in a single node at 120% utilization. NOT GOOD.

Is there a way to restrict the resources available on the remaining node following a node failure? Sort of a dynamic resource governor...

Let's say no governor is available. If my 120% utilization fails to a single node, will it crash or just cause the applications to experience slower performance? I'd be fine with some degraded performance, but not a crash.

Thanks for your insights!
Post #1118937
Posted Thursday, June 2, 2011 11:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:16 AM
Points: 1,270, Visits: 2,784
I guess the big question is do both instances run at 60% utilization every single second of every single day 24X7. My guess is no. If it does then you will see some slowness yes. If not, then most likely you won't see any problems.

On a SQL 2000 cluster I had an issue with the CPUs being pegged at 100% for hours on end and it would cause the cluster to fail over. This is because the heartbeat check would time out and it would think the other node is dead so it would fail the cluster over to the inactive node. We did increase the timeout which helped a lot. However, we fixed some bad code and also installed two more CPUs in the one node and licensed it that way.



Post #1118940
Posted Friday, June 3, 2011 4:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 4,350, Visits: 6,162
ken.herring (6/2/2011)
We have recently converted an Active/Passive cluster to an Active/Active cluster (SQL2005). I understand that if one of the nodes fails, the remaining node will have to carry all of the load. I'd like to run each of the Active nodes at better than 45% utilization. But, I'm concerned about the consequences of this if one of the nodes fails. For instance, if I plan for each node to run at 60% utilization, logically, a failure would result in a single node at 120% utilization. NOT GOOD.

Is there a way to restrict the resources available on the remaining node following a node failure? Sort of a dynamic resource governor...

Let's say no governor is available. If my 120% utilization fails to a single node, will it crash or just cause the applications to experience slower performance? I'd be fine with some degraded performance, but not a crash.

Thanks for your insights!


1) higher levels of windows server come with a resource management capability

2) enterprise SQL Server 2008+ also comes with a resource governor

3) outside of those two, you 'COULD' set processor affinity at the instance level after a failover, but I would be very leery of that solution.

I don't think any of those solutions have the ability to throttle IO though (could be wrong about windows server one).


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1119316
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse