SQL Server 2008 R2 Review


In August 2008, Lady Gaga released her premier album The Fame. This 13-track work produced several hit singles including Just Dance, Poker Face, and Paparazzi – but it also produced plenty of controversy. Her exaggerated use of fashion, musical cliches, and sex gave her plenty of media attention – both good and bad. If you’ve never heard one of her songs or seen a video, the first 30 seconds of Poker Face will give you a pretty good idea of her style (musical and otherwise). Actually, you don’t even really have to hit play – the first frame showing her poolside between to two Great Danes probably gives you a pretty good idea where things will go next.

Anybody can get famous doing an Internet donkey Great Dane show. The big challenge for suddenly-famous artists isn’t the first release, though – it’s the sophomore release. The second album shows if the star has the potential to keep cranking out new material, or if they drop off the face of pop culture as a flash-in-the-pan.

A little over a year later, Gaga released a followup album called The Fame Monster, but there’s some debate over whether it should even be considered her sophomore work. Due to the short time between releases, its shorter length (only 8 tracks), and its sales method (often packaged with The Fame in a single box), it was seen as an extension of her debut album. This effort didn’t win over any new fans; if you didn’t like The Fame, then The Fame Monster wasn’t going to turn you into a Little Monster, as Gaga fans are known.

It’s a lot like SQL Server 2008 and 2008 R2.

Microsoft SQL Server 2008: The Fame

It’s impossible to review this month’s newest release from Microsoft, SQL Server 2008 R2, without reflecting back on the recent release of SQL Server 2008. R2 comes quickly on the heels of 2008 and tells more of the story that was first started in 2008.

SQL Server 2008’s hit singles for production DBAs included:

  • Backup compression – this instant-winner of a feature has been available in third party software like Quest LiteSpeed for years, and 2008 Enterprise Edition users got it built into the engine.
  • Transparent Data Encryption – if you need it, you desperately need it. TDE makes it ridiculously easy to protect your company’s data from headlines about stolen backup tapes.
  • IntelliSense – wildly popular at first, but quickly fell off the charts as users discovered it didn’t work with previous versions of SQL Server.

The less popular B-side songs were:

  • Data compression – like backup compression, this feature can give an instant performance benefit under certain circumstances. Even better, the data is only compressed once, and then all backups are smaller and faster without repeated CPU work. I think this is the most underrated song in the album, although filtered indexes & statistics are a close second.
  • Policy-Based Management – a cult favorite that hasn’t managed to crack the charts for widespread adoption. Everybody’s heard about this song, but nobody knows the words.
  • Resource Governor – originally touted as the ability to stop runaway queries or ease consolidation pains, this didn’t actually work as intended due to its inability to throttle IO. Storage is usually SQL Server’s biggest bottleneck.
  • Spatial data – programmers have worked around the challenges of storing latitude/longitude data in RDBMS’s for years. With support built into the engine, now developers could use spatial indexes to execute tough queries much faster.
  • Filestream storage – answering a question DBAs everywhere kept asking, “How can we make our databases larger and more difficult to back up?”

The release was chock full of upbeat songs, but the beats were just a little too tricky for mainstream dance clubs. SQL Server 2005 was good enough, and companies didn’t have the money to roll through quick upgrade projects, so I didn’t see a widespread adoption of 2008. In the recent PASS Summit poll, 52% of responders said less than half of their servers were SQL Server 2008. I haven’t heard anyone say they dislike SQL Server 2008 – they just didn’t care quite enough to deploy it to every server in the shop.

SQL Server 2008 R2: The Fame Monster

Real music critics seem to have a somewhat positive view of The Fame Monster, giving it 75 points out of 100 on Metacritic. Gaga clearly resonates with the purchasing public, having earned over 17 platinum records as of this writing. People between those two extremes have a much more negative reaction, however – my music-critic friends (people who think they know a lot about music, yet don’t have jobs in the business) uniformly hate her. They say she’s low-brow, populist, and oversexed.

SQL Server 2008 R2 will have a similar reputation in the database world. Analysts, the IT industry’s equivalent of music critics, love SQL Server. On the other extreme (paying customers), Microsoft sells the heck out of SQL Server, so it seems to be popular with the folks with money. People in the middle are more divided, though – I hear plenty of people on other platforms referring to SQL Server as not good enough for all kinds of reasons. It’s too expensive, it’s not full-featured enough, it’s too-full-featured (yes, that’s the NoSQL claim), it’s not open source, the list goes on and on.

