Problem solve Get help with specific problems with your technologies, process and projects.

For your next SQL Server upgrade, rely on replication

SQL Server upgrades have a reputation for causing hassles. Learn how SQL Server replication can simplify the process of migrating from old version to new.

As Microsoft tweaks and polishes the next generation of SQL Server, now called SQL Server 2012, it's easy to forget there are any number of servers in back rooms and data centers still running everything from SQL Server 2005 all the way back to 2000. If this sounds familiar, it may be time for a SQL Server upgrade.

Many organizations slog away on those old versions because migrating from one version of SQL Server to another often is a pain in the neck. But there are many ways of migrating between database versions, and at least one greatly simplifies the process: replication.

The No. 1 reason to use replication is straightforward: You can leave your existing SQL Server installation in place and running during the migration process. Whether you're dealing with one physical server or two, install the new edition of SQL Server side by side, replicate the databases to bring both installations in sync and then transition to the new server. This minimizes the downtime for the old server -- critical if your organization has only one server.

There are several ways to do this. The most basic is to perform a backup-and-restore operation, coupled with one-way replication to make sure all the data and objects are consistent. This way, the majority of the data and the schema are copied in one go, and replication keeps the two copies consistent.

Schema changes during replication could lead to problems. For the most part, schema changes are migrated seamlessly, but there are specific things that do not migrate or generally cause trouble:

  • Identity columns cannot be altered or dropped; if they are, they will not replicate correctly.
  • Indexes cannot be added, altered or dropped. Indexes created implicitly for the sake of a constraint (such as a primary key) are handled automatically, though.
  • Constraints that are not explicitly named will have names automatically generated for them, potentially creating inconsistencies between the publisher and the subscriber.

There are other limitations as well. In short, steer away from making any significant changes to schemas or object designs while using replication to bring your old and new installations in sync.

For more on SQL Server upgrades

Get three tips for SQL Server upgrades

A new release on its way, Microsoft shares insight on SQL Server upgrades

Also note that depending on the hardware you're dealing with, the backup process itself may cause your SQL Server instance to respond more slowly than usual. As with any other major operation, the best way to minimize sluggishness is to perform the backup during an off-peak period. The impact of replication itself on system performance is negligible, but backups -- especially if they are written to the same physical spindle that holds your database -- can slow things down quite a bit.

One way to perform a backup followed by replication is to use an existing backup file of the original (publisher) database. Then initialize any subscriptions directly from that backup. That way, you don't have to restore it. In SQL Server 2008, this kind of migration can be done with the sp_addsubscription command and the @sync_type='initialize with backup' option.

It's a real timesaver: You don't have to ferret out the backup yourself and feed it to the new instance of SQL Server unless the backups are stored offline somewhere. Plus, if you have an automatic backup plan in place on the old server, you don't need to touch anything there; you can concentrate all your work on the new server.

However, it works only if both the original and target server can speak to each other and access the same repository of file shares or if they are on the same physical machine. Note that you may want to stop the distribution cleanup agent if the entire initial subscription process takes longer than the distribution cleanup agent's current retention period.

Finally, if you are migrating from a much older version of SQL Server (say, SQL Server 2000), which uses Data Transformation Services (DTS), you might want to migrate to SQL Server Integration Services (SSIS). DTS has been deprecated for some time, and SSIS yields better performance and is more flexible to boot. However, there's no one direct migration path for DTS, and replication alone won't help you here -- you'll have to look into one of the various methods Microsoft suggests for migrating DTS to SSIS.

About the author:
Serdar Yegulalp has been writing about computers and IT for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.

Dig Deeper on SQL Server Migration Strategies and Planning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.