Errors when Joining a Remote Replica to a SQL 2016 Availability Group
I am hoping some one can help me out here. i have searched everywhere and cannot find a solution to my issue. Here is my environment to begin with:
Here is my current environment functioning just fine in our current offices
SQL1-Corp (Primary Synchronous Replica - Corporate Office)
SQL2-Corp (Secondary Synchronous Replica - Corporate Office)
I am trying to add an Azure VM to the Availability Group in order to plan a DR solution
SQL1-Azure (Soon to be Secondary Asynchronous Replica - Azure VM)
Our database that is in the availability group at corporate is a pretty big DB (about 30GB). The process I am doing to add this replica is as follows.
I tried to just add the replica with a "Full" selected as the preference in the data synchronization, but due to the size it failed as it was taking too long to back up and restore across the internet.
With that I decided to take a recent bak of the database and transfer it over to the Azure Replica. It takes about 5 hours to get a VHD with the bak transferred up to Azure and attach the disk to the Azure VM. Once attached I can restore the DB with a NoRecovery setting. The database restores and shows in Restoring state. After that I then proceed through the Add Replica wizard and select Join Only. Given it is now 5 hours after the bak was created. I receive the following error once the wizard tries to join the DB to the Availability Group.
"The remote copy of database "xyz-DB" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)"
The first time I completed this process it went just fine. Months later we finally got a chance to test and the Azure replica got out of sync, so I decided to refresh the DB and received the error above. I then thought the issue was due to me restoring the DB on the Azure VM after another scheduled backup job ran. I then went through the process again before the next backup ran and received the same error.
Any help would be appreciated to fix this error or understand the proper process to add a remote replica with a large DB to an existing availability group.
Here is my current environment functioning just fine in our current offices
SQL1-Corp (Primary Synchronous Replica - Corporate Office)
SQL2-Corp (Secondary Synchronous Replica - Corporate Office)
I am trying to add an Azure VM to the Availability Group in order to plan a DR solution
SQL1-Azure (Soon to be Secondary Asynchronous Replica - Azure VM)
Our database that is in the availability group at corporate is a pretty big DB (about 30GB). The process I am doing to add this replica is as follows.
I tried to just add the replica with a "Full" selected as the preference in the data synchronization, but due to the size it failed as it was taking too long to back up and restore across the internet.
With that I decided to take a recent bak of the database and transfer it over to the Azure Replica. It takes about 5 hours to get a VHD with the bak transferred up to Azure and attach the disk to the Azure VM. Once attached I can restore the DB with a NoRecovery setting. The database restores and shows in Restoring state. After that I then proceed through the Add Replica wizard and select Join Only. Given it is now 5 hours after the bak was created. I receive the following error once the wizard tries to join the DB to the Availability Group.
"The remote copy of database "xyz-DB" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)"
The first time I completed this process it went just fine. Months later we finally got a chance to test and the Azure replica got out of sync, so I decided to refresh the DB and received the error above. I then thought the issue was due to me restoring the DB on the Azure VM after another scheduled backup job ran. I then went through the process again before the next backup ran and received the same error.
Any help would be appreciated to fix this error or understand the proper process to add a remote replica with a large DB to an existing availability group.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THanks for the idea. I am trying it now and will let you know how it works. Makes sense though.
did you check the post that can help you to address the originally posted error?
"The remote copy of database "xyz-DB" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)"
and it is still unclear (see the below Mirror solution that can be used for AG secondary setup in this case)_
https://social.technet.microsoft.com/Forums/Azure/en-US/8395193e-0947-4ce4-8934-e3417a976232/the-remote-copy-of-database-has-not-been-rolled-forward-to-a-point-in-time-that-is-encompassed-in?forum=sqldatabasemirroring
also check this article as an idea
SQL 2012 & High Availability Disaster Recovery in Azure
http://www.oneneck.com/news-events/blog/posts/2016/8/2/sql-2012-high-availability-disaster-recovery-in-azure
"The remote copy of database "xyz-DB" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)"
and it is still unclear (see the below Mirror solution that can be used for AG secondary setup in this case)_
https://social.technet.microsoft.com/Forums/Azure/en-US/8395193e-0947-4ce4-8934-e3417a976232/the-remote-copy-of-database-has-not-been-rolled-forward-to-a-point-in-time-that-is-encompassed-in?forum=sqldatabasemirroring
also check this article as an idea
SQL 2012 & High Availability Disaster Recovery in Azure
http://www.oneneck.com/news-events/blog/posts/2016/8/2/sql-2012-high-availability-disaster-recovery-in-azure
ASKER
Sorry for the delayed feedback. This project is on my list of to do's but I had some other high priority tasks come up I have to attend to. I am hoping to get back on this shortly. I will update you all on the solution that helps me to fix this issue.
ASKER
I was able to solve the problem by reducing the size of the VHD that I was uploading to Azure with the bak file on it. This allowed the transfer to finish in half the time. Once I performed the restore since it was not too long the task of joining to the availability group had no issues.
ASKER