Patching SQL Server Availability Group Servers

Now that you have an Availability Group setup, what can you do with it?  Well, now you can patch your servers with almost zero downtime by doing rolling upgrades.  For our 24*7 economy, downtime can be very expensive in lost sales, people not being able to work, etc.   I am going to walk you through the process of installing Windows Updates on my three server cluster.

The process of Failing over is a concise set of steps

1. Change all replicas to manual failover (wincomputer1, wincomputer2)
2. Patch the servers that are running Asynchronous Commit (wincomputer3)
3. Patch Synchronous servers that are not the Primary (wincomputer2)
4. Manually fail over the AG to a local synchronous-commit secondary replica  (wincomputer2)
5. Patch the server that was your original Primary (wincomputer1)
6. Manually fail over to the server that was your original Primary, and set servers that were changed to manual failover, back to Automatic.

For more information about these Fail Over steps, follow this link:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances?view=sql-server-2017

 

Scenario

We get a notification that our servers need to have windows updates applied.  Before we had Availability Groups, we would need to schedule downtime with the business, and apply the patches on our servers.  Now with AG’s, you can almost do the patching at anytime.

 

 

Step 1

We need to change the failover mode from Automatic to Manual.  This is so we do not failover when we do not want it to happen.  Below we see that wincomputer1 and wincomputer2 are currently set to Automatic.

 

To change them to Manual, right click the computer name under Availability Replicas under SSMS, and choose Properties.

 

In the drop down, change the Failover Mode from Automatic, to Manual.

 

Do the same thing on Wincomputer2.

 

Step 2

On the dashboard, make sure everything is green and either synchronized or synchronizing and the failover mode shows Manual for all servers..

 

On server Wincomputer 3, our asynchronous mode one, apply windows updates and then reboot.

 

While it is rebooting, the dashboard will flip out.  This is normal, because the server is rebooting and lost contact with the cluster.

If we click on the warning, we see this error message which is normal, because the server is rebooting.

After the server comes backup, we can see all green on the dashboard.  We can now patch our Synchronous secondary server Wincomputer2.

 

Step 3

Now we can patch our servers that are synchronous secondaries, wincomputer2 in our case.  All we need to do is run windows updates on server2, and reboot.  The dashboard will show some errors, but after it shows all green, we can proceed to step 4.

 

Step 4

From the dashboard, click the Start Failover Wizard.

 

That starts the Failover Wizard, click next.

 

 

This screen lets us choose which server we want to failover to.  We will want to failover to our synchronous sever (wincomputer2), that way we will not lose any data.  If wincomputer2 was also down, we could failover to wincomputer3, but then we have the chance of losing some data.  In an emergency situation, this will probably be allowed.

After selecting our server and pressing next, it wants us to connect to the server.  Click the connect button.

 

Then provide credentials.  We are using the same username for all servers.

Click next.

This gives us a summary of what is about to happen.  We could also click script to get the SQL commands in case we want to automate this in the future.

The wizard will go through the process and failover the servers.

 

If all goes well, you will get a success message and the Wincomputer2 server will now be the Primary.

 

Step 5

We can now patch and reboot our Wincomputer1 server.  While it is rebooting, the dashboard shows red, but that is expected.  We are still up and running on the new Primary server Wincomputer2.

After Wincomputer1 reboots, the dashboard shows green and it is now a secondary.

 

Step 6

Now we can either leave everything the way it is or failover to Wincomputer1 to how it was before we started patching.  I am going to failover backup to Wincomputer1.

Click the start failover wizard again.  Go through the steps of when we failed over to wincomputer2, by connecting.

After connecting.

Summary screen.

Failover was successful.

The dashboard shows all green, but now we need to change the failover modes to how they were before we started.  Going back to Step 1, change the failover mode on wincomputer1 and wincomputer2 to Automatic.

After we make the failover mode change, we can see everything looks good and Wincomputer1 is back to being the Primary.

 

We have successfully patched our servers.  This same process can be done for SQL Server updates also.