SQL Server – Adding DB to Always On AG and Setting Up Replicas at Different Times

availability-groupssql server

I have a 3-node SQL Server Always On availability group. 2 nodes are on the same LAN, and the third one is on a remote site with high latency.

I will add a huge DB (10TB) to the AG, so I would like to be able to just get it added to 2 replicas on the LAN in a first stage, and some days later, to get it added on the 3rd (remote) replica after manually copying it on-site.

Is this a supported scenario?
If so…What would be the recommended approach?

Adding the database to all nodes at the same time is not an option unfortunately.

Best Answer

Is this a supported scenario? If so...What would be the recommended approach?

Yes, you can do that, one of the way by creating new availability group so that exiting AG databases would not be interrupted, an availability group can be created without adding database and additional replicas into it.

In your case you can follow the same approach, following are the steps that you may want to follow:

Summarized steps:

  1. Create an AG without adding database and secondary replicas
  2. Add the desired database into AG
  3. Restore the database on secondary replica WITH NORECOVERY option (that is in same LAN)
  4. Add the secondary replica into AG
  5. Join the secondary replica (added in step 4)
  6. Set HADR of the secondary database (restored in step:3) to add into AG

Steps 3,4,5 and 6 can be repeated for 3rd replica once it ready.

Detailed steps:

To create an empty Availability Group, select New Availability Group option via
SSMS → Always On High Availability → Availability Groups (right click)

enter image description here

Once an empty Availability Group created, you can apply following commands accordingly (Primary and Secondary), same steps can be done using SSMS (GUI).

on PRIMARY replica

ALTER DATABASE Test_2RepAG SET RECOVERY FULL; 
BACKUP DATABASE Test_2RepAG TO DISK = '\\Shared\SQL-Backups\TestDB.bak'; 
GO

USE MASTER;
ALTER AVAILABILITY GROUP [2RepAG] MODIFY REPLICA ON 'PrimaryRep\SQL1' with
    (ENDPOINT_URL = 'TCP://PrimaryRep.Domain.LOCAL:5023'); ---- Just to be safe-side as it it might add default port via wizard 

ALTER AVAILABILITY GROUP [2RepAG] ADD DATABASE Test_2RepAG;

ALTER AVAILABILITY GROUP [2RepAG] ADD REPLICA ON 'Secondary1\SQL1' with
    (  
       ENDPOINT_URL = 'TCP://Secondary1.Domain.LOCAL:5023',  
       AVAILABILITY_MODE =  SYNCHRONOUS_COMMIT, --{SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT | CONFIGURATION_ONLY },  
       FAILOVER_MODE = AUTOMATIC --{ AUTOMATIC | MANUAL }   
    );
GO

on SECONDARY replica

RESTORE DATABASE Test_2RepAG FROM DISK = '\\Shared\SQL-Backups\TestDB.bak' with stats, replace, NORECOVERY;
GO

ALTER AVAILABILITY GROUP [2RepAG] JOIN;
ALTER DATABASE Test_2RepAG set HADR AVAILABILITY GROUP = [2RepAG];

You can use following commands once you decided to add 3rd replica, restore the desired database at 3rd replica with recent backups WITH NORECOVERY option.

on PRIMARY for 3rd Replica

USER master;
ALTER AVAILABILITY GROUP [2RepAG] ADD REPLICA ON 'Secondary2\SQL1' with
    (  
       ENDPOINT_URL = 'TCP://Secondary2.Domain.LOCAL:5023',  
       AVAILABILITY_MODE =  ASYNCHRONOUS_COMMIT, --{SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT | CONFIGURATION_ONLY },  
       FAILOVER_MODE = MANUAL --{ AUTOMATIC | MANUAL }   
    );

on 3rd Replica

RESTORE DATABASE Test_2RepAG FROM DISK = '\\Shared\SQL-Backups\TestDB.bak' with stats, replace, NORECOVERY;
GO

ALTER AVAILABILITY GROUP [2RepAG] JOIN;
ALTER DATABASE Test_2RepAG set HADR AVAILABILITY GROUP = [2RepAG];

Although, an AG can be created using 2 replicas while WSFC has 3 replicas (nodes), the health of AG and it's listener decided by 3 nodes at WSFC unless no quorum votes counted from 3rd node in WSFC. For more details..

Related Question