ProjectSMM.com
Gonzo TechNet
How To Enable & Configure MS DTC
for SQL Clustering on Windows Server 2003

Step-By-Step directions on how To Enable & Configure MS DTC for SQL Clustering on Windows Server 2003

This HOW-TO is one of a series for all those SQL Server DBAs that need to set up a Clustered SQL server. As usual, Microsoft provides minimal instruction with maximum confusion for those of us trying to complete our daily duties. Hopefully, this will shed some light on how to actually do it. (As always: use at your own risk, I might be wrong, MicroSlut keeps changing things, objects are closer than they appear, your mileage may vary, etc., etc....)

One of the requirements for clustering SQL Server 2000 and 2005 is that you enable & configure the MSDTC (Microsoft Distributed Transaction Coordinator) service in "network" mode. The reason (briefly) is that the SQL server will need to track transactions on the currently active SQL node and provide fault-tolerancy if it fails over. In order to achieve this, the MSDTC service needs to be part of the cluster environement. For myself, I "inherited" what was supposedly a fully configured Cluster ready for an SQL Server install. But in reality, not so much....

If you are tasked with installing or even upgrading an SQL Server instance in a clustered environment, the first thing you will probably do is Google search for: "How to install SQL Server Cluster". And the results...a BILLION hits on USELESS information. Why? (um any answer Bill G.????) Why all the useless info? Simple, Microsoft doesn't write "How-To" articles. They would rather you buy their consulting services, pay to attend their training seminars, etc., etc....

Well, since most of us lack the time (and money) to do that, here's some step-by-step instructions.

Prerequisites

