How to Get More Juice Out of a Secondary Replica on an AlwaysOn Availability Groups in SQL Server

In this article, we understand how to make the most out of secondary replica for AlwaysOn Availability Groups.

Users working on SQL Server are familiar with AlwaysOn feature available in 2012 and subsequent editions. AlwaysOn availability groups help users in providing higher availability, read scale balancing and disaster recovery. There is an Active Primary replica and along with is a secondary replica which is passive. So the essential question is how can we assign certain tasks to secondary replica and save some resources and use both the replicas effectively.

Get More Out of Secondary Replica on AlwaysOn Availability Groups in SQL Server

The article focuses on the use and functionality of secondary replica for SQL server AlwaysOn Availability Groups. 

A Look at How Things Worked in Past

In earlier versions, the secondary replica would remain idle if the primary replica is active as most of the solutions would require an active passive configuration. However, with the new updates, the 2012 edition Always On feature changed the equations and now Secondary replica can be essentially in a read-only mode and can effectively handle queries and Database backup jobs which shift the load from primary replica. ‘Always On’ availability groups basically boost the availability of a set of user DB for an enterprise/Organization.

Secondary Replicas

Secondary replicas came to light after the SQL Server 2012 Update. They are helpful in database mirroring and are able to use secondary copy to carry out backups and run other queries which are resource heavy. This takes the load off from primary replicas and improves the performance and makes use of secondary replica which was otherwise idle during operations. 

To perform SQL Database backup on an active secondary replica users can utilize the “copy_only option” designed for filegroup, file, full database, and log-backups. Users can also configure an availability group to perform the backups. Scripts for backup jobs can be scripted while choosing replica backups. Or alternatively, users can just configure while running the Wizard of New Availability Group.

Then, there is also an option of using BACKUP_PRIORITY to locate the backups. It should be noted that designated backup preferences are used by log shipping and maintenance plans.

Secondary Replica Restrictions 

Although there are major benefits of using Secondary Replicas, there are some restrictions too. Notably, changing data tracking and data capturing are not really supported in databases using Secondary Replicas in 2012 iteration of SQL Server.

Also, the ghost records’ clean up on primary replica can get blocked by secondary replica’s transactions. So they will only get cleaned up when Secondary replicas won’t need them. DBCC Shrinkfile can also fail on your primary replica in a similar situation where secondary replica requires ghost records. These are the main restrictions of secondary replicas. 

Users should also note that if there are any active transactions existing on the primary DB during the joining of readable secondary replicas to availability groups, then row versions won’t be fully accessible on secondary DB which means queries will be blocked temporarily until the transactions which were on primary replica rolls back.  

Besides implementing AlwaysOn Availability Groups in correct manner, companies should also invest in specialized mdf fix tool to deal with any incident of data corruption.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdb repair and sql recovery software products. For more information visit https://www.datanumen.com/