Sql-server – Can’t resume synchronization after patch failed on secondary node of Availability Group

availability-groupspatchingsql server

I applied Service Pack 4 on secondary node of an Availability Group. It failed so I restored the whole VM for the secondary node to an earlier version, and now the data is not synchronizing.

I have active and passive two nodes. I tried to resume the data movement manually. But it failed. Can you suggest anything here?

Best Answer

When you restored the VM, its LSN was no longer in sync with the primary and thus it can't be resumed. The primary is too far ahead to resume.

You'll need to drop the secondary and add it to the Availability Group again in order to resume synchronizing. You can do this with an existing FULL backup plus the LOG chain.

Related Solutions

SQL Server – Availability Group Database Stuck in Not Synchronizing Mode

Since the server had been offline for a while we thought it may have gone outside the recovery window of the primary. We decided to try applying the latest transaction logs on the database to see if that would kick-start the recovery process:

-- Remove database from Availability Group:    
Alter Database [StackExchange.Bicycles.Meta] SET HADR OFF;

-- Apply t-logs to catch up. This can be done manually in SSMS or via:
RESTORE LOG [StackExchange.Bicycles.Meta] FROM DISK = '\\ny-back01\backups\SQL\_Trans\SENetwork_AG\StackExchange.Bicycles.Meta\StackExchange.Bicycles.Meta_LOG_20160217_033201.trn' WITH NORECOVERY;

-- Re-join database to availability group
ALTER DATABASE [StackExchange.Bicycles.Meta] SET HADR AVAILABILITY GROUP = [SENetwork_AG];
ALTER DATABASE [StackExchange.Bicycles.Meta] SET HADR RESUME;

Afer running the above on the secondary server for both databases they were able to start synchronizing again.

UPDATE: We had a similar issue where after a Manual AG Failover one of the databases on the new primary replica was stuck in Not Synchronizing mode (switched to Not Synchronizing / Recovery Pending after restarting SQL Server), and the above steps worked to resolve that issue as well.

Sql-server – AG (SQLServer 2014) – Suspend Data Movement

The short answer is No you cannot do that. First, if you suspend the data movement in one of the availability group database, you no longer able to failover it to your secondary node. Unless you make a forced failover. If you want to suspend the data movement in one of your databases, and also want to make the failover, you have to remove that specific database from the availability group and change the data. However, to add it back to the availability group, first you need to synchronize it back again.

Best Answer

Related Solutions

SQL Server – Availability Group Database Stuck in Not Synchronizing Mode

Sql-server – AG (SQLServer 2014) – Suspend Data Movement

Related Question