This article assumes that we are working with a two node cluster and that the base cluster has already been configured. After that, there are a few things you will need before you proceed with the MSDTC setup. First, you will need an IP address for the "network enabled" MSDTC service to use when communicating with itself and its clients. Secondly, on the clustered system, you will need a shared drive/directory dedictated the MSDTC service to use for tracking and storing transactions. The MS articles are rather vague on how big the drive needs to be. At my current job site, there is a Network Storage Team that configures the NAS/shared arrays so I requested a shared drive with 10gb of space. (I would have liked to be there to document the steps they took, but, it's appearantly a matter of natal security...). So, put this on your check list:

Which Method To Use?

Again, in typical Microsoft fashion, providing minimal instruction with maximum confusion, there are two possible ways to enable & configure MS DTC as a network resource. And, depending on which KB article you find first and start with, one tells you to enable & configure, while another tells you to configure then enable. (Shhhhhhhheeeeeeeeeesh!) Here are the two possiblities:

  1. Method 1:Use Cluster Administrator to:
  2. Method 2:Use cluster.exe to:

Regardless of which method you use, the outcome is the same. And, they are interchangeable...(sheeesh!)

METHOD 1: How to Configure network DTC access with Cluster Administrator

(See Microsoft KB article 301600)

This section documents how to install MS DTC through Cluster Administrator on all nodes in a cluster. You only have to install the MS DTC once in Cluster Administrator and MSDTC will be configured on all nodes in the cluster.

At this point, you will create a new resource "Group" and then add "resouce" items to that group for use by the MS DTC Network enabled service.

  1. Either:
    1. Login (RDC) to the currently ACTIVE node
    2. Start Cluster Administrator (Start ->All Programs->-Administrative Tools->Cluster Administrator
      (Note: You can also start the Cluster Administrator GUI by clicking Start->Run and typing in "cluadmin")
    3. Connect to the target Cluster
    Or:
    1. Run Cluster Administrator from your local system
    2. Connect to the target Cluster
    Cluster Administrator
  2. Create a resource Group Named "MSDTC Group": This resource Group will contain the following resource items necessary for MS DTC. To create the group:
    1. In the File Menu select New->Group. The New Group Wizard is available. MS DTC Cluster Configuration New Group
    2. Set the "Name" and "Description" of the new group and click "Next" MS DTC Cluster Configuration New Group Wizard
    3. Add the nodes that will be owners of this group from "Available Nodes" to the "Preferred Owners", and click "Finish". MS DTC Cluster Configuration New Group Owners
    4. Click OK to confirm the new "Group" has been created. MS DTC Cluster Configuration New Group Confirm
  3. Note: When creating the MS DTC, moving the resource group into a group other than SQL Server or Exchange Server group is highly recommended. Creating the MS DTC resource in its own resource group and assigning it to a separate cluster group keeps the resource highly available.
    Note: If you create the resource in the same cluster group, and if Resource A has failed over, Resource B will also failover. When you create the resouce in a different cluster group, even when Resource A has failed over, the resource B does not failover. Creating Cluster Resouces in the different cluster groups decreases unnecessary failovers of the Cluster Resouces.

    Now that you have the resource group (MSDTC Group) you need to add the individual "Resources".

  4. Create an IP address resource:
    1. Right-click the MS DTC group, and then click New->Resource. MS DTC Cluster Configuration - New Resource
    2. Type a descriptive name such as MSDTC IP Address.
    3. In Resource Type, select "IP Address"
    4. Select "MSDTC Group" from the group list, and then click Next. MS DTC Cluster Configuration New Resouce IP
    5. In Possible Owners, click Next unless you do not want MS DTC to run on a particular node. MS DTC Cluster Configuration New Resource IP Owners
    6. In Dependencies, do not add any dependencies, and click Next. MS DTC Cluster Configuration New Resource IP Dependencies
    7. In TCP/IP Address Parameters, select the public network, type the unique static IP address for MS DTC, and then click Next. MS DTC Cluster Configuration New Resource IP Parameters
    8. Click Finish, and then click OK to confirm that the resource has been created.
  5. Create a Network Name resource:
    1. Right-click the MS DTC group, and then click New->Resource. MS DTC Cluster Configuration New Resource
    2. Type a descriptive name such as "MSDTC Network name"
    3. In Resource Type, select "Network name" from the dropdown list
    4. Select "MSDTC Group" from the group list, and then click Next. MS DTC Cluster Configuration Network Name
    5. In Possible Owners, click Next unless you do not want MS DTC to run on a particular node. MS DTC Cluster Configuration Network Name Owners
    6. In Dependencies, add the "MSDTC IP address" as a resource dependency, and then click Next. MS DTC Cluster Configuration Network Name Dependencies
      • In Name, type a desrciptive name such as "MYCLUSTER-MSDTC" for the MSDTC Network Name.
        (If you have more than one cluster in your network, use a unique name for the MSDTC Network Name on each cluster)
      • Check the "DNS Registration Must Succeed" box
      • Click Next
      MS DTC Cluster Configuration Network Name Name
    7. Click Finish, and then click OK to confirm that the resource has been created. MS DTC Cluster Configuration Network Name Confirm
  6. Create a "Physical disk" resource:

    Note: You may already have a physical disk resource created. If so, you can move this physical disk resource into the MS DTC group and go to the next step. Otherwise, follow these steps to create the new "Physical Disk" resource.

    1. Right-click the MS DTC group, and then click New->Resource. MS DTC Cluster Configuration - New Resource
    2. Type a descriptive name such as MSDTC Physical disk.
    3. In Resource Type, select "Physical disk",select the "MSDTC Group", and then click Next. MS DTC Cluster Configuration - New Physical Disk
    4. In Possible Owners, click Next unless you do not want MS DTC to run on a particular node. MS DTC Cluster Configuration - New Physical Disk Owners
    5. In Dependencies, do not add any dependencies, and then click Next. MS DTC Cluster Configuration - New Physical Disk Dependencies
    6. In Disk Parameters, click the physical disk that you will use for MS DTC. MS DTC Cluster Configuration - New Physical Disk Parameters
    7. Click Finish, and then click OK to confirm that the resource has been created.
  7. Create Distributed Transaction Coordinator resource:

    Once you have created the individual resource items, you whill need to create a DTC resource that is dependent on the other items.

    1. Right-click the MS DTC Group, then click New->Resource. MS DTC Cluster Configuration - New Resource
    2. Type a name, such as "MSDTC Resource".
    3. In Resource Type, select "Distributed Transaction Coordinator"
    4. Verify that the MSDTC Group is selected, and then click Next. MS DTC Cluster Configuration - Distributed Transaction Coordinator
    5. In Possible Owners, click Next unless you do not want MS DTC to run on a particular node. MS DTC Cluster Configuration - DTC Owners
    6. In Dependencies, add BOTH the Physical Disk and Network Name resources you created in the previous steps, and then click the Add button. MS DTC Cluster Configuration - DTC Dependencies
    7. Click Finish, and then click OK to confirm that the resource has been created. MS DTC Cluster Configuration - DTC Confirm
  8. ENABLE network access for MS DTC
    ** CRITICAL ***
    You should NOT put the MS DTC resource online at this point.
    Follow the instructions in the next section ("How to enable network DTC access in Windows Server 2003") then, when that is complete, bring the MSDTC resource and group online.
How to ENABLE network DTC access in Windows Server 2003

The first step is to "enable network DTC access in Windows Server 2003". (See Microsoft KB article 817064)

For the rest of us....

  1. Login (RDC) to the currently ACTIVE node of the cluster (this step is missing from the MS directions...DOH!)
  2. Click Start->Control Panel->Add or Remove Programs.
  3. Click Add/Remove Windows Components.
  4. Select Application Server, and then click Details.
  5. Select Enable network DTC access, and then click OK. Enabling MS DTC for network access Application Set Up
  6. Click Next.
  7. Click Finish.
  8. Restart the computer. Since this is a clustered environment (and you are making changes to the ACTIVE node), the "restart" of will cause the currently active node to fail over. Once it does, you can login to the newly-activated node and you will find that the changes were applied there also.

If you are running Windows server 2003 Service Pack 1(SP1) or greater, you will need to peform the following additional steps:

  1. MS Error: In Control Panel, open "Component services"
  2. On the currenlty ACTIVE node click Start->Program->Administrative Tools->Component Services
  3. Expand Component Services, expand Computers, right-click My Computer, click Properties, then click on the MSDTC tab On the MSDTC tab,
    1. Set the "Location" to a directory on the shared drive previously configured as the shared drive/Physical Resource. For our example this will be "M:\MSDtc".
    2. The default capacity is set to 4mb. If you choose you can increase this to 10mb or greater.
    3. Make sure the "Client Newtwork Protocol Configuration" is set to "TCP/IP"
    4. Click on [Apply]

    Enabling MS DTC for network access Component Properties
  4. On the MSDTC tab,
    1. click Secuity Configuration under Transaction Configuration
    2. click to select the Network DTC Access check box under Secuity Settings
    3. then click to select the following check boxes under Transaction Manager Communication:
      1. Allow Inbound
      2. Allow Outbound

      MS DTC Security Configuration
    4. You cannot select Mutual Authentication Required.
      Therefore, click to select one of the following check boxes:
      1. Incoming Caller Authentication Required (prefered)
      2. No Authentication Required

      Note: For more information about these options, see the Microsoft Knowledge Base ariticle 899191
    5. Make sure that the Logon Account is set to NTAUTHORITY\NetworkService.
      MS DTC Security Configuration
    6. Click [OK] to continue
      A message box explains that the MS DTC Service will be stopped and restarted, and that all dependent services will also be stopped and restarted.
      Click [Yes] to continue
      Note: Do not use the MNS resource as the storage device for MS DTC. MS DTC requires a storage resource such as a physical disk. (Explained later)
    Enabling MS DTC for network access
  5. Restart the computer. Since this is a clustered environment (and you are making changes to the ACTIVE node), the "restart" of will cause the currently active node to fail over. Once it does, you can login to the newly-activated node and you will find that the changes were applied there also.
  6. IF you are following the steps in Method 2,SKIP to the next section!
    IF your performed this section as part of the steps in Method 1, you will now need to bring the MSDTC resource "Online"
    1. Start the Cluster Administrator and connect to the target Cluster Cluster Administrator
    2. Click on the "MSDTC Group" to expand its resource list Cluster Administrator expand MSDTC Group Resource list
    3. Right-click the "MSDTC Resource", and then click Bring Online. Cluster Administrator Bringing the MSDTC Resource Online
METHOD 2:How to CONFIGURE network DTC access with "cluster.exe"
Having "enabled" network access to MSDTC using the steps in the previous section, now you need to CONFIGURE the MS DTC "resource" on the Cluster. This section documents how to install MSDTC with "cluster.exe". Cluster.exe is optimal for scripting the installation and configuration of MSDTC on several clusters. You only have to configure MS DTC once on a cluster and all nodes in the cluster are updated. (See Microsoft KB article 301600)

*** WARNING ****
The following is DIRECTLY from the Microsoft documentation.
In typical MircoSlut fashion, these directions DO NOT WORK!!!
Note: In the following example substitute "CLUSTERNAME" with the name of your cluster....

  1. Complete the steps outlined in How to ENABLE network DTC access in Windows Server 2003

    Note: It is not necessary to enable network DTC access if the cluster is dedicated to Exchange Server.

  2. Create the resource group "MSDTC Group"

    Note: Microsoft recommends that you create the MS DTC resource in its own resource group. If you cannot do this, or if you have a light-use DTC scenario such as a cluster dedicated to Exchange Server, you can use the default group "Cluster group". On dedicated Exchange Server clusters, the MSDTC resource should be placed in the default group "Cluster group".

    1. Open a command prompt window (Start->Run->cmd)
    2. Type the following command at a command prompt:
      cluster CLUSTERNAME group "MSDTC Group" /CREATE
  3. Create an "IP Address" resource
    From the command prompt, execute the following
    cluster CLUSTERNAME res "MSDTC IP Address" /CREATE /GROUP:"MSDTC Group" /TYPE:"IP Adddress"
  4. Create the "Distributed Transaction Coordinator" resource labeled "MSDTC Resource"
    Type the following command at a command prompt:
    cluster CLUSTERNAME res "MSDTC Resource" /CREATE /GROUP:"MSDTC Group" /TYPE:"Distributed Transaction Coordinator"
    Note: If you are using an existing cluster group, substitute "MSDTC Group" with the name of the existing group.
  5. Create the "Network Name" resource
    To create a resource network name labeled "MSDTC Network Name" and bind it to the resource labeled "MSDTC Resource", execute the following from the command prompt:
    cluster CLUSTERNAME res "MSDTC Resource" /ADDDEP:"MSDTC Network Name"
  6. Create the "Physical Disk" resource:
    To create a physical disk resource labeled "Disk M:\" and bind it to the resource labeled "MSDTC Resource", execute the following from the command prompt: The follwoing command will
    cluster CLUSTERNAME res "MSDTC Resource" /ADDDEP:"Disk M:\"
  7. Bring the "MSDTC Resource" Online:
    Run the following command to bring MS DTC online:
    cluster CLUSTERNAME res "MSDTC Resource" /ON
  8. Note: You cannot install MS DTC in a mixed-mode Cluster. All nodes in the Cluster must run Windows Server 2003 to use these methods. See "Help and Support" for more information.

Home | TechNet | ADO.Net | DOS | ASP.NET | IIS | VB.NET | VIM (vi) | Windows | XHtml
MS-SQL | T-SQL | SSIS | Oracle