Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

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

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

  • Comments 6
  • Likes

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

  • 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

  • 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 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 new tools is not a business case (and now, data compression doesn;t save money if it doesn't work with blob data).

  • hi anon.

    Clearly not all databases are the same and not all mission critical applicatiosn are the same however all the stuff I put on this post does give business value to someone. For example I know of one major Uk government who upgraded to SQL Server 2008 for a mission critical application just on the basis of resource governor.

    As for compression you are absolutley right blob data won't compress that well, but then I would point you to vastly superior indexing and full text search in SQL Server 2008 plus filestream or remote binary storage  which make SQL server 2008 a far better backend for content management applications.

    Of course testting is essential to migrating any application and the resources I put in this post help you do that to the level of detail you need from checking the static code and objects in the database to using traces to pick up code in applications that may not work in SQL Server 2008 R2.  

    So I simply don't get the fun new tools comment,  I don't see encryption or policy based management as being fun, they are ther to ensure that mission critical systems are proiected and say in a compliant state.  Good use of these tools gives you time to have fun or plan your next project.  


  • 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.

  • It's simpler than that , but you'll want to test it...

    you upgrade the secondary failover to it and then upgrade the other instance (now the secondary , was the primary).  This is supported (there is an issue if you have test indexing on here and the good thing is you don't have to take the database/instances off line

    ping me offline ( is you have any further questions


  • 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


    SIS Director

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment