SQL Server AlwaysON – Start Sync with Secondary for Selected Databases

The fastest way would be to create a cluster an join your current stand alone server to the cluster as a new node. Set the database to full recovery model, take a full backup and a transaction log backup and restore them to the instance on the other node with norecovery.

If you have a good full backup (most recent), it would be possible to set the recovery model to full, take a differential and then take a log backup using the last full from the simple recovery model as your base. The differential should bridge the lsn gap.

To answer your questions:

1. I'm not sure why you're switching the database into single_user except that you want no changes to be made. If you add the server as a node as I've set forth, this won't be an issue. Otherwise, yes, you'll need downtime to migrate the database. Your other option would be to set the recovery model to full, get it in full, setup mirroring to the new instance on the cluster and then cut over during a downtime which would be much less time.

2. That's entirely possible to do, but in your initial question you said you didn't want to do this.

3. You must restore at least one log or differential to bring the lsn gap as close as possible.

4. I would not use the wizard, but the join only means you've staged a database that is currently in norecovery and it only needs to join the AOAG. The wizard will want to pre-stage the databases for you and ask you for shared locations, etc. Like I said it's best to pre-stage and not use the wizard for the best flexibility.

If the secondary is up and running, when the log block is flushed to disk (either because it is full or a commit), the record gets pushed to the log writer on the primary and to the log scanner (log reader) process on the primary simultaneously. Then the log scanner communicates with the secondary and the secondary then pulls the transaction from the log scanner on the primary to the secondary and processes the log record. The primary log writer doesn't push transactions across, it just communicates with the secondary, it only does that to see if it is up so that it knows it doesn't have to mark the replica as NOT SYNCRONIZED.

When the secondary is not up, then the log writer cant communicate with the secondary so it marks it as NOT SYNCHRONIZED and stores the records in the tran log on the primary. If you look at sys.databases.log_reuse_wait_desc column it should show AVAILABILITY_REPLICA which means the primary is hanging on to all the records.

Once the secondary is up, it will communicate with the primary to request a log scan, it then processes the transactions and communicates with the primary using progress messages to indicate the hardened LSN, presumably the primary is then adjusting its MinLSN, which in turn means the records prior to MinLSN will get deleted as checkpoints happen and hence VLFs will get truncated releasing space when you do a log backup.

But yes short answer is, if your secondary is down you need as big a log file as you need for as long as it is down. Once it is backup and synched at some time you may need to remove the db from the always on group to shrink the log if it is humungous and you dont want it that big.