If connection fails between linked servers on SQL Server 2008

Question

I have 2 databases on 2 different SQL Server instances which are not in the same local network area.

What I need is when I have changes on table A in database A, I want to update table B in database B.

So I decided to link those servers but my question is if connection between servers is lost and I get update/insert/delete on database A, how can I apply those changes on database B later when connection is available again? Do I get any automatic generated log for that?

Thanks for your help.

Answer 1

You should split your update in two parts (1-st - for the first server, 2-nd - for the linked one) and execute each part in single transaction. If the second transaction fails, insert your update parameters into some table of failed updates. Schedule this table for each hour (or daily) to try to execure UPDATE, and onsucceed remove the update record from table of failed updates.

UPDATED

BEGIN TRANSACTION
BEGIN TRY
    --UPDATE
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    --INSERT INTO UpdateFail TABLE
    ROLLBACK TRANSACTION 
END CATCH