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.
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.
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.
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.
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.
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;
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
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
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…”
Once you click on this option another window named “Join Database to Availability Group” will appear on your screen as shown below.
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.
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.
Next Steps
- Explore more knowledge on SQL Server Database Administration Tips
- Learn how to configure SQL Server AlwaysOn in to multi-subnet network
- Learn how to Removing a Secondary Replica from a SQL Server AlwaysON Availability Group
- Learn how to Add secondary replica to existing SQL Server AlwaysON Availability Group
