28
As I continue to explore how 2008 will affect an application upgraded from 2000 and 2005, one question that I get almost consistently every week is “should I upgrade from 2000 to 2005 first or jump directly to 2008?”. Sure, both are supported and of course, there is no shortage of opinions and recommendations from Microsoft and 3rd parties.
One of the better 3rd party write-ups on the subject is from Steve Jones who recommends users to skip 2005 and move straight to 2008. I’ve known Steve for a while though we typically only catch up at conferences (TechEd, PASS, etc…). Still, he’s been working with SQL Server a long time and knows what he’s talking about so it’s worth taking a peek at his article on SQL Server Central.
Of course, Microsoft too has a couple of papers on the subject that are worth reviewing. While they may sound like a gentle nudge towards upgrade to 2005 first, some of the reasons are good ones. There’s the original “Why upgrade to SQL Server 2005” paper which is ok if you really are brand new to 2005. While quite dated, the info is still relevant. The newer “SQL Server 2005 Upgrade FAQ” has the latest and greatest information including a short section on the benefits of moving to 2005 first then 2008 later. It’s a good start but IMHO, doesn’t provide enough to help the average user decide.
I really don’t believe there is a hard rule for this; it really depends on each individual deployment (yes, deployment not company). Here are a few important points to consider when trying to decide whether to first upgrade to 2005 then on to 2008 or do both in 1 step. I’ve laid them in in pros/cons format to make it easier to get most folks started but the right approach really depends on your specific deployment and what’s important to you.
| Path | Pros | Cons |
| Direct to 2008 | One time effort | Major changes for both DBAs and developers |
| One downtime window | Deprecated features now removed; more intensive testing required | |
| Latest capabilities and security | ||
| Intermediate 2005 upgrade then 2008 | Smaller skills delta for DBAs and developers | 50-80% repeated effort (process is reused but testing effort doesn’t change) |
| Window for stabilization, tuning and removing legacy code | Two downtime windows | |
| Faster upgrade to “supported” version | Lacks latest capabilities until second upgrade cycle (may be > 1 year later) | |
| Potential need for “workaround code” for certain capabilities |
Note the the lists is not ordered or weighted in anyway. They are just intended to get folks thinking about some of the issues involved. For example, testing efforts for a 1-step upgrade might be significantly more involved than a 2-step approach. This is because there are (a small number of) deprecated features that are fully de-supported/removed from SQL Server 2008. If you had taken a 2-step approach, you would have some time to setup alerts (use of unsupported features/command will fire an event create an entry in the SQL Server log) and phase them out gradually. With the 1-step upgrade, you’d have to hunt down every line of code including dynamic SQL to be analyzed by Upgrade Advisor or run through the Application Compatibility Testing tool.
Not purely an IT decision.
Sure, the DBAs and application admins are intimately involved since they need to handle the testing and deployment. However, there’s a fair bit of learning involved especially if you want to take advantage of Policy Based Management (aka DMF) so there’s the first involvement from the business side - training budgets. Still, the most important points, IMHO, from a business perspective are the downtime window and regulatory compliance. I’ve worked with some customers where extended downtime is schedule up to 2 years in advanced. This doesn’t leave much room for doing a lot of upgrade operations so you might have to just bite the bullet and make one big jump. On the other hand, if there are specific regulations that require you to run only products that have mainstream support, you’ll likely have to get to 2005 asap (2008 will not be available till summer).
The SP1 rule
One important point I’d like to make is about SP1. I know of a lot of companies that have this rule - no RTM deployments, wait for SP1. I used to subscribe to this rule also, a very long time ago when I helped manage some Oracle DBs and in my early SQL Server days. As of 7.0, this SP1 rule really made less sense with every release. My rationale is simple. Beta versions of SQL Server have been running in production environments for SAP customers and a bunch of Microsoft’s own internal applications (its own SAP and other business critical applications) since 7.0 and the practice continues today. If you know anything about SAP, you’d know running it in production on a beta database is no small feat regardless of how many modules you run. I don’t know of any other enterprise database that does this. So if multi-billion dollar companies can run their very complex business critical applications on beta versions of the product, I’m pretty comfortable running the released version.
If you still can’t decide after all this, do a POC. Download a copy of the AppCompat Tool, aka SSUA for SQL Server 2008 (currently slated for March release) and do your own application compatibility testing. That should give you a good idea of the effort it’ll take to upgrade your database/application which hopefully, puts you in a place to make the right decision on which path to take.
In short, there are no easy answers and your decision should not be based on features alone. Though the most common response to this question is “do you need the new features”, it is hardly the most important one. Most features can be worked around with some creative code or 3rd party applications. Downtime windows and regulatory compliance requirements are generally very inflexible. Of course, your environment might be such that neither of these apply so all you really care about are the new features. Like I said from the start, it depends on each individual deployment.
joe.
