I have an SQL Server 2012 AlwaysON cluster composed of :
win-mssql1 : witness file share
win-mssql2 (INSTANCE 1) : primary server
win-mssql3 (INSTANCE 2) : secondary server
Recently I had the host "win-mssql4 (INSTANCE 3)" as a new secondary replica. My goal is to :
- Manually synchronise a huge database (let's call it "BaseSyncManual") with this new server. 2.Later, synchronize the other databases which are in the AG.
To do the first step, I have used the followings commands :
On primary server :
BACKUP DATABASE BaseSyncManual TO DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_full.bak'; BACKUP log BaseSyncManual TO DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_log_2.trn';
On secondary server :
RESTORE DATABASE BaseSyncManual FROM DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_full.bak' WITH NORECOVERY RESTORE DATABASE BaseSyncManual FROM DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_log_2.trn' WITH NORECOVERY
On primary : start the wizard to add a replica and choose "skip initial data synchronisation"
On secondary :
alter database BaseSyncManual set hadr availability group = AvailabilityGroup1;
Everything runs fine and the result is what I have expected :
Now I want to re-run the "add a new replica assistant" in order to synchronize the remaining databases (Base1,Base2,BaseSyncAuto) with the "full" parameter (automatically backup restore and join to AG). But there is no way to do that ! the replica as already been added to the AvailabilityGroup :
and I can't Add a database using "add database" because they are already member of the AvailabilityGroup on the fist secondary replicate :
What are my options to sync those databases with my new secondary replica ? Am I condemned to do it with manual backup-restore-join to AG ?