Upgrades - SQL Server to Aurora MySQL Migration Playbook
SQL Server UsageMySQL UsageSummary
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Upgrades

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

N/A

N/A

N/A

N/A

SQL Server Usage

As a database administrator, from time to time a database upgrade is required, it can be either for security fix, bugs fixes, compliance, or new database features.

The database upgrade approach can be planned to minimize the database downtime and risk. You can perform an upgrade in-place or migrate to a new installation.

Upgrade In-Place

With this approach, we are retaining the current hardware and OS version by adding the new SQL Server binaries on the same server and then upgrade the SQL Server instance.

Before upgrading the database engine, review the SQL Server release notes for the intended target release version for any limitations and known issues to help you plan the upgrade.

In general, these will be the steps to perform the upgrade:

Prerequisites steps

  • Back up all SQL Server database files, so that it can be restored if required.

  • Run the appropriate Database Console Commands (DBCC CHECKDB) on databases to be upgraded to make sure that they are in a consistent state.

  • Ensure to allocate enough disk space for SQL Server components, in addition to user databases.

  • Disable all startup stored procedures as stored procedures processed at startup time might block the upgrade process.

  • Stop all applications, including all services that have SQL Server dependencies.

Steps for upgrade

  • Install new software.

    • Fix issues raised.

    • Set if you prefer to have automatic updates or not.

    • Select products install to upgrade, this is the new binaries installation.

    • Monitor the progress of downloading, extracting, and installing the Setup files.

  • Specify the instance of SQL Server to upgrade.

    • On the Select Features page, the features to upgrade will be preselected. The prerequisites for the selected features are displayed on the right-hand pane. SQL Server Setup will install the prerequisite that aren’t already installed during the installation step described later in this procedure.

  • Review upgrade plan before the actual upgrade.

  • Monitor installation progress.

Post upgrade tasks

  • Review summary log file for the installation and other important notes.

  • Register your servers.

Migrate to a New Installation

This approach maintains the current environment while building a new SQL Server environment. This is usually done when migrating on a new hardware and with a new version of the operating system. In this approach migrate the system objects so that they are same as the existing environment, then migrate the user database either using backup and restore.

For more information, see Upgrade Database Engine in the SQL Server documentation.

MySQL Usage

After migrating your databases to Amazon Aurora MySQL-Compatible Edition (Aurora MySQL), you will still need to upgrade your database instance from time to time, for the same reasons you have done it in the past like new features, bugs and security fixes.

In a managed service like Amazon Relational Database Service (Amazon RDS), the upgrade process is much easier and simpler compare to the on-prem SQL Server process.

To determine the current Aurora MySQL version being used, you can use the following Amazon CLI command:

aws rds describe-db-engine-versions --engine aurora-mysql --query '*[].[EngineVersion]' --output text --region your-AWS-Region

This can also be queried from the database, using the following queries:

SELECT AURORA_VERSION();

In an Aurora MySQL version number scheme, for example 2.08.1, the first digit represents the major version. Aurora MySQL version 1 is compatible with MySQL 5.6 and Aurora MySQL version 2 is compatible with MySQL 5.7. To find all Amazon Aurora and MySQL versions mapping, see Database engine updates for Amazon Aurora MySQL version 2.

Amazon doesn’t apply major version upgrades on Amazon Aurora automatically. Major version upgrades contains new features and functionality which often involves system table and other code changes. These changes may not be backward-compatible with previous versions of the database so applications testing is highly recommended.

Applying automatic minor upgrades can be set by configuring the Amazon RDS instance to allow it.

You can use the following Amazon CLI command (Linux) to determine the current automatic upgrade minor versions.

aws rds describe-db-engine-versions --output=table --engine mysql --engine-version minor-version --region region
Note

If no results returned, there is no automatic minor version upgrade available and scheduled.

When enabled, the instance will be automatically upgraded during the scheduled maintenance window.

If you want to upgrade your cluster to a compatible cluster, you can do so by running an upgrade process on the cluster itself. This kind of upgrade is an in-place upgrade, in contrast to upgrades that you do by creating a new cluster. The upgrade is relatively fast because it doesn’t require copying all your data to a new cluster volume. In place upgrade preserves the endpoints and set of DB instances for your cluster.

