Keep seeing 'AG currently does not allow read only connections'

Question

Hello, Experts

I am testing with 3 node. Two nodes are using AG FCI, and another is AG. Like following:

 * i know there is something is wrong with this architecture, it is only for testing AG.

All replica, Listener within a same subnet for the test. Actually thoes are on same VM and VM network.

I completed AG Wizard, and i checked that I can directly access to Secondary Replica using sqlcmd -K readonly option.

C:\Users\sysadmin>sqlcmd -S SQL11FC1 -K ReadOnly -d AdventureWorks2008R2 -q "SELECT @@SERVERNAME"
--------------------------------------------------------------------------------------------------------------------------------
SQL11FC1

But when i try to access using listener, i have following messages.

C:\Users\sysadmin>sqlcmd -S TCP:AG3_Listener -K ReadOnly -d AdventureWorks2008R2 -q "SELECT @@SERVERNAME"
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : The target database ('AdventureWorks2008R2') is in an availability group and currently does not allow read only connections. For more information about application intent, see SQL Server Books Online..

I set both server, primary role allow connection READ_WRITE and secondary_role_allow_connections READ_ONLY.

 

Which part do i need to check?

Thanks,

Hyun

---

Hyunseok

Answer 1

I cannot see your picture.

 

but from the error message i think you didn't configure the secondary as "Allow ReadOnly Connection" or "Allow All connectoins"?

please check the link:

http://msdn.microsoft.com/en-us/library/hh213002(v=sql.110).aspx

 

You also need to configure readonly routing list and readonly routing URL - if you don't know how to do it:

DMVs to show your configurations for Readonly Routing List and ReadOnly Routing URL

sys.availability_read_only_routing_lists 

sys.availability_replicas

Examples:

For Machine1

alter availability group <yourAGname> MODIFY REPLICA ON N'<Machine1>'

WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://<Machine1>.sys-sqlsvr.local:<your instance port>))

alter availability group <yourAGname> MODIFY REPLICA ON N'<Machine1>'

WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'<Machine2>')))

For Machine2

alter availability group <yourAGname> MODIFY REPLICA ON N'<Machine2>'

WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://<Machine2>.sys-sqlsvr.local:<your instance port>))

alter availability group <yourAGname> MODIFY REPLICA ON N'<Machine2>'

WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'<Machine1>')))

Powershell script:

The cmdlet is Set-SqlAvailabilityReplica, and the important parameters are –ReadOnlyRoutingConnectionUrl (accepts a string) and –ReadOnlyRoutingList (accepts a list of strings)

Example:

$primaryReplica = Get-Item SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg\AvailabilityReplicas\PrimaryServer

$secondaryReplica = Get-Item SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg\AvailabilityReplicas\SecondaryServer

# Set ROR Urls for replicas

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica

# Set ROR List for a replica

Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

Driver for .NET 4 client to use “ApplicationIntent=ReadOnly”

Download link is here:

http://support.microsoft.com/kb/2544514

Answer 2

Thanks for your help. The problem is solved with you answer.

---

Hyunseok