As you may be aware if you have been reading my blogs or books over the years, I am a pretty strong advocate of staying current with both SQL Server Service Packs and SQL Server Cumulative Updates. This is also something that SQLskills recommends on our customer engagements. Despite this, it is still quite common to see people running very old builds of the major version of SQL Server that they are using. They might be running a SQL Server build that is 12, 18, or 24 months old. It might be so old that it is unsupported by Microsoft, because it is considered to be an “unsupported service pack”, meaning that Microsoft CSS will only provide limited troubleshooting support until you upgrade to a supported service pack.
As Microsoft releases new Service Packs for SQL Server, they eventually “retire” previous branches of code for that particular major release of SQL Server. When a new major version of SQL Server (such as SQL Server 2008 R2) is released, that initial build (which is what comes on the installation media unless you have built a slipstream installation) is called the release-to-manufacturing (RTM) build. Eight weeks after the RTM build, Microsoft will release a rollup of hot fixes called a Cumulative Update (CU). A SQL Server CU typically has anywhere from about ten to fifty hotfixes. Cumulative Updates are not fully regression tested like a full Service Pack is, but I have had very good luck with Cumulate Updates over the years. The first CU for the SQL Server 2008 RTM branch was called SQL Server 2008 RTM CU1. Every eight weeks after that, Microsoft will release another CU for that RTM branch.
Eventually, Microsoft will release Service Pack 1 for that major version of SQL Server, which starts another separate release branch of the product. Eight weeks after that, the first CU for that new Service Pack will be released. So for example, you would have SQL Server 2008 R2 SP1 RTM, and then eight weeks later, you would have SQL Server 2008 R2 SP1 CU1. Eventually, Microsoft will release Service Pack 2 for that major Version of SQL Server, which starts another separate release branch of the product. When SP2 is released, the original RTM release branch is retired, and the entire RTM release branch becomes an “unsupported service pack”, regardless of what RTM CU you have installed. That means you will have to upgrade to SP1 or SP2 to be on a supported service pack.
As a database professional, you need to be aware of how this servicing system works, and what exact build of SQL Server that you are running on your various servers. Then you can make more informed decisions on when you want to plan, test, and implement SQL Server updates (whether you use only Service Packs or both SPs and Cumulative Updates), so that your servers are on a fully supported build of SQL Server. I also firmly believe that you will have fewer problems over time if you make a concerted effort to stay on a current build. Going through the planning and testing effort required to install a Service Pack or Cumulative Update on a production server is a good exercise for you and your organization. It exercises your testing plan and your HA procedures, and it gives you more experience rolling out updates.
I recently was asked for some specific reasons why someone should upgrade from SQL Server 2008 R2 SP1 RTM (10.50.2500) to SQL Server 2008 R2 SP2 CU3 (10.50.4266). I decided to look through the Knowledge Base (KB) articles for every single Cumulative Update released after SQL Server 2008 R2 SP1 RTM was released, looking for specific hotfixes that seemed to affect database engine performance or basic reliability. I went from SQL Server 2008 R2 SP1 CU1 through CU7, then I jumped to SQL Server 2008 R2 SP2 CU2 and then finally to SQL Server 2008 R2 SP2 CU3. I did it this way to avoid overlap between the two branches.
You can look at the KB article list for each Service Pack to put together your own list of “important” fixes based on what features of SQL Server you are using, using the links below:
Microsoft SQL Server 2008 R2 Cumulative Update KB Article Lists
Here are all of the Cumulative Updates that I looked at, with the relevant fixes listed for each one. Keep in mind that Cumulative Updates are actually cumulative, meaning that a later CU will include all of the hotfixes from all of the previous cumulative updates. In this scenario, SQL Server 2008 R2 SP1 CU5 is synchronized with SQL Server 2008 R2 SP2 RTM, which is why I jump from SP1 CU7 to SP2 CU2.
SQL Server 2008 R2 SP1 CU1 (10.50.2769.0)
SQL Server 2008 R2 SP1 CU2 (10.50.2772.0)
SQL Server 2008 R2 SP1 CU3 (10.50.2789.0)
SQL Server 2008 R2 SP1 CU4 (10.50.2796.0)
SQL Server 2008 R2 SP1 CU5 (10.50.2806.0)
SQL Server 2008 R2 SP1 CU6 (10.50.2811.0)
SQL Server 2008 R2 SP1 CU7 (10.50.2817.0)
SQL Server 2008 R2 SP2 CU2 (10.50.4263.0)
SQL Server 2008 R2 SP2 CU3 (10.50.4266.0)