People who liked SQL Server 2008 will like 2008 R2, but for different reasons. It brings some really new material to the party, albeit with exactly the same theme as 2008.

PowerPivot Caters to the Paparazzi

Lady Gaga Fan Art
She's no Donald Farmer, but she'll do

Microsoft used sex to sell SQL Server 2008 – and that sex was Business Intelligence. BI is by far the sexiest topic for businesses today. It’s in every magazine, every book, and companies are dying to figure out how to get more actionable information out of their raw data. SQL 2008’s material read like a pinup calendar – “We’ve got all your forms of data right here, big boy! C’mon over and analyze me.”

R2 takes the sex appeal even further – BI isn’t in the hands of pros anymore, but rather amateurs. Any sleazy middle manager with a fetish for data can whip out an Excel spreadsheet and get his PowerPivot on, doing multi-million-row analysis on his laptop.

This high-quantity, low-quality analysis comes with its own challenges. Who rates the data? Are you sure the numbers in that loose schema are right? Is that juicy profit figure really profit, or a big, fat, hairy loss masquerading as something else? Who let that underage data into the corporate report? And in the middle of a steamy analysis session, does anyone even care?

PowerPivot doesn’t appeal to buttoned-down database administrators accustomed to pure, cleansed data stored behind locked doors, but that’s not the point. It sells to managers and executives who want fast, easy access to decisionmaking numbers, and they’re going to love it – especially when combined with SharePoint.

StreamInsight: It’s Money Honey

Packaging StreamInsight in the SQL Server box will be controversial, but the feature is great for developers. StreamInsight helps developers analyze massive amounts of incoming data – think stock trades, Twitter, or clickstream data for web sites. Developers can then build rules (no, not with T-SQL) to handle and parse that data.

Notice how I said “developers” three times in three sentences?

StreamInsight doesn’t really have anything to do with SQL Server. The coding is done in Visual Studio and the data doesn’t go through the SQL Server engine. SQL Server just happens to be a really expensive box – especially the Datacenter Edition, where StreamInsight is best used – and Microsoft needs to monetize this product. Presto: StreamInsight becomes part of SQL Server.

Database administrators can safely ignore this feature because they won’t be deploying it, managing it, backing it up, etc. CIOs, however, need to pay close attention to what features are bundled into what editions, because buying SQL Server is getting a little more complicated.

CIOs are Seeing Microsoft’s Poker Face

My poker face, let me show you it
My poker face, let me show you it

SQL Server 2008 R2 brings a new top-end SKU, Datacenter Edition. Microsoft breaks down features by edition, but here’s my simplified explanation:

Express Edition (Free) has:

  • Databases up to 10GB
  • Completely free

Standard Edition (roughly $6k/socket) adds:

  • No limits on database size
  • Backup compression

Enterprise Edition (roughly $30k/socket) adds:

  • Up to 8 CPU sockets
  • Management features for up to 25 instances
  • Virtualization rights for four guests – on a two-socket server, you can buy two sockets of SQL Server 2008 R2 Enterprise Edition, then run four virtual servers on there with SQL Server.
  • Data compression, online index rebuilds, and other engine features that help with databases over around 100GB

Datacenter Edition (roughly $60k/socket) adds:

  • Unlimited CPU sockets
  • Management features for over 25 instances
  • Unlimited virtualization rights – on a two-socket server, you can buy two sockets of SQL Server 2008 R2 Datacenter Edition, then run an unlimited number of virtual servers on there with SQL Server.

With R2, prices went up, and I’m totally okay with that. I’m not a Microsoft shill here – I think the market has changed, and Microsoft’s prices needed to reflect it. Virtualization is on fire. Microsoft has to price products not just for today, but for now through the next release. Intel’s packing more and more cores per chip, Cisco’s got blades that handle an insane amount of memory, and I routinely see virtualization hosts with more than 4 guests on them – usually several times that many. It’s not inconceivable that we’ll see hosts with more than 4 virtual SQL Servers on them soon as well, and Microsoft licensing revenues would plummet.

The price increase also sort of reflects the new tools like PowerPivot and StreamInsight – but only kinda. Some of the new features don’t have polish, and there’s a risk of them being one-and-done.

I Like It Rough: DAC Packs

Maybe not that rough.
Maybe not that rough.

The iTunes release of Fame Monster includes a bonus track, “I Like It Rough,” that depicts a relationship where both sides do a lot of giving and taking. It’s insanely catchy, but with the subject matter, I can see why it didn’t get widespread release.

DAC Packs are R2’s bonus track. I’ve written about DAC Packs before, and I believe they have an awesome long-term potential. The version shipped in R2 is very rough for all kinds of reasons – it does side-by-side deployments (copying your entire database just to change a single stored proc), it doesn’t support a lot of database features, there’s no tooling whatsoever for DBAs, and it does a worse job of just about everything than Data Dude did. (And nobody bought Data Dude, either.)

Microsoft says that DACs are a down payment on a bigger vision – and I do believe in that vision – but Microsoft has a pretty bad credit score. They make an awful lot of down payments, and then when the first monthly invoice comes in, they seem to have left their wallet in their other pants. Want to see a credit check?

  • SQL Server Notification Services
  • English Query
  • DTS (how do you like rewriting all those packages into SSIS? Good times, eh?)
  • HTTP SOAP access (I’d almost forgotten about that one, but it was even one of Microsoft’s Top 10 Features for DBAs in 2005. Quote: “…eliminating the need for a middle-tier listener, such as IIS, to access a Web services interface that is exposed by SQL Server 2005.”)
  • Vardecimal (yes, we actually had a one-and-done datatype)
  • Service Broker (yes, it still works great, but there haven’t been any investments here whatsoever)

Ouch. That’s a lot of things they bought on layaway, and then never paid for.

In my Top 10 Developer Mistakes That Won’t Scale presentation, my #1 thing to avoid is relying on v1.0 features. Sometimes Microsoft follows through and improves the feature over time, like SQL Server Integration Services. Sometimes, though, they forget their wallet and look the other way. When I’m making architectural decisions on where to invest programmer resources, I follow Microsoft’s own lead – if they didn’t care enough to invest the time it takes to do something right, then I won’t invest my own time in it either. Let’s see if they make the next payment first.

The old adage used to be, “I won’t deploy it until Service Pack 1, because there might be bugs.”

Now I tell my developers, “Don’t code against it until V2, because there might not be one.”

Like Fox Mulder, I want to believe, but Microsoft needs to pour a whole lot more work into DAC Packs – fast. Otherwise, server virtualization is going to bypass database virtualization as a better way to solve management problems.

SQL Server 2008 R2: Not a Bad Romance

If you’re into dance music, you couldn’t help but hear The Fame when it came out. If you liked it, you’re going to like The Fame Monster, but if you didn’t like it, The Fame Monster won’t change your mind about Lady Gaga. The Fame Monster is really just more of the same songs in the same style.

Most SQL Server professionals who liked SQL 2005 are going to pick up this latest release, throw it into their playlists, and keep right on working as if nothing changed – because for the most part, it hasn’t. SQL Server’s engine hasn’t really changed since 2005 came out. Unlike The Fame Monster, though, I think R2 has a chance of winning over people who weren’t fans of SQL Server before. Microsoft is extending its range with StreamInsight, PowerPivot, and Parallel Data Warehouse Edition, and these might bring in a new audience.

Previous Post
5 Things SQL Server Should Truncate
Next Post
I passed the MCM! iPads for everyone!

