Fix SQL Server AlwaysOn Availability Group Error: 1408 Joining database on secondary replica resulted in an error

By:   |   Updated: 2017-06-28   |   Comments (9)   |   Related: > Availability Groups


Problem

I have been configuring a three node SQL Server AlwaysOn environment.  During the configuration we received "Error: 1408 Joining database on secondary replica resulted in an error."  How did this error occur and how do we resolve it?

Solution

We have three machines named PRI-DB1 ( IP: 10.X.3.XXX ), PRI-DB2 ( IP: 10.X.4.XXX ) and SEC-DB2 ( IP: 172.X.15.XXX ). The IPs of all machines reflect their subnet which belongs to a different series of addresses independent of each other. PRI-DB1 and PRI-DB2 are hosted in the corporate data center whereas SEC-DB2 is hosted in the Amazon cloud platform. All three machines are running Windows Server 2012 R2 Enterprise Edition and SQL Server 2014 Enterprise Edition. PRI-DB1 will be the primary Replica and the remaining two nodes are secondary replicas. Data replication between PRI-DB1 and PRI-DB2 will use the synchronous-commit mode and failover mode will be Automatic with no data loss which can be used for High Availability (HA) in case the primary replica goes down. Data replication between PRI-DB1 and SEC-DB2 use asynchronous mode and failover mode is Manual which can cause some data loss in a disaster recovery (DR) scenario.

Before going ahead, I have followed the step by step process to configure SQL Server AlwaysOn between multi-subnet cluster, but the process ended with an Error 1408 at the end of the SQL Server AlwaysOn configuration window. SQL Server AlwaysOn failed with the following error: Joining database on secondary replica resulted in an error. (Error: 1408). You will notice that this error only appeared on the secondary replica i.e. SEC-DB2. SQL Server AlwaysOn configuration has worked well on the other secondary replica (PRI-DB2). Let's go and check the issue.

Note:  This tip will not cover the SQL Server installation, building a Windows Server Failover Cluster steps and initial SQL Server AlwaysOn configuration steps in this tip. You can review my latest tips to complete these items. I am assuming that these steps will be completed prior to fixing the issue in this tip.

NOTE : MAKE SURE TO IMPLEMENT THIS SOLUTION IN LOWER LIFE CYCLE. DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTING IN LOWER-LIFE CYCLE ENVIRNOMENTS.

Troubleshooting AlwaysOn Error 1408

Step 1: As mentioned above, this tip will not demonstrate the steps to configure SQL Server AlwaysOn because I am assuming that you have already gone through the steps mentioned in my latest tips. At this point, I will go directly to the last window of the SQL Server AlwaysOn configuration where we get errors post execution as shown below.

Error during configuring AlwaysON

Here you can see that SQL Server AlwaysOn was successfully configured on secondary replica PRI-DB2 whereas an error was thrown for the secondary replica SEC-DB2. Now we will check the error details to fix this issue. You can click on the  "Error" link to get the details of the error as shown in the below screenshot.

Error during configuring AlwaysON

We can see in the error details that "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.

Step 2: Now if you launch the SQL Server AlwaysOn dashboard report then you can see the current state of  the AlwaysOn configuration as shown in the below screenshot.

AlwaysON dashboard

You can see the Availability Group state is not healthy and is showing a warning. If you look down the list, you will see the warning for the secondary replica SEC-DB2 whereas another secondary replica PRI-DB2 is green indicating a "Synchronized" state. Now click on the "Warnings (1)" link to get more details. Once you click on the warning link you can see that the issue is due to the synchronization issue between the availability databases on the primary and this secondary replica.

AlwaysON dashboard error

Step 3: As we have seen, the availability databases on the secondary replica SEC-DB2 are not in sync with the primary replica because they are missing some log records.  This is why SQL Server AlwaysOn is not able to apply the remaining logs to keep them synchronized. Now we will take a full backup and transaction log backup of both databases (DRTest and Test) on the primary replica and then we will restore it on the secondary replica SEC-DB2. 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;
GO

BACKUP 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.

Full Backup

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;
GO

BACKUP LOG Test
TO DISK = 'F:\BACKUP\Test_Copy.trn'
WITH COPY_ONLY;
Tlog backup

Step 4: Now we will restore both backup files on the secondary replica SEC-DB2. Either copy these backup files to the secondary replica and restore there or use a network path to access the backup files to restore both databases as I have done in the below screenshot. Run the below command to restore both databases with the NORECOVERY option on the secondary replica.

--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;
GO

