MSDTC in active/active sql config?

Question

Post reply

MSDTC in active/active sql config?

dieterbaylor

SSC Journeyman

Points: 82
More actions
November 6, 2002 at 2:33 pm

#79252

When creating the MSDTC resource in an active / active sql cluster, is it recommended to create the MSDTC resource in one of the sql server group or is it recommended to create it (MSDTC) in the cluster (quorum) group? Thanks

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

dieterbaylor SSC Journeyman Points: 82 More actions · Answer 1

dieterbaylor

SSC Journeyman

Points: 82

November 6, 2002 at 2:39 pm

#440546

Forgot to mention:

Clustering active / active using window 2K advanced with sql 2k ent. Thanks.

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 2

MS DTC will need to be configured for cluster support before you install the first SQL Server instance. Therefore it'll be located in the Cluster Group (quorum). After you have the cluster built, run comclust from the command line.

For more info, see Brian Knight's step-by-step article:

http://qa.sqlservercentral.com/columnists/bknight/stepbystepclustering.asp

Configuring MS DTC is on page 3.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Allen Cui-55137

SSC Guru

Points: 51650

November 7, 2002 at 7:26 am

#440637

I am not sure whether MS DTC have to to be configured before installing SQL Server. I did it by running comclust after the SQL Server installation.

dieterbaylor SSC Journeyman Points: 82 More actions · Answer 4

I agree that msdtc needs / should be configured prior to sql install. However once windows clustering is done I can go into cluster admin and create 2 new groups not including the cluster (quorum) group. The 2 new groups would be for each of the sql instance in a active / active config. So for example: I have a Cluster (quorum) group, SQLNode1 group for first instance and SQLNode2 group for the second instance. I believe Microsoft recommends in an Active / Passive config that the msdtc resource be created in a group other than the Cluster (quorum) group. I'm just wondering if this holds true for an Active / Active config as well. Thanks for your reply.

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 5

For our Active/Active clusters we've left MS DTC in the cluster group. If you need DTC and the SQL Server resource group it's in is down, then the other node can't use it.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

dieterbaylor SSC Journeyman Points: 82 More actions · Answer 6

Does this hold true for active / passive config as well. I'm confused with the following statement you made: "If you need DTC and the SQL Server resource group it's in is down, then the other node can't use it." Wouldn't the other node pick up the resources of the failed node? Hope these are legitimate questions. Thanks. Hopefully my last post on this subject. Thanks for all your help.

dieterbaylor SSC Journeyman Points: 82 More actions · Answer 7

dieterbaylor

SSC Journeyman

Points: 82

November 7, 2002 at 10:39 am

#440679

What are the dependencies of msdtc in the cluster (quorum) group. I think the dependencies are different if dtc in located in a group other than the cluster (quorum) group. Thankx

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 8

Sorry, my use of "node" was me typing too fast. If DTC is in one resource group and that resource group isn't available, then the other resource group wouldn't be able to use it. Also, I've not heard of any issues with having MS DTC in the Cluster Group, which is why we've left it there. If someone has come across some docs, please post the links.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 9

Allen Cui-55137

SSC Guru

Points: 51650

November 7, 2002 at 11:47 am

#440701

Depends on which group MS DTC is in. Generally, it depends on the network name and disk in the same group.

dieterbaylor SSC Journeyman Points: 82 More actions · Answer 10

dieterbaylor

SSC Journeyman

Points: 82

November 7, 2002 at 1:43 pm

#440724

Do you recommend created dtc resource in cluster (quorum) group in an active / passive config? Thanks.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 11

Allen Cui-55137

SSC Guru

Points: 51650

November 7, 2002 at 1:52 pm

#440725

I would create MD DTC resource in SQL Server group in active/passive configuration and in the default cluster group in active/active.

click-fund Ten Centuries Points: 1028 More actions · Answer 12

What's the advantage of moving MSDTC from the Cluster Group (where it is put when installed) to the server group in active/passive? i.e. Why bother to do this, rather than just leave it alone?

I'm not 100% sure, but I think the MSDTC resource can be used by apps. other than SQL to coordinate distributed processing. Anybody know if this is right/wrong?

dieterbaylor SSC Journeyman Points: 82 More actions · Answer 13

Other apps that use dtc include BizTalk Server.

Allen, I agree. Thanks.

Scenario 1: Active / Active SQL Configuration

3 Groups:

a) Cluster group (quorum)

b) SQLVS1 (For named instance one)

c) SQLVS2 (For named instance two)

Question? Where should be msdtc resource be located? In the Cluster (quorum) group? Why? If so, what should be it's dependencies? In SQLVS1 or SQLVS2 group? Why? What are the benefits of one over the other.

My guess: I would think to create msdtc resource in cluster (quorum) group.

My reason: If dtc in located in (active) SQLVS1 group but distrubuted transactions occure on (active) SQLVS2 and SQLVS1 has a failure, then SQLVS2 will not be able to process distributed transacions until SQLVS1 fails over to SQLVS2, which could take some time. If dtc is located in cluster (quorum) group, then no distributed transactions are lost during a SQL failover.

Scenario 2: Active / Passive SQL config

3 Groups:

a)Cluster (quorum) group

b)SQLVS1 (Active)

c)SQLVS2 (Passive)

Where should you create dtc resource?

My guess: In SQLVS1 group

Reason: By creating dtc resource in SQLVS1 (active) node which processes all distributed transactions, during a failover, all the resource, including dtc, would failover to SQLSV2 and continue to process all distributed transaction.

Just my thoughts. Any reply would be appreciated. If replying, please give reason for your decisions. Thank you.

click-fund Ten Centuries Points: 1028 More actions · Answer 14

For active/active it seems to be unreasonable to put DTC into one of the server instances. The asymmetry seems ugly to me.

For active/passive, maybe I'm just being dense, but what advantages you get by moving it into the server group?

If your entire server node fails and msdtc is on that node for some reason, whether or not it's in the server group, it's going to failover to another node at that time.

If only your virtual server fails and msdtc is on the same node in a different group or on a different node (in a different group), then it's not going to be shutdown due to the virtual server failure. This means other process that are using it won't get interrupted needlessly.