次の方法で共有

Best Practise for Patching SQL Server

  • [アーティクル]

Patching SQL Server is a topic I get questions about a lot, so I thought it would be a good idea to write a post about it so that I could try and answer all those questions in 1 place. 

  

Where can I find out what the latest patch level is for SQL Server ? 

My site of choice of patch levels of SQL Server is https://sqlserverbuilds.blogspot.com

This site lists all the patches for SQL Server from SQL Server 7.0 right through to the very latest version (2014 at the time of writing).  The great thing about this site is that it lists the patches, build levels and links to the patches in chronological order which makes it really easy to navigate and understand.

 

In addition to knowing what patches are out there, you should also be aware of what the current supported patch level is.  The support lifecycle for SQL Server is documents on the Microsoft support website: https://support.microsoft.com/lifecycle/?c2=1044.  This site will show you the mainstream and extended support start and end dates for the different versions of SQL Server.  Note that this site shows the minimum required patch level for Microsoft support, not the current latest patch level.

 

What Type of Patches Are Out There For SQL Server

Patching SQL Server is actually very easy, and since SQL 2008 it has become even easier.  One of the great things with SQL Server patches is that they are cumulative, so if you find yourself getting behind on your patching you will not have to apply all the patches in order.  Broadly speaking there are 3 types of patch for SQL Server:

  • Service Packs
  • Security Patches
  • Cumulative Updates

Service Packs should be considered as major patch levels and generally speaking it is the product at a certain patch level that forms the minimum supported build for Microsoft support. Once a Service Pack has been released, you have 1 year to apply that Service Pack in order to remain at a supported level.

Cumulative Updates are regularly released for SQL Server and these updates include bug fixes and improvements for SQL Server, however CU's are not mandatory and do not form part of the support lifecycle timelines.  By that I mean that if the most recent patch for SQL 2012 is SP1 followed by CU6, you would only need to have SP1 installed to be at a supported level, and CU6 is optional.  It may however be advisable to apply the CU's since they include many fixes and improvements, and it is better to apply a proactive approach to patching rather than waiting for an issue that affects a production server, and then frantically search for a patch to fix it.  Cumulative Updates area applied on top of Service Packs, not as a replacement to them.  Therefore if you have SQL Server RTM build, but you want to apply CU6 that was released after SP1, you would need to apply SP1 and then CU6.  If however you are already at SP1 level, but you have not applied any CU's, you only need to apply CU6 and not all CU's in order.

Security patches are additional patches that address specific security issues in SQL Server and should be applied as they are released.  Since CU's include all updates since the last Service Pack, you would only need to apply the most recent CU which would include any previous security update. 

 

What is the process for Patching SQL Server

If you are patching a stand-alone, the process is quite simple:

  1. Copy the patch files to the server
  2. Run the patch file and follow the on-screen instructions
  3. Select the instances that you wish to patch
  4. Allow the patch to finish
  5. Reboot if a reboot is required (the patch will tell you if there were any locked files)

Stand-alone server, even multi-instance stand-alone servers are simple because the steps above are limited.  You can apply the patch in an interactive mode as shown above, or you can apply it in a quite non-interactive mode as shown below:

<package.exe> /QUIET /INSTANCENAME=<instance>

or

<package.exe> /QUIET /ALLINSTANCES

so for example to install SQL 2008 CU1 quietly on all instances you would run this

SQLServer2008-KB956717-x64.exe /QUIET /ALLINSTANCES

 

The quite options apply to stand alone and clustered installations, and if you have a number of instance to patch you may want to consider using that option for speed of patching.  Obviously it takes longer to apply the patch when we have to render many forms to the screen, so you can bypass this by running in quiet mode from the command line.  There isn't a best practise for running in GUI or command line mode, it is totally your choice, but I would certainly look at using the command line mode for speed of nothing else.

SQL 2005 Clusters 

Patching a cluster is slightly more complicated and the process is different fro SQL 2005 than it is for versions later than SQL 2005.  With SQL Server 2005, the patch is applied to the active node of the cluster and it is the patch that will go off to all passive nodes and will apply the patch files remotely. This can be the most problematic part of the process and fortunately this was removed in SQL 2008.  Here is the process for patching a SQL 2005 cluster

  1. Copy the patch to the active node of the cluster
  2. Ensure that no-one is using the SQL Server, and then run the patch installer
  3. The initial screens for the patch will be very similar to that of the stand alone installer, so just follow the on-screen instructions

Once you start physically copying file patch files, this is where is gets different as the installer will start doing remote installations.  What you will find is that once you start patching a SQL 2005 cluster, the instance is unavailable to users for the duration of the patching process.  Since the patch does the remote installations on all nodes in the cluster this may take quite a bit of time (I have seen a 2 node cluster take 40 minutes to patch), and you need to be aware that the instance will effectively be down during that time.  With SQL 2005 the patch does everything so once it's finished either all nodes are patched, or none are patched.  If there is an issue during the patching phase, then the entire patch needs to be rolled back on all nodes.  Therefore if you have 3 node cluster and the last node fails to patch, the patch will be rolled back on all nodes - which will take quite a bit of time to do.  Bottom line for patch a SQL 2005 cluster is to do it out of hours or during some other down time, and allow yourself plenty of time to do it.

 

SQL 2008 onwards (Clusters)

With the introduction of SQL 2008, we remove all of the remote installation process from the installer.  This also applies to the patching process as well which is very useful to us from a patching process as it means we can maintain the service availability whilst we are patching.  With a SQL 2008+ cluster, we always patch the passive node rather than the active node.  Patching the passive nodes means that the SQL Server is available on the active node and therefore customers can continue to use the system.  The process is as follows:

  1. Connect to a passive node in the cluster and run the patch installer
  2. Follow the on-screen instructions, selecting the options that you wish to patch
  3. Fail over the cluster once the install has finished
  4. Now patch the remaining passive node

 

 

When you patching the passive and failover, the unpatched node gets removed from the possible owners list so that the cluster can't be failed back to an unpatched node.  This can be important as some patches will update the internal database version of the installed databases, which would cause them to not start if you tried to run them on an unpatched node.  You can alter this behaviour at the command line and adding the following switch:

SETUP.EXE —/FAILOVERCLUSTERROLLOWNERSHIP=0 | 1 | 2

 

  •  —/FAILOVERCLUSTERROLLOWNERSHIP=0
    • Do not roll cluster ownership to the upgraded nodes.
    • Do not add this node to possible owners of the SQL Server cluster at the end of
      upgrade of this node.
    • Note: this is not an option after the ownership has already transferred.

 

  • —/FAILOVERCLUSTERROLLOWNERSHIP=1
    • Roll cluster ownership to the upgraded nodes (if this has not happened already).
    • Add this node to possible owners of the SQL Server cluster at the end of upgrade of
      this node.

 

  • —/FAILOVERCLUSTERROLLOWNERSHIP=2
    • This is the default setting.
    • This setting indicates that SQL Server setup will manage the cluster ownership as
      needed.

You can also use the cluster administration commandline tool to do this, which is discussed in this article: https://support.microsoft.com/kb/958734

 

 

Slipstreaming Patching during Setup

As of SQL 2008 SP2, we introduced the ability to integrate Service Packs and CU's into the installation media of SQL Server.  This has the great advantage of speeding up the patching process for new installations, as well as allowing us to address any setup related issues.  Slipstreaming places the patch files into the installation media so that they are installed as the SQL instance is being installed, so that you only run setup once, and you have a fully patched and ready to go instance.  It is a great time save, and I use this all the time for Service Packs and CU's for my demo systems.  I won't re-document the process in this blog as a full description of the process can be found on this blog:

 

https://blogs.msdn.com/b/petersad/archive/2011/07/13/how-to-slipstream-sql-server-2008-r2-and-a-sql-server-2008-r2-service-pack-1-sp1.aspx

 

This blog talks about SQL 2008 and 2--8 R2, however the process is identical for SQL 2012 and 2014, so simply replace the filenames with the versions that you are using and away you go.  Please note that slipstreaming is not available for SQL 2005.

 

 

How can I find out about new Patches

I have already mentioned https://sqlserverbuilds.blogspot.com, but each month I publish a technical roll-up mail for the various Microsoft products.  This TRM is compiled by myself and a number of other engineers, and you can find them at https://blogs.technet.com/b/trm/.  The TRM is published in the first few weeks of each months and amongst other thing, it will include a list of new KB articles for SQL Server.

Comments

  • Anonymous
    April 09, 2014
    This post is a good starting point for someone looking at patching their SQL Servers. It has all the right information and the right links.

  • Anonymous
    June 25, 2014
    Thanks so much for this Article :D This has definitely made my patching and keeping our systems up to date much more easier.

  • Anonymous
    July 31, 2014
    Does sql service restart mandatory after any smaller patch applied?

  • Anonymous
    August 08, 2014
    Some say it's good practise to restart after patching, but to be honest you only need to restart if the installer needed to modify files that were in use and could not be modified during the match install. The installer will usually let you know this though, and I have installed many patches that have not required a service re-start. I generally say to customer that during a patching window - allow for a service re-start so that you can be sure modified files have been replaced etc ....

  • Anonymous
    September 17, 2014
    Questions:

  1. Do we need to start SQL/Agent services on the passive node prior to installing the Service Pack?
  2. In case the patch install is messed up, what is the remedial action?

  • Anonymous
    January 08, 2015
    We have not patched our sql server for around 2 years... if we are patching it now... should we stop the SQL services and proceed with the update or we can patch it as it is...

  • Anonymous
    March 02, 2015
    Great post.

  • Anonymous
    March 02, 2015
    No you don't need to stop SQL Services before patching, but you may be required to restart SQL after a patch has been applied for changes to take effect.  The setup program will tell you if this is required.

  • Anonymous
    March 09, 2015
    Very Very good article. This is the first time i am reading about the patching document, it is pretty simple and easy understandable for beginner.

  • Anonymous
    April 24, 2015
    Glen - I must admit, this is on of the best written document I have seen for addressing patching related questions. Very simple yet detailed.

  • Anonymous
    June 19, 2015
    Thanks, Glen.  This is very helpful, it is the first comprehensive article I have seen on SQL Server patching.

  • Anonymous
    November 18, 2015
    Hi Does the Windows OS patches, which are released regularly by Microsoft apply SP for SQL Server databases as well? Or do we need to apply SP patches separately for SQL Server Instances. Also we have database mirroring, can the principal database be on a SQL Server Instance which is the same SQL Server version but different SP level than the SQL Instance where the mirrored database is? We want to apply SP on the mirrored SQL Instance and then patch the Principal after a few days. Thanks

  • Anonymous
    December 15, 2015
    Great Article on SQL CLUSTER patching. Much needed info and explanation.

  • Anonymous
    February 29, 2016
    Hi Glen, great post..!! Please one question.. You wrote: "...the unpatched node gets removed from the possible owners list so that the cluster can't be failed back to an unpatched node..." That occurs automatically? (Not using guide in KB958734)