Install Cumulative Updates

For years I haven’t recommended installing Cumulative Updates (CU) as they are released. The main reason is that Microsoft has had language associated with their CUs that say customers should not install a particular CU unless they are experiencing the specific issues the CU corrects. Each CU has (in the past) also noted that you should stick with the latest Service Pack otherwise. That disclaimer has been enough for me to be concerned about CUs in a general sense, despite the insistence that the CUs were tested as well as SPs. My thought was that if the testing was the same, that disclaimer wouldn’t exist.
Well, things have changed. The latest CUs have this language in the section where a KB says that Microsoft recommends CUs as they are release:
  • SQL Server CUs are certified to the same levels as Service Packs, and should be installed at the same level of confidence.
  • Historical data shows that a significant number of support cases involve an issue that has already been addressed in a released CU.
  • CUs may contain added value over and above hotfixes. This includes supportability, manageability, and reliability updates.
That’s good news, though as Kendra Little notes, you still need to test. Bugs will still exist in patches, and really all software, so it’s important that you test in your own environment. That means you need a test plan that can easily run, preferably an automated test plan. If nothing else, this is a good reason to use tSQLt and have tests written for your system. At least you can verify important queries and systems are working. Kendra has a good list, so read her post.
While I think the quality of CUs is up and they are probably as safe as most of the Windows patches we get every month (and are often installed no matter our individual feelings), I’d still be wary. If you can’t test, if you’re busy, if this is a month you can’t afford for things to fail, then don’t install the CU. This is like throwing your own developers’ code into production without any testing. Make sure you know what is being changed, and you look for obvious problems. No one will be too upset of an obscure issue, but if your scheduled jobs start failing, you’ll dramatically reduce the confidence people have in you.
I am still wary of all patches. They’re disruptions, to both your routine, and potentially to availability as well. Make sure you test, but if you have the time, I’d say keeping up with patches is worth doing. Microsoft is constantly fixing issues, and you want to take advantage of their hard work, if you can verify the patches don’t degrade your system.
Steve Jones

Reasonable Timeframes

Many of us hear about problems with various systems on a regular basis. We report them in Database Weekly far too often, and I’m sure a few of you have been informed (or found) bugs, nagging issues, even vulnerabilities in your systems.

What’s a reasonable time to fix a system after an audit?

It’s a simple question, but it’s not very simple to answer. After all, most of us don’t have a lot of slack development time built in to fix issues. Unless the issue is a broken application that doesn’t work, the items disclosed in an audit need to scheduled in among other work. After all, most of the time the audit finds something that no one is aware of, or no one has wanted to fix. This is work that no one really planned on completing.

I ran across an interesting piece about the Employment Department for the state of Oregon hasn’t fixed a number of issues after an audit last year. While some strides have been made, there are still outstanding issues, the sum total of which it is estimated will take a decade to complete. That’s a long time, but in large systems, especially ones where the entire application cannot be rewritten because of resources, it’s not unusual. I’ve worked in a few places where we had large scale systems that we knew had issues, but we couldn’t easily re-design and implement fixes in any reasonable length of time. Often this was because of downstream dependencies, but certainly culture and management hadn’t made change a priority.

I sympathize with those people dependent on mainframe systems. The power and reach of those systems, the poor documentation, not to mention the complex training required to change clients’ habits is huge. I would hope that the government groups using these large scale systems would work together to jointly proceed on development, with politicians also involved to help standardize the requirements across state lines (or countries’ borders) and simplify the software needed.

However, no one ever makes software simpler, especially when it’s being designed.

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

The Service Pack Fiasco

I remember getting a Service Pack years ago that caused a blue screen of death on Windows 2000 servers when it was installed. In fact, a quick search shows that quite a few operating systems (and SQL Server 2005) have had issues with SP2 over the years. Perhaps we shouldn’t be waiting for the first service pack to upgrade, but upgrading and then skipping SP2 for various products.

Microsoft has responded fairly well to problems with service packs by removing them quickly from their download sites and re-releasing them. This occurred recently for SQL Server 2014 SP1, where an issue was discovered with the SSIS Catalog. This is disconcerting to me, not because of the bugs, but because these mistakes and problems seem to lead Microsoft in the direction of abandoning Service Packs. The idea floated in the past, and still pushed by many people, is that the Cumulative Updates are good enough for patching SQL Server.

That seems crazy to me. If we have problems with a service pack, wouldn’t we still have problems with a CU? Or is Microsoft hoping that the smaller group of people that are impacted with CU issues are good beta testers and limit the impact of issues to the community? I’m assuming the testing is similar for all patches, if not the same. However as we all know, testing can’t cover every possible scenario. Microsoft notes this as their latest documentation includes these quotes:

I really prefer that a service pack is released every year for each product. It can contain all the CUs up to that point, with no additional patches, thought hopefully a bit more testing. It should be a line in the sand that helps administrators manage their systems to keep up with patching and reduce support requirements. I’d actually even suggest Microsoft require vendors that want to certify their products on SQL Server and use any logo, validate their software on an SP within four months of release. In most cases, there isn’t any work for vendors, merely the effort to re-run all their tests.

