1

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 :

  1. 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 :

enter image description here

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 :

enter image description here

and I can't Add a database using "add database" because they are already member of the AvailabilityGroup on the fist secondary replicate :

enter image description here

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 ?

1

Yes, you will need to manually backup and restore the databases, with logs, to the new secondary and then join the databases to the AG. This might help.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.