How to keep end users happy during migration to SQL Server 2008
Written by Sadeesh Poovalur, Senior Microsoft Premier Field Engineer, based in Canada.
To achieve a smooth and seamless transition when upgrading versions of your SQL Server database(s), appropriate planning is required or you risk having unhappy users and/or malfunctioning database applications.
There are two fundamental upgrade methods: an in-place upgrade and a side-by-side upgrade. The main distinction between these two methods is that in-place upgrade replaces the old database instance, so that only one working instance remains.
The upgrade method you choose depends on numerous factors, as follows:
- Have you considered all the SQL Server Components? In general, backwards compatibility is retained for older versions of SQL Server when upgrading, but it’s possible that certain components of SQL Server have been discontinued. Thus, a given upgrade strategy may not be possible because particular components don’t support it.
- Do the versions of SQL Server you’re migrating from/to allow for in-place upgrades? The in-place upgrade strategy does not support all paths between versions and editions. For example, you can’t upgrade a SQL Server 2000 Enterprise Edition instance to SQL Server 2008 Standard Edition.
- Are you performing a partial database upgrade? To upgrade only a few databases to SQL Server 2008 and leave the rest on the legacy version, you must use the side-by-side method.
- Are you upgrading over time? To upgrade databases gradually, a few databases at a time, from a legacy instance to SQL Server 2008, you can only use the side-by-side method.
- What’s the impact to your database applications? If minimal disturbance to the existing applications and users is required, you may want to choose an in-place upgrade if possible.
- When do your applications need to be available? There is going to a application downtime for both in-place and a side-by-side upgrades. The amount of downtime required depends primarily on the size of the data sets, and should be taken into consideration when performing any upgrade. For in-place upgrades there is no data transfer, so the majority of application downtime will be associated with the duration of the SQL Server installation process. For side-by-side upgrades where SQL Server 2008 is already installed on another instance, if the data transfer proceeds quickly and few changes are needed on the new instance, then a side-by-side upgrade might be faster than an in-place upgrade.
- Which method has faster rollback? If the legacy SQL Server instance is replaced by a new SQL Server 2008 instance, rolling back an in-place upgrade can be complex and time-consuming, whereas in a side-by-side upgrade the legacy instance remains available if a rollback is needed.
Test your upgrade with appropriate thoroughness and you will become justifiably confident that you will succeed.
Additional References: