InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 02:12:13 GMT20http://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspx[quote][b]ken.herring (6/2/2011)[/b][hr]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![/quote]1) higher levels of windows server come with a resource management capability2) enterprise SQL Server 2008+ also comes with a resource governor3) 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).Fri, 03 Jun 2011 04:54:23 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxI 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.Thu, 02 Jun 2011 11:53:18 GMTMarkushttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxWe 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!Thu, 02 Jun 2011 11:44:46 GMTken.herringhttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspx[quote][b]mohsinabdul (2/14/2011)[/b][hr]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[/quote]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!Mon, 14 Feb 2011 08:49:14 GMTSchadenfreude-Meihttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxDo you mean that 42 DBs will be less for 3 servers? i would prefer 3 a and 2 p but here we have cost constraintsMon, 14 Feb 2011 08:17:26 GMTmohsinabdulhttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxI 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.Mon, 14 Feb 2011 01:59:28 GMTSchadenfreude-Meihttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxThat 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.Sat, 12 Feb 2011 09:00:31 GMTMarkushttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxI 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 way2) how many instances & DBs our passive server have? as any if the active server fails it should handling the request3) will the recommended solution be different of we go for SQL server 2008 r2?thanksFri, 11 Feb 2011 23:41:31 GMTmohsinabdulhttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspx[quote][b]jamesn 89076 (10/12/2010)[/b][hr][quote][b]TheSQLGuru (10/12/2010)[/b][hr]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.[/quote]Kevin, what type of monitoring agents are these that will automatically adjust the sql server max memory?[/quote]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. ;-)Wed, 13 Oct 2010 02:13:15 GMTSchadenfreude-Meihttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspx[quote][b]TheSQLGuru (10/12/2010)[/b][hr]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.[/quote]Kevin, what type of monitoring agents are these that will automatically adjust the sql server max memory?Tue, 12 Oct 2010 21:19:59 GMTjamesn 89076http://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspx[quote][b]Markus (10/12/2010)[/b][hr]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.[/quote]I work with 3 different clusters at present and they all perform without issue, have you verified your cluster configurations?[quote][b]Markus (10/12/2010)[/b][hr]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.[/quote]it's fine if you slipstream the SP1 during install time or create a merged drop installerTue, 12 Oct 2010 09:42:05 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxA/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.Tue, 12 Oct 2010 08:20:39 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxIn 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) [i]to be part of a Windows cluster the hardware must be identicle[/i], 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) [i]disavantages with a/a are that it is not a high availability solutiion r ather a high performance solution[/i] 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.Tue, 12 Oct 2010 07:07:14 GMTSimon Facerhttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxI 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.Tue, 12 Oct 2010 06:50:28 GMTSteve-3_5_7_9http://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspx[quote][b]Markus (10/12/2010)[/b][hr]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.[/quote]Sorry to hear that. I'm afraid to say that your clusters have obviously not been configured or tuned correctly. Ours are bomb-proof!Tue, 12 Oct 2010 06:08:10 GMTSchadenfreude-Meihttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxMicrosoft 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.Tue, 12 Oct 2010 05:53:37 GMTMarkushttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxEssentially 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.Mon, 11 Oct 2010 04:32:43 GMTSchadenfreude-Meihttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspx[b][u][url=http://www.mssqltips.com/tip.asp?tip=1554]Read this.[/url][/u][/b]Mon, 11 Oct 2010 01:49:19 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1001961-146-1.aspxHi if any one knows ACtive-ACtive Clustering SQL server 2005 disadvantages. Please help me out.Mon, 11 Oct 2010 01:38:04 GMTviswanath09