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