Solved

SQL server 2005 Distributed Transaction Coordinator service

Posted on 2012-12-20
10
722 Views
Last Modified: 2013-01-03
Dear all,

Right now some of our Windows 2003 server is running DTC service I expected, please see attached.

But what I found out is not all of the SQL group include the DTC resource, is that right that all SQL server instance should have this DTC resource ? or just one SQL group in the cluster need that ?

from my understanding, the installation of cluster will do this for us, we don't have to worry about that, right?

if we need to install it, do we need to restart the SQL instance/service after that ?
DTC.jpg
0
Comment
Question by:marrowyung
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38711967
Yes it should be there on all Servers.

From where you are executing command to different SQL server instance
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38712089
PatelAlpesh,

so if I found it is not existing on any SQL instance group ,the nI have to add that myself?

"From where you are executing command to different SQL server instance " but it seems needs the links server to be created also ?
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 167 total points
ID: 38715027
It will be there by default. You have to enable it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38717816
"It will be there by default. "


so if it is not there then I have to enable it, any restart of the SQL instance?
0
Don't lose your head updating email signatures!

Do your end users still have the wrong email signature? Do email signature updates bore you or fill you with a sense of dread? You can make this a whole lot easier on yourself by trusting an Exclaimer email signature management solution. Over 50 million users do...so should you!

 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 166 total points
ID: 38733415
unless you have problems: you are good: there is 1 MSDTC resource for 2003 cluster all nodes

just in case:

How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster
http://support.microsoft.com/?kbid=301600
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 167 total points
ID: 38733842
Agree...

MSDTC when installed on a cluster becomes clustered. Only a single instance is used on a cluster.

On a non-clustered environment, only those machines involved in a distributed transaction need to have it enabled.

By default (from 2003 SP1 on) it is disabled to protect network security.

It is an operating system service (not just for SQL server) and is configured / started / managed seperately. It uses RPC on ports over 1024 and will use as many as needed to handle the requests - you can limit and manage, but generally best to leave it to manage or respond to the workloads.

A lot of time, the only reason to use it (as far as DBA's are concerned) is because of SQL Server and so it is often mistaken as a SQL Server utility.

You dont have to stop or start SQL server unless there are permissions issues that have to be resolved for the SQL Server service account (and even then there are often other ways like sdset etc). There are a couple of DB settings like RPC that might need to be set, but that is a simple config and doesnt require restarts.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38735753
mark_wills,

Thansk a lot. yeah, once people see "transaction", they will consider that this is a SQL resouce.

"Only a single instance is used on a cluster"

So this means if there are 2 x SQL instance in the cluster group, then ony one can own that.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38735795
well, there will be one instance og msdtc and that service will be started / owned by an account.

It is similar to SQL Server Service in that regard. It gets fired up under an account, then whatever resources (or people) need to use it, then they consume the service as required.

SQL Server doesnt necessarily "own the service", but SQL Server does place the distributed transaction request (or the transaction gets escalated because it engages another server).

Think of it as a queue manager for RPC... One server says to another "I need stuff" and the DTC manages that request checking to see if everything is OK (like permissions).
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38739338
thanks.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now