Upgrading to SQL Server 2005: Six migration tips

1. Get to know SQL Server 2005 first. Don't make the actual migration process your first hands-on experience with SQL Server 2005. Many things have changed. For instance, Microsoft did away with the Query Analyzer, and instead of the MMC-style Enterprise Manager, there's now the Management Studio. If you don't want to install a full instance of SQL Server 2005, you can download a free virtual machine implementation of a SQL Server 2005 instance or pick up a free copy of SQL Server 2005 Express Edition, whose biggest limitation is any single database that's larger than 4 GB. Otherwise, functionally, they are very similar. One way or another, get used to what you'll be working with long before you actually have to get your hands dirty with the real thing.

2. Be mindful of your last known good backups before migrating. It seems terribly obvious, doesn't it? Yet, many people don't make a manual backup of their database schema and data before attempting an upgrade, and instead rely on whatever they have from the last automatic backup. I say "last known good backup" specifically because there are many people who take for granted that the last backup set they created works fine -- without actually testing that hypothesis. This warning goes double if you're using a backup set to restore data on the new instance of SQL Server, and it goes hand in hand with the next item on this list.

3. Make an effort to migrate in parallel. Instead of backing up one SQL Server, decommissioning it and installing another, try very hard to have both running side by side, either on different machines or the same machine. Multiple instances of SQL Server can coexist on the same computer if you need them to. You might have to set the memory requirements fairly low for one of them.

   If you're just getting started with SQL Server 2005 and don't actually have it in production yet -- but the point here is not to have both of them running full-bore -- just find a way to get them to coexist long enough to move the data over. One of the nice things about SQL Server is that it's designed to be installed in multiple instances on the same machine, so all you need to do is make sure each instance is named separately during the setup process. Also, a parallel migration means you can transfer data either by restoring a backup made by one instance of the server or by doing a server-to-server transfer. Migrating in parallel makes it that much easier to roll back the migration if things don't work out.

4. Pay attention to the advice from the Upgrade Advisor. Grab a copy of the Microsoft SQL Server 2005 Upgrade Advisor. Run it against your current SQL Server 7.0 or 2000 installations and think about the advice it gives you. It's not a big download, and you can run it totally non-destructively -- all it does is analyze your existing setup. SQL Server 2005 site expert Adam Machanic has written his own article about it: SQL Server 2005 Upgrade Adviser reduces unknowns. One of the best things about this tool is that it gives you both problems and solutions. You'd want to know if there's a "time bomb" issue sitting around in the database while you're planning to upgrade.

   As a side note, consider running the Best Practices Analyzer for SQL Server 2005 once you have the new server running. As of this writing, though, the BPA for 2005 is still only in a Community Technology Preview (read: beta) form, so any advice you get from it should be considered tentative until the finished version is out.

5. Take the opportunity to re-optimize your SQL Server setup. If you're migrating to a whole new edition of SQL Server and you're planning for some downtime anyway, don't just move everything as-is. Instead, see if you can take advantage of the moment to implement

   More on upgrading to SQL Server 2005: SQL Server 2005 Upgrade Adviser reduces unknowns SQL Server 2005 performance tuning tools A-Z SQL Server 2000 to 2005 upgrade checklist

   optimizations that might help. For example, if you've commissioned a new machine for SQL Server 2005 with more physical spindles than your previous machine, work out a new strategy for redistributing data tables and logs across those spindles. In addition, think about how new hardware (more and faster processors, etc.) will affect things like the MAXDOP setting. You might not have had to think about such things before because they simply weren't an issue, but what about now?

6. Test and test again. It's too easy to quit early when you're trying to verify that your newly-migrated database works. Don't take any behavior for granted -- make sure everything works as intended in as many different scenarios as possible. If you have an automated testing suite that you used with SQL Server 2000, dig it out again. This is especially important if you're migrating away from features in SQL Server 2000 that Microsoft doesn't support anymore because you want to find out if you transitioned away from them successfully.

ABOUT THE AUTHOR:

Serdar Yegulalp has been writing about Windows and related technologies for more than 10 years and is a regular contributor to various sections of TechTarget as well as other publications. He hosts the Web site WindowsInsider.com, where he posts regularly about Windows and has an ongoing feature guide to Vista for emigrants from Windows XP. Copyright 2007 TechTarget

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. Digg This!     StumbleUpon     Del.icio.us    RELATED CONTENT SQL Server 2005 (Yukon) How to use rank function in SQL Server 2005 Manage traces in SQL Server 2005 Analysis Services with XMLA commands Tutorial: SQL Server 2005 Analysis Services Create a computed column in SQL Server using XML data Open SSIS packages without validation using these SQL properties Using the OUTPUT clause for practical SQL Server applications Create DDL table in SQL Server 2005 to audit DDL trigger activity How to process SQL Server 2005 Analysis Services for data availability Configure SQL Server Service Broker for sending stored procedure data SQL Server 2005 log shipping setup using the wizard SQL Server 2005 (Yukon) Research SQL Server installation Virtual database storage for SQL Server: Friend or foe? Tutorial: Migrating to SANs from local SQL Server disk storage How to restore SQL Server database to transition server during upgrade Storage area network (SAN) basics every SQL Server DBA must know Tips for moving from SQL Server local disk storage to SANs SQL Server 2005 log shipping setup using the wizard SQL Server tools don't appear in menu after SQL Server 2005 install Troubleshoot SQL Server 2005 SP2 installation error Configuring SQL Server memory settings Optimize SAN setup for improved SQL Server performance SQL Server installation Research Microsoft SQL Server Upgrade live applications to SQL Server 2005 for high availability How to use rank function in SQL Server 2005 SQL Server high availability when upgrading to SQL Server 2005 Secure SQL Server from SQL injection attacks Create a computed column in SQL Server using XML data SQL Server memory configurations for procedure cache and buffer cache How insiders hack SQL databases with free tools and a little luck Upgrade Active/Active cluster to SQL Server 2005 and Windows 2003 Using the OUTPUT clause for practical SQL Server applications Tips for moving from SQL Server local disk storage to SANs RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary application server  (SearchSQLServer.com) Yukon  (SearchSQLServer.com) RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary 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.