How to Obtain and Install SQL Server Service Packs and Cumulative Updates

If you are in charge of one or more SQL Server instances, whether they are on your laptop, or a large Production data center, you should be aware of how SQL Server updates and fixes are released, and how you can obtain them from Microsoft.

First, a few definitions:

RTM means Release to Manufacturing. It is the original, released build version of the product, i.e. what you get on the DVD or when you download the iso file from MSDN.

A Hotfix is designed to fix a single issue, usually after a case has been escalated through Microsoft CSS to the SQL Server Product Team.

A Cumulative Update is a cumulative package of hotfixes (usually 20-40) that also includes all previous cumulative updates for that Service Pack. Cumulative Updates are not fully regression tested. Cumulative Updates are released every eight weeks.

A Service Pack is a much larger collection of hotfixes that have been fully regression tested. Service Packs are typically released every 12-18 months.

When you first install a shiny new copy of SQL Server 2012 (or an earlier version of SQL Server) from the DVD that came with the box, what you have is the RTM build of that major version of SQL Server. Depending on how long ago that major version of SQL Server was released, that build of SQL Server that you just installed could be years old. I often run into production instances of SQL Server that are still running the RTM build of a particular major version of SQL Server, which in my opinion is both lazy and irresponsible. If you don’t do something about it, you could be on an “Unsupported Service Pack”, which means that you will only get limited troubleshooting support from Microsoft if you ever need to open a support case with Microsoft CSS. You are also more likely to run into issues that were fixed after the product went RTM.

Hotfixes don’t get added to SQL Server on a whim, they are only added after working with CSS and the Product Team, and convincing them that the issue that is prompting the hotfix is important enough to actually fix and release. Cumulative Updates are a collection of hotfixes over the previous eight weeks. When you look at the list of fixes for each Cumulative Update, you can determine whether they seem to address any issues that you have been experiencing in your environment. Then you can decide whether to go through the pain of getting them tested and installed. You can download Service Packs directly from Microsoft without making any special requests. Service Packs are cumulative, so you can go directly from RTM to SP4 without installing any intervening Service Packs. If you have Microsoft Update (which is a superset of Windows Update) installed on your machine, it will offer up SQL Server Service Packs as updates, but I prefer to obtain and install them myself.

Some organizations have a policy of only testing and deploying Service Packs (which Microsoft sometimes calls Public Cumulative Updates (PCU)), deciding to ignore any regular Cumulative Updates that are released between Service Packs. They argue that only Service Packs are fully regression tested by Microsoft, and that they don’t have the resources to test and certify Cumulative Updates with their applications and environments. They also argue that third party software vendors have not certified these Cumulative Updates for their products, which is another reason to skip them. Personally, I am against this approach. As I said before, you should look at the fix list for each Cumulative Update when it is released, and make the determination whether you need to install that CU. Generally speaking, I am biased towards trying to stay current on my Service Packs and CUs. Going through the functional testing of your applications for a Service Pack or Cumulative Update and periodically exercising your HA solution (you do have an HA solution) is a good exercise.

At any rate, how do you find out about these Cumulative Updates? One way is to read my blog or follow me on Twitter, since I am pretty good about finding them, and announcing them to the world. Fellow SQL Server MVP Aaron Bertrand (blog|twitter) also does a good job of spreading the word. You can also check the Microsoft SQL Server Release Services blog, and the SQL Server Solution Center.

Once you know that a CU for your major version and Service Pack of SQL Server has been released, you need to find the Knowledge Base (KB) article that lists the fixes and has the link where you can “View and request hotfix downloads”. Here is the KB for SQL Server 2012 RTM CU2. In the top left portion of the KB page, you will see the “View and request hotfix downloads” link.

Once you follow that link, you need to click on the “Show hotfixes for all platforms and languages” link in the center of the page. Otherwise, you will only see the hotfixes for the platform (x86, x64, or ia64) that you are running on the computer where your browser is running, which is probably your laptop or workstation. After you choose the correct packages (which can be confusing), you must provide a valid e-mail address, and fill in the CAPTCHA information. A few minutes after that, you will get an e-mail with a link to download a zip file with the Cumulative Update setup program. I always immediately unzip the file and save it in a directory structure like you see in Figure 1 (with further subdirectories for x86 and x64), so I don’t get confused later about which CU I am dealing with.

image

Figure 1: SQL Server 2012 Update Directories

Finally, after jumping through these small hoops, you are ready to install it on a test instance (which could be a virtual machine) just to make sure that the setup program works, and does not seem to break SQL Server. After this initial smoke test, you should do additional installs and further, more involved testing before you eventually deploy it to your Production environment during a scheduled maintenance window.

When it is time to deploy a CU in Production, you are looking at an outage of some duration, no matter what you do. At a minimum, the CU setup program will stop and start the SQL Server Service, and it could be stopped for several minutes while the CU installation is running. Sometimes, the CU setup program will want a reboot after it finishes, depending on what SQL Server components you are running. Making sure you are not running a local copy of SSMS on the instance that is being patched will help minimize the chance of a reboot. If you don’t have any high availability solution (such as AlwaysOn availability groups, database mirroring or fail-over clustering) in place, this outage could last anywhere from 5 to 20 minutes, which is kind of bad…

