0

Cross database querying if both databases are in the same AG is easy. We just use standard 3 part naming:

SELECT t1.ID
FROM [database1].[dbo].[table1] t1
JOIN [database2].[dbo].[table2] t2  ON t1.ID = t2.ID

How does this work, or can it work if database1 and database2 are in different availability groups? Do I have to create a Linked Server that references the other AG Listener or this is just something that's not possible?

planetmatt
  • 407
  • 3
  • 10
  • 5
    IMHO, if the databases are tightly-coupled enough to warrant cross-database queries, they should be in the same AG. The queries will work even with different AGs as long as both are on the same primary node. – Dan Guzman Mar 28 '23 at 14:22
  • If my primary connection connects to one AG containing one DB, how does a query in that connection reference the database in the other AG? If I do `SELECT t1.ID FROM [database1].[dbo].[table1] t1 JOIN [listener2].[database2].[dbo].[table2] t2 ON t1.ID = t2.ID` I get "Could not find server 'Listener2' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver" To be clear, they SHOULD be in the same AG, but if we can't move the DB, I'm looking at options. – planetmatt Mar 28 '23 at 15:22
  • Because the 2 databases that are in different AGs might not be active on that replica so I thought targeting the listener would get an automatic redirect to the active replica? So we're saying, that unless the databases are in the same AG, the only guaranteed way to do a cross database query is to use a linked server to the second listener? – planetmatt Mar 29 '23 at 08:04

0 Answers0