Skip to content

choose-a-database-engine-upgrade-method.md

Latest commit

 

History

History
124 lines (78 loc) · 15.2 KB

choose-a-database-engine-upgrade-method.md

File metadata and controls

124 lines (78 loc) · 15.2 KB
title Choose a database engine upgrade method | Microsoft Docs
ms.custom
ms.date 07/19/2017
ms.prod sql-non-specified
ms.prod_service database-engine
ms.service
ms.component install-windows
ms.reviewer
ms.suite sql
ms.technology
server-general
ms.tgt_pltfrm
ms.topic article
ms.assetid 5e57a427-2e88-4ef6-b142-4ccad97bcecc
caps.latest.revision 23
author MikeRayMSFT
ms.author mikeray
manager craigg

Choose a database engine upgrade method

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md-winonly]

There are several approaches to consider when you are planning to upgrade the [!INCLUDEssDE] from a prior release of SQL Server in order to minimize downtime and risk. You can perform an upgrade in-place, migrate to a new installation, or perform a rolling upgrade. The following diagram will help you to choose amongst these approaches. Each of the approaches in the diagram are also discussed below. To assist you with the decision points in the diagram, please also review Plan and Test the Database Engine Upgrade Plan.

Database Engine Upgrade Method Decision Tree

Download

Note

You may also consider upgrading the Azure SQL Database or virtualizing your SQL Server environment as part of your upgrade plan. These topics are out of scope for this topic, but here are some links:

Upgrade in-place

With this approach, the SQL Server setup program upgrades the existing [!INCLUDEssNoVersion] installation by replacing the existing [!INCLUDEssNoVersion] bits with the new [!INCLUDEssnoversion] bits and then upgrades each of the system and user databases. The upgrade in-place approach is easiest, requires some amount of downtime, takes longer to fallback if a fallback is necessary, and it is not supported for all scenarios. For more information on supported and unsupported upgrade in-place scenarios, see Supported Version and Edition Upgrades.

This approach is frequently used in the following scenarios:

  • A development environment without a high-availability (HA) configuration.

  • A non-mission critical production environment that can tolerate downtime and that is running on a recent hardware and software. The amount of downtime is dependent upon the size of your database and the speed of your I/O subsystem. Upgrading SQL Server 2014 when memory-optimized tables are in use will take some extra time. For more information, see Plan and Test the Database Engine Upgrade Plan.

Warning

When running the [!INCLUDEssNoVersion] setup program, the [!INCLUDEssNoVersion] instance is stopped and restarted as part of running the pre-upgrade checks.

Caution

When you upgrade [!INCLUDEssNoVersion], the previous [!INCLUDEssNoVersion] instance will be overwritten and will no longer exist on your computer. Before upgrading, back up [!INCLUDEssNoVersion] databases and other objects associated with the previous [!INCLUDEssNoVersion] instance.

The following diagram provides a high-level overview of the steps required for an in-place upgrade of the [!INCLUDEssDE].

Database Engine Upgrade Non-HA In-Place Upgrade

For detailed steps, see Upgrade SQL Server Using the Installation Wizard (Setup).

Migrate to a new installation

With this approach, you maintain the current environment while you build a new [!INCLUDEssNoVersion] environment, frequently on new hardware and with a new version of the operating system. After installing [!INCLUDEssNoVersion] in the new environment, you perform a number of steps to prepare the new environment so that you can migrate the existing user databases from the existing environment to the new environment and minimize downtime. These steps include migrating the following:

  • System objects: Some applications depend on information, entities, and/or objects that are outside of the scope of a single user database. Typically, an application has dependencies on the master and msdb databases, and also on the user database. Anything stored outside of a user database that is required for the correct functioning of that database must be made available on the destination server instance. For example, the logins for an application are stored as metadata in the master database, and they must be re-created on the destination server. If an application or database maintenance plan depends on SQL Server Agent jobs, whose metadata is stored in the msdb database, you must re-create those jobs on the destination server instance. Similarly, the metadata for a server-level trigger is stored in master.