To verify application compatibility, performance and maintenance procedures for the upgraded cluster, you can perform a simulation of the upgrade by doing following

  • Clone a cluster.

  • Perform an in-place upgrade of the cloned cluster.

  • Test applications, performance and so on, using the cloned cluster.

  • Resolve any issues, adjust your upgrade plans to account for them.

  • Once all the testing looks good, you can perform the in-place upgrade for your production cluster.

For major upgrades, this is the recommended:

  • Check for open XA transactions by running the XA RECOVER statement. Commit or Rollback the XA transactions before starting the upgrade.

  • Check for DDL statements by running a SHOW PROCESSLIST statement and looking for CREATE, DROP, ALTER, RENAME, and TRUNCATE statements in the output. Allow all DDLs to finish before starting the upgrade.

  • Check for any uncommitted rows by querying the INFORMATION_SCHEMA.INNODB_TRX table. The table contains one row for each transaction. Let the transaction complete or shut down applications that are submitting these changes.

Aurora MySQL performs a major version upgrade in multiple steps. As each step begins, Aurora MySQL records an event. You can monitor the current status and events as they occur on the Events page in the Amazon RDS console.

Amazon Aurora performs a series of checks before beginning the upgrade process. If any issues are detected during these checks, resolve the issue identified in the event details and restart the upgrade process.

Aurora takes the cluster offline, performs a similar set of tests as in the previous step. If no new issues are identified, then Aurora moves with the next step. If any issues are detected during these checks, resolve the issue identified in the event details and restart the upgrade process again.

Aurora backups up the MySQL cluster by creating a snapshot of the cluster volume.

Aurora clones the cluster volume. If any issues are encountered during the upgrade, Aurora reverts to the original data from the cloned cluster volume and brings the cluster back online.

Aurora performs a clean shutdown and it rolls back any uncommitted transactions.

Aurora upgrades the engine version. It installs the binary for the new engine version and uses the writer DB instance to upgrade your data to new to MySQL compatible format. During this stage, Aurora modifies the system tables and performs other conversions that affect the data in your cluster volume.

The upgrade process is completed. Aurora records a final event to indicate that the upgrade process completed successfully. Now DB cluster is running the new major version.

Upgrade can be done through the Amazon Console or Amazon CLI.

Console

  1. Sign in to the Amazon Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the navigation pane, choose Databases, and then choose the DB cluster that you want to upgrade.

  3. Choose Modify. The Modify DB cluster page appears.

  4. For DB engine version, choose the new version.

  5. Choose Continue and check the summary of modifications.

  6. To apply the changes immediately, choose Apply immediately. Choosing this option can cause an outage in some cases. For more information, see Modifying an Amazon Aurora DB cluster.

  7. On the confirmation page, review your changes. If they are correct, choose Modify cluster to save your changes. Choose Back to edit your changes or Cancel to cancel your changes.

Amazon CLI

To upgrade the major version of an Aurora MySQL DB cluster, use the Amazon CLI modify-db-cluster command with the following required parameters:

For Linux, macOS, or Unix:

aws rds modify-db-cluster \
--db-cluster-identifier sample-cluster \
--engine aurora-mysql \
--engine-version 5.7.mysql_aurora.2.09.0 \
--allow-major-version-upgrade \
--apply-immediately

For Windows:

aws rds modify-db-cluster ^
--db-cluster-identifier sample-cluster ^
--engine aurora-mysql ^
--engine-version 5.7.mysql_aurora.2.09.0 ^
--allow-major-version-upgrade ^
--apply-immediately

Summary

Phase SQL Server Step Aurora MySQL

Prerequisite

Perform an instance backup

Run Amazon RDS instance backup

Prerequisite

DBCC for consistent verification

N/A

Prerequisite

Validate disk size and free space

N/A

Prerequisite

Disable all startup stored procedures (if applicable)

N/A

Prerequisite

Stop application and connection

N/A

Prerequisite

Install new software and fix prerequisites errors raised

Commit or rollback uncommitted transactions

Prerequisite

Select instances to upgrade

Select right Amazon RDS instance

Prerequisite

Review pre-upgrade summary

N/A

Runtime

Monitor upgrade progress

Can be reviewed from the console

Post-upgrade

Results

Can be reviewed from the console

Post-upgrade

Register server

N/A

Post-upgrade

Test applications again the new upgraded database

Same

Production deployment

Re-run all steps in a production environment

Same

For more information, see Upgrading Amazon Aurora MySQL DB clusters in the User Guide for Aurora.