Service Packs coming for SQL Server 2008/2008 R2

I’m not prophetic, I promise. However, some good news on the service pack front with regards to SQL Server 2008 and 2008 R2. There have been rumblings about a last service pack for these versions of SQL Server for a while, but nothing official had been said. However, an official announcement was made yesterday afternoon:

“We are planning to ship one last Service Pack for both SQL Server 2008 and SQL Server 2008 R2. Because of the maturity of SQL Server 2008 and 2008 R2, these Service Pack(s) will be an exception in terms of timing and will ship after mainstream support of these releases ends on July 8th 2014.”

The good news is that we will be seeing one final service pack for both SQL Server 2008 and SQL Server 2008 R2. If you’re not familiar with the support dates, you can see them at the Microsoft Product Lifecycle site which includes a search by product. For instance, you can see the support dates for all the versions of SQL Server from SQL Server 2000 on.

 

Do you apply SQL Server Cumulative Updates?

I think Steve Jones makes a great point here with respect to cumulative updates:

“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.”

When you look at the fact that service packs for SQL Server (and most Microsoft products) have been few and far between, this presents a problem. There aren’t a lot of bug fixes for SQL Server specifically, but there are important ones likes ones to fix data corruption, inaccurate result sets, and an infinite loop condition against certain dynamic management views. However, if you consider applying a cumulative update, here’s the text Steve was referring to:

“A supported cumulative update package is now available from Microsoft. However, it is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems. This cumulative update 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 2008 R2 service pack that contains the hotfixes in this cumulative update package. “

When was the last time a service pack released for SQL Server 2008 R2 (just taking one supported version)? It was July 26, 2012. In other words, we’re approaching the two year point. Therefore, is it wise to wait for that service pack? According to Microsoft, you should unless you are “severely affected.” However, what is meant by “severely?” If I don’t get accurate result sets back because I implement a FULL JOIN with CROSS APPLY, that’s a problem. If I have data corruption because LOB data, that’s a problem. If I try to query what’s executing and lock up my SQL Server, that’s a problem. In my view, all three of those qualify as severely. Great, but will I get the kind of support I should? If I take that text at face value, it basically says, “Installer beware.” That’s a terrible position to be in as a customer.

Which leads me to the conclusion that either (a) Microsoft should step up support on the cumulative updates and reflect this in their language or (b) Microsoft should release service packs more regularly. I don’t foresee either happening in the near future, but as a customer, I believe it’s a reasonable request.

Minimize permissions for file locations

When we talk about security, we often point to the Point of Least Privilege. I write a lot about applying this to SQL Server, but it’s important to handle this outside of SQL Server, especially at the file / share level. Why would we care about this as DBAs / DB Pros? Here are a few good reasons:

Too Much Read Access:

By having these locations exposed with greater than required read access, it means folks can potentially get to the data and abuse it. You have an information disclosure / data exposure issue. It’s not just about trusting the individual. Anyone can fall prey to a malicious email which then deploys malware onto the system. From there the attacker hops, using the credentials of said user. Therefore, it’s important to lock down read access as much as possible.

Too Much Write Access:

This one is more of a concern with regards to ETL processes as there is typically minimal validation done on the files. For instance, do you perform an MD5 hash check on the files you might import into SQL Server? Therefore, if someone understands what’s in the files, it would be trivial to undermine the contents therein. This is why reconciliation checks and the like are so important. But even they can be beat.

Another problem, though, is that if someone has write access in both production and development, a write intended for development can happen in production. If it’s not caught, you could have a big problem with respect to that ETL process. Again, this isn’t a trust issue. All it takes is someone who has too much work to do (that describes just about everyone in IT), hasn’t had enough sleep lately (ditto), or someone who is distracted for some other reason.

Typically, when someone has write access, that person also has delete access. Therefore, if someone wanted to be malicious, you can imagine the kind of damage that could be done if they deleted the snapshot replication files or the backup files. The same is true for the ETL files, especially ones where there are some cycles turning to produce them or the files that are the result of manual processes (like where Excel spreadsheets are filled out and then imported).

The Bottom Line:

That’s why we have the Principle of Least Privilege. Applying it, regardless of your full trust in your personnel, is important. And as I described to someone recently, people change. Someone who is trustworthy but who is certainly underwater with respect to finances could consider committing an act that he or she would normally avoid. How fast can this happen? Divorce, unexpected medical bills, being ripped off, totaling a brand new car, etc., are all ways that a person’s finances can suddenly do a 180. Therefore, seek to lock down anywhere files are used as part of any of your processes.

The Scary DBA Comes to Columbia, SC

Grant Fritchey*sound of glass crashing* *cue theme music*

(in a wrestling announcer’s shocked voice) “It can’t be! He’s not supposed to be here! It’s the Scary DBA! What’s he doing here!”

That’s right, folks, SQL Server MVP Grant Fritchey (blog | twitter) will be coming to speak in Columbia, SC on May 22, 2014. You can register to attend (free) here:

Midlands PASS – May 22nd Meeting with Grant Fritchey

Here is what Grant will be talking about:

Building a Database Deployment Pipeline

The pace of business accelerates fairly continuously and application development moves right with it. But we’re still trying to deploy databases the same way we did 10 years ago. This session addresses the need for changes in organizational structure, process and technology necessary to arrive at a nimble, fast, automatable and continuous database deployment process. We’ll use actual customer case studies to illustrate both the common methods and the unique context that led to a continuous delivery process that is best described as a pipeline. You will learn how to customize common practices and tool sets to build a database deployment pipeline unique to your environment in order to speed your own database delivery while still protecting your organization’s most valuable asset, it’s data.

 

If you are closer to Raleigh or Charlotte, Grant will also be appearing in those venues. You can find details about those visits at the Charlotte SQL Server user group site.