A successful upgrade to SQL Server 2008 R2/2012 should be smooth and trouble-free. To achieve that smooth transition, we must have to devote a plan sufficiently for the upgrade, and match the complexity of database application, otherwise, it risk costly and stressful errors and upgrade problems. Like all IT projects, planning for every Contingency/eventuality and then testing our plan gives us confidence that will succeed. Any ignorance may increase the chances of running into difficulties that can derail and delay upgrade.
Upgrade scenarios will be as complex as our underlying applications and instances of SQL Server. Some scenarios within environment might be simple, other scenarios complex. Start to plan by analyzing upgrade requirements, including reviewing upgrade strategies, understanding SQL Server hardware and software requirements for specific version, and discovering any blocking problems caused by backward-compatibility issues.
There may be two Upgrade Scenarios as In-Place Upgrade and Side by side upgrade.
In-Place Upgrade : By using an in-place upgrade strategy, the SQL Server 2008 R2 Setup program directly replaces an instance of SQL Server 2000 or SQL Server 2005 with a new instance of SQL Server 2008 R2 on the same x86 or x64 platform. This kind of upgrade is called “in-place” because the upgraded instance of SQL Server 2000 or SQL Server 2005 is actually replaced by the new instance of SQL Server 2008 R2. You do not have to copy database-related data from the older instance to SQL Server 2008 R2 because the old data files are automatically converted to the new format. When the process is complete, the old instance of SQL Server 2000 or SQL Server 2005 is removed from the server, with only the backups that you retained being able to restore it to its previous state.
Note: If you want to upgrade just one database from a legacy instance of SQL Server and not upgrade the other databases on the server, use the side-by-side upgrade method instead of the in-place method.
Side by side upgrade : In a side-by-side upgrade, instead of directly replacing the older instance of SQL Server, required database and component data is transferred from an instance of SQL Server 2000 or SQL Server 2005 to a separate instance of SQL Server 2008 R2. It is called a “side-by-side” method because the new instance of SQL Server 2008 R2 runs alongside the legacy instance of SQL Server 2000 or SQL Server 2005, on the same server or on a different server.
There are two important options when you use the side-by-side upgrade method:
- You can transfer data and components to an instance of SQL Server 2008 R2 that is located on a different physical server or on a different virtual machine, or
- You can transfer data and components to an instance of SQL Server 2008 R2 on the same physical server
Both options let you run the new instance of SQL Server 2008 R2 alongside the legacy instance of SQL Server 2000 or SQL Server 2005. Typically, after the upgraded instance is accepted and moved into production, you can remove the older instance.
A side-by-side upgrade to a new server offers the best of both worlds: You can take advantage of a new and potentially more powerful server and platform, but the legacy server remains as a fallback if you encounter a problem. This method could also potentially reduce an upgrade downtime by letting you have the new server and instances tested, up, and running without affecting a current server and its workloads. You can test and address hardware or software problems encountered in bringing the new server online, without downtime of the legacy system. Although you would have to find a way to export data out of the new system to go back to the old system, rolling back to the legacy system would still be less time-consuming than a full SQL Server reinstall and restoring the databases, which a failed in-place upgrade would require. The downside of a side-by-side upgrade is that increased manual interventions are required, so it might take more preparation time by an upgrade/operations team. However, the benefits of this degree of control can frequently be worth the additional effort.
Source: SQL Server 2008 Upgrade Technical Reference Guide
WikiDBA 