November 25, 2009 at 12:25 pm
We have two sql 2005 servers that we want to enlist in a distributed transaction. Do these servers need to be linked to each other for MS DTC (Distributed Transaction Coordinator) to work?
TIA,
Barkingdog
November 25, 2009 at 4:36 pm
Hi,
You need the linked servers between them to make it work, otherwise, how are you going to establish the connection?
Regards,
Jose Santiago Oyervides.
November 25, 2009 at 11:26 pm
As per my understanding MS DTC (Distributed Transaction Coordinator) is not required for the sake of linked server. You just create the linked server by using sp_addlinkedserver. MS DTC is a required component when clustering or transaction replication with updating subscriber (either immediate or queued) is needed.
-lk
November 27, 2009 at 8:55 am
MSDTC and linked servers are not inter-related. I note however that configuring DTC properly can be a real b-tch! Numerous articles online about problems and how to resolve them.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 27, 2009 at 12:42 pm
As TheSQLGuru wrote:
" I note however that configuring DTC properly can be a real b-tch! "
Seems to be the case but are there any alternatives if one needs a distributed transaction between two sql servers?
TIA,
barkingdog
November 27, 2009 at 12:50 pm
Linked Servers are there, is it not something you are considering?
I believe this thread started around that, I second Luckysql's thoughts regarding the DTC not a requirement to have linked servers.
Have you tested configuring Linked Servers?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 27, 2009 at 1:07 pm
Our developer's claim they need to run a "ransaction" that updates two databases on two servers. So I think that linked servers are were not enough. We need a Distributed Transaction. (Do you agree?)
Barkingdog
November 27, 2009 at 1:08 pm
1) I believe the OP inquired is DTC required for distributed transactions. remember that dist trans can be done completely from client-side code.
2) even with linked servers I believe DTC can still be required??
declare @i int
begin tran
select @i = sum(myfield) from mylocaltable
update remoteserver.remotedb.dbo.remotetable
set linkedtotal = @i
where id = 1
commit tran
you can also force it explicitly:
BEGIN DISTRIBUTED TRAN ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 27, 2009 at 1:09 pm
Barkingdog (11/27/2009)
Our developer's claim they need to run a "ransaction" that updates two databases on two servers. So I think that linked servers are were not enough. We need a Distributed Transaction. (Do you agree?)Barkingdog
I for one agree that DTC is required here, assuming both updates must be ATOMic - either both complete successfully or both get rolled back completely.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply