Plan ahead for a smooth SQL Server migration
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
This was first published in February 2012
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.
- 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.
- 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.
- 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.
ABOUT THE AUTHOR
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.”
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation