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.
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 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
- 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
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:
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.
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.
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.
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.
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
- Learning Guide: SQL Server 2005 Learning Guide
- Step-by-Step Guide: Choosing your SQL Server 2005 migration tactic
- Topic: SQL Server 2005 tips and resources