Sql-server – Using listener_name in 4 part naming convention gives sys.servers error

availability-groupssql server

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.

Best Answer

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:

Have app make code change to use 4 part naming convention using the appropriate listener_name.
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

Related Solutions

Sql-server – Execute stored procedure in another server from sql agent job

As the error message suggests, OPENQUERY looks for a linked server. If you don't have that set up, and don't have the rights to get that set up, then perhaps using an OPENROWSET query might be the way to go. This will allow you to connect to a datasource on a more ad-hoc basis, by writing a query which contains a full connection string.

In your case, you may get the results by using something like:

SELECT spresults.* 
FROM OPENROWSET('SQLNCLI', 'Server=1.1.1.1;Trusted_Connection=yes;',
                'EXEC [db_name].dbo.[sp_ProcName]') AS spresults

CAVEAT: This will fail at runtime if your SP returns multiple resultsets.

CAVEAT 2: This will also fail if Ad Hoc queries haven't been allowed. To sort that out, you need to run the appropriate sp_configure statement(s):

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'ad hoc distributed queries', 1
reconfigure

Of course, in order to run sp_configure, you'll need the appropriate rights in SQL Server. According to the "Permissions" section of the sp_configure documentation, that's "ALTER SETTINGS", which is by default part of the sysadmin and serveradmin roles, but may be assigned to other roles too.

SQL Availability Groups – Using Cluster Name Instead of Listener Name

First, when using AGs, you generally are not using a clustered instance of SQL Server, you are using multiple standalone SQL Servers. You are able to technically connect to each individual SQL Server in the Windows Cluster Separately. Each having their own configuration and resources.

Secondly, each group has its own name in AD as a computer object and its own IP address. These are registered in DNS. These are different than the cluster name. The listener name is a special object to route traffic based on incoming query type. For instance, if you configured your AG to use a secondary node for read only queries, the listener name is what routes that query to the secondary node, not the cluster group. Backups are also attached to that listener name for routing traffic from the primary to the secondary node.

If you are monitoring AG health, it will be based off of the listener name/object, not the cluster name. It is possible to have a 3 node windows cluster, but only have an AG involving 2 nodes. The cluster name would not properly handle that.

Adding further complexity to this question. With SQL 2016, you have distributed availability groups which uses separate clusters on different networks or domains. Those separate clusters are clusters all to them selves and are only joined via the listener name for failover purposes.

Lastly, if you chose to extend your AG to Azure, you will need to use the listener when connecting to the "active" database so your query can route to the appropriate node. The cluster resource name does not know about the Azure secondary node.

Best Answer

Related Solutions

Sql-server – Execute stored procedure in another server from sql agent job

SQL Availability Groups – Using Cluster Name Instead of Listener Name

Related Question