When you move the database for an application to another server instance, you must re-create all the metadata of the dependant entities and objects in master and msdb on the destination server instance. For example, if a database application uses server-level triggers, just attaching or restoring the database on the new system is not enough. The database will not work as expected unless you manually re-create the metadata for those triggers in the master database. For detailed information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server)

  • Integration Services packages stored in MSDB: If you are storing packages in MSDB, you will need to either script out those packages using the dtutil Utility or redeploy them to the new server. Before using the packages on the new server, you will need to upgrade the packages to [!INCLUDEssNoVersion]. For more information, see Upgrade Integration Services Packages.

  • Reporting Services encryption keys: An important part of report server configuration is creating a backup copy of the symmetric key used for encrypting sensitive information. A backup copy of the key is required for many routine operations, and enables you to reuse an existing report server database in a new installation. For more information, see Back Up and Restore Reporting Services Encryption Keys and Upgrade and Migrate Reporting Services

Once the new [!INCLUDEssNoVersion] environment has the same system objects as the existing environment, you then migrate the user databases from the existing system to the [!INCLUDEssNoVersion] instance in a manner that will minimize downtime on the existing system. You accomplish the database migration either using backup and restore, or by repointing LUNs if you are in a SAN environment. The steps for both methods are delineated in the diagrams below.

Caution

The amount of downtime is dependent upon the size of your database and the speed of your I/O subsystem. Upgrading SQL Server 2014 when memory-optimized tables are in use will take some extra time. For more information, see Plan and Test the Database Engine Upgrade Plan.

After migrating the user database(s), you point new users to the new [!INCLUDEssNoVersion] instance using one of a variety of methods (e.g. renaming the server, using a DNS entry, modifying connection strings). The new installation approach reduces risk and downtime as compared to an in-place upgrade , and facilitates hardware and operating system upgrades in conjunction with the upgrade to [!INCLUDEssNoVersion].

Note

If you already have a high availability (HA) solution in place or some other multiple [!INCLUDEssNoVersion]instance environment, go Rolling upgrade. If you do not have a high availability solution in place, you can consider either temporarily configuring Database Mirroring to further minimize downtime to facilitate this upgrade or taking this opportunity to configure an Always On Availability Group as a permanent HA solution.

For example, you may use this approach to upgrade:

  • An installation of [!INCLUDEssNoVersion] on an unsupported operating system.

  • An x86 installation of SQL Server as [!INCLUDEss2016] and later do not support x86 installations.

  • [!INCLUDEssNoVersion] to new hardware and/or a new version of the operating system.

  • [!INCLUDEssNoVersion] in conjunction with server consolidation.

  • SQL Server 2005 as [!INCLUDEss2016] and later do not support the in-place upgrade of SQL Server 2005. For more information, see Are you upgrading from SQL Server 2005.

The steps required for a new installation upgrade vary slightly depending upon whether you are using attached storage or SAN storage.

  • Attached storage environment: If you have a [!INCLUDEssNoVersion] environment using attached storage, the following diagram and the links within the diagram to guide you through the steps required for a new installation upgrade of the [!INCLUDEssDE].

    New installation upgrade method using backup and restore for attached storage

  • SAN storage environment: If you have a [!INCLUDEssNoVersion] environment using SAN storage, the following diagram and the links within the diagram to guide you through the steps required for a new installation upgrade of the [!INCLUDEssDE].

    New installation upgrade method using detach and attach for SAN storage

Rolling upgrade

A rolling upgrade is required in SQL Server solution environments involving multiple [!INCLUDEssNoVersion] instances that must be upgraded in a certain order to maximize uptime, minimize risk, and preserve functionality. A rolling upgrade is essentially the upgrade of multiple [!INCLUDEssNoVersion] instances in a particular order, either performing an upgrade in-place on each existing [!INCLUDEssNoVersion]instance or performing a new installation upgrade to facilitate upgrading hardware and/or the operating system as part of the upgrade project. There are a number of scenarios in which you need to use the rolling upgrade approach. These are documented in the following topics:

Next steps

Plan and Test the Database Engine Upgrade Plan
Complete the Database Engine Upgrade