By:Vitor Montalvo || Related Tips:More > AlwaysOn Availability

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)
Connect to a SQL Server AlwaysOn replica with Mangement Studio when the Readable ...
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:

No - No user connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting. 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. 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 a SQL Server AlwaysOn replica with Mangement Studio when the Readable ...

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


Connect to a SQL Server AlwaysOn replica with Mangement Studio when the Readable ...

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:


Connect to a SQL Server AlwaysOn replica with Mangement Studio when the Readable ...
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 Check out these other AlwaysOn Availability tips

Last Update: 10/14/2016


Connect to a SQL Server AlwaysOn replica with Mangement Studio when the Readable ...
Connect to a SQL Server AlwaysOn replica with Mangement Studio when the Readable ...
About the author
Connect to a SQL Server AlwaysOn replica with Mangement Studio when the Readable ...
Vitor Montalvo 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

More SQL Server DBA Tips...

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerRYTICTI
分页:12
转载请注明
本文标题:Connect to a SQL Server AlwaysOn replica with Mangement Studio when the Readable ...
本站链接:http://www.codesec.net/view/483564.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(19)