27 Comments. Leave new

  • Got to lova the GaGa 🙂

    As a B.I. pro I am loving the new release of SQL Server, the problems come when trying to persuade my Boss it is a good investment (currently putting together proposals for next years budget).

  • Pretty funny stuff, Brent. Microsoft actually did make some important performance improvements in the database engine that will help many DBAs out with no engineering work.


  • Hi Brent, The licensing for virtualization on Enterprise Edition is not exactly as you state. If you license in a server/CAL model, you indeed have rights to up to 4 virtual servers. In the per processor license model, you have rights for up to 4 virtual servers per licensed processor. The licensing guide spells this out (clear as mud): http://download.microsoft.com/download/2/7/0/270B6380-8B38-4268-8AD0-F480A139AB19/SQL2008R2_LicensingQuickReference-updated.pdf

    In your example of 2 licensed processors, you have rights for up to 8 virtual servers.

  • yeah, for BI, 2008 R2 is money. I’m excited.

  • Looks like your Paparazzi video link has been removed. Amusing theme for your SQL server review, though 🙂

    At the SQL 2008 R2 launch event, I asked them about the DAC pack “feature” that copies the whole database (based on your original blog post). Their response was that they don’t envision this feature will be used for the handful of extremely massive databases in your organization (where making a copy would be prohibitively expensive), but instead for the dozens/hundreds of smaller databases/applications.

    I actually have a bigger issue with the lack of transparency/visibility to the DBA of what kinds of changes the developer is trying to deploy via a DAC pack. We have a separation of duties in our organization where the DBA is required to review and execute scripts created by the developer, where the “packaged” format of the DAC pack is almost a disadvantage.

  • Excellent article!
    One note – Enterprise edition is limited to 8 CPUs and standard edition to 4 CPUs.


  • So if I married Lady Gaga, would I have to change my name to Lord Gaga?

    By the way, the PDW team doesn’t like it when we refer to PDW as an edition of SQL Server. It’s an appliance that uses SQL Server. It’s not part of SQL Server. At least that’s what they told us when we asked about it in pre-beta days.

  • Great review and for the most part I totally agree.

    I was surprised you didn’t mention Master Data Management which is not even close to be fully baked to the point of almost being a joke. I also agree with regards to Service Broker, the lack of attention/investment is unnerving as I’ve seen and been part of some real interesting and effective work with it.

    However I do have to disagree on 1 point though – I’ve been using Data Dude (VSDBPro) on many different projects since it’s release – Source Control, Schema and Data comparisons, Data Gen and Unit testing. Plus debugging with the app, even going into the triggers (although I still don’t like triggers), plus being part of the TFS workflow, bug tracking. It’s intention was not really focused on being DBA tool for migrations.

    When it was first released (2005) it was Team Suite only, however they moved it to the Pro SKU and starting with VS2010 it’s just part of the install, not to mention it’s now that magical v.3 version, I would expect to see more adoption.

    Course I also agree on your assessment of DACPacks. 🙂

    However with regards to R2, v.Next coming out next year and probably having CTP’s be out sometime this year, I would be surprised if there’s a lot of R2 adoption.

  • I am looking forward to R2, obviously less for the DB and more for the additional BI functionality. Nice post!

  • Backup Compression was the quick win maybe im asking for the moon on a stick but I would like to be able to adjust how aggresively backups are compressed. I was puzzled that this was released as an enterprise feature and glad to see it will be in std edition from R2.
    Great article nothing I have read so far would cause me to upgrade our existing 2008 servers to R2, but I have not read Glen Berry’s article yet!

  • Lady GaGa? Seriously? Aren’t you at least a little bit embarrased? I listen to her all the time but I don’t broadcast it to the whole SQL community…

  • You should review your facts on the number of processors supported, 4 for Stanadrd, 8 for Enterprise, >8 for Datacenter, they changed things with R2: http://download.microsoft.com/download/7/B/6/7B65BDD8-9D5E-42A5-A8AA-AD61FD8265E2/SQLServer2008R2EditionsDatasheet.pdf

    You might mention the memory limits for Standard and Enterprise as well. (64GB and 2TB)

  • Steve Hindmarsh
    May 14, 2010 4:21 am

    Got to say Brent this is probably the most entertaining\original blog post I’ve ever read on SQL Server – more of the same in future please !

  • Douglas Johnston ("DJ")
    May 17, 2010 10:26 am

    “She’s no Donald Farmer” … LOL; best SQL quote ever. 🙂

  • For SQL Server Express aren’t the limits:

    – 1 core
    – 1 GB of RAM
    – 4 GB database size

    Or did R2 really increase the database size (and if so, any increase in the cores or RAM)?

  • I’m not really a Lady Gaga fan, I was hoping MS would release a product more along the lines on Carmina Burana (big loud and spectacular). I guess I will have to wait for the next version… 🙂

  • When reading a review about SQL Server, I would have never imagined it compared to Lady Gaga. Thanks for the review!

  • Alin Winters
    June 10, 2010 6:23 am

    Great article. One comment…. According to http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx, Backup compression is only available in Enterprise Edition, not Standard.

  • Brent, if I had not met you at SQLBits7 before I read this article I would have thought you were totally mad. Now that I have met you…I know you are totally mad. (How’s the Snickers diet by the way…..:-))


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.