August 15, 2023 at 12:02 pm
Have a bit of a situation with database mirroring and table joins, hopefully someone can shed some light on possible solutions.
Scenario:
DB01 is on Server01 and is mirrored to Server02
DB02 is on Server01 and is mirrored to Server02
DB03 is on Server 03 and is mirrored to Server04
Linked servers are setup between Server01 and Server 03 to facilitate the join between DB's on different servers.
Application query looks as follows:
use DB01
SELECT t1.*
,t2.*
,t3.*
FROM DB01.dbo.table1 as t1
inner join DB02.dbo.table2 as t2 on t1.id=t2.id
inner join Server03.DB03.dbo.table2 as t3 on t2.id=t3.id
Now the conundrum:
If there is a failover to the mirror server on either one of the databases DB01, DB02 or DB03 the query fails, this stands to reason as there is no way for the connection pool to offer a connection with a failover partner for something outside of what is defined in the connection string.
How would I be able to go about getting a working join over different DB's which due to a failover puts them on a server that is outside the definitions within the connection string?
August 16, 2023 at 12:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 16, 2023 at 6:11 pm
Note: Availability Groups are Mirroring version 2.x; you can repurpose the mirrors into AGs that include an AG Listener that will redirect clients after a failover.
Convert the separate mirrors of DB01 and DB02 to a single Availability Group with both DB01 and DB02 in it hosted on Server01 and Server02. These databases will fail over together when placed in the same Availability Group, and three-part queries between the two will work on either server. Note: you may need to break the mirrors first, but you will be left with databases on the mirror servers that are ready to be added to an AG.
To solve connectivity problems after failover, add an Availability Group Listener to the DB01+DB02 Availability Group. Change client (and Linked Server) connection strings to point to that Listener instead of directly to a specific SQL Server instance. Clients will use the same connection string regardless of the current location of DB01 and DB02; connecting to the Listener will forward their connection to the server hosting the live databases.
Do the same for DB03: Convert the mirroring of DB03 on Server03 and Server04 to an AG, add a Listener to that AG, and update all clients to connect to the Listener instead of Server03.
Eddie Wuerch
MCM: SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply