Upgrade the database major version in-place

This page describes how to upgrade the database major version by upgrading your Cloud SQL instance in-place rather than by migrating data.

Introduction

Database software providers periodically release new major versions that contain new features, performance improvements, and security enhancements. Cloud SQL intakes new versions after they're released. Once Cloud SQL offers support for a new major version, you can upgrade your instances to keep your database updated.

You can upgrade the database version of an instance in-place or by migrating data. In-place upgrades are a simpler way to upgrade your instance's major version. You don't need to migrate data or change application connection strings. With in-place upgrades, you can retain the name, IP address, and other settings of your current instance after the upgrade. In-place upgrades don't require you to move data files and can be completed faster. In some cases, the downtime is shorter than what migrating your data entails.

The Cloud SQL for SQL Server in-place upgrade operation uses the SQL Server upgrade in-place utility.

Plan a major version upgrade

  1. Choose a target major version.

    See the list of versions that Cloud SQL supports.

  2. Consider the features offered in each database major version and address incompatibilities.

    See SQL Server 2019 discontinued features and breaking changes.

    New major versions introduce incompatible changes that might require you to modify the application code, the schema, or the database settings. Before you can upgrade your database instance, review the release notes of your target major version to determine the incompatibilities that you need to address. If you're skipping major versions, address the incompatibilities listed for each version that you're skipping.

  3. Test the upgrade with a dry run.

    Perform a dry run of the end-to-end upgrade process in a test environment before you upgrade the production database. You can clone your instance to create an identical copy of the data on which to test the upgrade process.

    In addition to validating that the upgrade completes successfully, run tests to ensure that the application behaves as expected on the upgraded database. If you haven't enabled automatic storage increases, take note of the disk storage used by the upgraded dry run instance to determine whether you need to increase the storage capacity for the production instance before upgrading.

  4. Decide on a time to upgrade.

    Upgrading requires the instance to become unavailable for a period of time. Plan to upgrade during a time period when database activity is low.

Upgrade the database major version in-place

When you initiate an upgrade operation, Cloud SQL first checks the configuration of your instance to ensure that it's compatible for an upgrade. After verifying your configuration, Cloud SQL makes your instance unavailable, makes a pre-upgrade backup, performs the upgrade, makes your instance available, and makes a post-upgrade backup.

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Click Edit.
  4. In the Instance info section, click the Upgrade button and confirm that you want to go to the upgrade page.
  5. On the Choose a database version page, click the Database version for upgrade list and select one of the available database major versions.
  6. Click Continue.
  7. In the Instance ID box, enter the name of the instance and then click the Start upgrade button.
The operation takes several minutes to complete.

Verify that the upgraded database major version appears below the instance name on the instance Overview page.

gcloud

  1. Start the upgrade.

    Use the gcloud beta sql instances patch command with the --database-version flag.

    Before running the command, replace the following:

    • INSTANCE_NAME: The name of the instance.
    • DATABASE_VERSION: The enum for the database major version, which must be greater than the current version. See the available database version enums.
    gcloud beta sql instances patch INSTANCE_NAME \
    --database-version=DATABASE_VERSION
    

    Major version upgrades take several minutes to complete. You might see a message indicating that the operation is taking longer than expected. You can either ignore this message or run the gcloud sql operations wait command to dismiss the message.

  2. Get the upgrade operation name.

    Use the gcloud sql operations list command with the --instance flag.

    Before running the command, replace the INSTANCE_NAME variable with the name of the instance.

    gcloud sql operations list --instance=INSTANCE_NAME
    
  3. Monitor the status of the upgrade.

    Use the gcloud sql operations describe command.

    Before running the command, replace the OPERATION variable with the upgrade operation name retrieved in the previous step.

    gcloud sql operations describe OPERATION
    

