MS DTC and Linked servers

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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?


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • 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

  • 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

  • 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