August 26, 2011 at 5:24 pm
Per http://support.microsoft.com/kb/926150 cross database transactions are not supported. I want to implement a mirror with auto failover so that I can use FusionIO cards. The databases in question routinely use cross database queries, however, I do not believe they have the scenarios with inserts into both dbs in a single transaction, as is the case in the examples. In general, they read from one db and write to the other. As for DTC, there might be a transaction that deletes data from a linked server and inserts new data, based on data contained in the local database.
Example:
BEGIN TRAN
delete from otherdb.dbo.MyTable
insert into otherdb.dbo.MyTable
Select * from thisdb.dbo.MyTable
Commit tran
I don't see much risk with this, since writing is only occurring on a single DB.What are your thoughts? DTC will be involved sometimes because of linked servers, but again, as long as the data only flows one way, I think it's okay.
August 31, 2011 at 5:12 am
You have to ensure that linked server configuration is correctly set in the case of automatic failover, also you can think about software load balancing solution to see it constantly talks to the preferred servers which are linked in your principal server.
-Satya SK Jayanty
SQL Server MVP (Follow me @sqlmaster)
Author of SQL Server 2008 R2 Administration CookBook
SQL Server Knowledge Sharing network
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply