The SQL Dude!

Today I am going to talk about the different patching options that are available with SQL Server and how they work. Often people get confused and assume that there is only one single sequence of updates for SQL Server, starting from a lower build number to higher build. This is not true, because there are different branches for patching and different types of patches released. Let me try and clarify a few of those common assumptions/misconceptions today.

SQL Server is a large product (well, it’s a Database server after all) and so requires a different patching mechanism to other products like Windows.

Types of SQL Server Patches (Product Updates)

1. Service Packs (SP):

Consider this as a consolidated list of different updates bundled into one large package. Typically, there are 2-3 Service Packs for the lifetime of a product. A typical product is in mainstream support phase for roughly about 5-6 years after which it goes into extended support phase. So if a service pack is released once every 2 years, that makes it 2-3 SP’s for the lifetime of a product.

There are exceptions to this. E.g. SQL Server 2005. This has 4 Service Packs released and probably because it was so popular that people were comfortable using it even in extended support phase instead of upgrading to the next version. Hence, this required SP4 to be released to cater to the community base that was still using SQL 2005. Makes business sense, doesn’t it? 🙂

I don’t want to get into the details of the different support phases. But you read the simplified explanation of this in Bob Ward’s blog post here.

2. Cumulative Updates (CU):

To put it simply, Cumulative updates are released once every 2 months for each branch of the product that is in mainstream support [I’ll explain about “branches” shortly]. The SQL Server release team shifted to this “Incremental Servicing Model (ISM)” some years back to make it easier for administrators to predict and plan for updates. This fits in nicely with change control and downtime planning, to apply these cumulative updates.

Branching

This is where most people get confused. I said earlier that CU’s are released, but I wasn’t totally honest. Cumulative Update packages are release for a Service Pack. Still don’t see the big picture? Let me explain. After each Service Pack, a CU is released every 2 months and this CU is only applicable for that particular service pack branch. E.g., CU2 for SP1 != CU2 for SP2. You may ask, why is this stuff so complicated?

My answer: Think Linear, cause Time flows in only 1 direction.

Let me explain this with a practical example. After Service pack 1 is released, lets says 10 cumulative updates were released which makes it roughly 2×10=20 months. Now, its time for a new Service Pack. Once Service pack 2 comes out, we have SQL Servers that are in RTM build, SP1 build and others somewhere between SP1 CU1 to SP1 CU10. Can all of these be upgraded to SP2 immediately? Obviously not! People do take time to test in QA/Dev/Test etc.. before a service pack is applied (all of us go through this as part of our jobs, don’t we). Now as time goes on and 2 more months are past, the next CU for SP2 comes out. And this is exactly what Branching is all about. We now have SQL Servers that are on the base RTM, some on SP1 branch and some on SP2 branch.

You might have noticed that once a service pack is released, cumulative updates continue to be released for older service packs. Why? Think back to the mainstream vs. extended support phase. Till a service pack goes into extended support phase, Microsoft has a legal obligation to continue to service that branch of the product. This tends to be the confusion for most people, but if you understood Branching, then you will understand why CU’s are released even when SP’s are also being released.

Want me to complicate things further? The strong minded may read on …

Quirky Scenario:

You have a SQL Server instance on SP1 CU5 build. You hit a known issue (Bug) which you find out is fixed in CU6. Great! You test CU6 on your test system and the fix does solve your issue, so you implement CU6 on your production server. Life is good!

A month later, a new service pack (SP2) is released. Your boss wants you to immediately apply this SP2 on Production (due to company policy, compliance blah blah blah…). The dutiful employee that you are on, you successfully apply SP2 on Production and find out that the old issue is back again. WTF!

Think about this carefully and you will see the answer.

Service Pack 2 released just a month after CU6 was released. I mentioned that a CU is released every 2 months, which means your CU6 must have been developed/tested 2 months + 30 days = 3 months back by Microsoft. A Service Pack is considered a major upgrade, so obviously the development/testing time would be more for a SP vs. a CU. Let me point you to a piece of documentation most of you would have missed.

(Sample taken from KB 2285068)

So, the fix for your issue which was part of SP1 CU6 is not part of SP2 because of the testing time required for a service pack. What happens in such a scenario is, the next cumulative update for SP2 (which is CU1) will contain the fixes there were present in CU6. This is called a post service pack roll-up fix. As a DBA, you need to keep in mind that if you are following the regular CU schedule of patching your SQL Servers every 2 months and then applying a Service Pack, you must immediately apply CU1 for the newly released Service Pack. CU1 for Service Packs are released pretty soon after the SP is released (usually in a week’s time). Even this minute detail is documented.

(Sample taken from KB 2289254)

 

3. General Distribution Release (GDR):

These are special patches that are released mainly for security issues or issues having a broad customer impact. I simply like to call them “security fixes”. These are not a scheduled fix and are released by Microsoft as appropriate. The important thing about these updates are that it impact every SQL Server and is applicable for most SQL Server versions. That is why, whenever a GDR is released it is available for public download in download centre, available through Windows Updates as a High/Critical fix and in all likeliness you IT team will push them to your machines for automatic update .

The other thing to keep in mind with GDR releases it that even if your product is in extended support phase, you will still get these critical security fixes. For each GDR update, a security bulletin is posted which lists the severity rating, the affected software as well as the affected versions of the software.

If you have ever read the KB article for any SQL Server security bulletin, you may have noticed two different download options.

a. GDR Software Updates
b. QFE Software Updates

You need to choose the correct version based on the build number of your SQL Server instance. GDR updates are typically if you are on the base service pack build or earlier CU’s, whereas QFE updates are for those who have service pack + some cumulative update applied. In the latter, case the same GDR security fix is available for you with the existing CU fixes. In the former, you have the service pack + only the security fix applied.

True or False?

1. You need to apply each CU in sequence to be able to apply the latest CU: False

As the name says these are cumulative fixes, meaning the fixes from the previous CU’s are carried over to the next CU. You only need to apply the latest CU to have the fixes from the previous updates.

2. I have to apply each Service Pack to be able to apply the latest Service Pack: False

Same logic as CU. Each Service Pack is also cumulative, so only need the latest Service Pack to have all the fixes (The only exception is the Quirky Situation mentioned above).

Starting with SQL Server 2008, it is possible to uninstall Service Packs and cumulative updates. You can see each SQL update present in Add/Remove programs (or “View Installed Updates” in Vista+) and these can be uninstalled. Refer screenshot below.

For more information on this topic, please read the following MSDN article.

Overview of SQL Server Servicing Installation
http://msdn.microsoft.com/en-us/library/dd638062(v=sql.100).aspx

Folks, I know this was a long post, but this was something that I wanted to talk about for a long time. If you have any questions leave a comment behind or drop me an email and I will definitely try and get back to you. Cheers!

.