29
SQL Server 2012 Upgrade Technical Guide
Consideration
In-Place Upgrade
Advantages
Side-by-Side Upgrade Advantages
Applications 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
(see Murphy’s Law). If a problem does occur,
then you will be able to roll back to the
legacy system.
Require minimal DBA
skill set expertise (or no
DBA available to
implement the
upgrade)
A junior DBA, system engineer,
or person with similar basic
knowledge and adherence to
good IT practices can
complete upgrades without
special scripts or manual
interventions. Setup
automates the upgrade
process.
System recovery: If a junior DBA or anybody
who is not familiar with the upgrade process
has any problems, the production
environment remains untouched. Only when
the new system is approved by Test/QA will
the users be migrated to the new server.
Require minimal user
downtime
For small data sets, the total
end-to-end time might be
smaller because this is the
most automated upgrade
strategy.
By controlling the steps directly, you can do
much of the preparation including much of
the data transfer without user downtime;
some user downtime is still required to
update the instance version.
Require fastest possible
revert/rollback in case
issue encountered
The legacy instance of SQL Server is still
present at the end of the upgrade and may
be used as a rollback option.
Schedule different
downtime windows for
different user
databases within the
same instance
Can separately control when each user
database is upgraded; after the last user
database is upgraded, the legacy instance
can be removed.
Preserve the server and
instance name
Preserves the same server and
instance name.
Server consolidation
project
The upgraded instance can be placed on the
consolidation server, after which the old
server can be taken out of service.
Applications required
to run in parallel with
the original and new
instances of SQL Server
Can maintain both systems with production
transactions until ready to turn off the
original system.
New server hardware or
operating system
The upgraded instance can be put on the
new server, after which the old server can be
taken out of service.
30
SQL Server 2012 Upgrade Technical Guide
Consideration
In-Place Upgrade
Advantages
Side-by-Side Upgrade Advantages
Shortage of disk space
in production
Because there is only one
resulting instance, there is less
additional data space required
than is possible with two
complete resulting instances
running side by side.
Legacy server cannot
meet the SQL Server
2012 Setup
requirements for
installation
None—not supported.
Only possible with a side-by-side upgrade to
a new server that meets the Setup
requirements.
Changing to a lower
edition of SQL Server
2012
None—not supported.
Only possible with a side-by-side upgrade.
Upgrade a 32-bit
version of SQL Server
2005/2008/2008 R2 to
a 64-bit version of SQL
Server 2012
None—not supported.
Only possible with a side-by-side upgrade.
Upgrade a 64-bit
version of SQL Server
2005/2008/2008 R2 to
a 32-bit version of SQL
Server 2012
None—not supported.
Only possible with a side-by-side upgrade.
Target instance is SQL
Server 2000 SP3a and
only one downtime
window is available
Transferring data to a new instance may be
faster than the three steps required:
Apply the required SQL Server 2000
SP.
Upgrade to SQL Server
2005/2008/2008 R2.
Upgrade to SQL Server 2012.
Upgrading from a
nonclustered legacy
instance of SQL Server
to a clustered instance
of SQL Server 2012
None—not supported.
Only possible with a side-by-side upgrade.
Upgrading from SQL
Server 7.0
None—not supported. First
upgrade to SQL Server 2005
and then to SQL Server 2012.
Can be done in one direct upgrade by using
manual data transfer.
Upgrading multiple
instances
Generally faster because data
transfer and configuration
steps are handled by Setup.
31
SQL Server 2012 Upgrade Technical Guide
Consideration
In-Place Upgrade
Advantages
Side-by-Side Upgrade Advantages
Upgrading very large
databases (VLDBs)
Setup converts existing data
files automatically; no data
transfer steps are required.
Can control the timing of several steps and
the rollback if it is necessary.
Upgrade testing
Retesting might be easier because the legacy
instance of SQL Server does not have to be
rebuilt.
Testing can be done while the legacy system
still supports production applications. SQL
Server Profiler can be used to capture SQL
commands against the legacy system and to
play back against the new instance of SQL
Server to verify that everything is working
well.
Data must be
transformed during the
upgrade window
Data transformation tools such as SSIS can
be used to transform data as it is being
transferred from the legacy instance of SQL
Server to SQL Server 2012.
Localization: change of
SQL Server language
Enables upgrade to SQL Server 2012 with a
different language from the legacy instance
of SQL Server. Note: This issue is not to be
confused with collation settings. This applies
to the localized language of the SQL Server
product.
Upgrading Notification
Services
None—not supported.
Only possible with side-by-side upgrade.
Requires Notification Services backward
compatibility add-in.
Application integration Simpler because the same
server name, instance name,
and database security settings
are preserved by Setup,
without manual intervention.
Applications can be moved from 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.
If a problem does occur, then you will be
able to roll back to the legacy system.
Server integration
Might be simpler because
linked server, replication, and
log shipping settings can be
preserved, depending on the
SQL Server version being
upgraded.