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.
Great Britain | English
Locations
Search
Back to listing
