We have an Always On "cluster" that consists of 2 servers (there will be more), so one is PRIMARY and other(s) is(are) SECONDARY. The idea was to dedicate SECONDARY as a readonly replica, so it would be a somewhat search-server.

I do know how to set ConnectionString in the C# so it will use SECONDARY replica - just add ApplicationIntent=ReadOnly.

But I don't understand how to address the SECONDARY from stored procedure if I'm using distributed query. Is there a way to set some parameters to the query (like WITH statements or something), so query would use only replica and not the PRIMARY node?

The thing is, obviously from the start server1 is PRIMARY and server2 is SECONDARY, but when server1 fails then server2 is PRIMARY and server1 will be SECONDARY after it get fixed. So I cannot just use the static server2's name.

So far I've managed to get current replica's server name as a variable and use it in EXEC:

-- find first available replica
DECLARE @replicaServer nvarchar(50)

    @replicaServer = RCS.replica_server_name
    sys.availability_groups_cluster AS AGC
    INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
          ON RCS.group_id = AGC.group_id
    INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
          ON ARS.replica_id = RCS.replica_id
    INNER JOIN sys.availability_group_listeners AS AGL
          ON AGL.group_id = ARS.group_id
      AND connected_state = 1

-- and query it
DECLARE @cmd nvarchar(MAX) = 'SELECT * FROM [' + @replicaServer + '].[somebase].[someschema].[sometable]'
EXEC (@cmd)

But that's a shame, I guess.


After some help and hints (especially thanks to Andriy M) I've managed to understand what I actually want and how to do it. This article from Microsoft helped as well: https://technet.microsoft.com/en-us/library/hh403414.aspx

First I had to create a read only listener. And then at the other server add linked server like this (borrowed from: https://stackoverflow.com/a/19585304):

USE [master]
EXEC master.dbo.sp_addlinkedserver
    @server     = N'cluster-readonly', -- just a name
    @srvproduct = N'SQL', -- anything except 'SQL Server'
    @provider   = N'SQLNCLI11',
    @datasrc    = N'ClusterListener', -- name of the readonly listener
    @provstr    = N'ApplicationIntent=ReadOnly',
    @catalog    = N'AnyActualDataBase' -- system database like 'master' is no good

How do I check, where am I:


Here's some trick - if I don't specify @catalog, then connection will be to the PRIMARY node for some reasons.

  • Thanks! You've saved me a lot of time searching for this. – Michael Cherevko Aug 30 '16 at 11:15
  • 1
    @retif the reason you must specify appropriate database is that it must check if target database participates in Availability Group before routing kicks in. > An alternative to this would be the login used in the connection has the database configured as its default database. docs.microsoft.com/en-us/sql/database-engine/… – Janis Veinbergs Mar 18 '19 at 12:20

SQL Server Management Studio supports adding items to the connection string when connecting to a server through the "Connect to Server" dialog box:

enter image description here

Adding ApplicationIntent=ReadOnly; to the 'Additional Connection Parameters' box (as shown in the above image) should accomplish what you need.

You may also need to add Database='YourDatabase'; if your default database is not part of an Availability Group. If you don't add the Database='YourDatabase'; part, you may still connect to the primary host, since the default master database is not part of your Availability Group.

  • Well, and what if I'm connected to the any other server, not in a cluster, and I'm in stored procedure wanting to SELECT some table from the SECONDARY replica of cluster? Like <code>SELECT * FROM [CLUSTERname].[somebase].[someschema].[sometable]</code>. Is there a way to set that I want to query SECONDARY server for readonly purpose? – retif Mar 24 '15 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.