Problem with Enable Availability Group in Secondary Database (Clustering)

Article
07/30/2019

Question

_{Tuesday, July 30, 2019 11:31 AM}

I have existing Cluster Existing Primary Server

I needs to add one new secondary server for the Existing Cluster server, I have added node to the Existing cluster,

I have created Mirroring Endpoint and , Modify the Availability Group for include secondary server details with Endpoint URL

Taken Full Back up the Existing Primary Server Database, Taken Log Back up ,

Restore the backup in the Secondary Server, Restore the log backup in the Secondary server

Some of the databases able to Enable the Availability Group and Sync the database from Primary to Secondary

But huge size database restore giving the below issue.

ALTER DATABASE [WWPGT] SET HADR AVAILABILITY GROUP = RAG01;
GO

Error

The remote copy of database "WWPGT" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

Some time, I am getting an error, while restore the Transaction Log backup

Error

Please help me to rectify the error

Primary server and Secondary server having same edition and SQL Server 2016, and Service pack two, but minor differences in the release

Secondary server

================

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Primary Server

Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64)
Sep 13 2018 22:16:01
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

All replies (13)

_{Wednesday, July 31, 2019 11:45 AM ✅Answered}

1. Taken Full Backup

2. Differential Backup

3. Log backup

4. Restore Full backup , differential backup, log backup into the Secondary server

5. Take again Differential backup

and able to Make DB to Availability Group

but I am not sure the same sequence work for all other huge DB

Thanks for all of your support

Regards

Nagarajan

_{Friday, August 2, 2019 5:44 AM ✅Answered}

Hi,

I have completed all huge DB to Enable Clustering

Based on the Database size the steps for taking backup will be vary, if small database then

Full backup of the DB is alone enough

If Huge database then

1. Take Full Backup

2. Take Differential Backup

3. Take Transaction Log Backup

4. Restore these backup into the Secondary Server

5. Again, needs to take Differential backup, and Restore into Secondary

(if we try to take Transaction log backup and restore into secondary, giving error , and also not able to understand these Log as recent, So take Differential backup)

Thanks for all of your support

Regards

Nagarajan

_{Tuesday, July 30, 2019 11:35 AM | 1 vote}

Hi Nagarajan,

You can try restoring the differential backup and then restore log backup before joining to Always on.

Hope it helps.

ManyThanks, NARI

_{Wednesday, July 31, 2019 12:01 AM}

Looks like additional log backups have possibly occurred since you applied the log backup you took. Check for additional log backups on Primary, apply on replica with norecovery and retry add.

_{Wednesday, July 31, 2019 4:21 AM}

Please review about my scripts

Taking backup from the primary server

BACKUP DATABASE [EPGCommerce]
TO DISK = N'\LSD-DB01\SharedFolder\EPGCommerce.bak'
WITH FORMAT ,INIt
GO

BACKUP DATABASE [EPGCommerce] TO DISK = N'\LSD-DB01\SharedFolder\EPGCommerce.dif' WITH init, DIFFERENTIAL

BACKUP LOG [EPGCommerce]
TO DISK = N'\LSD-DB01\SharedFolder\EPGCommerce.trn'
WITH Init,NOFORMAT
GO

Restoring Full backup , Differential, Log backup to Secondary server

RESTORE DATABASE [EPGCommerce]
FROM DISK=N'\LSD-DB01\SharedFolder\EPGCommerce.bak'
WITH NORECOVERY,
MOVE 'EPGCommerce' TO
'D:\Data\EPGCommerce.mdf',
MOVE 'EPGCommerce_log' TO
'L:\Log\EPGCommerce_log.ldf';
GO

RESTORE DATABASE [EPGCommerce]
FROM DISK=N'\LSD-DB01\SharedFolder\EPGCommerce.dif'
WITH NORECOVERY,
MOVE 'EPGCommerce' TO
'D:\Data\EPGCommerce.mdf',
MOVE 'EPGCommerce_log' TO
'L:\Log\EPGCommerce_log.ldf';
GO

RESTORE LOG [EPGCommerce] FROM DISK = N'\LSD-DB01\SharedFolder\EPGCommerce.trn' WITH NORECOVERY, NOUNLOAD;

After restored all backup, I have executed the below query on the secondary server

ALTER DATABASE [EPGCommerce] SET HADR AVAILABILITY GROUP = EAG01;
GO

then getting the same error

The remote copy of database "EPGCommerce" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

I have checked any other Log backup happened in Primary server using following query

select top 5 d.name, b.backup_finish_date as 'Last LOG Backup',b.first_lsn,b.last_lsn,b.checkpoint_lsn,b.database_backup_lsn
from sys.databases d
join msdb.dbo.backupset b
on d.name = b.database_name
where --d.recovery_model_desc ='SIMPLE' and
b.type = 'L' and d.name = 'EPGCommerce'
order by b.backup_finish_date desc

But I could not find any new log back up in the Primary server, it displays the log file, which I already restored into secondary server

Please advise me

Thanks & Regards

Nagarajan

_{Wednesday, July 31, 2019 4:22 AM}

Please review about my scripts

Taking backup from the primary server

BACKUP DATABASE [EPGCommerce]
TO DISK = N'\LSD-DB01\SharedFolder\EPGCommerce.bak'
WITH FORMAT ,INIt
GO

BACKUP DATABASE [EPGCommerce] TO DISK = N'\LSD-DB01\SharedFolder\EPGCommerce.dif' WITH init, DIFFERENTIAL

BACKUP LOG [EPGCommerce]
TO DISK = N'\LSD-DB01\SharedFolder\EPGCommerce.trn'
WITH Init,NOFORMAT
GO

Restoring Full backup , Differential, Log backup to Secondary server

RESTORE LOG [EPGCommerce] FROM DISK = N'\LSD-DB01\SharedFolder\EPGCommerce.trn' WITH NORECOVERY, NOUNLOAD;

After restored all backup, I have executed the below query on the secondary server

ALTER DATABASE [EPGCommerce] SET HADR AVAILABILITY GROUP = EAG01;
GO

then getting the same error

The remote copy of database "EPGCommerce" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

I have checked any other Log backup happened in Primary server using following query

But I could not find any new log back up in the Primary server, it displays the log file, which I already restored into secondary server

Please advise me

Thanks & Regards

Nagarajan

_{Wednesday, July 31, 2019 5:51 AM}

Hi Nagarajan,

Script is fine,,

As per the error it is clearly saying that

"The remote copy of database "EPGCommerce" has not been rolled forward to a point in time that is encompassed in the local copy of the database log."

Which means after completion if your restore sequence also it is still not sufficient enough to catch up with primary server's log to sync up.

Hence you will have to perform further log backups and restore them in sequence to secondary. that too very quick and fast.

i suggest you take the another log backup from primary and quickly apply it on secondary before you join it.

***The key factor to remember here is ,you need to reduce the gap between the primary & secondary as much as possible before joining the db to group so that it can catch up with its syncing process.***

ManyThanks, NARI

_{Wednesday, July 31, 2019 7:09 AM | 1 vote}

Hi Naagarajan,

>>The remote copy of database "WWPGT" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

Would you please try to take another log backup on the primary replica and apply it on the secondary replica with norecovery?

Best regards,

Dedmon Dai

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

_{Wednesday, July 31, 2019 7:26 AM}

If I take another log backup and try to restore the log backup then

it saying as

Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 822668000007199700001, which is too recent to apply to the database. An earlier log backup that includes LSN 822463000042831400001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

If I refer the Version for "LSN 822463000042831400001", it is nothing but old log backup, which I earlier taken, again restore the old log backup, and tried to restore the new log backup, then again getting the same issue.

I am suspecting may be the issue because of the SQL Server Version minor difference?

but smaller db will not give any problem

Secondary server (version)

================

Primary Server

Thanks & Regards

Nagarajan

_{Wednesday, July 31, 2019 7:41 AM | 1 vote}

Hi Nagarajan,

It is evident that log chain has been broken some how with regards to the latest log backup:

You have 2 options:

Option 1:

i) Take Differential back then take fresh log backups and apply these latest backups in sequence.

Option2:

Start with Full ,DIff and Log backups freshly and apply them in sequence.

ManyThanks, NARI

_{Wednesday, July 31, 2019 7:45 AM}

Hi Naagarajan,

This means that between the latest log backup and the previous log backup, you have other log backups that are not applied to the secondary replica.

I recommend that you re-complete the full backup and log backup, then restore them to the secondary replica in turn.

Best regards,

Dedmon Dai

_{Thursday, August 1, 2019 9:12 AM}

Hi Naagarajan,

I am glad to know that your problem has been solved In order to close this thread, please mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,

Dedmon Dai

_{Friday, August 2, 2019 9:10 AM}

Hi Nagaraju,

Felt Happy that you have successfully resolved it!!

Please **"Mark as Answer" **the response that helped your issue, so that this help others in identifying and quickly finding it as useful article.

Thanks Again!!!

ManyThanks, NARI

Share via

Problem with Enable Availability Group in Secondary Database (Clustering)

Question

All replies (13)

Additional resources