Home > SQL, SQL2008 > It’s never easy is it?

It’s never easy is it?

We recently made a business decision that upgrading some of our business systems to current versions didn’t make much sense – they don’t really meet the needs of the business and the upgrade project would take almost as many resources as replacing them.  The replacement project has kicked off and it looks like we will have new systems in place within 18 months or so.

This is all well and good – migrations are always a more interesting project for me than upgrades, but it left me in a bit of a quandary about what to do with the SQL install that supports these systems.  I’m still running these databases on SQL 2005 (I know! I know!) and could really take advantage of page/row compression for some of them.  Of course, to do that I need to be running at least SQL 2008 so it is time to start planning and testing my upgrade paths.  After doing some research on what versions of SQL the applications supported with our current version of software I realized that as much as I wanted to I couldn’t upgrade to SQL 2008 R2, and instead was limited to SQL 2008.  It’s not great, but better than supporting  these systems on 2005 for another 18 months.

I have a big old honking test system that holds five named instances of SQL for test/dev/qa.  Because we were going to upgrade the software to current it was a mixed bag as far as my SQL versions.

My current reality looked like this:

Instance1 – 2005 sp2

Instance2 – 2005 sp2

Instance3 – 2005 sp2

Instance4 – 2008 R2

Instance5 – 2008 R2


I needed it to get to here:


Instance1 – 2008 sp3

Instance2 – 2008 sp3

Instance3 – 2008 R2

(Instances 4 and 5 could be removed – they were installed for the application upgrade, and not needed any more).

Easy-peasy right?  Not so much!

Initially I (naively) assumed that because I was dealing with named instances I could upgrade the 2005 instances to 2008 with ease even though I had a those two 2008R2 instances living out there.. I mean, I don’t care what version of SSMS is running, I just cared about the engine version for each instance.

That was my first poor assumption.  After attempting the upgrade and it failing for Instance2 I realized that I needed to remove the 2008 R2 bits from the server.

No problem I thought – I’ll remove 2008 R2 via the control panel since I don’t really need it currently and go on with my upgrades.  If only it were that easy!

I went round and round with this, and eventually wound having to use Aaron Bertrand’s guidance in this blog post to remove all of the 2008 bits completely.

Once that was complete I was able to perform in place upgrades to SQL 2008 SP3 for Instance2 and Instance3 and only once those were complete did I dare to attempt the upgrade on Instance1 to 2008 R2.

All in all I spent entirely too many hours on this, but it would have been even longer had Aaron not blogged about removing the eval bits of 2008R2 back in 2010.


For me it was just another reminder that you should ALWAYS test before attempting upgrades or changes in production.   Certainly not my favorite way to spend a Sunday afternoon, but it would have been much worse had it been in production and I had a steadily decreasing maintenance window.

Categories: SQL, SQL2008
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: