Readable Async Always On Replicas
How Can We Help?
In environments that have set up SQL Server Always On Availability Groups and have set up asynchronous replicas for use in reporting and ad-hoc query scenarios, many times users will want to use off the shelf applications such as SSMS to access these databases. This may present a clash between the default permissions that are assigned for these replicas (read-intent only) and the permissions that applications like SSMS request (read only or read-write) (yes, "read-intent only" and "read only" are different, at least to SQL Server").
This is an example of what would happen if you tried to open the async replica directly:
Changing AlwaysOn Settings
There is a way to address this after the Availability Group is set up. This is done by connecting directly to the Availability Group through SSMS as an administrator and opening the AG properties window. A window that looks like this should appear: In this example, there are three servers in the AG: DOODLE (the primary), ROMANO (the synchronous secondary and the failover partner) and GOAT (the asynchronous secondary):
Notice that, under the "Readable Secondary" column on the bottom half of the page, the entry for "GOAT\GOAT2014" reads "Read-intent only". This was the default setting for this type of replica and what was causing SSMS to fail when it tried to open the replica copy of the database. As shown by the screen below, we change that value to 'Yes' (and left "Availability Mode" at "Asynchronous"), then pressed OK to save the settings.
As soon as we did that, we were able to go back to SSMS, open the database and read from the table.
If you want to find out "far behind" the asynchronous copy is from the synchronous copies, you can go in the SSMS to the Availability Group, to the Availability Replicas section, then use the link on the right hand part of the page that allows you to "Add/Remove Columns". There you can select counters that tell you how far behind the replica is from the original.
NOTE: the reason this server shows "Data Loss" under "Failover Readiness" is because this is an asynchronous replica — it should. If I were to select one of the other two servers, it would display "No Data Loss" in the same column.