RESTORE DATABASE Test
FROM DISK = '\\PRI-DB1\F$\BACKUP\Test_Copy.BAK'
WITH NORECOVERY, REPLACE
restore on secondary replica

As we can see both databases have been restored successfully, so the next step is to apply the transaction log backups which we have taken after the full backup. Run the below commands to apply the transaction log backups on these databases.

--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;
GO

RESTORE LOG Test
FROM DISK = '\\PRI-DB1\F$\BACKUP\Test_Copy.trn'
WITH NORECOVERY, REPLACE
restore log backups on secondary replica

Step 5: You can see a "warning" icon on both availability databases on replica SEC-DB2. Ideally these availability databases should be green. So now we will join both databases to the Availability Group, so they show that they are healthy with a green status. Right click on database "DRTest" and choose "Join to Availability Group..."

Join to Availability Group

Once you click on this option another window named "Join Database to Availability Group" will appear on your screen as shown below.

join Database to Availability Group window

Step 6: You can see the details about the database name on the screen. Now click on the "OK" button to proceed with the process of joining the Availability Group. Once you click on the OK button the execution will start and then disappear from your screen if it has executed successfully. The status of the availability databases should then become green.  If there is an issue, it will appear on the screen with the error details.

Step 7: Now repeat step 5 and step 6 for the second availability database "Test" to add it to the availability group DBAG.

Step 8: You might be see the red cross bar for the availability database after adding them to Availability Group for few seconds. Don't worry about that just right click on the Availability Databases folder and choose the Refresh option. Once refreshed, you can see Availability Database state should be healthy and in green as shown in the below screenshot.

Availability database state post adding them to AG

Step 9: Now go to the primary replica PRI-DB1 and launch the SQL Server AlwaysOn dashboard report for this configuration. We can see the dashboard report for Availability Group "DBAG" in the right pane. The Availability Group state is now healthy and all status values are green. You can validate the configuration for the secondary replica SEC-DB2 as well as shown below.

AG dashboard report
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2017-06-28

Comments For This Article




Thursday, May 19, 2022 - 2:12:31 AM - Rahul Shende Back To Top (90100)
Thanks a lot for such a detail explanation. It's very useful.

Tuesday, April 13, 2021 - 11:44:00 AM - vic Back To Top (88521)
Manvendra thank you for sharing.
This was very helpful.

Friday, February 19, 2021 - 6:25:55 AM - jangar Back To Top (88264)
:primary sql
BACKUP LOG dbname
TO DISK = N'\\storage\backup\AG_dbname_copyonly.trn'
WITH NOFORMAT, NOINIT,
NAME = N'dbname-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

:secondary sql
RESTORE LOG dbname
FROM DISK = N\\storage\backup\AG_dbname_copyonly.trn'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO

-- after that
-- attempt joining to primary successfully

Wednesday, July 17, 2019 - 3:13:58 PM - Landry D Salles Fh Back To Top (81782)

Strange... COPY_ONLY fail on sync Database Mirror at SQL Server 2012.

Sunday, December 24, 2017 - 11:41:53 PM - Krishna Kumar Rai Back To Top (74430)

 Thanks a lot for this article

 

Thursday, June 29, 2017 - 4:21:08 PM - Manvendra Back To Top (58570)

Hi Robert

Technically you are right and it should work even logs were able to apply on secondaries but issue got fixed post resynching the secondary dbs. That is why i recommend to go with fresh reinitialization of secondary dbs. If anybody is facing this issue then he should try to fix by just applying pending log backups and if it is not working then go with resynching. Moral of the story is that all dbs should be in sync  at time of creation if not this error will come.

Wednesday, June 28, 2017 - 9:02:33 PM - Robert L Davis Back To Top (58515)

In that case, a full backup was taken since you started the AG set up and the log records from before that full were still missing. Restoring log files sould still have worked if you had restored the right ones.

Wednesday, June 28, 2017 - 11:43:45 AM - Manvendra Back To Top (58499)

Thank you for your comment Robert.

I tried both differential as well as log backup. I was able to apply them on secondary dbs but this has not fixed the issue. Once i took full backup along with tlog backup and apply it on secondary then this has fixed the issue.

Wednesday, June 28, 2017 - 9:14:09 AM - Robert L Davis Back To Top (58491)

You did NOT need to do a new full backup. You could have taken a differential backup and restored it with norecovery or you could have simply applied the new log backups that had been taken on the primary. Either of these options would have been better.

Also, the key take away here is that you should disable your log backup job while setting up an AG because the secondaries must be up to date with that last log backup taken on the primary.














get free sql tips
agree to terms