AlwaysOn Availability-Solution Error: 1408
AlwaysOn Availability Group Error: 1408
Three node SQL Server AlwaysOn environment. During the configuration we received "Error: 1408 Joining database on secondary replica resulted in an error."
Error Msg: The remote copy of database "DRTest" is not recovered far enough to enable database mirroring or to join it to the availability group. You need to apply missing log records to the remote database by restoring the current log backups from the principal/primary database. (Microsoft SQL Server, Error: 1408)". That means the source and destination databases are not in sync. We will fix this issue in this tip. Click on the OK button to close this window.
Solution: We can use the COPY_ONLY backup option to not break the LSN numbers of the log files.
-Run the below command to issue a full backup of both databases
BACKUP DATABASE DRTest TO DISK = 'F:\BACKUP\DRTest_Copy.bak'WITH COPY_ONLY;GOBACKUP DATABASE Test TO DISK = 'F:\BACKUP\Test_Copy.bak'
WITH COPY_ONLY;
I executed above commands on the primary replica to take a full backup of both databases.
Now we will run transaction log backup of these databases then we will restore them on SEC-DB2 replica.
--Run below command to issue Transaction log backup of both databases BACKUP LOG DRTest TO DISK = 'F:\BACKUP\DRTest_Copy.trn'WITH COPY_ONLY;GOBACKUP LOG Test TO DISK = 'F:\BACKUP\Test_Copy.trn'WITH COPY_ONLY;
Now we will restore both backup files on the secondary replica SEC-DB2
--Run below command to restore both databases into NO RECOVERY mode. RESTORE DATABASE DRTest FROM DISK = '\\PRI-DB1\F$\BACKUP\DRTest_Copy.BAK'WITH NORECOVERY, REPLACE;GORESTORE DATABASE Test FROM DISK = '\\PRI-DB1\F$\BACKUP\Test_Copy.BAK'WITH NORECOVERY, REPLACE
--Run below command to restore transaction log backups on both databases into NO RECOVERY mode. RESTORE LOG DRTest FROM DISK = '\\PRI-DB1\F$\BACKUP\DRTest_Copy.trn'WITH NORECOVERY, REPLACE;GORESTORE LOG Test FROM DISK = '\\PRI-DB1\F$\BACKUP\Test_Copy.trn'WITH NORECOVERY, REPLACE
Once you click on this option another window named "Join Database to Availability Group" will appear on your screen
The Availability Group state is now healthy and all status values are green.
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL SERVER – AlwaysOn Join Error – Msg 1408, Level 16 – The Remote Copy of Database “SQLAUTH” is Not Recovered Far Enough to Enable Database Mirroring or to Join it to the Availability Group.
One of my customer who had a multi GB database wanted to configure the AlwaysOn availability group and add a replica for disaster recovery purposes as well. They already had a secondary replica in the same data center for high availability purposes.Below error facing.
Since its not advisable to use UI for such big database to initialize the secondary, they were initializing secondary by will manual backup / restore of backup from primary followed by “join” option in the wizard. Whenever they tried this, below was the error.
Error 1408 while trying to join the database to AG. Error is reported when there is a log backup taken from primary replica and there is a mismatch of LSN between the restored copy of secondary and current copy on the primary.
Solution : Error is due to the LSN mismatch, they missed one of the core concepts of a backup taken from the AlwaysOn availability database.
It was easy to explain and demonstrate that the error would appear when a log backup is taken, after the full back-up from primary, even on a secondary replica. I used my below blog to find the history of the backup of the database.
When we ran the same query on the current secondary replica (remember, they already had one secondary) we found that log backups are taken regularly on that replica. As soon as we restored all the pending log backups to new secondary and tried to join option again – it worked!
AlwaysOn Availability Replica is in Resolving State
why it's in "RESOLVING" while the AG has not been setup for an automatic fail-over.
The reason for the lease expiration perhaps was due to "sp_server_diagnostics" not returning the result quickly enough. There were a few entries in the SQLDIAG such as "[hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel" and "[hadrag] Failure detected, diagnostics heartbeat is lost" and "[hadrag] ODBC Error: [HYT00] [Microsoft][SQL Server Native Client 11.0]Query timeout expired (0)".
If your node is constantly going down because of different reasons you can schedule a script to bring it online automatically. solution in Windows/Scripting section
EXAMPLE 1 Review the cluster diagnostic logs
Consider a scenario where the availability group FAILURE_CONDITION_LEVEL is set to 3 and a health issue transitions the availability group from PRIMARY to RESOLVING. To diagnose, open the cluster diagnostic logs on the primary replica, and by filtering we find a system component_health_result event reporting error.
This will affect automatic failover also incase primary replica goes down.
EXAMPLE 2 Review the cluster diagnostic logs
-> Checked if NT Authority\System is created and has appropriate permission.
-> It seems like NT Authority\System was removed from SQL Server Instance.
-> Recreated NT Authority\System on SQL Server instance and provide required permission.
USE [master]
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO