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