We pay a large licensing cost for SQL Server, and the lifecycle support policy under which many of us purchased our software notes that we’ll get ten years of support and the term “service pack” is embedded throughout the SQL Server support pages. We should receive a service pack every year to provide continuing support for our platforms.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and LibSyn.

The Final Service Packs

There was an announcement recently that Service Pack 3 for SQL Server 2008 R2 was released. This is the final SP for that version and is mostly a rollup of all the previous cumulative updates (CU), though there are a couple additional items included.

This is good news, and if you are running the “R2” version, I’d test this and install it. No more CUs will be released, though you’ll want this SP installed in case additional security patches are released during extended support. The last thing you’ll want is a crisis situation where you need to install a security patch based on SP3, and you haven’t tested this update.

Earlier this year, Microsoft announced that we’d get one last SP for both SQL Server 2008 and 2008 R2. That was good news as many people are running these versions, and they haven’t installed all the previous CUs. I can understand this situation, and it would be good to bundle all the changes together in one final patch for companies that find the platform suits their needs. It’s also a fair request for customers that paid Microsoft for  a platform and should receive all patches developed during its lifecycle. Including a final patch that closes out support.

I’m still waiting for an SP4 for SQL Server 2008 and I hope we get one soon. I’m not sure what the plans are for SQL Server 2012, 2014 and future releases, but I’d like to continue to see the bi-monthly CUs, an annual SP, and a final SP when each version transitions from mainstream to extended support.

Steve Jones

UPDATE: SQL Server 2008 SP4 was released just after this was written. It is available here: http://www.microsoft.com/en-us/download/details.aspx?id=44278

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.3MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

Patching Problems

I applied the Windows 8.1 update a few weeks ago and had some issues with my machine. Windows was fine, but I lost my SQL Server service. A few other users, including some of the SQLServerCentral community also had issues and sent me ideas, but their fixes didn’t work for me. That was OK because the problems gave me a chance to use PoSh to solve a real problem. I’ll be blogging about that in the next week.

However the 8.1 update has caused lots of issues, and Microsoft is acknowledging these problems. That’s good, but the process gives me pause, and to a large extent, I think this makes more and more people suspect about all of Microsoft’s patching processes. I bet there are companies that feel even more justified in waiting for SP1 for SQL Server 2014 before upgrading, even though there is a chance that the patch itself will cause problems.

This is one reason I’ve been hesitant to remain current with Cumulative Updates (CUs). Microsoft doesn’t stand behind them, with the text on each CU page that users should only apply the patch if they are experiencing specific problems. Otherwise users are told to wait for the next Service Pack, which seem to be coming less and less often.

Any patch can cause issues, and I certainly don’t like the idea of automatic updates always being applied because if there are issues, they can become much more widespread than controlled updates. There is also the issue of vendor responsiveness. Microsoft has pushed out patches that caused issues, and while they’ve try to fix issues quickly, I don’t want to have all of my desktops, or all of my SQL Servers, down because of a bad patch.

I don’t know how we patch in a more effective manner, but I do know that I want to have some control over updates as an end user, and I also want ways to remove patches. Moving to the app model of always applying patches over patches, and never rolling back seems to be a step in the wrong direction.

PS – If you want better servicing for SQL Server, vote for final service packs for products still under support.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.8MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Patching Problems

I applied the Windows 8.1 update a few weeks ago and had some issues with my machine. Windows was fine, but I lost my SQL Server service. A few others, including some of the SQLServerCentral community also had issues, but their fixes didn’t work for me. It was OK, because the problems gave me a chance to use PoSh to solve a real problem. I’ll be blogging about that in the next week.

However the 8.1 update has caused lots of issues, and Microsoft is acknowledging these problems. That’s good, but the process gives me pause, and to a large extent, I think this makes more and more people suspect about all of Microsoft’s patching processes. I bet there are companies that feel justified in waiting for SP1 for SQL Server 2014 before upgrading, even though there is a chance that the patch will cause problems itself.
This is one reason I’ve been hesitant to remain current with Cumulative Updates (CUs). Microsoft doesn’t stand behind them, with the text on each CU page that users should only apply the patch if they are experiencing specific problems. Otherwise users are told to wait for the next Service Pack, which seem to be coming less and less.
Any patch can cause issues, and I certainly don’t like the idea of automatic updates always being applied because if there are issues, they can become much more widespread than controlled updates. There is also the issue of vendor responsiveness. Microsoft has pushed out patches that caused issues, and while they’ve been responsive, I don’t want to have all of my desktops, or all of my SQL Servers, down because of a bad patch.
I don’t know how we patch in a more effective manner, but I do know that I want to have some control over updates as an end user, and I also want ways to remove patches. Moving to the app model of always applying patches over patches, and never rolling back seems to be a step in the wrong direction.
Steve Jones

Beware the Windows 8.1 Update Upgrade

