In this article, I am trying to show the two SQL Server Upgrade options and How to upgrade SQL Server 2000 to SQL Server 2012 using the appropriate option based on your situation by explaining the advantages for each option.
For an in-place upgrade, you cannot upgrade a SQL Server 2000 instance or database to SQL Server 2012 directly, you should upgrade the SQL Server 2000 instance to SQL Server 2005 SP 4 , SQL Server 2008 SP 2, or SQL Server 2008 R2 SP1. Then apply SQL Server 2012 Setup.
For a side-by-side upgrade, first restore the SQL Server 2000 databases to SQL Server 2005 SP4 , SQL Server 2008 SP 2, or SQL Server 2008 R2 SP 1 and then restore the resulting database to SQL Server 2012.
The overall advantages of an in-place upgrade include the following:
- An in-place upgrade can be easier and faster, especially for small systems,because data and configuration options do not have to be manually transferred to a new server.
- It is mostly an automated process.
- The resulting upgraded instance has the same name as the original.
- Applications continue to connect to the same instance name.
- No additional hardware is required because only the one instance is involved.
However additional disk is required by Setup .
- Because it is mostly automated, it takes the least deployment team resources.
The overall advantages of a side-by-side upgrade include the following:
- It gives more granular control over which database objects are upgraded.
- The legacy database server can run alongside the new server. You can perform test upgrades and research and resolve compatibility issues without disturbing the production system.
- The legacy database server remains available during the upgrade, although it cannot be updated for at least the time that is required to transfer data.
- Users can be moved from the legacy system in a staged manner instead of all at the same time. Even though your system might have passed all validation and acceptance tests, a problem could still occur. But if a problem does occur, you will be able to roll back to the legacy system.
Some overall disadvantages of an in-place upgrade include the following:
- You must upgrade the whole instance or a major SQL Server component. For example, you cannot directly upgrade a single database.
- You must inspect the whole instance for backward-compatibility issues and address any blocking issues before SQL Server 2012 Setup can continue.
- Upgrading in place is not recommended for all SQL Server components, such as some DTS packages.
- Because the new instance of SQL Server 2012 replaces the legacy instance, you cannot run the two instances side by side to compare them. Instead, you should use a test environment for comparisons.
- Rollback of upgraded data and the upgraded instance in an in-place upgrade can be complex and time-consuming.