Using listener_name in 4 part naming convention gives sys.servers error

Question

App uses cross db connections, the dbs are in different AG's, so I need them to use 4 part naming convention - in the place of servername would be listener name. But when I run a simple query like:

select * from Listener_Name.DatabaseName.[schemaname].[objectname]

I get:

Could not find server 'Listener_Name' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Listener_Name is not in sys.servers table.

My current environment has 2 nodes and 3 AGs, each having their own listeners. Do I need to add the 3 listener names as linked servers on both nodes? And does anyone have a script on how best to create the linked server using listener name? I would like it to be similar to the local server which shows up on the sys.servers table with server_id 0.

Answer 1

There isn't one place where I found everything written properly, which maybe is because not many people use cross AG dist txs, so this is what needs to be done:

1. Have app make code change to use 4 part naming convention using the appropriate listener_name.

2. Create linked servers for all listeners on all nodes:

Script is from https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-clustered-dtc-for-an-always-on-availability-group?view=sql-server-ver15 step 12:

IF NOT EXISTS (SELECT * FROM sys.servers where name = N'Listener1')
BEGIN
    EXEC master.dbo.sp_addlinkedserver @server = N'Listener1';  
END

Do the same for the other listeners. above should suffice - you can do a distributed query test now (its given in the same step 12 of above link) , but as I am on SQL Server 2016 SP2+, I did this also:

ALTER AVAILABILITY GROUP AG_Name

   SET (

      DTC_SUPPORT = PER_DB  

      );

Did the same for other AG's per https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-availability-group-for-distributed-transactions?view=sql-server-2017#manage-unresolved-transactions