If you do have an HA solution in place, you can do a “rolling upgrade” where you upgrade the Witness, then the Mirror, and then the Principal (for mirroring) or each node in turn (with fail-over clustering), where you can install a CU with a couple of sub minute outages. Using database mirroring, my outages are typically 10-15 seconds for each failover for this type of maintenance.

How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Imagine that you are using synchronous database mirroring with SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012 Standard Edition. Now, you want to upgrade the instances on both sides of the mirroring partnership to SQL Server Enterprise Edition.  This will let you take advantage of some of the valuable Enterprise Edition-only features in that version of SQL Server.  These include online index operations, data compression, table partitioning, etc.. How would you go about doing this without having to take a long outage or re-establish your mirroring partnership?

Rolling Edition Upgrade

I had this question come up recently, but I was not 100% sure of the best answer without some testing. Remember, this is going to be a rolling edition upgrade from SQL Server Standard Edition to SQL Server Enterprise Edition. This is different from a rolling version upgrade from an older major version of SQL Server to a newer major version of SQL Server. If you need to do a rolling version upgrade, you can use log shipping or database mirroring to get it done with a very short outage.

Here is some guidance from Microsoft about how to do a rolling version upgrade: How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances

SQL Server Versions and Editions

Let’s cover some background on the terminology that Microsoft uses to differentiate between the different flavors of the SQL Server boxed product.

First, you have the major version of SQL Server, which could be one of these:

  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012

You also have the edition of SQL Server, which depending on the major version involved could be one of these:

  • SQL Server Express Edition
  • SQL Server Workgroup Edition
  • SQL Server Standard Edition
  • SQL Server Business Intelligence Edition
  • SQL Server Developer Edition
  • SQL Server Enterprise Edition
  • SQL Server Data Center Edition

Most people will be dealing with upgrading from SQL Server Standard Edition to SQL Server Enterprise Edition, which is what we will discuss here.

If you need to do a rolling edition upgrade, you need to make sure that you have an existing database mirroring partnership for all of the databases that need to be mirrored between the two instances. This is because SQL Server will prevent you from establishing a new mirroring partnership between databases that are on different editions of SQL Server.

Figure 1 shows the error message that you will get from the “Configure Database Mirroring Security Wizard” if you try to create a new mirroring partnership between a database on an instance running SQL Server 2008 R2 Standard Edition to a database running on SQL Server 2008 R2 Enterprise Edition.

image

Figure 1: Error Message When Trying to Create a New Mirroring Partnership between Different Editions of SQL Server

This means that you will need an existing mirroring partnership between your databases on the two instances before you attempt to do an Edition Upgrade of SQL Server from Standard Edition to Enterprise Edition. In order to minimize your downtime, and actually perform a rolling edition upgrade, you are going to want to upgrade the mirror instance first. You start this out by running Setup.exe from the SQL Server installation media, and then choosing Maintenance in the left hand pane of the screen, as shown in Figure 2.

image

Figure 2: Maintenance Screen Showing Edition Upgrade Option

After this, you just have to click though several different screens for various setup checks and licensing prompts as shown in Figures 3 through 10. The actual installation portion of the Edition Upgrade will take about 30 seconds. During the install, the mirrored database(s) will go into a disconnected state for about 10 seconds, and then will automatically reconnect and change from a synchronizing to a synchronized state.

image

Figure 3: Setup Support Rules Screen Results

image

Figure 4: Product Key Screen

You will have to enter a valid product key for SQL Server 2008 R2 Enterprise Edition.

image

Figure 5: License Terms Screen

You must accept the license terms in order to proceed.

image

Figure 6: Select Instance Screen

image

Figure 7: Edition Upgrade Rules Screen

image

Figure 8: Edition Upgrade Detailed Report Screen

image

Figure 9: Ready to Upgrade Edition Screen

After you click on Upgrade, it will take about 30 seconds to complete the process

image

Figure 10: Upgrade Complete Screen

Here is what you want to see when you are done with this instance.

image

Figure 11: SSMS showing Mirror instance upgraded to SQL Server 2008 R2 Enterprise Edition

Confirming the Edition Upgrade

Running SELECT @@VERSION shows that this instance has been upgraded to SQL Server 2008 R2 Enterprise Edition. You can also see that the mirrored database has reconnected and is also synchronized. Your next step would be to failover all of the mirrored user databases from the original Principal instance (which is still running on SQL Server 2008 R2 Standard Edition) to this newly upgraded instance. After that, you would repeat the Edition Upgrade process on the other instance, with the same steps and results. After both sides of the mirroring partnership have been upgraded to SQL Server 2008 R2 Enterprise Edition, you would probably want to failover all of your mirrored user databases back to the original Principal instance so that you don’t end up violating the licensing requirements that allow you to only need licenses for the active side of the mirroring partnership.

Conclusion

The key takeaway of this exercise is that by following these steps, you can perform a rolling Edition Upgrade with an existing database mirroring partnership. You can do this without having to reinitialize your mirrored databases or take a longer outage while you are doing an Edition Upgrade of the active instance.  You do have to have an existing mirroring partnership to make this work. This is because SQL Server will not let you create a new mirroring partnership between SQL Server Standard Edition and SQL Server Enterprise Edition.