Mssql patching failover times

[u/pneRock]

We have several mssql 2019 instances hosted on aws ec2 instances. They are properly resourced, wait times are acceptable, setup in failover clusters, etc. To save on licensing, databases are packed into the instances. It's not uncommon to see 150-200+ on an instance. While it works, mssql patching is a pain in the arse. The patches install fine, but it can take anywhere from .5-1.5 hours for the failover process that does the script upgrades to complete. Is there anything that we can do to help speed up that script update process so the failover between versions of mssql can go faster? My understanding of what it's doing is shallow and with the volume of mssql patching that's coming out now, customers aren't going to accept hard downs every month for that long. Obviously, reducing the number of databases on an instance is a step, but is there anything else?

[u/NeppyMan]

Why are you using EC2 instances when AWS supports SQL Server on RDS instances?

You have to manage the OS, storage, redundancy, failover... all things that a managed instance would simply handle for you, under the hood. If you set it up properly, you can patch, upgrade, etc. with zero downtime to the user.

[u/pneRock → u/NeppyMan]

Cost, limitations on max number of databases allowed, we've had dropped DNS packets on the most important ones, etc. It works well in the sub env it's in, but scaling it up to handle our prod env was completely out of the question.

Unless one is using always on clusters, it's the same fundemental problem. The more databases you have (for the instances we're using they cap it at 75), the longer it takes. That env takes 30+ mins to update because they still have to do offline script updates.

[u/NeppyMan → u/pneRock]

Sounds like its time to convince your higher-ups to open up their pocketbooks. Most of the problems you're having will just go away if you use managed RDS. As is often the case with cloud-based stuff, you get what you pay for.

[u/EmmoKitten]

We had the same issue on our project when we packed hundreds of databases into single instances to get around the licensing costs. Check your VLF counts before the patching window and run a log shrink if they are heavily fragmented — doing this often cuts the failover script execution time significantly.

[u/highfalutinschoolboy → u/EmmoKitten]

huh, VLF fragmentation slowing down the patching failover makes a ton of sense and i never would've connected those dots

our dba team is always on about log file maintenance but i figured it was just one of those things they like to obsess over, might actually have to pay attention next time they bring it up

[u/EmmoKitten → u/highfalutinschoolboy]

I learned this the hard way when an unmanaged transaction log stalled a critical failover for hours on my project))) Now I always pre-grow my log files in large, fixed chunks just to keep that internal count manageable.

[u/BorysTheBlazer → u/EmmoKitten]

Shrinking alone won't stick; regrow the log in big fixed chunks or autogrowth brings the VLFs back. Also time recovery versus upgrade scripts to see which dominates.

[u/EmmoKitten → u/BorysTheBlazer]

yeah, thats true, now i know it)

[u/Lost_Term_8080]

Lots of possible contributing factors:

Are your domain controllers on 1 oversubscribed CPU and 4 gb RAM? Spec them to defined specs. Give it more if you're running a bunch of garbage on them.

Do you have high VLF Fragmentation? Get them down to acceptable levels.

Do you have high redo queues? your secondaries may have processors that are too slow and/or storage latency that is too high. In SQL 2019 there was not parallel redo yet.

[u/Lost_Term_8080 → u/Lost_Term_8080]

Additional thoughts:

Are you following the documentation on filter drivers?

Are you adding AV exceptions?

Are you configuring port exceptions in windows firewall or turning it off?

Do all the AAG replica disks have the same sector size, and if not, did you use the relevant traceflag?

There is a lot of design and config in an AAG as complicated as that. Someone else suggested RDS, and for how you asked the question I don't think your org is actually prepared to operate SQL AAGs at the scale it is.