Reference to different database in "Database Mirroring" environment

We have a stored procedure in one database that (among other things) insert a row to another database on the same server -something like

INSERT INTO OtherDatabase.dbo.LogTable.

We are using "Database Mirroring"  feature of SQL Server 2005 having mirror on another server. However failover switch happens for each database separately and it can be situations, when one database run on the same principle server, but another switched to the mirror server. For this situation the mentioned above SP failed, and the design is not acceptable.

The recommended design is to keep all tables in the same database and avoid cross-database calls, Assumption that databases are always on the same server is not valid in "mirriring" environment.
To copy/syncronize data between databases use some replication/DTS procedures that are asyncronous to main transactions.

UPDATE: After writing the post I found that it is documented in MSDN: Database Mirroring and Cross-Database Transactions and in MS support KB Using database mirroring for cross-database transactions 


posted @ Saturday, May 26, 2007 2:54 PM
Print

Comments on this entry:

No comments posted yet.

Your comment:



(not displayed)

 
 
 
 

Live Comment Preview:

 
«December»
SunMonTueWedThuFriSat
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910