Availability Groups: Things to remember

Here are a few things to remember when working with Availablity Groups:

Seeding New Databases on Asynchronous Replicas

When adding a new database to the AG, Remember it will auto-seed the database to the synchronous replicas, but the asynchronous replicas will need a FULL and TAIL LOG backup to add them to the AG on that replica. Here is the script below to perform that task:

 

Readable Replica

Configuring read replica’s allows you to utilize the secondary servers for processing SELECT queries off of the primary replica, thus increasing the performance on the primary for data updates and inserts.

Below is the connection string attribute that the client applications should add if they are able to use the secondary read capabilities of the availability group and do not need to read from the primary:

Server=AGCorp,1433;Database=AdventureWorks2016;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly

Configuring the read-only routing URL’s:

Configuring the read-only routing list:

NOTE: some aspects of configuring READ_ONLY_ routing can only be configured with T-SQL. There are no SSMS GUI screens to configure them. See page 156 of Apress’s ‘Pro SQL Server AlwaysOn Availability Groups’ book.
 

Data Latency

Remember that on the AlwaysOn Dashboard the data latency column is based on the log being committed. If the secondary is reporting 0ms latency, that does not necessarily mean that the database is current. The REDO thread still needs to apply them yet to the database itself.

MONITOR THE REDO QUEUE SIZE ON ALL SECONDARIES

This has a big impact on the Primary replica. Set REDO queue thresholds for alerting. Read more about this on page 162.

Views – 35