question

Mandar Alawani avatar image
Mandar Alawani asked

SQL Server 2005 and MSDTC use

Hi All, We have MSDTC installed as a Clustered resource in SQL Server 2005 environment. I know MSDTC is used for cross-database and distributed transactions. I wanted to know: 1. Even if there are no distributed transactions, I heard there is a known issue with SQL 2005 Database engine which enlists(makes use of) MSDTC. Is this true and when? 2. We are going to implement DB mirroring. What has to be considered with regards to how MSDTC is used in case of Disaster recovery? Will there be any transactions mismatch etc? Thanks.
sql-server-2005transactiondistributedmsdtc
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mandar Alawani avatar image Mandar Alawani commented ·
Can anyone help?
0 Likes 0 ·

1 Answer

·
Shawn_Melton avatar image
Shawn_Melton answered
Well the basic answer is use of MSDTC with Database Mirroring it is not supported by Microsoft: [Database Mirroring and Cross-Database Transactions][1] > Database mirroring is not supported with either cross-database transactions or distributed transactions. [1]: http://technet.microsoft.com/en-us/library/ms366279(v=sql.90).aspx
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mandar Alawani avatar image Mandar Alawani commented ·
Hi Shawn, Yes I am aware of this. However, I have read that we can install DB mirroring with MSDTC if DB mirroring is configured in synchronous mode (i.e. transaction first commits on mirror DB and then on principal DB) My main question is: Suppose on the principal server there are 2 DBs mirrored, and the transaction is committed to Db1 on both principal and mirror server but not on Db2, will that be a data mismatch(assuming this is a distributed transaction using msdtc). Hope I was able to explain clearly. Regards, Mandar
0 Likes 0 ·
Shawn_Melton avatar image Shawn_Melton commented ·
What source did you read that you can do that?
0 Likes 0 ·
Mandar Alawani avatar image Mandar Alawani commented ·
Hi Shawn, I must have read it on one of the forums(sorry don't recollect which one) Are you saying, we cannot(should not) install DB mirroing(in synchronous mode) on SQL clustered instance which has MSDTC as a clustered resource?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.