SQL Server: Forced failover with AlwaysOn & Availability Groups

During a workshop at one of my customer about the implementation of SQL Server AlwaysOn and availability groups, I had an interesting discussion. It was about asynchronous replication between 2 replicas and the effect of the forced failover operation with allowed data loss. Does SQL Server resynchronize availability databases if I loose some transactions during the failover process?

The quick answer is yes but probably the most interesting question is how SQL Server achieves resynchronization and this is exactly what we will see in this blog post.

Simulate a data loss is pretty easy if you have a SQL Server AlwaysOn architecture with availability groups and 2 replicas enrolled in an asynchronous replication. You can pause the replication on secondary and then perform a manual forced failover from the same server for instance. During this blog post I will use the undocumented function sys.fn_db_log() and some dynamic management views related to availability groups as well.

Let’s begin with my SQL Server architecture: One availability group with the adventureworks2012 database that hosts a table dbo.aag_test and 2 replicas SQL141 and SQL143 configured in asynchronous replication. SQL141 is the current primary.

Step 1: Create a replication issue by pausing the availability database on the secondary

After suspending the replication from the secondary, we insert an additional record in a table aag_test on the primary (SQL141) which will be not replicated to the secondary (SQL143). Notice that we are not aligned on both sides by viewing the content of the sys.fn_db_log() function on each transaction log.

Here is the content of the AdventureWorks2012 transaction log file on the primary:

And this is the content of the same database transaction log file on the secondary:

On the secondary, the last record in the transaction log file is identified by the LSN: 260:14984:1 (in decimal format). We notice additional records in the transaction log file on the primary after pausing the replication – we can identify among them LOP_INSERT_ROWS / LCX_HEAP records that correspond in fact to the insertion of additional records in the table aag_test.

We can also take a look at the DMV related to the availability groups by using this query:

The column last_redone_lsn for the secondary (SQL143) represents the last log record that has been redone. The value (convert in decimal format) is the same previously found in the transaction log by using the sys.fn_db_log() function. The last_hardened_lsn value represents the last transaction blog hardened to the transaction log file. This value is usually greater than the last_redone_lsn value.

Step 2: SQL143 becomes the new primary – after restarting SQL141 and initiating a manuel forced failover with potential data loss

At this point we have some lost data that have not been replicated (LSN between 00000104:00003a88:0001 and 00000104:00003a90:000c).

The new status of each replica is as following:

Some additional records are inserted to the transaction log but they are not related directly to the replicated data lost previously from the old primary SQL141.

Step 3: Replication from the new primary SQL143 is resumed

Here the new status of the replication of each replica:

We can confirm now that we have lost the data remained in the send queue list on the old primary SQL141. The last hardened lsn is not the same between the initial situation and the new situation (260000001501600001 vs 260000001499200001). Remember that the hardened lsn (transaction log block) 260000001499200001 corresponds to the last hardened lsn on the old secondary SQL143 before the synchronization issue.

Step 4: Last step – resuming the database movement on the new secondary SQL141 in order to resynchronize the both replicas

At this point SQL Server has resynchronized perfectly the both replicas. Take a look at the output of the sys.fn_db_log() on each side. We retrieve effectively the same pattern on both sides.

Content of the transaction log on the new secondary SQL141:

Content of the transaction log on the new primary SQL143:

Compared to the first output in the transaction log of the old primary SQL141 we definitively lost the entries identified previously at the top of this article.

Wonderful but how SQL Server has achieved this process? The answer is in the SQL Server error log of the new secondary SQL141:

• First, we notice the recovery LSN (transaction log block) identified by SQL Server for the AdventureWorks2012 database here (260:14992:1). The same value was founded previously by looking at the availability groups DMV and the column last_hardened_lsn. This is the starting point for the resynchronizing process

• Then we resumed manually the replication and we retrieve the event in the error log.

• The new secondary automatically restarted to resynchronize with the current primary. The commit LSN here is probably in the transaction log block (14992) that is lower than the transaction log block related to the Hardened Lsn (15016).

• And finally the most important information in this error log. SQL Server reverted to the transaction log block related to the recovery LSN (260:14992:1) and reinitialize to the last log record redone by the old secondary SQL143.

By David Barbarin