none
Link server configured to connect Always on read only connection to secondary replica RRS feed

  • Question

  • Hi  we have always on server 2016 R2 configured and read only routing also configured . Basically "user1" connect from client server and display as secondary  server. That ensured the read only routing is working 

    sqlcmd.exe -S  LSNRname -d databasename -E -K ReadOnly

    1>     Go

    2>     Select @@servername

    3>     Go

    --------------------------

    Question is on same client server(other 2016 R2 SQl server) I need to create a linked server to always on. and i created as follows. 

    EXEC sp_addlinkedserver 
    @server = N'Linkservername, 
    @srvproduct=N'SqlServer',
    @provider=N'SQLNCLI11', 
    @datasrc=N'Listenenrname ', 
    @provstr=N'ApplicationIntent=ReadOnly', 
    @catalog=N'DBname';

    .Linked server created. 

    then I run following command to see which server is accessing from clinet server 

    select * from OPENQUERY ( [Linkservername] ,'select @@servername' )

    Its returned the correct servername.

    Q1. I am not sure what user is use to connect which assess DB from the link. When select "Be made using the login's current security context"

    Q2 . Secondly when I try to add user on  , then I am getting error. But please note I connect  using SSMS  same user specifying   listenername + ApplicationIntent=ReadOnly  and connected correctly.  Not sure why. Any one has any idea would be great . 

     





    • Edited by ashwan Thursday, August 22, 2019 5:45 AM
    Thursday, August 22, 2019 5:33 AM

All replies

  • Hi ashwan,

     

    >> I am not sure what user is use to connect which assess DB from the link. When select "Be made using the login's current security context"

     

    This setting uses the same login connected to the local instance to connect to the remote instance. When using a SQL authenticated user, the username and password need to be the same on both instances. For Windows authenticated users, they can have direct access or be part of an AD group on the remote instance.

     

    >>Secondly when I try to add user on  , then I am getting error. But please note I connect  using SSMS  same user specifying   listenername + ApplicationIntent=ReadOnly  and connected correctly.  Not sure why. Any one has any idea would be great . 

     

    'Be made using this security context' setting uses a SQL authenticated login on the remote instance for all connections using the Linked Server. Did you add the remote login to the linked server?

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 23, 2019 6:18 AM
  • Dedmon, I able to setup user and now connect to secondary sever2(which is read only replica) . But when failover done server 1 to server 2(now server 2 is primary )  when I checked the link server , still connect server 2 which primary now. But it supposed to connect now server 1.  My routing is working correctly . When I check from sqlcmd. Any idea why server link server connection will not change depending on the role of the servers?

    many thanks 

    Saturday, August 24, 2019 6:44 AM
  • In your linked server configuration the data source is given as 'listenername' . You may want to try 'select host_name()' instead of select @@servername.
    Saturday, August 24, 2019 3:51 PM
  • Is your read-only routing list properly configured on both Node 1 and Node 2?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 26, 2019 9:36 AM
  • yes Configured in both nodes 
    Thursday, September 19, 2019 1:45 AM
  • hi When we try select host_name() , then it returned source SQL node(Where linked created ). not the any of the Always on nodes
    • Edited by ashwan Thursday, September 19, 2019 1:47 AM
    Thursday, September 19, 2019 1:46 AM
  • Following artical  got the same issue which I am having now. But when I create the link server using MSDASQL  then I am getting an error.

    @provider=N'SQLNCLI11'   -> @provider=N'MSDASQL'

    https://social.msdn.microsoft.com/Forums/SqlServer/en-US/0f519050-ae2b-4456-8b08-f516a42c1f32/linked-server-to-alwayson-ag-readonly-replica?forum=sqldatabaseengine

    Thursday, September 19, 2019 6:00 AM
  • HI ashwan

    are you sure your login in both of server same sid ?

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 


    Thursday, September 19, 2019 6:13 AM
  • Hi Natiq What does it mean  sid?

    To ensure routing is working : When run this from client mechine  

    Step 1: sqlcmd.exe -S  LSNR -d dbname  -E -K ReadOnly

    select @@servername

    display node 1

    Step 2 failover to node2

    Step 1: sqlcmd.exe -S  LSNR -d dbname  -E -K ReadOnly

    select @@servername

    display node 2

    its works vise versa. 

    ---------------------------------------------------------------------

    When I create a Dblink using the script as described, then initally goes to node2(read only site)  on following command

    EXEC sp_addlinkedserver 
    @server = N'Alwasysonlink, 
    @srvproduct=N'SqlServer',
    @provider=N'SQLNCLI11', 
    @datasrc=N'Listenenrname ', 
    @provstr=N'ApplicationIntent=ReadOnly', 
    @catalog=N'DBname';

    select * from OPENQUERY ( [Alwasysonlink] ,'select @@servername' )node 2.

    step 3: failover to node 1 to node 2(now primary node 2 )

    but   select * from OPENQUERY ( [Alwasysonlink] ,'select @@servername' )  remain same as node2. even we failover /failback  , then 

    will not change.

    any idea would be highly appreciated .

    regards

     


    • Edited by ashwan Thursday, September 19, 2019 12:50 PM
    Thursday, September 19, 2019 12:49 PM