Geeks With Blogs

Michael Freidgeim's Blog MS .Net Development

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 | 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: GeeksWithBlogs.net | Join free