SQLTeam.com | Weblogs | Forums

Using Join Only Option Always On

Hi all

First time poster but long time lurker, hopefully a fairly simple question to start things off :slight_smile:

I have a requirement to add another node to a current Always On system but the change window to do the work is short so I was thinking to save time I could manually restore the databases to this new instance earlier in the day then use the Join Only option during the actual change window hours later.

I wouldn't be able to stop the T-Logs on the Primary in between the manual restore to the new Secondary and adding the Replica so was wondering if this would work, correctly synch up the missing txns?

Last time I did this I had the luxury of being able to suspend data movement and stop t-logs so the Join Only option took minutes. I could use Automatic Seeding instead, enable the trace flag that switches on compressio, but again not sure how long that will take (databases add up to 2.5TB, SQL Server is 2017 CU17).

Hoping someone can give me the right steer on this, thanks in advance.

Ali

throughout the day, you could also apply log backups so it is not that far behind when you actually add it. Just be sure that there aren't automatic log backups in between all of this as they would need to be applied in order to the replica

Thanks for the response. Yes, have thought about that but thankfully the t-logs shouldn't be too large so can either take the scheduled ones and manually restore OR wait until start of change window, suspend log dumps at source then apply them sequentially.

Cheers!

Ali

How it is will take to add the database using automatic seeding will depend on the network. If the network is 10GB and the servers are local - it could take less than an hour (with compression enabled).

I use automatic seeding on a daily refresh of a 5.5TB database into an AG and it is synchronized in less than an hour. Without compression it can take a couple of hours - but that is still a reasonable time for that environment.

That's quite impressive, I've used Auto Seeding before but not on databases of that size and know it's quick. I'll check with the network team tomorrow regards the size of pipe between the two data centres and if it is that big (get a feeling it's 1Gbps not 10) then might not bother with manual restore + tlogs and go the Auto Seeding route for all of the databases.

Cheers!

You missed a point - if the servers are local and it appears you are doing this across data centers. For a large database that will have a significant impact and take quite a bit of time to complete.

For that - I would copy the latest full backup across and restore with no recovery. Once that has completed I would then run a differential backup - copy that across and restore with no recovery - then start applying tlogs until current - once current, perform the join.

If the tlog backups are run too often and you can't get the latest file copied and restored - then you would have to suspend the tlog backups. But that would only be for a minimal amount of time, just long enough to copy the latest backup, restore and join.