Is it possible to add a DB to an Always On AG and setup replicas at different time?

Question

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.

Answer 1

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)

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

Answer 2

I would recommend you either restore/attach the database to the primary node on the SQL server. From there you could add it to the AG but not have it replicate. Ultimately, to get it on the 3rd(remote) node - you'd likely have to break the AG and re-add the database (if you're allowed the downtime) after it's already copied to the remote node. This would allow it to sync up.

So this approach would mean you'd have 3 stages:

• Stage 1: 10TB Database on Primary Node restored/attached - not in AG
• Stage 2: 10TB Database added to AG. Remove remote node from AG.
• Stage 3: Copy 10TB on remote node. Rebuild AG to include remote node. Allow AG to sync and catch up with data.

If this isn't clear let me know more so I can help you (such as OS/SQL version).

Answer 3

If I understand the question, yes this is a supported scenario and it can be done quite easily without as much work as the other answers. You do not have to remove the remote node from the AG or create a new AG to do this. High level steps would be to go through the SSMS wizard to add the DB to the AG, and at the end hit the button to create the scripts, and cancel the wizard. Use the parts of the script that sync and join the database on the local secondary. Then when you're ready to do the remote secondary, restore the database to it, get it in sync up to the latest log backup, and then join it with:

ALTER DATABASE <database_name> SET HADR AVAILABILITY GROUP = <AG_name>

You can even save the parts of the SSMS-generated script that are relevant to the remote server to do this and you'll see the command above in it.

I wrote up a similar process at How to remove a secondary database from an availability group and rejoin it.

There is separation from the AG membership and database replica ownership. The replication of each database in the AG is handled separately, so you can have one database being replicated to 2 other nodes, and another database only being replicated to 1 other node. It has to work like this because replication can't be started until the replica is in sync up to the last log backup. Small databases may just take a few seconds, but large databases, particularly at remote sites, can take hours or days to get in sync, so SQL Server is not going to force you to have gigantic transaction logs full of data that's queued for replication to a remote site that it can't send yet because the remote database is not caught up to the current LSN.