Geeks With Blogs

News My Blog has been MOVED to
Michael Freidgeim's OLD Blog My Blog has been MOVED to

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 on Saturday, May 26, 2007 2:54 PM SQL Server | Back to top

Comments on this post: Reference to different database in "Database Mirroring" environment

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Michael Freidgeim | Powered by: