Transactions in Distributed Server Architecture

Question

Hi,

We are designing an sql2k8, .net based desktop banking application. The application has distributed server architecture. Most of the transactions take place in local server, whilst a few number of transactions are run on both remote and local servers.

Scenario:

Local Server Table --> Inter_Bank_Logs
Remote Server Table --> Inter_Bank_Trans

Question:
Actually, I have'nt tested or done like this before. Is something like this acceptable ?

DECLARE @GUID UNIQUEIDENTIFIER = NewID()
DECLARE @IDENT TABLE(ID BIGINT)

BEGIN TRY
	BEGIN DISTRIBUTED TRANSACTION
	INSERT 
		Remote_Server.My_Database.Inter_Bank_Trans(GUID, ....)
	OUTPUT 
		inserted.ID INTO @IDENT 
	SELECT 
		@GUID, ....
	
	INSERT 
		Local_Server.My_Database.Inter_Bank_Logs(GUID, Trans_ID, ...)
	SELECT 
		@GUID, @IDENT.ID, ....
	
	If @@trancount > 0 COMMIT TRANSACTION
END TRY
BEGIN CATCH
	If @@trancount > 0 ROLLBACK TRANSACTION
	--Some error handling
END CATCH

Any idea will be greatly appreciated.

Answer 1

Nick,

That appears to be the right track.

Here is a link: BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms188386.aspx

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com