Making the Case for Regular SQL Server Servicing

There has been some ongoing controversy in the SQL Server community about the wisdom of installing Service Packs (SP) and Cumulative Updates (CU) for SQL Server. There are several different basic positions that organizations typically tend to take on this subject, as listed below:

1. The Organization installs Service Packs and Cumulative Updates on a regular basis
2. The Organization installs Service Packs, but does not install Cumulative Updates
3. The Organization does not install Service Packs or Cumulative Updates

The first case is an organization that will try to stay reasonably current with both SQL Server Service Packs and SQL Server Cumulative Updates using a thorough testing and implementation procedure. This is the best policy in my opinion. My position is that your organization is much better served by staying up-to-date with both Service Packs and Cumulative Updates (as long as you have the testing and implementation procedures and the required infrastructure in place to support that policy).

The second case is an organization that will (perhaps after some delay), install SQL Server Service Packs, but they will not install SQL Server Cumulative Updates for any reason. This is not as good as the first case, but is much better than the third case.

In the third case, some organizations never install any SQL Server Service Packs or SQL Server Cumulative Updates, for any reason whatsoever. In some cases, they actually stay on the original release to manufacturing (RTM) build of the major version of SQL Server that they are running, for the life of the instance. This is the least desirable policy, for a number of reasons.

Microsoft has a policy of retiring branches of code (either the RTM branch or a subsequent Service Pack branch) for a particular version of SQL Server when it is two branches old. For example, when SQL Server 2008 R2 Service Pack 2 was released, the original RTM branch (regardless of the CU level) was retired, and it became an “unsupported service pack”. This means that there will be no more hotfixes or Cumulative Updates for that branch, and that you will only get limited troubleshooting support from Microsoft CSS during a support case until you install a supported service pack on your instance.

Reasons that SQL Server maintenance is deferred

In some cases, an organization may not be aware of how SQL Server is normally serviced with a combination of Service Packs, Cumulative Updates and Hotfixes. Many organizations have rigid, top-down policies in place about how they maintain and service products like SQL Server, which preclude the regular installation of SPs and/or CUs by database administrators. They may also be restricted from servicing their SQL Server instances by the fact that they are using 3rd party databases that are only vendor-supported with certain vendor-specified version and Service Pack levels of SQL Server.

Many organizations also have an understandable fear of “breaking” either a SQL Server instance or an application that depends on that instance. They also may lack the time and resources to do an appropriate level of application and system testing after installing an updated SQL Server build on an instance in a test environment. In some cases, they may not have a dedicated test environment (which is a separate, major problem).

Some organizations may not have a working high-availability solution (such as traditional fail-over clustering, database mirroring, or availability groups) in place in their Production environment, so they are much more hesitant to do any type of servicing that will possibly cause a database server reboot, and cause a relatively long outage. They may actually have a high-availability solution in place, but they seldom test it with a production fail-over, and they may have less confidence in its functioning and reliability.

Reasons to regularly maintain SQL Server

After listing some of the common reasons why organizations may choose not to regularly service SQL Server, it is time to address some of these arguments. First, ignorance about how SQL Server is normally serviced by Microsoft is not really a valid excuse anymore. Microsoft has a SQL Release Services Blog, where they announce both Service Packs and Cumulative Updates for SQL Server. Matthias Bernt explained the general servicing strategy for SQL Server in his post: A changed approach to Service Packs, with more detail about the SQL Server incremental servicing model approach available in this Micosoft knowledge base article.

The condensed version of the servicing model is that individual SQL Server issues are corrected with hotfixes. You must contact Microsoft CSS and open a support case in order to get access to an individual hotfix (unless it is a security-related hotfix, which is pushed out by Microsoft Update). Depending on your level of paid support with Microsoft, this can be a relatively tedious and time-consuming process. There is also the issue that most SQL Server customers are very unlikely to even be aware of existing hotfixes that have not been released as part of a SQL Server Cumulative Update. This means that most customers are unlikely to obtain and deploy individual hotfixes on a regular basis.

Cumulative Updates are rollups of a number of hotfixes (typically anywhere from about 10-50 hotfixes) that are released every eight weeks. These Cumulative Updates are actually cumulative (as the name implies), so you will get all of the previously released hotfixes for your version and branch (RTM, SP1, SP2, etc.) of the code when you install a Cumulative Update. This means that the common statement about organizations “only applying Cumulative Updates to correct specific issues that they are experiencing” is actually not particularly valid in real life.

