Today we were discussing the AlwaysOn connectivity options, and the various combinations therein of Readable Secondary setting and the connection strings became very complex. So I have summarized all the possible outcomes in the pivot tables below.

For convenience, we have two tables. The first table covers the cases when the Primary Replica is set to allow all types of connections (i.e. those with and without the ApplicationIntent=ReadOnly attribute.)

Case 1: Primary Replica allows ALL connections				Readable Secondary Setting
		Additional Connection Parameters	Initial Database	No	Yes	Read-intent
	Through Listener	None	Non-AG	Primary	Primary	Primary
		ReadOnly		Primary	Primary	Primary
		None	AG	Primary	Primary	Primary
		ReadOnly		Not allowed (5)	Secondary	Secondary
	Direct to Primary	None	Not applicable / don’t care	Primary	Primary	Primary
		ReadOnly		Primary	Primary	Primary
	Direct to Secondary	None		Not allowed (4)	Secondary	Not allowed (1)
		ReadOnly		Not allowed (4)	Secondary	Secondary

The second table covers the cases when the Primary Replica is set to allow Read/Write (i.e. those without the ReadOnly attribute.)

Case 2: Primary Replica allows only Read/Write connections				Readable Secondary Setting
		Additional Connection Parameters	Initial Database	No	Yes	Read-intent
	Through Listener	None	Non-AG	Primary	Primary	Primary
		ReadOnly		Not allowed (2)	Not allowed (2)	Not allowed (2)
		None	AG	Primary	Primary	Primary
		ReadOnly		Not Allowed (3)	Secondary	Secondary
	Direct to Primary	None	Not applicable / don’t care	Primary	Primary	Primary
		ReadOnly		Not allowed (2)	Not allowed (2)	Not allowed (2)
	Direct to Secondary	None		Not allowed (4)	Secondary	Not allowed (1)
		ReadOnly		Not allowed (4)	Secondary	Secondary

For some cases, the access to the database is not allowed (either at connection time or at run time, when the database is tried to be accessed) with the following error messages:

1. Error message: The target database (‘salesdb’) 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.

2. Error message: The target database (‘SalesDb’) is in an availability group and currently does not allow read only connections. For more information about application intent, see SQL Server Books Online.

3. Error message: Unable to access the ‘SalesDB’ database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.

4. Error message: The target database, ‘salesdb’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online

5. Error message: Unable to access the ‘SalesDB’ database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.

This table should help you predict how the connectivity will behave in each of these combinations, and therefore setup your Availability Group replica properties correctly.

More information:

• Active Secondaries- Readable Secondary Replicas (AlwaysOn)
• AlwaysOn- Offloading Read-Only Workloads to Secondary Replicas
• SQL Server Native Client Support for High Availability, Disaster

I hope you enjoyed this post as well. If you would like to see more, please leave a comment!