I had Windows 8.0 on my laptop, and went to Win 8.1 earlier this year. Everything went well. Even this spring, when I installed the 8.1 update, things went well on my laptop. This was my test environment, as most days I work on my desktop. I decided to then update my desktop, and things went well overall, until I went to use SQL Server.

At first, I couldn’t connect to SQL Server from SSMS. No big deal, I thought the update had stopped a service. I checked Services, and was surprised to not see a SQL Server database service. I had a browser and VSS service, but nothing else.

I rebooted my machine, which didn’t help. I manually ran sqlservr.exe, and it failed with permissions issued. I tried repairing the SQL install, but that didn’t work either. More than a few people reported similar things, but nothing I found on the Internet worked. I kept getting security SPID errors, so I just uninstalled, and then reinstalled SQL Server.

I’m not sure under which accounts I had my service running, and it’s possible I used my own account, but usually I set up a SQLService account on my machines. In any case, it was a hassle and pain.

I’m not telling you to avoid the 8.1 Update, but I am warning you and noting that you might want to be sure you have good backups of your systems.

Lobbying for Change

I ran across a note recently on Twitter from Adam Machanic. He wrote:  Just spent most of the day working through a subtle PK issue – 1 bad row out of 18M. Would have killed for this. The item in question was a Connect item, one with almost 500 votes. It’s a good one, and I’d encourage you to vote for it. I know that it seems many of these items are never worked on, but some changes make it into the product, so I’d ask you to continue to vote for change.

When Connect was first introduced, Andy Warren and I debated the value of the platform. On one hand it made good sense to directly feed information back to developers, but on the other hand, it was likely that those items that got more notoriety or votes might get fixed, even if they weren’t necessarily good ideas. The popularity of an item doesn’t necessarily mean it’s one that should be fixed in the product first. We also worried about one of the big problems of the platform and that is that a tremendous amount of noise of entered and it becomes hard to triage the submissions.

As I watch Connect evolve, I can’t help but think that it’s been mostly a failure from my perspective, with a few notable successes, like Service Pack 3 for SQL Server 2005. There’s too much noise and too many items ignored. However I also do think that those items that get lots of vots do get more consideration from Microsoft. More votes doesn’t mean that the feature will get fixed, but I do believe the item gets talked about. (As an aside, please vote for more, final Service Packs)

Personally, I think that raising awareness of possible suggestions or problems is a good idea. I’d love to see a top 10 list of Connect items from MS for consideration every month. Having them highliht some items they’re considering from the list might help focus attention from customers. I don’t think that’s likely, but I wonder if highly debated suggestions might be worth highlighting at SQLServerCentral. Would you like to see a Connect item of the week? Something you could vote on or even debate as a good idea? I would, and I’d consider adding them as a way to help improve the platform that I enjoy working on the most.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Mevio . feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Servicing SQL Server in the Future

Service Packs have become the way that many of us patch the various Microsoft products we use. Many administrators find patching to be time consuming and difficult to justify, even for security patches, and as a result, often wait for a Service Pack before they inform clients and schedule testing. Most people used to always wait for Service Pack 1 before installing a new version of a server product, but I don’t think that’s necessary anymore. The quality of Microsoft server products has gone up quite a bit in the last 5 years.

However, I don’t like the way that the servicing, or patching, of products has evolved. For SQL Server, we get patches every other month, known as Cumulative Updates. Exchange Server has also moved to this format, though it seems many of the other server products (Windows Server, Sharepoint, System Center, etc) still release Service Packs. A few years ago it was rumored that Microsoft would not release Service Pack 3 for SQL Server 2005. A number of us voted on this patch and it was eventually released.

The strategy announced at that time was that SQL Server would receive a service pack 6 months after the RTM release and thereafter annual service packs until support expired for the product. In between, Cumulative Updates would be released. That seemed to be the case for a few years, but once again Microsoft seems to have quietly decided not to move forward with Service Packs. We’ve been getting cumulative updates, but no service packs in over a year and a half. The last SPs we have are SP1 for 2012 (Nov 2012), SP2 for 2008 R2 (July 2012), and SP3 for 2008 (Oct 2011).

If you’re like to express your opinion, take a moment and vote for these items:

It just takes a moment to click on them, and I’d also ask that you pass along the URL (or this editorial) to friends that work with SQL Server.

Personally I’d also like to see a solid strategy moving forward that includes annual service packs, especially with a 2 year release cycle. I know some of my fellow MVPs and writers like the CU strategy and wish more people would adopt it, but I don’t agree. It’s time consuming to test and prepare for updates, and as long as this paragraph appears on CU pages, I do not think we should recommend CUs to DBAs.

“This cumulative package is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems. The updates in this package may receive additional testing. Therefore, if you are not severely affected by any of these problems, we recommend that you wait for the next SQL Server 2012 service pack that contains the hotfixes in this package.”

To me that shows even Microsoft doesn’t completely want to stand behind their cumulative updates.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Mevio . feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.