SQL Server 2008 upgrade poll results & thoughts

Last week I posted a poll asking DBAs when they would use SQL Server 2008 in production.  Here’s the results after one week:

• In the next 3-6 months. (40%, 8 Votes)
• Not planning it yet. (30%, 6 Votes)
• In the next month or two. (20%, 4 Votes)
• Already done. (10%, 2 Votes)

This week, I had the pleasure of going to a few meetings at Microsoft.  Roger Doherty gave us a presentation talking about why database administrators need to upgrade to SQL Server 2008.  He’s a member of the Developer & Platform Evangelists team, and he’s rightfully excited about all the cool stuff inside SQL 2008.

I played devil’s advocate – and when I say devil, I mean database administrator.

One of the slides was this spiffy graph that showed different approaches to upgrades: when you should do an upgrade-in-place, when you should use the Upgrade Advisor, and so on:

Apps that are simple and have a low strategic importance can just be upgraded in place, whereas on the opposite end of the spectrum, apps that are very complex and have a high strategic importance should have careful planning with code changes to take advantage of the new features.

Okay, I get it, that makes sense.

So how do we as DBAs gather the information to populate this chart?  How do we analyze all of the applications (not just servers, but the individual databases) to determine whether they’re simple or complex, and whether they have a high or low strategic importance?  Roger discussed some of the options in determining app complexity, and basically, we don’t have a good option right now.  The closest thing we have to an automated solution is simply gathering a trace of all queries that hit the database over the course of say, a week, and looking at the queries to see whether they’re using SQL Server’s advanced or deprecated features.  We can pass the profiler data to the Upgrade Advisor, but it won’t give us a measure of how complex the app is – it will only tell us whether the SQL Server upgrade will pass or fail.

During the presentation, I sent out a Twitter message asking my readers how many instances they support per DBA.  The answers I got back ranged around 20-70.  Whaddya know – that’s kinda like the number of instances shown on that slide.

So here’s my problem: that’s a beautiful line graph.  Makes perfect sense.  But now let’s look at how much time it will take to actually upgrade all 20-70 instances that a DBA has to manage:

• Gather a good inventory of servers & databases
• Find out from the app owner whether SQL 2008 is supported (either internally or by the vendor)
• Gather the information to populate the line chart
• Take the actions suggested by the line chart (Upgrade Advisor, plan code changes, etc)
• Arrange for an outage window with the application owners
• Perform the SQL 2008 upgrade
• Test the application post-upgrade

Holy moly.  If each of those took an average of four hours (and I know I’m seriously underestimating that) we’re still talking about 3 days per app.  Say your instances host just one app apiece (a truly laughable concept in the days of consolidation) and we’re talking about 60-210 business days to upgrade 20-70 instances!

Starts to make sense why Microsoft needs guys like Roger – you really do have to bang the drum to get users excited about doing that much work to upgrade.  It’s a tough battle.  I know when I was a DBA a few months ago, I was excited about 2008, but I knew there were few apps I could upgrade right away.  For the rest, the vast majority, I planned to wait until the application owners wanted to drive the upgrade process – so that way they could hassle with the meetings!