Select Page

Reducing SQL Server 2000 to SQL Server 2014 DB Migration Outages

Andy McDermid | | February 1, 2017

sql

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.

 

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS