When moving from one installation of SQL Server to another, whether it's the same iteration of SQL Server or a higher revision, it helps to do things in parallel. A parallel upgrade means installing the new database product side by side -- either on the same system or on a different one -- with your existing database, and moving or copying data between the two. This sort of operation seems easy enough, but there are a couple of caveats involved, especially when doing a parallel upgrade on the same system.
First, some context. One of the nicer features of SQL Server is that it supports multiple instances of the program on the same computer. This allows you to have one (or more) copies of, say, SQL Server 2000 running side by side. If you're upgrading to SQL Server 2005 or 2008 on the same box, you can install the newer version of SQL Server along with them and simply run it as a separate instance.
It's the exact process of migrating that can often be sticky, and there are three basic ways you can accomplish a SQL Server migration:
- Migrate by exporting directly from one SQL Server instance to another.
- Back up the database on one instance and restore it on the other.
- Detach the database from the original server instance and reattach it on the new one.
Each approach works best in specific scenarios. The first two tend to work best with a relatively small database (a couple of gigs or less). They also work well when you plan on moving the database to another storage device, especially if you're also
planning to repartition the database's physical file structure. Backup and restore is handy since it has the added side bonus of creating a backup copy before the migration that you can stash somewhere. For really big databases, however, it might not be practical. The third option, detach and reattach, is the best overall option. It works no matter what size database you're using. It's also the best way to work if you're hosting the data structures on an external storage device like a SAN, since the files don't have to be copied or recreated anywhere.
In each case, be aware of how parallel installations on the same machine will impact performance. Mainly, this manifests in two ways:
1) whether or not you're taking the original installation offline for the sake of the migration, and 2) if you're not, how disk operations for the migration are going to affect usability. On the whole, you're probably best off setting up a formal window of downtime and performing the migration in single-user mode (i.e., nobody's using the database except you) whenever possible.
If you're required to keep things running while performing this migration, detach and reattach are almost certainly going to be out of the picture. You'll most likely have to use direct migration or backup/restore. You'll get the best results if you're backing up to and restoring from a physical drive that does not actually host the database tables themselves. The process will unfold that much faster, and you won't interfere as much with existing database operations.
When you are moving between instances of SQL Server 2005 and higher, one option for the sp_detach_db stored procedure (which is used to perform the detach operation) that isn't available in earlier versions of SQL Server is the KeepFulltextIndexFile option. It preserves any full-text indexes that have already been created on that database. If you rely on full-text indexing and don't want to recreate those indexes from scratch -- which, again, reduces disk activity if you have other database instances running -- this method comes in handy.
Incidentally, the sp_attach_db stored procedure has been used in the past for reattaching a database, but it's being phased out. Get into the habit of using CREATE DATABASE with the FOR ATTACH clause instead. Among other things, sp_attach_db is limited by the number of physical files used by the database -- and I doubt that most production-level databases are all in one physical file by now. Be sure to make the character set, sort order and Unicode collation the same on both instances of SQL Server when you perform a detach/reattach operation. One final bit of cleanup you may need to perform when migrating is repairing any orphaned user entries in the database. But, as you'll see, this isn't too difficult a situation to deal with.
| ABOUT THE AUTHOR: |
|
Serdar Yegulalp has been writing about Windows and related technologies for more than 10 years and is a frequent contributor to various sections of TechTarget as well as other publications. He hosts the Web site Windows Insight, where he posts regularly about Windows and has an ongoing feature guide to Windows Vista for emigrants from Windows XP.
Copyright 2007 TechTarget
|
