Home > Blogs > Virtualize Business Critical Applications


SQL Server Rolling Patch Upgrade using Standby VM

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 the sp_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

18 thoughts on “SQL Server Rolling Patch Upgrade using Standby VM

  1. Frans van Dokkumburg

    Hi Wanda He,
    Very nice article. My question with the above solution is, what kinds of products do i need to get this functional? Is it just VC + ESXi5 or…? Further a special version of SQL?
    And will the sql db server be the whole time online or has it a minimum of downtime when it failsover
    Can you email me on the email address
    Thanks in advance
    Frans van Dokkumburg

    Reply
  2. Wanda He

    This solution uses out-of-box features in vSphere. It’s been tested on vSphere v4.x and v5.0. It should work on older version of vSphere as well. As for SQL Server, any SQL Server version that supports sp_detach_db, sp_attach_db should work with this.
    During the failover stage (steps 3 to 5), application connecting to the SQL Server database(s) would experience temporary connection problem. Depending on what users are doing, and application reconnection logics, users may or may not notice the connection problem. The user experience is similar to running a failover cluster. This solution is like a poor man’s failover cluster.

    Reply
  3. Wanda He

    Hi Rasheed,
    Thanks for your comment. As far as what I can remember, SQL Server support for sp_detach_db, sp_attach_db is available even on SQL Server v6.x. Not sure about SQL Server v4.x. But you can check. As long as sp_detach_db and sp_attach_db are supported, this solution would work.

    Reply
  4. Ryan

    Hi Wanda, Very clever idea. I would also assume that you could use this to quickly roll back a sql server if it had problems with the newly applied patches. This would be very handy especially with highly sensitive applications.

    Reply
  5. Wanda He

    Good thought, Ryan. However this solution would not work for rolling back SQL Server service patches. With SQL Server patch upgrade, SQL Server changes the meta data version of the database. Which means you may not take an up version database and attach it to a down versioin SQL Server. A better feature to use for the rollback requirement would be VMware snapshot.

    Reply
  6. Robert L Davis

    Interesting article, but a couple of points:
    1. sp_attach_db is deprecated. Please stop recommending that people use it.
    2. Detaching and attaching a database is hardly a metadata only operation.
    3. Unlike mirroring or clustering, detach and attach has a risk of losing the database if the shutdown process doesn’t complete correctly. I always recommend taking a backup immediately before performing a detach operation, just in case.
    Disclaimer: I work for Microsoft, and I’m not trying to attack VMWare. If this article was about Hyper-V, I would maje the exact same comments.

    Reply
  7. Denny Cherry

    If you can’t afford to have any downtime with your SQL Server you should be configuring SQL Server a more traditional High Availability SQL solution. Not using this sort of solution.
    I see all sorts of potential problems here.
    1. Logins aren’t part of the user databases, so they may be missing. If they are there, the users may not be aligned with the logins.
    2. The hostname has now changed. Either a DNS change needs to be propagated or all the client machines need to be updated with the new connection string.
    3. VMware Snapshots make your SQL Server database unsupported by Microsoft CSS.
    4. The cost to build a SQL Database Mirror is basically the cost of an extra Windows license. There’s no cost for the extra SQL VM as passive nodes don’t require licensing.
    5. Jobs wouldn’t be copied across as those are stored in a system database.
    6. Server collation may not be the same.
    7. TempDB may not be the correct size.
    8. TempDB may not be configured correctly for the workload.
    9. RAM config may not be correct
    10. Extended stored procedures may not be attached
    11. vCPUs may not be the same
    There are probably more, but this is what I could come up with in just a few minutes.

    Reply
  8. Georges

    Do we have Enterprise class customers, who adopt this in production?
    Who would support SQL Servere in this case? I beleive, Microsoft won’t.

    Reply
  9. Wanda He

    Hi Robert,
    Thank you for pointing out sp_attach_db is deprecated. Like many others, I still use it out of habits. But going forward, we should all start using the new replacement syntax: CREATE DATABASE…FOR ATTACH.

    Reply
  10. Wanda He

    Hi Denny,
    Thanks for providing the additional details. As described in step 1, it’s critical that the binary version of the SQL Server is identical between the primary and standby VMs. Similar to how you setup database mirroring, you need to manually synchronize the logins, jobs, and any other instance level configurations between the primary and standby. Denny listed most of them here. But your list may vary.
    Like any kind of upgrade, before you apply any patch, you would want to do a full backup of your database.

    Reply
  11. Wanda He

    I’d also like to add. The solution presented here is an option for minimizing down time for SQL Server patch upgrade. This is not meant for replacing SQL clustering or database mirroring.

    Reply
  12. Denny Cherry

    Normally when patching a SQL Server 2008 or 2008 R2 instance the downtime is just a few minutes. How is detaching and attaching all the databases and reconfiguring all the clients to connect to the new server going to be faster than just patching the server?
    Databases don’t exist in a void. Just because the database is up and running on another server that doesn’t mean that the application is up and still impacts the overall SLA for the application which uses the database.
    If my customer want to use this method that’s great. I get paid by the hour until all the end users are working again. Or I can just install the patch and take the app down for 10 minutes.

    Reply
  13. Wil van Antwerpen

    It is an interesting idea and might be a solution when the patch level that needs to be done is quite big. I’m a bit hesitant of using it on anything mission critical at first thought.
    I would urge anyone who is going to try this to upgrade their HA system to do it in their lab first. It is a bit of a risky operation, the risk of things going wrong is much higher as the patch process itself (which has been tested by your suppliers), you are working with a LIVE database.
    In a reply you wrote “A better feature to use for the rollback requirement would be VMware snapshot.” umm.. No it isn’t as rolling back a snapshot means you destroy all transactions that already have been processed. Not very pretty to suggest as “rollback” solution.

    Reply
  14. Dave Demeulenaere

    Hi Wanda,
    Great article, thank you.
    I am wondering if you could explain a little about why it is necessary to deatch the databases for this process.
    If all database files (including system DBs) are on a shared drive and the primary/standby nodes match exactly, wouldn’t the patched node pick up the databases normaly when the drives are attached and the SQL service started up?

    Reply
  15. craig

    This is a daft idea all round. If you can’t afford the downtime of patching a SQL server then it is far easier and less risky to implement a proper SQL failover cluster. It costs nothing in SQL licenses and is pretty simple to set up these days in a couple of vm’s.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>