Rajendra Gupta
Availability group validations

Applying Transaction Logs to the Secondary Replica in SQL Server Always On Availability Groups

July 12, 2019 by

Introduction

SQL Server Always On Availability Groups provides HADR solutions for the SQL databases. Here HA refers to high availability and DR refers to disaster recovery. The priority of this feature is to keep the database highly available and then provide Disaster recovery. Due to this reason, if the secondary replica goes down in a synchronous data commit mode, SQL Server changes commit mode to Asynchronous so that users can continue run the transactions and a secondary replica can be in sync later once it bought up. SQL Listener also points to the primary replica and continues redirects connection to the primary replica.

Suppose for a two-node synchronous data commit mode availability group, Secondary replica is down due to some hardware or power failure issues, and it might take longer for the respective team to fix it.

However, users will continue to use the primary replica database, but SQL Server holds the transaction log records on the primary replica. It cannot clear the transaction log despite the regular transaction log backups. It will cause transaction log growth, and we might face disk space-related issues if we do not have sufficient free space in the drive.

In this case, it is good to remove the secondary replica (unhealthy) from the SQL Server Always On Availability Group. Once we remove the unhealthy replica, it is not part of the AG group, and SQL Server does not need to hold the transaction log for late usage.

Remove a SQL Server Always On Availability Group

To remove an availability group, connect to primary replica and go to Availability Replicas. Right-click on the replica we want to remove and click on Remove from Availability Group.

Remove from Availability Group

Alternatively, you can execute the following query on the primary replica in SQL Server Always On Availability Group.

Once you remove a replica from the availability replicas, all database status changes to Not Synchronizing.

Database status changes to Not Synchronizing

Once the secondary database instance becomes available, connect to the secondary replica, you can drop an availability group.

It changes the database status to Restoring mode.

Database status changes to Restoring

Add the database back in the SQL Server Always On Availability Group

Now we want to add the database back to the SQL Server Always On Availability Group. We can see the following scenarios to add this database into the AG group.

Scenario 1: No log backup occurred after the secondary replica is down

Suppose you have not taken any log backup after you removed the replica from the availability group replicas. Once the replica is up, we want to add it again in the availability group.

In the primary replica, expand Availability Groups and right-click on the availability replicas. Now click on Add replica. In the initial data synchronization page of SQL Server Always On Availability Group, select the JOIN ONLY data synchronization method. Primary replica holds all the transaction logs, and it should bring the secondary database to the state in sync with the primary replica.

Select Initial Data Syncronization in SQL Server Always On Availability Group

It performs availability group validations and skips unwanted validations. We can ignore the warning for the listener because you can create SQL listener at any point after adding the replica and database in the AG group as well.

Availability group validations

In the next step, we can see that it adds both the TestAG and AdventureWorks2014 database as part of the availability group.

Availability group validations results

We can verify the status of AG synchronization using the following query.

SQL Server Always On Availability Group synchronization status

Scenario 2: Regular log backups occurred after the secondary replica is down

To demonstrate this scenario, I configured a maintenance plan to take regular log backups on a one-minute interval on the primary replica. Let’s replicate the scenario again by removing the secondary replica from the Availability replica group.

At this point, you only have one node SQL Server Always On Availability Group replica and regular backups happening the primary replica for AdventureWorks2014 database. We do not need transaction log backups for the TestDB database.

Suppose secondary replica is up again and you try to add the replica by using the JOIN ONLY method.

In the below screenshot, we can see the followings:

  • Joining TestDB to the availability group is successful. You can recall that we have not taken any log backups for this database after removing the secondary replica instance
  • Joining AdventureWorks2014 database to the availability group is failed

    Error while adding a database in AG group

Click on hyperlink Error in front of the failed entry. You get the following detailed error message.

The remote copy of database “Adventureworks2014” has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)

Erorr message while adding database back to AG group

Execute the query to check the synchronization status of both the databases between the primary and secondary replica in SQL Server Always On Availability replica. It shows the Synchronized and Healthy status for the TestDB database.

The status for the adventureworks2014 database is still Not synchronizing and Not healthy for the secondary replica.

The status for the adventureworks2014 database

In the Availability databases, we can see a warning message in front of the adventureworks2014 database.

Warning message for the AG database

You can plan the following approaches to fix this issue.

Approach 1: Backup and Restore to sync the primary and secondary replica

  • Take a Full database back of the database and corresponding log backups
  • Restore database into each secondary replica ( in case you have multiple secondary replicas)
  • Join the database into the availability group

Approach 2: Apply the transaction log backup and use JOIN ONLY method

In the secondary replica of SQL Server Always On Availability Group, check the SQL Server error logs. You can find the entry for the adventureworks2014 database similar to below.

SQL Server Error logs

You can note the last hardened LSN 97:5609:1 in this error logs. Execute the following query on MSDB database of the primary replica to check the LSN backup range.

Backup LSN information

We need to apply all transaction log backup that occurred after this log backup. We can find the list of all required log backups using the following query.

We get a list of required transaction log backups that needs to apply on a secondary replica database before adding a database to the replica.

Backup LSN information

Let’s apply these database log backups on the secondary replica; database status should be NORECOVERY after all log backups restore.

You get the output of each log backup restore similar to the below.

Restore Logs information

Once we restored all log backup, connect to the secondary replica and right-click on the adventureworks2014 database in SQL Server Always On Availability Group. Click on the Join to Availability Group.

Join to Availability Group in SQL Server Always On Availability Group

It opens the following wizard to join the database to an existing availability group. In the error option, by default selected option is – Continue executing after error.

Join database to existing AG group

Click Ok and it completes the wizard. You can see that the warning message icon turns into a green icon.

Healthy status of the AG database

Verify the database synchronization status for the adventureworks2014 database.

Healthy and syncroonization status of the AG database

Conclusion

In this article, we walked through the process to resolve issues when the secondary replica is down. We also learned to bring the secondary database in sync in case it lags from the primary replica. You should be aware of these approaches. You need to be quick on resolving the issues, especially in the production environment. If you have any comments or questions, feel free to leave them in the comments below.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
1,410 Views