August 5, 2009 at 9:27 am
I'm setting up my first cluster (SQL Server 2005 on Windows 2008) and need advice regarding MSDTC (Microsoft Distributed Transaction coordinator). From my research it seems one of the steps in setting things up is to cluster MSDTC and that appears to be a fairly complicated process for a humble DBA. In a non clustered environment you don't have to worry about it - MSDTC is at the operating system level and just works. Apparently it's role is to coordinate distributed transactions to ensure consistent updates across multiple databases.
When clustering SQL on Windows 2003 you have to first cluster MSDTC (otherwise I think the SQL install won't complete). But with Windows 2008 clustering has changed in some ways and you can actually install a clustered SQL instance without clustering MSDTC, as I have done. I think this has something to do with a 2008 cluster being able to access the local MSDTC service whereas 2003 can't. I have monitored our database servers with SQL profiler and I can't find any applications that use distributed transactions, although we do use distributed queries via linked servers.
So my question is, given that I'm using Windows 2008 and none of my apps use distributed transactions, do I need to go to the trouble of clustering MSDTC? I've read something about Integration Services relying on it, but I have that running on my cluster now without issue. Also, in the future if I do get an app that uses distributed transactions, what behaviour should I expect on a cluster without MSDTC clustered? Will errors show in the SQL logs or will things seem to work as normal except updates won't be consistent between databases? In my current environment I've been able to run a BEGIN TRANSACTION statement across 2 servers without issue, so I don't see why I need to waste time clustering MSDTC.
August 5, 2009 at 11:46 am
What type of cluster are you using? Active/Passive or Active/Active.
I have read that in Window Server 2008 the MSDTC provides support for Active/Active clusters, it never mentions use of it in Active/Passive except for earlier versions. (http://technet.microsoft.com/en-us/library/cc730992(WS.10).aspx)
With regards to SSIS, this part of SQL is not cluster aware and Microsoft does not recommend it to be setup as a cluster resource (http://msdn.microsoft.com/en-us/library/ms345193.aspx)
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 5, 2009 at 6:59 pm
I’m a bit confused about whether I have an active/passive cluster or an active/active one. Here’s my configuration…
Physical Server 1
SQL Instance 1 (clustered, primary node)
SQL Instance 2 (clustered, primary node)
SQL Instance 3 (clustered, primary node)
SQL Instance 4 (clustered, passive node)
SQL Instance 5 (clustered, passive node)
Physical Server 2
SQL Instance 1 (clustered, passive node)
SQL Instance 2 (clustered, passive node)
SQL Instance 3 (clustered, passive node)
SQL Instance 4 (clustered, primary node)
SQL Instance 5 (clustered, primary node)
SQL Instance 6 (local)
SQL Instance 7 (local)
August 6, 2009 at 3:44 am
That looks very Active/Active. Basically you are running resources on both nodes, as opposed to one node active and the other standing by. It's more usual to have only one or two instances. There's a large overhead of memory running that many separate instances.
August 6, 2009 at 6:13 am
So it's active/active because each physical node has at least one active virtual node, even though each virtual instance has one active and one passive virtual node? No wonder it's confusing.
Note that many of the instances are for testing or possible future use (thought I'd install them ahead of time to avoid disruption later on if I need them). There's really only 2 production instances - 1 and 2 both hosted on physical server 1 as the primary node. Each server has 80Gb RAM and 24 CPU cores, so that should be enough power. I'll use SQL min max to ensure the production instances get enough RAM and Windows Resource Manager to allocate enough CPU.
August 6, 2009 at 8:35 am
Agree with Steve, thats an awful lot of sql, for a relatively low powered box (our prod cluster is 128GB and we wouldnt dream of running more then 1 instance).
The problem you get is that each instance of sql basically has a 1gb overhead (just to start and run) and thats without doing anything. On top of that you have aditional memory pressures from awe and other stacks (assuming your using clr, is and as.
My advise to you would be to have no more then 3 instances active on one node at once; powerdown any instances not needed and not to think of sql clustering as a 'load-balancing' solution (IT ISNT). Its basically a high availability solution intended to give higher resiliance to high tran environments. ALthough at first it may seem like a good way to leverage tin investment; assuming your primary node fails (cause thats the point of a cluster), can your passive node comfortably handle the processing and memory requirements of everything the active was doing, PLUS any local active instances it is already running?
My feeling in your case is No.
Be careful not to overtax your active node, cause in a pinch, it wont failover successfully.
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 6, 2009 at 8:47 am
I agree that is a LOT of SQL on a cluster. I heard a hard number of 3 instances, I think it depends on a lot of factors, such as processors, memory, and disk, all the important stuff.. And as pointed out earlier, you have to have enough capacity left over to handle any instances that get failed over to a particular machine.
I was most troubled by what I thought I read about mixing prod and test/dev instances on the same cluster. In small shops they might not think anything of that but pretty much everywhere else that is a HUGE no-no. Prod should be seperated both logically and physically from development and test.. There is an argument about dev and test being physically and logically sperated as well, but it doesn't carry the same weight IMNSHO.
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply