Sql-server – AlwaysON AG Readable Secondary is set to ‘Yes’. How to connect from the client

availability-groupssql server

For AlwaysON AG, if Readable Secondary is set to 'Yes', do you use the listener name or the secondary replica instance name to connect for read-only purposes?

I know when Read Secondary is set to 'Read-intent only', you are required to specify ApplicationIntent=readonly and the database to be routed to the secondary replica. However, my understanding is that if you set it to 'Yes' instead of 'Read-intent only', you have to explicitly specify the secondary replica instance name instead of the listener name. Is that correct?

Best Answer

If you want to access a secondary replica in an Always On configuration, the Readable Secondary property must be set to "Read-Intent" or "Yes".

If you set "Read-Intent" then your connection string must specify ApplicationIntent=ReadOnly otherwise the connection is rejected. If you set "Yes", your connection is allowed without ApplicationIntent=ReadOnly, however, any non-read queries you run will raise an error.

If you set "No" then all connections to the secondary will return an error. NOTE: That these conditions only apply when specifying an AG-joined database in the connection string. If you connect to a system database or non-AG database then you can connect to a secondary without issue, but you can't access the AG-joined databases. See here for more info.

Lastly, when connecting you have two options. Firstly, you can simply connect to the secondary replica via the normal name for that replica. Secondly, you can connect via the listener name.

When connecting via listener name, if you have configured read-only routing and your connection string specifies ApplicationIntent=ReadOnly then you will be routed to a readable secondary for your connection. If you do not specify ApplicationIntent=ReadOnly or you have not configured read-only routing, then you will connect to the primary replica. See here for more info.

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

There are several steps to configuring a server to accept ReadOnly traffic. The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each.

Here's the T-SQL involved:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER01' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER02' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Sounds like you may be missing the configuration and/or routing information for the primary.

Sql-server – AlwaysON – Force users connection to use Readable Secondary Nodes

There is no way to complete this with just SQL Server and the interfaces it exposes. In order to do this you'll either need an appliance between the clients and SQL Server (the users connect to the appliance endpoint and the appliance does the redirection) or you can implement your own through a cname and some type of configurable load balancer like an F5.

There is nothing in 2016 to change this behavior AFAIK.

Best Answer

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

Sql-server – AlwaysON – Force users connection to use Readable Secondary Nodes

Related Question