0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.