Reducing SQL Server 2000 to SQL Server 2014 DB Migration Outages

Say you’re making a big jump and upgrading a database from SQL Server 2000 to SQL Server 2014. Of course, it only makes sense that this will be a side-by-side upgrade and therefore you’ll need a plan to migrate your DB from the SQL 2000 box to the SQL 2014 box. SQL 2014 won’t allow a restore or attach of a SQL 2000 DB. SQL 2014 only allows restore or attach of SQL 2008 DBs and later. So we need a 2-hop migration plan. Here’s a “you get the general idea” sketch of the plan:

1. Disconnect DB connections – outage begins
2. Backup the SQL 2000 DB
3. Copy the backup to the SQL 2008 server
4. Hop 1- restore the DB to the SQL 2008 instance
5. Backup the SQL 2008 DB
6. Copy the backup to the SQL 2014 server
7. Hop 2 – restore the DB to the SQL 2014 instance
8. Redirect DB connections & etc. – outage ends

The good news for our 2-hop plan is the intermediate SQL 2008 instance need not be production caliber any way – it’s a just a temporary stop-over on the way to update the DB so we can restore it to SQL 2014. The bad news is, this 2-hop plan complicates the migration plan and extends the potential outage as we copy\paste database backups or data files over the network between the three servers.

But there is a way to simplify the plan and minimize the outage. Let’s try this:

1. Disconnect DB connections – outage begins
2. Detach the SQL 2000 DB
3. Copy the database files to the SQL 2014 server
4. Hop 1- attach the DB to the SQL 2008 instance
5. Detach the SQL 2008 DB
6. Hop 2 – attach the DB to the SQL 2014 instance
7. Redirect DB connections & etc. – outage ends

We’re using detach/attach here rather than backup/restore but that’s not too important – things would work out fine either way. The thing to notice is that we lost a step and, more importantly, gained back the time it takes to get to step 6 – copying the backup to the SQL 2014 server. We only need one copy operation – from the SQL 2000 server direct to the target SQL 2014 server. Depending on DB size, network speeds and copy methods, that might be a lot of time saved.

How can this work? Because SQL 2008r2 (and SQL 2008 with -T1807) allows the database file path to be a UNC – i.e. in this case a file share to the DB files on the SQL 2014 server.
So, the hop 1 (step 6 above) might look something like this:

USE master;

 

GO

 

CREATE DATABASE MyAdventureWorks

 

ON (FILENAME = '\\SQLServer2014\MySQLServer\AdventureWorks2012_Data.mdf'),

 

(FILENAME = '\\SQLServer2014\MySQLServer\AdventureWorks2012_Log.ldf')

 

FOR ATTACH;

 

GO

Read up on it here: Description of support for network database files in SQL Server.

This sketch is skimming over quite a few other considerations so be sure think through all the other migration do’s and dont’s. And don’t forget to clean up that file share once the migration is complete. Happy (migration) trails to you!

This post was originally published on Andy McDermid’s blog: http://andrewmcdermid.net/reducing-sql2000-to-sql2014-db-migration-outages/

Additional resource: Upgrading to SQL Server 2012 and Beyond 

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.