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

  • SQLRocker

    SSCrazy

    Points: 2206

    Hi, 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]

    go

    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 on sys.servers table.

    My current env 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.

  • Alejandro Santana

    SSCommitted

    Points: 1777

    Hey,

    You can't just put the server name in the naming convention, it has to be a linked server name.

    You have to create a linked server as your listener as the source, creating a linked server with a listener as the server is the basically the same thing as creating a linked server with the server as the source, just make sure your user have access to the database.

    for it to appear in the sys.servers you need to create a linked server, the local server will always show as the server_id 0.

  • SQLRocker

    SSCrazy

    Points: 2206

    Hi Alejandro,

    Do you have a script for the linked server - I created a loop back linked server (linked servername = listener, datasource= listener), but I am getting various errors on the creation about security etc, I am still working thru those but what would help me is a linked server script which people are already using & which works, thanks.

  • Alejandro Santana

    SSCommitted

    Points: 1777

    It really depends on your security context, if you want to go the easy way with security just create the user that's gonna be connecting to the remote server in the remote server, if you are trying to use the "Be made using the logins current security context" you'll need to enable some stuff on the AD enable delegation on your accounts.

    Here we use a remote username and a remote password.

    EXEC master.dbo.sp_addlinkedserver @server = N'YourListenerNameGoesHere', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'YourListnerNameGoesHere', @catalog=N'YourDatabaseNameGoesHere'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'YourListenerNameGoesHere',@useself=N'False',@locallogin=NULL,@rmtuser=N'YourRemoteUserNameGoesHere',@rmtpassword='YourRemoteUserNamePasswordGoesHere'
    GO

    Here's an old link provided by Sue (Thank you) from an old post regarding delegation.

    Configuring Linked Servers for Delegation

     

  • SQLRocker

    SSCrazy

    Points: 2206

    Thanks Alejandro, you are right on the delegation etc for linked servers, they still apply to below:

    Well, I had went thru the links and many more, 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 2016 sp2+, I did this also:

    ALTER AVAILABILITY GROUP AG_Name

    SET (

    DTC_SUPPORT = PER_DB

    );

     

    did the same for other AG's , 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

  • Alejandro Santana

    SSCommitted

    Points: 1777

    So you are in an AG, in 2016 they added the DTC_Support per DB so you didn't have to create it at the WSFC level.

    This information can come in handy for anyone with this issue, thanks for your research!

    We had that issue once regarding unsolved transaction, our main DB wouldn't start, thanks to the SQL Server log tells you the issue and the transaction that's causing the issue.

    I think there's another thing i have not tested or tried yet because i have not found the way to make a transaction unresolved in the DTC, but you can change the way SQL Server treat unresolved transactions, here's a link, i really advise some testing, but like i said, i have not found a way to change the state of a transaction to unresolved at the DTC level.

    in-doubt xact resolution Server Configuration Option

    Regards,

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply