SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ACtive-ACtive Clustering SQL server 2005


ACtive-ACtive Clustering SQL server 2005

Author
Message
Schadenfreude-Mei
Schadenfreude-Mei
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3599 Visits: 1336
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
mohsinabdul
mohsinabdul
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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
Summer90
Summer90
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6917 Visits: 3831
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.
Schadenfreude-Mei
Schadenfreude-Mei
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3599 Visits: 1336
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
mohsinabdul
mohsinabdul
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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
Schadenfreude-Mei
Schadenfreude-Mei
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3599 Visits: 1336
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
ken.herring
ken.herring
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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!
Summer90
Summer90
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6917 Visits: 3831
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.
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31250 Visits: 8670
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 on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search