Join Only Option - Always On

Question

Join Only Option - Always On

zashah@gmail.com

SSC-Addicted

Points: 442
More actions
October 28, 2021 at 5:09 pm

#3945627

Hi all
Hopefully a fairly simple question to start things off after a long time absence 🙂
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 won't be able to stop the T-Logs on the Primary in between the manual restore to the new Secondary and adding the Replica in so was wondering if this would work, correctly synch up the missing txns, with such a time gap between?
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 compression, 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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

How frequent are the log backups and how long do they take to restore?

If you can manually log ship throughout the day, then you can take that final log and restore it before the next log is backed up, then yeah join only. If not your going to need to delay or pause a log backup or two.

zashah@gmail.com SSC-Addicted Points: 442 More actions · Answer 2

Hi

Thanks for the response. Log backups are hourly and usually take < 5 minutes to restore so not too bad.

Revisiting the databases on the system there are only two from the 2.5TB total that may be a problem time-wise (1 x 1.5TB, 1 x .5TB) so I am now thinking maybe only pre-restore these two (or possibly only the 1.5TB one) in the morning (without recovery of course) then apply the ten or so t-logs that have built up during the day just before using Join Only?

And once these are done for the other smaller databases enable Auto Seeding + Trace Flag and use that method?

Again, appreciate your or any one elses thoughts on this.

Ali

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

I don't use automatic seeding, because I want to have control over the full process.

You can indeed restore the db on the new AG-member-to-be including the log restores ( with norecovery !) , but you need to be able to keep up with the log backups as it will not join if the backup forks are different.

Restore full - restore diff - restore log - restore log ... restore most recent log + join ag

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

zashah@gmail.com SSC-Addicted Points: 442 More actions · Answer 4

Thanks for your response. Yes, agreed less control when using Auto Seeding but I've found it to be very quick as uses the VDI stream.

I do have the 'luxury' of being able to suspend t-log backups during the change window as out of hours and no data modifications occurring so maybe I can do the following:

Morning - restore full dump from overnight of large db(s)

Change window - Ensure replicas in synch then suspend t-log backups

Add new Replica

Backup / restore ad-hoc diff backup of large db(s) to newly added Replica

Join AG

Other much smaller databases I can use Auto Seeding or the more conventional Backup / Restore method

Once complete re-enable t-log backups and ensure both Secondary Replicas in synch.

Think the above makes sense, any omissions or further suggestions please do comment!

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

If you're using SQLuser accounts, be sure to copy them up front

Easyiest using DBATOOLS powershell module

$SourceSQL = 'Server\instanceA'
$TargetSQL = 'Server\instanceA'
Copy-DbaLogin -Source $SourceSQL -Destination $TargetSQL -ExcludeSystemLogins

you can even specify only those sqluser accounts you actually need by adding:

-Login 'SQLUserA','SQLUserB', ...

It will retain the user SID from the sourceSQL, so no problems at failover time

if your SQLUsers have a specific default database, you'll need to set up that database before you can create the account and keeping the default database settings ( otherwise it will raise a warning the default database does not exist and has been replaced by "master" )

SQLAgent jobs may also need to be checked up front. ( and copied if needed )

, ....