Migrating database from AlwaysOn AG to another with JoinOnly option
Hi all,
In this blog, I am covering a scenario of migrating a SQL database from one AlwaysOn Availability Group (AG) to a new AG. I was involved in such requirement and the goal was to migrate the database which was in terabytes size to another AG without having to apply the Full back up of the database during AG configuration initial data synchronization. We were able to achieve this with AlwaysOn “Join Only” initial data synchronization option.
Here I am covering 2 approaches with which we could migrate the database effectively.
To demonstrate the scenario, I am using the servers with below setup:
2 node Windows 2012 R2 Cluster with SQL 2016 standalone default instances.
Node 1: SQLNode1.contoso.lab
Node 2: SQLNode2.contoso.lab
SQL Availability Group: SQLAG
Databases: pubs and Northwind
Existing Configuration: 
Maintenance plan is created on both the replicas to take the Log backup of the databases and the backup preference is set to “Prefer Primary”. Hence all the log backups are triggered on SQLNode1 replica which is the primary replica of existing configuration.
Requirement:
Requirement is to move the database NorthWind out of the SQL availability group SQLAG and add it to the newly created AG NorthwindAG.
1st Approach:
To remove the database out of the availability group: 
Once the database is removed out of AG, on the secondary replica SQLNODE2, the database state initially would go to “NOT SYNCHRONIZING” and then goes to restoring state.
Now, if you try to add the database Northwind to the new availability group “NorthWindAG” with Join Only option ( To avoid taking Full back up on primary replica)
Joining the database Northwind to new AG progress spins for a while and eventually fails with the below error:
You may get one of the below errors:
Error 1: 
Failed to join the database 'Northwind' to the availability group 'NorthWindAG' on the availability replica 'SQLNODE2'. (Microsoft.SqlServer.Management.HadrModel)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The mirror database, "Northwind", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)
Error 2: 
Failed to join the database 'Northwind' to the availability group 'NorthWindAG' on the availability replica 'SQLNODE2'. (Microsoft.SqlServer.Management.HadrModel)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The remote copy of database "Northwind" 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 above error clearly indicates that that there are few pending Log blocks which are not yet applied on the secondary replica and hence causing insufficient transaction log data on the secondary replica.
Now, to ensure that that the transaction log of the secondary replica is intact with the primary, we need to restore the T log backups on the secondary. Reviewing the backup location, there could be multiple Log backup files. To identify the backup file which contains the “Redo Start LSN” which is the start LSN from where the log need to be applied, you could simply pick a random backup file and try restoring it which will fail with the transaction log chain mismatch error. But the good thing is the error, contains the REDO Start LSN. 
Restore failed for Server 'SQLNODE2'. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: The log in this backup set terminates at LSN 167000000315200001, which is too early to apply to the database. A more recent log backup that includes LSN 177000001055200001 can be restored. (Microsoft.SqlServer.Smo)
Other approach would be run the below command on the secondary replica and get the “Redo Start LSN”
select database_id, name, redo_start_lsn, * from sys.master_files where database_id=db_id('Northwind')
Once you get the REDO START LSN, you could run the below query on primary replica where the Log backups are run (In this scenario, primary replica) to get the appropriate backup file which need to be applied on the secondary replica.
declare @redo_start_lsn varchar(200) = 177000001055200001 --get this value from sys.master_files of the restoring database
select name,first_lsn,last_lsn from msdb.dbo.backupset where
(first_lsn < @redo_start_lsn and last_lsn > @redo_start_lsn ) and
database_name = 'Northwind' and type = 'L'
The next objective is to apply all the rest of the transaction log backups following the t Log backup applied in above step and once all the transaction log backups are applied, setup a new AG with Join Only option and this time, joining the database to AG on secondary replica should go through fine.
2nd approach:
Assuming that there are changes happening on the primary database replica once the database is taken out of AG and you don’t want to apply any transaction log backups manually, simplest way to add the database to new AG is to disable the Log backup happening on the primary before the database is taken out of the AG. Ensure that there are no log backups triggered post removing the database out the existing AG. Next step is to create a new AG and select Join Only option and SQL reads the transaction log on the primary and takes care of the initial synchronization.
If the log backup job is not stopped, with the log backups, transaction logs get truncated. If the Log backups are not available for some reason then the only way to re-synchronize is to use the Full backup which is not desired. Only downside is that till the time, the database is added to the new AG, log would grow on the primary replica.
Please share your feedback, questions and/or suggestions.
Thanks,
Don Castelino | Premier Filed Engineer | Microsoft
Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.