Manage Learn to apply best practices and optimize your operations.

Plan ahead for a smooth SQL Server migration

The thought of a SQL Server migration makes many organizations nervous, but they don’t have to be, writes consultant Don Jones. Get tips on making the switch simple.

Whenever a new release of SQL Server is rolled out, a startling number of companies running older versions say, “No, thanks.” Especially when SQL Server is supporting third-party line-of-business applications, it’s often easier and less risky to just leave the old version in place. Eventually, an upgrade will be necessary: The old version will go off support, the third-party vendor will release an update that requires a newer version or some other do-or-die situation will come up. When that happens, you may be put into the hot seat to manage the migration.

Don’t worry about it. In general, Microsoft makes SQL Server migration painless. In fact, in many cases, you can have different versions of SQL Server running side by side on the same machine.

There are three ways to handle your upgrade:

SQL Server Integration Services can be used to copy a database from one instance of SQL Server to another, whether the instances are running on the same machine or different ones. Everything about the database can be copied, although you’ll need to also ensure that server-level dependencies like specific login accounts are also migrated. With a fast network connection between the two instances, even huge databases can be easily moved over the course of a weekend maintenance window.

The old detach-and-attach method means you close the database on one server and detach it -- just right-click on it in SQL Server Management Studio -- copy it to the new location and attach it to the new SQL Server instance. Again, this won’t pick up server-level objects like logins, so you’ll need to manage those yourself.

An in-place upgrade is sometimes an option, although it’s a good idea to test this on a spare server and make sure your database backups are up to date.

No matter which technique you choose, there are a few gotchas to be aware of. If you can plan for these up front, you’re less likely to run into problems later.

  1. Make sure the source and destination SQL Server instances are configured the same with regard to code page and other server-level options. And, don’t forget about those server-level logins. Also check and make sure the new server has all of the same SQL Server components installed -- Analysis Services, Reporting Services and so on.
  2. Consider setting the migrated database’s “compatibility level” option; you can access this in the database’s options in Management Studio. The compatibility level makes a newer version of SQL Server behave like an older version for specific features that have changed.
  3. Always review the “readme” information on the new version -- not every modified feature is covered by the compatibility level. This is especially true for what I call “infrastructure features” like database mirroring and encryption. Generally speaking, the operation of these features should be transparent to your databases and applications, but it’s worth reviewing release notes to be sure.

The best thing to do is virtualize SQL Server. Use a physical-to-virtual tool to copy your old SQL Server into a virtual machine (VM); if it’s already running in one, clone it. Then perform a thorough upgrade or migration test with the new VM. If you mess something up, you’ll be forewarned and can always roll back the test VM and try again.

A SQL Server migration is one of the easier server migrations you’ll deal with. It’s typically more straightforward than migrating to new versions of Exchange Server or SharePoint Server, and each new version of SQL Server brings advantages in performance, manageability and features.

Don Jones is a co-founder of
Concentrated Technology LLC, the author of more than 30 books on IT and a speaker at technical conferences worldwide. Check out the archive page for Jones’ series, “SQL Server for the Reluctant DBA.”

Dig Deeper on Microsoft SQL Server Integration Services (SSIS)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.