Farewell old friend–time to say goodbye to SQL Server 2005

It always nice to see historic aircraft , and vintage cars out and about rather than stuck in museums, the noise and even the smell of castor oil add to this nostalgia. However keeping them going requires a lot of effort  and keeping them current with modern rules means that a Fokker triplane will need a proper seat harness, radio etc. and my mates Porsche 356 now runs on unleaded fuel. 

 

Keeping software current means patching and possibly bolting on add-ons which can affect performance and make management more of an issue and I would argue that you don’t get the same feeling of pride and a job well done from looking after old software.  Getting hold of the bits in bot scenarios can be tricky as manufacturers cease production.  With old cars and planes this can lead to small engineering firms recreating new parts and at the extreme complete replicas.  However I can’t see many people writing their own hot fixes and service patches!

 

I mention all of this because SQL Server 2005 is now coming to the end of its life.  The key event is the 2nd anniversary of the release of its successor SQL Server 2008 and this occurs on 12th April 2011 and the implications of this are:

• Paid support (charged on an hourly basis per incident). Customers will no longer receive no-charge incident support and warranty claims, and won’t be able to request design changes or features.
• Security updates support at no additional cost.
• Non-security related hotfix support will require a separate Extended Hotfix Support Agreement to be purchased within 90 days of the end of Mainstream Support – July 11th, 2011.

Full details of the support arrangements for SQL Server are here (you’ll need to click on the SQL Server 2005 tab)

What you decide to do about this is of course up to you. However while I can see the fun in maintaining and restoring an old car or plane I can’t see the justification for running databases on SQL Server 2005 unless:

• the database/application was itself upgraded from SQL Server 2000 and you are still running it in SQL Server 2000 compatibility mode
• You have a third party application that itself is only supported on SQL Server 2005. Actually I don’t see how this works, unless the application vendor has plans to purchase extended support.

You will at this point tell me you don’t have software assurance and you can’t justify the upgrade.  However there is so much extra stuff in SQL Server 2008 R2  that you can just turn on without upgrading:

• Policy based management to do for SQL Server what group policy does for desktop in active directory
• Resource governor to manage memory and cpu when an instance of SQL Server is under pressure and decide which application/ group of users wins
• compression of databases and backups
• database level encryption and sophisticated auditing to keep SQL Server current with the latest standards for governance, risk and compliance
• improvements to analysis services which can in some circumstances make your cubes run 10x faster with no design changes
• complete re-architecting of reporting services to improve reporting speeds and provide self service reporting with sophisticated charts, gauges and maps.

The upgrade from SQL Server 2005 to 2008 should be a straightforward process but it is still important to run the application/database through the upgrade advisor and there are a couple of other useful links here..

• TechNet Upgrading to SQL Server 2008 R2

• SQL Server 2008 R2 Upgrade Advisor

• SQL Server 2008 R2 Upgrade Guide

As ever I am interested in your upgrade stories and why you feel you can’t upgrade so ping me I have polo shirts with SQL Server 2008 R2 on even if you can’ put it on your server yet

Comments

• Anonymous
  March 16, 2011
  I just conducted an upgrade of our product from SQL 2005 to SQL 2008 R2.  The upgrade was fairly painless.  We did have an issue with a snapshot being created, but it was because the snapshot agent was using memory faster than SQL could give it back.  So we set a memory limit and all was well. However R2 was release before 2008 SP2 - I would think that SP2 being the newer product would be the one to migrate too.  Perhaps I'm mistaken?  I'd like one of those polo's in XL if you're handing them out. Gerald Collier Project Manager gcollier24@hotmail.com

• Anonymous
  March 18, 2011
  I love the way that you can't see why people are still on sql 2005. Not that it's not a sensible thing to put plans in place but it's neither easy nor risk free to migrate between version of databases (especially if you have a business critical app with 2TB of data..like wot the one I work on has). I know you are evangalising a product but the lack of insight into why sql is out there devaules your comments as it just sounds so out of touch with where we are at in the IT industry. We need to save our customers money..fun new tools is not a business case (and now, data compression doesn;t save money if it doesn't work with blob data).

• Anonymous
  March 21, 2011
  The comment has been removed

• Anonymous
  March 21, 2011
  We are actually considering upgrading for a while now, I must admit i have not looked into it yet but we are running multiple mirrored databases. How would one go about upgrading here? (again without researching) I'd say you'd have to disable mirroring. Upgrade the primairy server, upgrade the mirror server, create new backups, restore those and reenable the mirroring. Must admit at the moment that seems like alot of work, all of which isn't allowed to wrong.

• Anonymous
  March 21, 2011
  The comment has been removed

• Anonymous
  March 22, 2011
  Your article touched on a primary reason, SQL 2005 is the back-end for several of our mission critical applications, and those application vendors do not yet support SQL 2008.  I strongly suspect their apps would run fine on 2008, but I know for a fact that the first time I called them for help with their apps and they saw 2008 running they'd stop right there.  Losing App Vendor support that I call twice a week vs losing Micorsoft's support that I never call is a no brainer. Second reason is that phrase "Mission Critical".  My applications, data mining, reporting, SSIS packages, etc. all run just fine on SQL 2005.  I, and my staff, know how to get business done with the tools we've got.  The effort to migrate and train and relearn is considerable, with no business payback.  You list some new features of SQL 2008, but nobody in my organization is asking for them.  So Best Case is I invest a considerable amount of time and effort in a migration nobody values, worst case is I disrupt one of those applications or tools people are depending on.  If it's not broke, don't fix it is simplistic and unrealistic, but it has a strong element of truth in it as well. Third reason is opportunity cost.  If I apply the same time and effort that I will eventually have to spend on upgrading, to developing new tools and reports and fulfilling requests for data analysis I will have a whole portfolio of "product" to show my bosses.  Stuff they value vs. stuff they couldn't care less about.  Putting off upgrading for as long as I can maintain a reliable system that gives my bosses what they want is just smart. So, two things will eventually force me to upgrade.  New features or capabilities that we actually must have (to include those application vendors eventually dropping their support of SQL 2005), or a fear that my environment is becoming too old to support.  Neither is the case just because SQL 2008 is hitting an anniversary date. All that said, I could use a new tee-shirt J Mhoyt[at]Durango.k12.co.us SIS Director