REST v1

  1. Start the in-place upgrade.

    Use a PATCH request with the instances:patch method.

    Before using any of the request data, replace these variables:

    • project_id: The ID of the project.
    • instance_name: The name of the instance.

    HTTP method and URL:

    POST https://sqladmin.googleapis.com/sql/v1/projects/project-id/instances/instance_name
    

    Request JSON body:

    {
      "databaseVersion": enum DATABASE_VERSION
    }
    

    Replace DATABASE_VERSION with the enum for the database major version, which must be greater than the current version. See the available database version enums.

    Send your request using curl or PowerShell. See Edit instances.

  2. Get the upgrade operation name.

    Use a GET request with the operations.list method after replacing project_id with the ID of the project.

    HTTP method and URL:

    GET https://sqladmin.googleapis.com/sql/v1/projects/project-id/operations
    
  3. Monitor the status of the upgrade.

    Use a GET request with the operations.get method after replacing the following variables:

    • project_id: The ID of the project.
    • operation_name: The upgrade operation name retrieved in the previous step.

    HTTP method and URL:

    GET https://sqladmin.googleapis.com/sql/v1/projects/project-id/operation/operation_name
    

When you place an in-place upgrade request, Cloud SQL first performs a pre-upgrade check. If Cloud SQL determines that your instance isn't ready for an upgrade, your upgrade request fails with a message suggesting how you can address the issue. See also Troubleshoot a major version upgrade.

Automatic upgrade backups

When you perform a major version upgrade, Cloud SQL automatically makes two on-demand backups, called upgrade backups:

  • The first upgrade backup is the pre-upgrade backup, which is made immediately before starting the upgrade. You can use this backup to restore your database instance to its state on the previous version.
  • The second upgrade backup is the post-upgrade backup, which is made immediately after new writes are allowed to the upgraded database instance.

When you view your list of backups, the upgrade backups are listed with type On-demand. Upgrade backups are labeled so that you can identify them easily. For example, if you're upgrading from SQL Server Enterprise 2017 to SQL Server Enterprise 2019, your pre-upgrade backup will be labeled Pre-upgrade backup, SQLSERVER_2017_ENTERPRISE to SQLSERVER_2019_ENTERPRISE. and your post-upgrade backup will be labeled Post-upgrade backup, SQLSERVER_2019_ENTERPRISE from SQLSERVER_2017_ENTERPRISE.

As with other on-demand backups, upgrade backups persist until you delete them or delete the instance.

Upgrade the database compatibility level

The database compatibility level determines how the database behaves with respect to the application it serves. The database compatibility level setting ensures backward compatibility with earlier versions of SQL Server and relates to Transact-SQL and Query Optimizer changes. When a SQL Server instance's database version is upgraded, the compatibility levels of existing databases are preserved, such that the application can continue to operate on the more recent version of SQL Server. Upgrading the compatibility level helps you benefit from new features, query processing improvements, and other changes.

After you've upgraded an instance's database engine version, when the application that the database serves is ready, upgrade the database compatibility level for each database in the instance. When the compatibility level is set to the latest, databases are upgraded with the latest features and improved performance.

To upgrade the database compatibility level, perform the following steps:

  1. Identify the current compatibility level of your database.

    For example, for SQL Server 2017, the default compatibility level is 140. To check the current compatibility level for your database, run the following command in Transact-SQL, after replacing DATABASE_NAME with the name of the database on your SQL Server instance.

    USE DATABASE_NAME
    GO
    SELECT compatibility_level
    FROM sys.databases WHERE name = 'DATABASE_NAME'
    
  2. Determine the target compatibility level.

    Identify the default compatibility level designation for your upgraded database version to determine the target compatibility level for your database. For example, for SQL Server 2019, the default compatibility level is 150. See the table mapping new versions of SQL Server with compatibility levels.

  3. Assess the differences between your current and target compatibility levels.

    Before you upgrade the compatibility level, study the differences in system behavior between your current compatibility level and your target compatibility level. See the complete list of the differences between compatibility levels.

  4. Collect a baseline of workload data.

    Before upgrading the compatibility level, collect a baseline of workload data by using SQL Server Query Store, so that you can later identify and address regressed queries. You use Query Store to capture queries and plans for a typical business cycle to establish a performance baseline. For a guided workflow, use the Query Tuning Assistant feature in SQL Server Management Studio.

  5. Upgrade the compatibility level.

    To change the compatibility level for the database, run the following command in Transact-SQL, after replacing DATABASE_NAME with the name of the database on your SQL Server instance and TARGET_COMPATIBILITY_LEVEL with the target compatibility level.

    ALTER DATABASE DATABASE_NAME
    SET COMPATIBILITY_LEVEL = TARGET_COMPATIBILITY_LEVEL;
    GO
    
  6. Collect upgraded workload data.

    Collect upgraded workload data using Query Store for comparison and regression detection.

  7. Address regressed queries.

    For the most part, Query Optimizer changes in upgraded compatibility levels improve performance. However, from time to time, certain queries might regress in performance. Query Store's Regressed Queries feature helps you identify the queries that have regressed and enables you to force the last known good query plan. SQL Server also offers automatic plan correction, which can automatically switch to the last known good plan in the event of a query regression.

