32
SQL Server 2012 Upgrade Technical Guide
Rolling Back an Upgrade
When you evaluate which upgrade strategy to use, consider the risk that an in-place
upgrade or side-by-side upgrade might have to be rolled back. The complexity and
effort required to roll back is an important factor in selecting which method to use.
Rolling back an in-place upgrade can be complex and time-consuming. The new data
file structures for SQL Server 2012 are incompatible with legacy instances of SQL Server
2005/2008/2008 R2. Because the new instance of SQL Server 2012 replaces the legacy
instance of SQL Server in an in-place upgrade, to roll back an upgraded instance, you
must uninstall the instance of SQL Server 2012, remove the data files and other
components, reinstall the legacy instance of SQL Server 2005/2008/2008 R2, and
restore the original data. Having a backup or image of the initial system might enable
you to shorten the time that is required to restore the original system on the server.
One option is copying the legacy data files from a backup location to the appropriate
disk volume, applying the ghost image to retrieve executables, and then applying any
scripts or components to complete the rebuild of the original system.
In a side-by-side upgrade, the new instance of SQL Server 2012 resides alongside the
legacy instance of SQL Server, either on the same server or on a different server.
Therefore, the legacy instance continues to be available for a rollback scenario.
However, after the upgraded instance of SQL Server 2012 goes into production and
starts capturing new data, there will come a point in time when enough new data has
been captured that a rollback is no longer realistic. For an in-place upgrade, if you
encounter problems after the system is in production, making adjustments or updates
to the new application would be a better option than trying a rollback. For a side-by-
side upgrade, you could use SSIS to transfer new data from the instance of SQL Server
2012 to the legacy instance of SQL Server 2005/2008/2008 R2 to bring it up-to-date.
However, this might be a difficult process, depending on the complexity of the data.
The complexity and expense of a rollback reinforces the importance of testing an
upgrade process beforehand. See the "Test the Upgrade Plan" section later in this
chapter for information about how to test an upgrade.
Considerations for Choosing an Upgrade Strategy
The upgrade method that is available for your specific needs depends on many factors,
including the components that you want to upgrade and the editions you want to use.
33
SQL Server 2012 Upgrade Technical Guide
Components. A certain upgrade strategy might not be possible because the
component does not support it. For example, there is no in-place upgrade for
SSIS from SQL Server 2000. For more information, see Chapter 17, "Integration
Services.” Also, we recommend that you transfer most SSAS components if the
source is SQL Server 2000. For more information, see Chapter 16, "Analysis
Services.”
Editions. The in-place upgrade strategy does not
support all paths between
editions. For example, to upgrade a SQL Server 2005 Enterprise instance to SQL
Server 2012 Standard Edition, you must perform a side-by-side upgrade because
Setup does not support an in-place upgrade path. See "Allowable Upgrade
Paths" later in this chapter for more information.
Partial upgrading. To transition only a few databases on a server to SQL Server
2012 and leave the rest on the legacy version, you must use a side-by-side
upgrade.
Upgrading over time. To transition databases gradually, several databases at a
time, from a legacy instance to SQL Server 2012, you can only use a side-by-side
upgrade.
Effect on applications. If your organization requires minimal disturbance to the
existing applications and users, choose an in-place upgrade if you can.
Availability. Both an in-place upgrade and a side-by-side upgrade require that
the databases be unavailable for some time. The downtime required depends
primarily on the size of the data sets. At first, it might seem that an in-place
upgrade would be faster than a side-by-side upgrade because the data is not
transferred from one server to another. However, an in-place upgrade also
requires time for the installation of SQL Server 2012. In a side-by-side upgrade,
SQL Server 2012 is already installed on another instance. If the data transfer
proceeds quickly and few changes are needed on the new instance, a side-by-
side upgrade might be faster than an in-place upgrade.
Rollback. For many database systems in production, it is impossible to justify a
change without a rollback strategy, in case the results are unacceptable. The
side-by-side upgrade strategy supports rollback at the time of acceptance
testing because the legacy instance can still be made available. However, after
users update the databases in the new instance, rollback might no longer be
workable.