This is in continuation to the post around SQL Server AlwaysOn-Readable Secondary Setting which I posted earlier. With every new release there are multiple enhancements and I know by the time we published our book on AlwaysOn many of these features were not quite available and we missed writing about them.
One of the main addition with SQL Server 2014 for AlwaysOn is the capability to have 8 secondary replica. This is has been discussed exclusively in the book and the number of synchronous nodes etc remain as in SQL Server 2012. Nothing much changes. It is important to understand that commits don’t wait for async replica’s hardening, so there will be not much of an impact wrt to deployments are concerned.
Enhanced Readable Secondary with SQL Server 2014
So what are the new scenario’s that we are talking about from a readability point of view? In SQL Server 2012, when the availability on the secondary databases was either NOT SYNCHRONIZED (Data movement SUSPENDED) or in RESOLVING state we were not able to connect to the secondary node for ReadOnly workloads.
Scenario 1: In SQL Server 2012 if we take the Availability group to Offline mode, the secondary databases get into a RESOLVING state and we get the following error:
Unable to access database ‘AdventureWorks2012’ because its replica role is RESOLVING which does not allow connections. Try the operation again later. (Microsoft SQL Server, Error: 983)
From SQL Server 2014, existing connections made to the secondary replica directly can still be served and new connections will also be allowed.
Scenario 2: In SQL Server 2012, if data movement gets suspended, the availability database gets into a NOT SYNCHRONIZED state. In such scenarios the connections are refused and we get the following error:
The target database, ‘AdventureWorks2012’, 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.
Or we might get a read access error on the secondary. Either way, with SQL Server 2014, this is no longer a problem as read directly to the secondary is possible even when the synchronization is suspended.
Note: Irrespective of the scenario mentioned above, the read operation cannot be performed using the Listener and routing list. In either of the case of RESOLVING or NOT SYNCHRONIZED state if you use the Availability Group Listener name/IP we will get the below error.
Unable to access the ‘AdventureWorks2012’ 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.
Thought this is a critical hidden enhancement with AlwaysOn, it can surely come in handy for Hybrid scenarios, or when quorum is lost scenarios where we still get the ability to read from our secondary directly as a last resort and worst case operation.
This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).