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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy