Patching strategy when Windows and SQL Server patches not applied for over 3 years

Question

The company I'm at hasn't patched their production servers for a while, neither SQL 2012 or Windows 2012. When I say "for a while" I mean, well, actually "never" - no patches or updates since the installation in late 2012. They are using synchronized AGs with auto-failover for redundancy so we can upgrade one node at a time.

The plan is to disable auto-failover, update the second node, failover the databases then update the first node.

Question: How do you recommend approaching this? Apply 3 years of DB and OS patches all at once to the second node, then failover to that node? How much out of sync can the nodes be and still expect a trouble-free failover of AG databases? Is it better to do the Windows update first, or the SQL CU?

The goal is to minimize downtime and the number of failover/failbacks.

Answer 1

IMHO I'd recommend breaking up the updates into major SPs and OS vs SQL. That way if anything breaks you can rollback more easily and pinpoint issues. For example, roll out Server 2012 SP1 let it mature for a week or two, then do a SQL SP wait a week or two, etc...

Your methodology sounds good for patching the secondary node, failover, patch primary node, and then balance the cluster.

Answer 2

Start by patching the operating system. Install the latest windows service pack and all updates on the secondary node rebooting as necessary. When the secondary is fully patched then failover the SQL Server to the secondary and if everthing works then start updateing the primary node until both are fully patched.

For the SQL Server you need to install a service pack and failover between both hosts before you install a cumulative update, and you do it the same way. Install service pack on secondary/failover, update primary/failover. Install CU on secondary/failover, install CU on primary and celebrate.

So the whole thing will take you at least 6 failovers of the sql service, but the good thing about doing the rolling updates is that you can patch the inactive servers during the day and the failover time is really short so you can schedule that early in the morning or late at night by which you prefer.

I would also look into updating the server's firmware and drivers while you're at it, check with the server admins (or the documentation), some server like the drivers to be updated before you update the firmware.