Complete the major version upgrade

After upgrading your primary instance, perform acceptance tests to ensure that the upgraded system performs as expected.

Troubleshoot a major version upgrade

Cloud SQL returns an error message if you attempt an invalid upgrade command, for example, if your instance contains invalid database flags for the new version.

If your upgrade request fails, check the syntax of your upgrade request. If the request has a valid structure, try looking into the following suggestions.

View upgrade logs

If any issues occur with a valid upgrade request, Cloud SQL publishes error logs to projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fsqlserver.err. Each log entry contains a label with the instance identifier to help you identify the instance with the upgrade error. Look for such upgrade errors and resolve them.

To view error logs, follow these steps:

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. In the Operations and logs pane of the instance Overview page, click the View SQL Server error logs link.

    The Logs Explorer page opens.

  4. View logs as follows:

    • To list all error logs in a project, select the log name in the Log name log filter.

      For more information on query filters, see Advanced queries.

    • To filter the upgrade error logs for a single instance, enter the following query in the Search all fields box, after replacing DATABASE_ID with the project ID followed by the instance name in this format: project_id:instance_name.

      resource.type="cloudsql_database"
      resource.labels.database_id="DATABASE_ID"
      logName : "projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fsqlserver.err"
      

      For example, to filter the upgrade error logs by an instance named shopping-db running in the project buylots, use the following query filter:

      resource.type="cloudsql_database"
      resource.labels.database_id="buylots:shopping-db"
      logName : "projects/buylots/logs/cloudsql.googleapis.com%2Fsqlserver.err"
      

Restore to the previous major version

If your upgraded database system doesn't perform as expected, you might need to restore your instance to the previous version. You do so by restoring your pre-upgrade backup to a Cloud SQL recovery instance, which is a new instance running the pre-upgrade version.

To restore to the previous version, perform the following steps:

  1. Identify your pre-upgrade backup.

    See Automatic upgrade backups.

  2. Create a recovery instance.

    Create a new Cloud SQL instance using the major version that Cloud SQL was running when the pre-upgrade backup was made. Set the same flags and instance settings that the original instance uses.

  3. Restore your pre-upgrade backup.

    Restore your pre-upgrade backup to the recovery instance. This might take several minutes to complete.

  4. Add your read replicas.

    If you were using read replicas, add them individually.

  5. Connect your application.

    Having recovered your database system, update your application with details about the recovery instance and its read replicas. You can resume serving traffic on the pre-upgrade version of your database.

FAQs

The following questions might come up when upgrading the database major version.

Is my instance unavailable during an upgrade?
Yes. Your instance stays unavailable for a period of time for Cloud SQL to perform the upgrade.
How long does an upgrade take?
Upgrading a single instance typically takes less than 10 minutes. Your upgrade time might be longer if your instance hosts too many databases or tables, your databases are very large, or your instance configuration uses a small number of vCPUs or memory. If you have multiple instances that need to be upgraded, your total upgrade time increases proportionately.
Can I monitor each step in my upgrade process?
While Cloud SQL allows you to monitor whether an upgrade operation is still in progress, you are unable to track the individual steps in each upgrade.
Can I cancel my upgrade after I've started it?
No, you can't cancel an upgrade once it has started. If your upgrade fails, Cloud SQL automatically recovers your instance on the previous version.
What happens to my settings during an upgrade?
When you perform an in-place major version upgrade, Cloud SQL retains your database settings, including your instance name, IP address, flag values, and user data. If a certain flag or a flag value is no longer supported in your target version, Cloud SQL automatically removes the flag during the upgrade.

What's next