none
Linked Server to AlwaysOn AG read-only replica RRS feed

  • Question

  • Hi All,

    I am trying to configure linked server to AlwaysOn AG read-only replica - routing is configured ok and everything seems to be working ok when you connect to AO directly with read only application intent..

    I have configured the linked server as:
    EXEC sp_addlinkedserver @server = N'myListener',
                                       @srvproduct=N'SQL',
                                       @datasrc='myListener',
                                       @provider='SQLNCLI11',
                                       @provstr='ApplicationIntent=ReadOnly;Database=myDB',
                                       @catalog='myDB'

    (I have also tried few variation based of above based on what I found on the net) but every time I am trying to test it I am presented with name of primary rather than secondary replica:

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

    Any ideas?

    Tomasz

    Friday, June 24, 2016 7:07 AM

Answers

  • I have a recollection of that to achieve this, you need to set up the linked server using MSDASQL + SQL Server Native Client as the ODBC driver.

    However, Microsoft says that using MSSDASQL for linked servers to other SQL Server instances is not supported. (That is, it works, but if you get an issue, they will hook up the phone when you call them.)

    • Marked as answer by Tomasz-Za Monday, June 27, 2016 9:04 AM
    Friday, June 24, 2016 12:15 PM

All replies

  • Verify that youare connecting to Listener Name.
    Verify that the Read-Only routing list (READ_ONLY_ROUTING_LIST) is defined.
    Verify that the Routing URL (READ_ONLY_ROUTING_URL) of each instance has a proper FQDN and port combination.
    Verify that the Sync_State is SYNCHRONIZED or SYNCHORNIZING for the secondary replica
    Verify that the secondary replicas are set to allow connections

    Friday, June 24, 2016 10:12 AM
  • Hi Andrea,

    thanks for your feedback but this is all verified and working ok - do not have any issues with basic sqlcmd test:

    Below works fine and 'select @@servername' returns secondary configured in routing list...
    SQLCMD.EXE -S myListener -E -d myDB -K readonly

    This is really a question about linked server and read-only secondary as AO itself is ok.

    Regards,
    Tomasz

    Friday, June 24, 2016 10:28 AM
  • try to replace the keyword "Database=" with "InitialCatalog="
    Friday, June 24, 2016 10:31 AM
  • no luck.. I have also tried with 'Application Intent =', 'InitialCatalog=' and 'Initial Catalog='

    In all instances 'select * from OPENQUERY ( [myListener] ,'select @@servername' )' returns primary instead..

    Friday, June 24, 2016 10:49 AM
  • I have a recollection of that to achieve this, you need to set up the linked server using MSDASQL + SQL Server Native Client as the ODBC driver.

    However, Microsoft says that using MSSDASQL for linked servers to other SQL Server instances is not supported. (That is, it works, but if you get an issue, they will hook up the phone when you call them.)

    • Marked as answer by Tomasz-Za Monday, June 27, 2016 9:04 AM
    Friday, June 24, 2016 12:15 PM
  • Thanks. That seems to work although it's bit strange that's not supported?

    Any idea if it's just me (seems not) doing something wrong or some problems between different versions of SQL?

    Thanks,
    Tomasz

    Monday, June 27, 2016 9:05 AM
  • I have not researched this in detail, but since they deprecated OLE DB for SQL Server access during the development of SQL 2012 and AGs, it may be that they never implemented the ReadOnly intent properly in OLE DB.

    Monday, June 27, 2016 10:21 AM