Linked Servers on SQL Server AGs

Question

When setting up Linked Servers in an Availability Group, do I need to set up the Linked Servers on the AG Listener as well as the AG Nodes? I've inherited a 2 node AG that currently has a set of linked servers on the Listener and on the individual nodes (so in SSMS, if I connect to the nodes and the listener, the linked servers appear in all three). I need to add a new linked server, so I'm not certain if I need to add it on the listener or just the nodes. One of our developers set up the linked server on the active node and the connection worked when he ran a test script connected directly to the active node, but could not connect to the linked server if he tried running the script while connected to the listener. We've had a similar issue running stored procedures from the MASTER database if the sp was not installed on the listener even if it was installed on MASTER on both nodes. I'm not sure if this is normal behavior or if the AG was set up in some non-standard way that is preventing sp's and linked servers that aren't set up on the listener itself from working.

Answer 1

When setting up Linked Servers in an Availability Group, do I need to set up the Linked Servers on the AG Listener as well as the AG Nodes?

The listener always points to the primary replica, if you need to have the linked server available no matter which replica is the primary (or secondary, depending) then it'll need to be setup on each of those replicas. The listener is a DNS entry and AD computer object, it can't have anything setup on it as it's not an actual SQL entity.

The only caveat here is if you're using SQL Server 2022 and contained availability groups. In this case the listener still isn't an entity, however it (along with database) will decide if you connect to the contained availability group or not, which is different in terms of msdb and master as these are redirected to the contained availability group.

I've inherited a 2 node AG that currently has a set of linked servers on the Listener [...]

See above.

One of our developers set up the linked server on the active node and the connection worked when he ran a test script connected directly to the active node, but could not connect to the linked server if he tried running the script while connected to the listener.

I have no idea if that node was the primary or not, nor do we know what the returned error was (if you have it, please update the question).

We've had a similar issue running stored procedures from the MASTER database if the sp was not installed on the listener even if it was installed on MASTER on both nodes.

This makes no sense unless you're using a contained availability group (again, see above).

• Wayne Cochran: Looks like I need to do some research into contained availability groups, as this is all running on SQL Server 2022 and I am still getting up to speed on AGs in general. Based on your reply and the behavior I've seen trying to setup instance wide objects, I'm willing to bet ours is a contained AG.
• Sean Gallardy: If it's a contained AG then you only have to do it once as it'll be part of the contained msdb/master.