Data Center

Tips To Upgrade to Microsoft SQL Server 2014

Follow this advice to move data from an existing server to a new one.

Twitter

Russell Smith is a technology consultant and trainer specializing in management and security of Microsoft server and client technologies. He is a Microsoft Certified Systems Engineer with more than 15 years of experience.

The main headache of any migration is figuring out how to get the data out of one server and into another. Here are some best practices for transferring data and tips for school districts that are about to start a migration to SQL Server 2014.

There a several options for migrating databases to new versions of SQL Server, but any district’s chosen route will depend on the version of SQL Server that it’s migrating from, and other factors, such as whether more than just a database needs to be moved.

In-place upgrades to SQL Server 2014 are supported from SQL Server 2012 SP1, 2008 R2 SP2, 2008 SP3, and SQL Server 2005, but are not necessarily the best option. Side-by-side migrations, where databases are copied to a new physical or virtual server, usually are preferred because they don’t require source database servers to be modified or taken offline, and a new server can be tested before going live.

Nevertheless, if a district decides to perform an in-place upgrade, it should check out the Upgrade Advisor tool that’s part of the SQL Server 2014 Feature Pack.

Using SQL's Copy Database Wizard

If the aim is to clone an old SQL Server to failover to the new one without making any changes to applications or the way users log in, the best course is to copy databases using the Copy Database Wizard. The Copy Database Wizard was introduced in SQL Server 2005 and offers the following options:

Transfers databases between different instances of SQL Server 2005 (and higher)
Transfers databases while they remain available online to users, with the SQL Server Management Objects (SMO) method
Transfers databases using the faster “detach and attach” method, but the databases are unavailable during the transfer process

The wizard can move all objects related to a database, such as keys and indexes, from one server instance to another. And unlike backup and restore (or SQL Server’s import/export feature), a district administrator can choose to copy external objects, like SQL Agent jobs and stored procedures, which are found in the master database.

Learn the Supported SQL Migration Paths

SQL Server 2005 SP4, 2008 SP2 and 2008 R2 SP1 databases can be migrated directly using the tool. A district migrating from SQL Server 2000 SP4 will need to migrate to SQL Server 2008 SP2 or 2008 R2 SP1 as a stepping stone to SQL Server 2014.

The service pack levels stated above are minimum requirements. Always install the latest available service pack for source and destination database servers. It’s important to note that the Copy Database Wizard can’t be used with the Express edition of SQL Server and does not support moving system databases.

Tool Prerequisites

Before using the wizard, make sure that the person doing the migration has the sysadmin server role on both the source and destination servers. To transfer databases using the detach and attach method, they also will need access to the file share that hosts the source database files. The wizard can be found by right clicking the database they want to copy in SQL Server Management Studio and selecting Tasks and then Copy Database from the menu.

Before-and-After Upgrade Checklist

Don’t forget to complete the following tasks as part of the migration plan:

Back up the databases, especially if performing an in-place upgrade.
Before running the Copy Database Wizard, decide on a transfer method.
Check access rights before using the Copy Database Wizard.
Execute dbcc checkdb with data_purity on the databases before starting the migration process to check for values that are not valid for column data type.
For optimal performance, run sp_updatestats against upgraded databases.
If performing an in-place upgrade, make sure to decide on a rollback plan.