Επεξεργασία

Κοινή χρήση μέσω

Plan and test the Database Engine upgrade plan

  • Άρθρο

Applies to: SQL Server - Windows only

To perform a successful SQL Server upgrade, regardless of approach, appropriate planning is required.

Release notes and known upgrade issues

Before upgrading the Database Engine, review:

Pre-upgrade planning checklist

Before upgrading the Database Engine, 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 SQL Server 2005 (9.x), or upgrading from a 32-bit version of SQL Server. For a decision tree, see Choose a Database Engine Upgrade Method.

  • Hardware and software requirements: Review the hardware and software requirements to for installing SQL Server. 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 SQL Server components that are being used and the clients that connect to your environment.

    • Client providers: While upgrading doesn't require you to update the provider for each of your clients, you might choose to do so. If you upgrade from SQL Server 2014 (12.x) or older, the following SQL Server 2016 (13.x) features either require an updated provider for each client or an updated provider to provide additional functionality:

    • Always Encrypted (Database Engine)

    • Stretch Database

      Important

      Stretch Database is deprecated in SQL Server 2022 (16.x) and Azure SQL Database. This feature will be removed in a future version of the Database Engine. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    • Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)

    • TLS Security update

  • 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 SQL Server instances to a single, new SQL Server instance, or the virtualization of your SQL Server environment to a private or public cloud.

  • Edition: Determine the appropriate edition of SQL Server 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 SQL Server to another, verify that the functionality that you currently use is supported in the edition to which you upgrade.

    Note

    When you upgrade SQL Server from a prior version of SQL Server 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 SQL Server database engine backward compatibility article to review changes in behavior between SQL Server and the SQL Server 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 SQL Server System Configuration Checker (SCC) to determine if the SQL Server 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 SQL Server 2014 (12.x) instance containing memory-optimized tables to SQL Server 2016 (13.x) 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 SQL Server 2014 (12.x) database to an instance running SQL Server 2016 (13.x) 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