SQL Server patching is a common use case for high availability deployments. When people think about minimizing down time for patch upgrades, most people will think of SQL Server failover clustering or SQL Server database mirroring. Those are the two SQL Server native availability features that support rolling patch upgrade. Did you know that you could also perform rolling patch upgrades with just a standby virtual machine (VM) if you are running SQL Server on VMware?
With VMware vSphere, a virtual disk can be hot removed or hot added onto a VM without impacting services running in the VM. Given that, you could put together a rolling patch upgrade solution that's similar to the SQL Server failover cluster share everything architecture by using a standby VM. The SQL Server data and log disks are shared between the primary and standby VMs, although they are only assigned to one VM at a time. The primary and the standby VM each runs an identical copy of the SQL Server binary. When you need to apply SQL Server patches to the primary VM, you can switch ownership of the SQL Server data and log disks from the primary VM to the standby VM. The standby SQL Server VM can continue servicing application requests. The followings describe the steps and process flows for the solution.
Step 1: Configure standby VM
- Create a standby SQL Server VM, if one does not yet exist, using VMware templates or cloning technologies.
- Confirm SQL Server logins, job, and other instance level configurations are configured identically between the standby and primary VM.
Step 2: Patch standby VM
- Apply service patches to the standby SQL Server VM.
Step 3: Hot remove SQL Server resources from primary.
- On the primary VM, stop client connections to the database(s). One way this could be accomplished is to disable the virtual machine network interface. Connection to the virtual machine can be made using a management interface for Remote Desktop Service connections or vSphere client console connection.
- Detach database(s) from primary SQL Server by issuing thesp_detach_db T-SQL command.
- From Windows Disk Management right click the data and log volumes and select Offline to prepare them for hot-remove.
- From the vCenter client, remove SQL Server data and log virtual disk(s) from the running primary SQL Server VM.
Step 4: Hot add resources to the SQL Server standby VM.
- From the vCenter client, add the virtual disk(s) containing the SQL Server data and log files to the standby VM.
- From Windows Disk Management, bring the disks online if needed and confirm that the disk(s) are mounted with the correct drive letter(s) assigned.
- Attach SQL Server database(s) by issuing the sp_attach_db T-SQL command(s).
Step 5: Switch role
- On the standby VM, enable application network traffic to the VM.
- The old standby VM is the new primary, SQL Server service is resumed for the application(s).
- The old primary VM is ready for service patching and may be left in the standby role if desired until the next patching cycle.
During steps 3 to 5, application(s) would experience temporary connection issues to the SQL Server. Similar to the failover clustering or database mirroring requirements, reconnection is expected to be handled by the application layer, with zero data loss, and any in flight transactions would need to be resubmitted. All operations in steps 3 through 5 are metadata only operations, and are expected to execute instantaneously.
If you would like the ability to do rolling patch upgrades, but don't want to put up with the cost and complexity of maintaining a failover cluster or a mirrored database, this solution provides a viable alternative for you. For those of you that are into scripting, the process flow can be automated using PowerShell and PowerCLI.
-Wanda
Wanda He, Technical Solutions Architect
