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.

agp1

 

 

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.

agp2

 

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

agp3

 

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

agp4

 

Do the same thing on Wincomputer2.

agp5

 

Step 2

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

agp6

 

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

agp7

 

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

agp8

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

agp9

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

agp10

 

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.

agp11

 

That starts the Failover Wizard, click next.

agp12

 

 

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.

agp13

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

 

agp14

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

agp15

Click next.

agp16

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.

agp17

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

agp18

 

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

agp19

 

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.

agp20

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

agp21

 

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.

agp23

After connecting.

agp24

Summary screen.

agp25

Failover was successful.

agp26

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.

agp27

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

agp28

 

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s