SQL FAQ – Preparing For The Retirement Of Microsoft SQL Server 2005

Rate this item
(0 votes)

Mainstream support for Microsoft SQL Server 2005 ends in half a year – April 2016. This provides plenty of time for CTOs and DBAs to prepare a Microsoft SQL Server upgrade or migration plan, but what are the options open to them?

The platform

At this point in time, the smartest move would be to skip Microsoft SQL Server 2008 and 2012, and move straight on to SQL Server 2014. Choosing to use the latest version of the database engine will help to maximise the lifespan of the platform and lengthen the time between upgrades.

Your business will also be able to take advantage of new features, like full text search, that are not available in intermediate editions of Microsoft SQL Server.

Upgrade option #1 – In-place upgrade

Simultaneously the easiest, and potentially most risky option, an SQL Server in-place upgrade involves installing the new software, preferably SQL Server 2014, over the top of the existing system. The database engine is then upgraded, as are the tables and any other “moving parts”.

However, anything but the most basic of SQL Server environments (think single instance) is unlikely to be quite so straightforward. The in-place upgrade route is probably not the correct upgrade path for enterprise databases.

The other potential problem with in-place upgrades is the lack of simple rollback in the event of a problem. The database server(s) will need to be taken offline, whilst a full SQL Server recovery procedure is performed using the last full back up.

Upgrade option #2 – Side by side upgrade

The side by side upgrade process has SQL Server recovery provisions built in. The production system is left untouched whilst a full install of SQL Server 2014 is performed on a separate system. Once complete data is then exported from the SQL Server 2005 instance, imported into the new SQL Server 2014 instance, upgraded, cleaned and tested.

Only after the new server has been properly tested and confirmed as working does the final changeover take place. And because the two systems exist independently, switching back to the original system is relatively simple, significantly reducing failover times.

Obviously there are drawbacks to the side by side upgrade process too; creating a parallel database install means having sufficient infrastructure in place to host twice as much data as your current system holds. But for environments that demand high levels of availability and complete data integrity, the side by side approach to Microsoft SQL Server upgrades is the only sensible approach.

SQL Server recovery – the foundation of any upgrade

Before performing any kind of upgrade however, it is essential to have a proper SQL Server recovery plan in place to cover any contingencies. The side by side upgrade relies on having an up-to-date server backup in order to carry out the necessary import routines for instance.

The SQL Server Upgrade Advisor tool will suggest a number of actions that need to be taken to prepare your environment for Microsoft SQL Server 2014, one of which is ensuring you have a full backup of everything coupled with a proper SQL data recovery plan.

Post-upgrade actions

Because there are changes to the database engine itself, along with some of the structures within the Master DB, certain fragments of redundant SQL 2005 metadata will remain. Although harmless to general operations this redundant data can have a minimal performance impact and should be removed. Check out the Microsoft documentation for more details on using the DATA_PURITY statement to remove the extraneous column values left over from previous SQL versions from your new SQL Server 2014 instance(s):

DBCC CHECKDB WITH DATA_PURITY;

Of equal importance is the UPDATE STATISTICS command which should be executed on every single database. Often thought to be an “optional extra”, the UPDATE STATISTICS command is used to ensure that queries compile with up-to-date statistics. This may sound relatively unimportant, but failure to perform this post-upgrade task could see queries running much more slowly than anticipated, wasting time and effort trying to troubleshoot suspected performance bottlenecks.

Six months to go

With six months until Microsoft SQL Server 2005 is finally retired, businesses have plenty of time to decide which upgrade path is right for their needs, and to plan for the migration project. But your starting point has to be the Microsoft SQL Server 2014 Upgrade Advisor tool.

Reference: http://blog.krollontrack.co.uk/top-tips/sql-faq-preparing-for-the-retirement-of-microsoft-sql-server-2005/

Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

43 comments

  • Comment Link Ray Monday, 02 April 2018 11:28 posted by Ray

    Heavy duty sanding of hardwood floors.

  • Comment Link Richie Sunday, 04 March 2018 19:45 posted by Richie

    Hey outstanding blog! Does running a blog such as this require a large amount of work?
    I've no expertise in computer programming but I was
    hoping to start my own blog soon. Anyways, should you have any
    recommendations or tips for new blog owners please share.
    I understand this is off topic however I just had to ask.
    Kudos!

  • Comment Link Ira Friday, 02 March 2018 22:47 posted by Ira

    Hey I know this is off topic but I was wondering
    if you knew of any widgets I could add to my blog that
    automatically tweet my newest twitter updates. I've been looking for a plug-in like this
    for quite some time and was hoping maybe you would have some experience with something like this.
    Please let me know if you run into anything. I truly enjoy reading your blog
    and I look forward to your new updates.

  • Comment Link Olen Thursday, 01 March 2018 00:53 posted by Olen

    This piece of writing will help the internet viewers for setting up new weblog or even a weblog from start to end.

  • Comment Link Moises Saturday, 24 February 2018 12:45 posted by Moises

    Usually Towing companies are always in want.

  • Comment Link Cathryn Wednesday, 21 February 2018 08:56 posted by Cathryn

    This can enhance the tree removal cost substantially.

  • Comment Link Kourtney Wednesday, 21 February 2018 07:17 posted by Kourtney

    Asbestos is a cloth made from tiny fibers.

  • Comment Link Mose Tuesday, 20 February 2018 05:39 posted by Mose

    This can be a job for storage door professionals only.

  • Comment Link Freya Monday, 19 February 2018 22:20 posted by Freya

    Carpets - Maid providers will vacuum your carpets.

  • Comment Link Carmella Monday, 19 February 2018 12:45 posted by Carmella

    However other tree services may cost further.

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.