Skip to content

Latest commit

 

History

History
102 lines (65 loc) · 9.97 KB

plan-and-test-the-database-engine-upgrade-plan.md

File metadata and controls

102 lines (65 loc) · 9.97 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic monikerRange
Plan and test the Database Engine upgrade plan
This article describes planning before you begin your SQL Server upgrade, including a planning checklist and developing and testing an upgrade plan.
rwestMSFT
randolphwest
05/23/2024
sql
install
upgrade-and-migration-article
>=sql-server-2016

Plan and test the Database Engine upgrade plan

[!INCLUDE SQL Server -Windows Only]

To perform a successful [!INCLUDE ssnoversion] upgrade, regardless of approach, appropriate planning is required.

Release notes and known upgrade issues

Before upgrading the [!INCLUDE ssDE], review:

Pre-upgrade planning checklist

Before upgrading the [!INCLUDE ssDE], review the following checklist and the associated articles. These articles apply to all upgrades, regardless of upgrade method and help you determine the most appropriate upgrade method: Rolling upgrade, new installation upgrade, or in-place upgrade. For example, you might not be able to perform an upgrade in-place or a rolling upgrade, if you upgrade the operating system, upgrading from [!INCLUDE ssversion2005-md], or upgrading from a 32-bit version of [!INCLUDE ssnoversion-md]. For a decision tree, see Choose a Database Engine Upgrade Method.

  • Hardware and software requirements: Review the hardware and software requirements to for installing [!INCLUDE ssNoVersion]. These requirements can be found at: Hardware and Software Requirements for Installing SQL Server. A part of any upgrade planning cycle is to consider upgrading hardware and the operating system. Newer hardware is faster, and can reduce licensing either due to fewer processors or due to database and server consolidation. These types of hardware and software changes affect the type of upgrade method you choose.

  • Current environment: Research your current environment to understand the [!INCLUDE ssNoVersion] components that are being used and the clients that connect to your environment.

  • Third-party components: Determine the compatibility of third-party components, such as integrated backup.

  • Target environment: Verify that your target environment meets the hardware and software requirements and that it can support the original system's requirements. For example, your upgrade might involve the consolidation of multiple [!INCLUDE ssnoversion-md] instances to a single, new [!INCLUDE ssnoversion] instance, or the virtualization of your [!INCLUDE ssNoVersion] environment to a private or public cloud.

  • Edition: Determine the appropriate edition of [!INCLUDE ssnoversion] for your upgrade and determine the valid upgrade paths for the upgrade. For detailed information, see Supported Version and Edition Upgrades. Before you upgrade from one edition of [!INCLUDE ssNoVersion] to another, verify that the functionality that you currently use is supported in the edition to which you upgrade.

    [!NOTE]
    When you upgrade [!INCLUDE ssnoversion] from a prior version of [!INCLUDE ssNoVersion] Enterprise edition, choose between Enterprise edition: Core-based Licensing and Enterprise edition. These Enterprise editions differ only with respect to the licensing modes. For more information, see Compute Capacity Limits by Edition of SQL Server.

  • Backward compatibility: Review the [!INCLUDE ssnoversion] database engine backward compatibility article to review changes in behavior between [!INCLUDE ssnoversion] and the [!INCLUDE ssNoVersion] version from which you upgrade. See SQL Server Database Engine Backward Compatibility.

  • Data Migration Assistant: Run the Data Migration Assistant to help diagnose issues that might either block the upgrade process or require modification to existing scripts or applications due to a breaking change.

    You can download the Data Migration Assistant here.

  • System configuration checker: Run the [!INCLUDE ssnoversion] System Configuration Checker (SCC) to determine if the [!INCLUDE ssnoversion-md] setup program detects any blocking issues before you schedule the upgrade. For more information, see Check Parameters for the System Configuration Checker.

  • Upgrading memory-optimized tables: When you upgrade a [!INCLUDE sssql14-md] instance containing memory-optimized tables to [!INCLUDE sssql16-md] and later versions, the upgrade process requires more time to convert the memory-optimized tables to the new on-disk format. During this process, the database is offline. The amount of time depends upon the size of the memory-optimized tables and the speed of the I/O subsystem. The upgrade requires three sizes of data operations for in-place and new installation upgrades (step 1 isn't required for rolling upgrades, but steps 2 and 3 are required):

    1. Run database recovery using the old on-disk format (including loading all data in memory-optimized tables into memory from disk)

    2. Serialize the data to disk in the new on-disk format

    3. Run database recovery using the new format (including loading all data in memory-optimized tables into memory from disk)

      Additionally, insufficient space on disk during this process causes recovery to fail. Ensure there's sufficient space on disk to store the existing database, plus extra storage equal to the current size of the containers in the MEMORY_OPTIMIZED_DATA filegroup in the database to perform an in-place upgrade, or when attaching a [!INCLUDE sssql14-md] database to an instance running [!INCLUDE sssql16-md] or a later version. Use the following query to determine the disk space currently required for the MEMORY_OPTIMIZED_DATA filegroup, and also the amount of free disk space required for the upgrade to succeed:

    SELECT CAST(SUM(size) AS FLOAT) * 8 / 1024 / 1024 AS [size in GB]
FROM sys.database_files
WHERE data_space_id IN
(
    SELECT data_space_id
    FROM sys.filegroups
    WHERE type = N'FX'
);

Develop and test the upgrade plan

The best approach is to treat your upgrade like you would any IT project. Organize an upgrade team that has the database administration, network, extraction, transformation, and loading (ETL), and other skills required for the upgrade. The team needs to:

  • Choose the upgrade method: See Choose a Database Engine Upgrade Method.

  • Develop a rollback plan: Executing this plan enables you to restore your original environment if you need to roll back.

  • Determine acceptance criteria: Verify that the upgrade is successful before you cut over users to the upgraded environment.

  • Test the upgrade plan: To test performance using your actual workload, use the Microsoft SQL Server Distributed Replay Utility. This utility can use multiple computers to replay trace data, simulating a mission-critical workload. By performing a replay on a test server before and after a SQL Server upgrade, you can measure performance differences and look for any incompatibilities your application might have with the upgrade. For more information, see SQL Server Distributed Replay and Administration Tool Command-line Options (Distributed Replay Utility).

Related content