Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Connect to SQL Server Availability Group replica with SSMS when Readable Secondary is Read-intent only


By:   |   Last Updated: 2016-10-14   |   Comments (5)   |   Related Tips: More > Availability Groups

Problem

When connecting to a SQL Server AlwaysOn database replica where the Readable Secondary is set to Read-intent only you get the following error message:

With SQL Server Management Studio (SSMS)

AlwaysOn replica error message

With a query

Msg 978, Level 14, State 1, Line 1

The target database ('DatabaseName') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

Solution

First we need to understand why this error is being raised.

When connecting to a database through SQL Server Management Studio (SSMS) the error message isn't clear, but the error thrown by a query is quite clear on why this is happening.

When configuring Read-Only Access on an Availability Replica you have 3 options:

  1. No - No user connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting.
  2. Yes - All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.
  3. Read-intent only - Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.

A Read-intent only replica means that the Secondary Replica only accepts connections that are explicitly configured for that purpose and the problem occurs when you are trying to connect to an AG Secondary Replica database configured for Read-Intent only without explicitly using the proper parameter.

There are two options to solve this problem:

Connect to the replica(s) with Application Intent=Read-only

When connecting to the AG Secondary Replica instance use the ApplicationIntent=ReadOnly parameter.

To do this from SSMS, in the connection windows press the "Options >>" button before connecting.

connect to sql server

Then go to "Additional Connection Parameters" tab and enter ApplicationIntent=ReadOnly in the text box. You should be able to connect now.

connect to sql server using application intent

For applications you'll need to add the proper parameter in the connection string. Here is an example:

("Driver={SQL Server Native Client 11.0};server=AG_Listener;Database=AdventureWorks;trusted_connection=yes;ApplicationIntent=readonly”)

When connecting with sqlcmd utility it will also need to provide the correct parameter (-K)

sqlcmd -SAG_Listener -E -dDatabaseName -Kreadonly

Configure the replica(s) Readable Secondary option to Yes

This option should be only followed if you are sure that will not impact any of your applications since it will not redirect them automatically to a Read Only replica after you change this configuration.

NOTE: The following configuration needs to be performed on the Primary Replica server.

USE [master]
GO
ALTER AVAILABILITY GROUP [AG_Name]
MODIFY REPLICA ON N'ReplicaInstance' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO

Alternatively you can use SSMS to change the configuration by editing the AG properties (AlwaysOn High Availability / Availability Groups / <"AG name">) and then change the Readable Secondary value as shown below:

alwayson replica secondary settings

Other Notes

It's always a good policy to connect to an AG database using the AG Listener instead of the SQL Server instance name. The listener will guarantee that you are always connecting to the Primary Replica or to the Read-Intent Only Secondary Replica depending on the connection parameter.

Next Steps


Last Updated: 2016-10-14


next webcast button


next tip button



About the author
MSSQLTips author Vitor Montalvao Vitor Montalvão is a SQL Server engineer with 20 years of experience in SQL Server, specializing in performance & tuning, data modelling, migration and security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, May 17, 2018 - 10:12:36 AM - Deyvid William Angioletti Evaristo Back To Top

Hello Vitor, thanks for your article, very useful tips.

You just forgot to mention that when using SQL Server Management Studio, besides entering "ApplicationIntent=ReadOnly" in the Additional Connection Parameters box, you must specify the name of the database in the second tab (Connection Properties).

Hope it helps,

Deyvid William Angioletti Evaristo


Friday, December 08, 2017 - 3:28:11 PM - Shawn Melton Back To Top

A note on use of SSMS. Just adding the ApplicationIntent is not going to be sufficient enough if the login you are using has a default database of master. You will always hit the primary replica and think your read-only routing is not working properly.

 

In order for read-only routing to be performed SQL Server has to know you are connecting to the database that is associated to that listener name. So you have to also set the default database to that AG database along with using the ApplicationIntent option. It will then validate the listener to the database and work through the routing list for read-only access.


Friday, October 13, 2017 - 7:25:32 AM - Govindaraju Back To Top

 

This article is very helpful and I have fixed lot if issues in SQL through "MSSQLTIPS" artcles.

 

Thanks a lot....


Monday, March 13, 2017 - 5:04:10 AM - Vitor Montalvão Back To Top

You can run the following query. It will return the role for the SQL Server instance that you are connected to:

SELECT r.replica_server_name, s.role_desc

FROM sys.dm_hadr_availability_replica_cluster_states AS r

INNER JOIN sys.dm_hadr_availability_replica_states AS s ON s.replica_id = r.replica_id

WHERE r.replica_server_name = @@SERVERNAME


Friday, March 10, 2017 - 6:18:19 PM - Bob Amy Back To Top

How can you confirm if You are actually reading the Secondary in SSMS?  Is there a property to check?


Learn more about SQL Server tools