In-Place Upgrade to SQL Server 2012

The in-place server upgrade is the easier but riskier of the two options. This is an all-or-nothing approach to upgrading; meaning that after you initiate the upgrade there is no simple rollback procedure. This type of upgrade has the added requirement of greater upfront testing to avoid using a complex back-out plan. The benefit of this approach is that you don’t need to worry about users and logins remaining in sync, and database connectivity changes are not be required for applications. In addition, SQL Server Agent jobs migrate during the upgrade process.

Following is a high-level scenario of an in-place upgrade.

1.   First, install the prerequisite files on your system. Before upgrading to SQL Server 2012, your server needs, at a minimum, the following:

• .NET Framework 4.0
• Windows PowerShell 2.0
• .NET 3.5 with Service Pack 1
• A current instance of SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2

2.   Next run the System Configuration Checker (SCC). The SCC examines the destination computer for conditions that would prevent an upgrade from completing, such as not meeting the minimum hardware or software requirements. If such a condition is found, setup aborts and the SQL Server 2012 components uninstall.

3.   Once verified, the SQL Server setup program can lay the 2012 bits and backward-compatibility support files on a disk while SQL Server 2008 (or 2005) is still available to users. However, don’t plan to upgrade a server while users are online. The setup program takes the server offline by stopping the existing SQL Server services. The 2012-based services assume control of the master database and the server identity. At this point, the SQL Server service takes over the databases and begins to update them while not allowing users back into the environment. When a request for data occurs in a database that has been only partially updated, the data associated with this request is updated, processed, and then returned to the user.

4.   Finally, kick off the uninstall procedure for the old binaries. This step occurs only if no remaining SQL Server 2005 or 2008 instances are on the server. SQL Server Agent jobs are now migrated.

Following are the advantages of an in-place upgrade:

• Fast, easy, and automated (best for small systems).
• No additional hardware required.
• Applications retain same instance name.
• Preserves SQL Server 2008 (or 2005) functionality automatically.

The disadvantages of an in-place upgrade are as follows:

• Downtime incurred because the entire SQL Server instance is offline during upgrade.
• No support for component-level upgrades.
• Complex rollback strategy.
• Backward-compatibility issues must be addressed for that SQL instance.
• In-place upgrade is not supported for all SQL Server components.
• Large databases require substantial rollback time.

Additionally, if you would like to change editions as a part of your upgrade, you must be aware of some limitations. You can upgrade SQL Server 2005 and 2008 Enterprise, Developer, Standard, and Workgroup editions to different editions of SQL Server 2012. However, SQL Server 2005 and 2008 Express Editions may only be upgraded to SQL Server 2012 Express Edition. If this is of interest to you, see SQL Server 2012 Books Online (BOL), “Version and Edition Upgrades,” under the section “Upgrading to SQL Server 2012.”

Alternatively, you can consider a side-by-side upgrade (read more here).