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 ...
   

   To continue reading for free, register below or login

   Requires Membership to View

   To gain access to this and all member only content, please provide the following information:

   Email Address:
   
   
   

   By joining SearchSQLServer.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
   
   TechTarget cares about your privacy. Read our Privacy Policy
   To read more you must become a member of SearchSQLServer.com

   As an existing member of the TechTarget network please activate your SearchSQLServer.com account 

   By joining SearchSQLServer.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
   
   TechTarget cares about your privacy. Read our Privacy Policy
   
   
   

   Digg This!     StumbleUpon     Del.icio.us

   
   
   
   

   RELATED CONTENT Microsoft SQL Server 2005 End of life comes for SQL Server 2005 SP2, 2008 SQL Server Reporting Services Fast Guide SQL Server Service Broker Tutorial and Reference Guide Tips for tuning SQL Server 2005 to improve reporting performance SQL Server consolidation: Why it's an optimization technique Parent-child dimensions in SQL Server 2005 with Analysis Services MDX Enforcing data integrity in a SQL Server database SSIS error message due to installation problem on SQL Server 2005 Should you upgrade to SQL Server 2005 or SQL Server 2008? Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005 Microsoft SQL Server 2005 Research Microsoft SQL Server Installation SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Migrating down to Standard Edition What's new for installation with SQL Server 2008? Creating fault-tolerant SQL Server installations SQL Server consolidation: Why it's an optimization technique SSIS error message due to installation problem on SQL Server 2005 Get SQL Server log shipping functionality without Enterprise Edition Tutorial: Migrating to SANs from local SQL Server disk storage SQL Server tools don't appear in menu after SQL Server 2005 install Troubleshoot SQL Server 2005 SP2 installation error Microsoft SQL Server Installation Research Database Management and Administration Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V How to create SQL Server virtual appliances for Hyper-V Push vs. pull: Configuring SQL Server replication

   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

   
   
   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 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. 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.