For example, if you were running the RTM build of SQL Server 2012 Service Pack 1 (11.0.3000), and you decided to install SQL Server 2012 Service Pack 1 Cumulative Update 3 (11.0.3349) because it included a hotfix for one specific issue that you were actually encountering, you would actually be getting all of the hotfixes for SP1 CU1, SP1 CU2, and SP1 CU3, which would amount to well over 100 hotfixes.

As Microsoft states about Cumulative Updates: “Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 SP 1 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix.” Basically this means that if you spot a particular, relevant issue that was fixed in an earlier CU, you should go ahead and deploy the latest relevant CU on the system (which will also include that hotfix).

One argument that I frequently hear about why organizations do not deploy Cumulative Updates is that, "they are not fully regression tested like Service Packs are, so we don’t deploy them." There is some validity in this point of view, but there is also a common misconception that Cumulative Updates are merely unit tested, with no regression testing whatsoever. This is not the case.

Microsoft documentation about Cumulative Updates indicates that since they “apply incremental regression testing throughout the development cycle followed by 2 weeks of focused testing within the 8 week release window, the quality assurance processes associated with CUs exceeds those of individual hotfixes.” This means that you are actually taking less risk by deploying a CU that has been incrementally regression tested and has also had two weeks of focused testing than if you were to deploy a single hotfix that has only been unit tested.

Over the past six to seven years, I have personally deployed many, many Cumulative Updates and Service Packs on a large number of systems running SQL Server 2005 through SQL Server 2012, and I have yet to run into any major problems. I have also not heard of any widespread issues doing this type of work being reported in blogs, on Twitter, etc. It could be that I (and everyone I know) have just been lucky, or perhaps Cumulative Updates and Service Packs are not quite as risky as some people believe (as long as you test and deploy them properly).

The importance of a testing and implementation plan

Unless you never plan on doing any sort of server maintenance or application updates for the life of your system (which seems like an unlikely proposition), you really need to develop some sort of testing and implementation procedure and plan that you would follow as a part of making any sort of change to the server.

This plan may start out relatively simple, but it will become more complex and complete as you become more experienced with regularly servicing your SQL Server instances and apply the lessons you learn with each deployment. Ideally, you would follow this plan anytime you make a change to the system, but that may not be possible in every single case.

Here are a few initial steps and tests that should be included in this sort of plan.

1. Install the CU on a test virtual machine
   1. Does the CU install without any issues or errors?
   2. Does the CU installation require a system reboot?
   3. Do all of the relevant SQL Server services restart after the installation?
   4. Does SQL Server appear to work correctly after the installation?
2. Install the CU on several development systems
   1. Does the CU install without any issues or errors?
   2. Does SQL Server appear work correctly during normal daily usage?
   3. Do your applications appear to work correctly during unit testing?
3. Install the CU in a shared QA or integration environment
   1. Did you follow a specific implementation plan and checklist for the installation?
   2. Do all of the applications that use SQL Server pass smoke testing?
   3. Do all of the applications pass any automated testing that you have available?
   4. Do all of the applications pass more detailed manual functional testing?
4. Install the CU in your Production environment
   1. Use a rolling upgrade strategy where possible
   2. Use a detailed, step-by-step checklist during the deployment
   3. Update your checklist with missed items and lessons learned

Conclusion

What I am hoping to accomplish here is to get more database professionals to start moving towards a mindset where they actually want to regularly maintain their SQL Server instances, rather than being hesitant or afraid to do it. This can involve a significant amount of extra work in the beginning, as you may have to convince other people in your organization to get on board with your plans. You may have to push other parts of the organization to develop better test plans, and you will have to build an implementation checklist. You will also have to get authorization from the business for maintenance windows (which should be relatively short with rolling upgrades), so you can actually get updates deployed on your Production systems on a regular basis.

In return for this extra work, you will have a better maintained system that is less likely to run into problems in the future. You will be in a fully supported configuration from Microsoft, and you will have more confidence in your high-availability technology(s), since you will actually exercise them on a regular basis. You will also gain valuable experience as you do the planning and implementation of all of this that will improve your troubleshooting skills in the future.