Add to High Availability while staying on-line

High Availability
1

Another problem we have... We use SQL Server 2014. We did a migration and restored a DB on Server Prod1. During the down-time window, we put the DB in High Availability with Server Prod2. But we didn't have enough time to put the DB in High availability with a remote Server (i.e. for Disaster Recovery). My understanding is that we must halt backups in order to add a Database to a secondary replica. And in this case the Database is large. Maybe 1 hr to backup and 2hrs to copy, another hour to restore. We don't want to stop our tran log backups which happen every 15 min.

Any suggestions? It's a catch 22. Dangerous to temporarily stop backups but dangerous to not have HA function to a DR site.

2

You don't have to stop the transaction log backups - you just have to apply all transaction logs taken since the backup you used to restore the database.

Here is how I would perform this:

  1. Full Backup - copy to secondary and restore with norecovery
  2. Perform a differential backup (after step 1 has completed) - copy to secondary and restore with norecovery
  3. Copy transaction log files taken since differential to secondary - restore with no recovery
  4. Add database to AG

The differential and transaction log backup files shouldn't take a long time to copy and restore - minimizing the number of transaction log backups needing to be restored on the secondary.

3

Thanks! Makes sense