SQL Server 2000 to 2005 upgrade checklist

SQL Server 2000 to 2005 upgrade checklist

 

The key to any successful project is having a successful plan. In most cases, business-critical applications and databases will migrate to SQL Server 2005, so put together a solid approach to get from point A to point B.

The second part of this two-part checklist below -- planning your upgrade to SQL Server 2005 -- will provide you with key considerations for making the move from SQL Server 2000. The first part offers educational tools and advice.

Select features

As with anything new, you must familiarize yourself with features and options available so you can properly decide what to use now, what to think

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.


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

This was first published in May 2006

about later and what you will probably never use. There are some handy features in the Enterprise Edition not available in the other editions. These new features may present a great benefit and help influence your decision about which components to use in your environment.

The following is a short list of enhancements and new features in SQL Server 2005. You should investigate each area and classify the feature as a now, future or never item.

Development and business intelligence features

    • T-SQL enhancements
    • XML enhancements
    • SSIS enhancements (formerly DTS)
    • SQL Server Management Objects (SMO)
    • Common Language Runtime (CLR)
    • Service Broker
    • Multiple Active Result Sets (MARS)
    • Common Table Expressions (CTEs)
    • Data partitioning (Enterprise Edition only)
    • Try Catch functionality
    • Reporting Services enhancements
    • Notification Services

Management features

    • Backup enhancements
    • Online-maintenance operations
    • Management-GUI enhancements
    • Security enhancements
    • Increased memory usage (Standard Edition only)
    • Full Text enhancements
    • Better e-mail handling
    • Full 64-bit support
    • Database mirroring
    • Data encryption
    • Database Tuning Advisor
    • Database snapshots (Enterprise Edition only)
    • Dedicated administrator connection

Select versions

Based on which features you find most beneficial in the list, you will be able to decide which version of SQL Server to adopt. With SQL Server 2005, there are more choices than before so get familiar with each version's features and select a version based on your needs. Here are the choices:

    • Enterprise
    • Standard
    • Workgroup
    • Express
    • Developer
    • Mobile

Select licensing model

In the past, many organizations went with the per-seat licensing model for SQL Server, based on client-server application use. Now companies are shifting toward more Web-based applications. Determine if time warrants switching your licensing model to see if there are any cost savings.

Consolidate

Is now the time to consolidate your servers? Take a look at the new e-book by Hilary Cotter, which goes into the benefits and the how-tos of consolidating your servers. As server power increases, there may be some cost savings in building consolidation into your plan.

Choose 32-bit or 64-bit

SQL Server 2005 offers all the functionality of both 32-bit and 64-bit versions. If the time is right to upgrade your hardware along with SQL Server, Consider migrating to the 64-bit platform. Such a move is inevitable; people have just been waiting to see what everyone else will do. I think the time is right to take this step as well.

Monitor

Consider monitoring your SQL Server environment and introducing methodologies that will pull new SQL Server 2005 servers into the mix. This may include making sure servers are running, monitoring failed jobs, checking disk and database space, watching blocking and deadlocking and so on. Most third-party monitoring tools now have components available for SQL Server 2005. If you have a homegrown approach, you must look at what needs to change.

Find a candidate database or application

Before you begin to roll out SQL Server 2005 to all of your servers, pick a database or application that is regularly used but not mission critical. This way you get your feet wet with an actual migration, but it won't ruin your business if a major problem occurs. Also, find a business group to support your efforts and be patient with difficulties you may encounter.

Create checklists

As you go through the mock upgrade process, develop checklists and a process that you can repeat over and over again. The initial planning and investigation stages will probably be unique, but when it comes time to do the move, many of the steps will be standard. Save yourself some aggravation and write down what works and change what doesn't work.

Develop a rollback plan

Have a contingency plan in place in case your upgrade doesn't go as anticipated. You will probably be migrating to a different server, so the simple approach is to just point your user base back to the old server. If the problem is more complex, you must have a plan and a process in place prior to moving your databases.

Develop a project plan

Although the upgrade seems trivial to some people -- because it could be as simple as detaching and reattaching your database to the new server -- you should still have a project plan in place along with your checklists, particularly if you have more than one server to upgrade.

Summary

As you can see, there are many things to do before moving your first database. Take time to educate yourself and the people who will be affected by the upgrade. Make the correct decisions up front and you will have a much smoother migration in the end.

For the first part, go to:
Checklist: Learning about SQL Server 2005

About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.

More information from SearchSQLServer.com

 

Back to top