Skip to main content
  • Place orders quickly and easily
  • View orders and track your shipping status
  • Enjoy members-only rewards and discounts
  • Create and access a list of your products
  • Manage your Dell EMC sites, products, and product-level contacts using Company Administration.
Some article numbers may have changed. If this isn't what you're looking for, try searching all articles. Search articles

Avamar: How to successfully restore SQL Databases on the secondary replica in an AlwaysOn cluster settings

Summary: Avamar 19.4.100-124 with SQL 2016 AlwaysOn 2 node cluster Customer doing redirect restore of the SQL Databases on the secodnary replica and although Avamar restore completes Databases fail to join the cluster with error : Failed to join the database 'ABC' to the availability group 'AG' on the availability replica 'secondary\AG_Sec' ...

This article may have been automatically translated. If you have any feedback regarding its quality, please let us know using the form at the bottom of this page.

Article Content


Instructions

  • A redirected restore of an Avamar backed up SQL Database is run to the secondary Replica.
  • The restore completes.
  • The Database on the secondary is in "Restoring state".
  • When trying to join to the Availability Group an error is displayed:

Failed to join the database 'ABC' to the availability group 'AG' on the availability replica 'secondary\AG_Sec'.

This procedure is documented in the Avamar SQL Guide under the section "Restore to the original availability group". It reads:
 
When you restore to the original availability group, the restore process can automatically restore the databases on both the primary replica and the secondary replicas. 
You can also restore databases only on the primary replica. 
When you restore a  database only on the primary replica, the corresponding database on the secondary replicas is in a restoring state. 
To restore the databases on the secondary replicas as part of the availability group, manually prepare and restore the databases, and join them to the availability group on the secondary replicas. 
You can also set the databases on a secondary replica online without rejoining them to the availability group by restoring the databases with the RECOVERY recovery operation. 

The secondary is expected to be in a restoring state. The problem is that the Database  'ABC' on the secondary replica 'secondary\AG_Sec' is not in sync with the primary replica. This is because it is missing some log records.

SQL Server AlwaysOn is not able to apply the remaining logs to keep them both synchronized. 

In such a situation the first step is to verify the LSN sequence for Database 'ABC' from both replica nodes.

Run the following query to check this:
 
SELECT msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.type,
msdb.dbo.backupset.database_backup_lsn,
msdb.dbo.backupset.first_lsn,
msdb.dbo.backupset.last_lsn
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE database_name IN ('ABC')
--and msdb.dbo.backupset.backup_finish_date between '1/20/2022' and '12/23/2022'
ORDER BY 
2 DESC,
3 DESC

This gives you a complete backup history detail for ('ABC') for year 2022.
You can customize the date range to meet your needs.
The important point is check if the Primary and Secondary LSN numbers for this Database are in Sync.

To resolve restore the missing log backups on the secondary from the Primary:
Back up and then check to make sure that LSN nunbers in sync before joining the Database to the AlwaysOn Group. 

Article Properties


Last Published Date

31 Jul 2023

Version

2

Article Type

How To