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:

 
«October»
SunMonTueWedThuFriSat
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678