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

Introduction

Database software providers periodically release new major versions that containnew features, performance improvements, and security enhancements.Cloud SQL takes innew versions afterthey're released. After 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 bymigrating data.In-place upgradesare a simpler way to upgrade your instance's major version. You don't need tomigrate data or change application connection strings. With in-place upgrades,you can retain the name, IP address, and other settings of your current instanceafter the upgrade. In-place upgrades don't require you to move data files andcan be completed faster. In some cases, the downtime is shorter than whatmigrating your data entails.

Plan a major version upgrade

1. Confirm that you have the required role to perform a majorversion upgrade:Cloud SQL Owner orCloud SQL Admin.

2. Choose a target major version.

   gcloud

   For information about installing and getting started with the gcloud CLI, seeInstall the gcloud CLI. For information about starting Cloud Shell, seeUse Cloud Shell.

   To check the database versions that you can target for an in-place upgrade on your instance, do the following:

   1. Run the following command.

   gcloud sql instances describeINSTANCE_NAME

   ReplaceINSTANCE_NAME with the name of the instance.

   2. In the output of the command, locate the section that is labeledupgradableDatabaseVersions.
   3. Each subsection returns a database version that is available for upgrade. In each subsection, review the following fields.
   • majorVersion: the major version that you can target for the in-place upgrade.
   • name: the database version string that includes the major version.
   • displayName: the display name for the database version.

   REST v1

   To check which target database versions are available for a majorversion in-place upgrade, use theinstances.get methodof the Cloud SQL Admin API.

   Before using any of the request data, make the following replacements:

   • INSTANCE_NAME: The instance name.

   HTTP method and URL:

   GET https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME

   To send your request, expand one of these options:

   curl (Linux, macOS, or Cloud Shell)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   curl -X GET \
        -H "Authorization: Bearer $(gcloud auth print-access-token)" \
        "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME"

   PowerShell (Windows)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   $cred = gcloud auth print-access-token
   $headers = @{ "Authorization" = "Bearer $cred" }
   
   Invoke-WebRequest `
       -Method GET `
       -Headers $headers `
       -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

   You should receive a JSON response similar to the following:

   upgradableDatabaseVersions:{  major_version: "SQLSERVER_2022_STANDARD"  name: "SQLSERVER_2022_STANDARD"  display_name: "SQL Server 2022 Standard"}

   REST v1beta4

   To check which target database versions are available for majorversion in-place upgrade of an instance, use theinstances.get method of the Cloud SQL Admin API.

   Before using any of the request data, make the following replacements:

   • INSTANCE_NAME: The instance name.

   HTTP method and URL:

   GET https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME

   To send your request, expand one of these options:

   curl (Linux, macOS, or Cloud Shell)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   curl -X GET \
        -H "Authorization: Bearer $(gcloud auth print-access-token)" \
        "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME"

   PowerShell (Windows)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   $cred = gcloud auth print-access-token
   $headers = @{ "Authorization" = "Bearer $cred" }
   
   Invoke-WebRequest `
       -Method GET `
       -Headers $headers `
       -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

   You should receive a JSON response similar to the following:

   upgradableDatabaseVersions:{  major_version: "SQLSERVER_2022_STANDARD"  name: "SQLSERVER_2022_STANDARD"  display_name: "SQL Server 2022 Standard"}

   For the complete list of the database versions that Cloud SQL supports, seeDatabase versions and version policies.

3. Consider the features offered in each database major version and addressincompatibilities.

   See SQL Serverdiscontinued features andbreaking changes.

   New major versions introduce incompatible changes that might require you tomodify the application code, the schema, or the database settings. Beforeyou can upgrade your database instance, review the release notes of yourtarget major version to determine the incompatibilities that you mustaddress.

   Note: While upgrading your major version, you can also upgrade yourSQL Server edition. For example, you can upgrade from SQL Server 2017Standard to SQL Server 2019 Enterprise or SQL Server 2022 Enterprise. If youdecide to upgrade your edition, be sure to review Cloud SQL for SQLServerlicense pricing.

4. Test the upgrade with a dry run.

   Perform a dry run of the end-to-end upgrade process in a test environmentbefore you upgrade the production database. You canclone your instanceto create an identical copy of the data on which to test the upgrade process.

   In addition to validating that the upgrade completes successfully, runtests to ensure that the application behaves as expected on the upgradeddatabase.

   Note: Ensure that you have enough disk space before running a major version upgrade.If you haven't enabledautomatic storage increases,take note of the disk storage used by the upgraded dry run instance. This helps youdetermine whether you need to increase the storage capacity for theproduction instance before upgrading.

5. Decide on a time to upgrade.

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

Perform the major version upgrade

You can upgrade the major version of a single Cloud SQL instance, oryou can upgrade the major version of a primary instance and include all ofits replicas in the upgrade, including cascading replicas and cross-regionreplicas.

Upgrade the major version of a single instance

When you initiate an upgrade operation for a single instance, Cloud SQL doesthe following:

1. Checks the configuration of your instance to ensure thatthe instance is compatible for an upgrade.
2. After Cloud SQL verifies the configuration,then Cloud SQL makes the instance unavailable.
3. Makes a pre-upgrade backup.
4. Performs the upgrade on the instance.
5. Makes your instance available.
6. Makes a post-upgrade backup.

resource "google_sql_database_instance" "instance" {  name             = "sqlserver-instance"  region           = "us-central1"  database_version = "SQLSERVER_2019_STANDARD"  root_password    = "INSERT-PASSWORD-HERE"  settings {    tier = "db-custom-2-7680"  }  # set `deletion_protection` to true, will ensure that one cannot accidentally delete this instance by  # use of Terraform whereas `deletion_protection_enabled` flag protects this instance at the GCP level.  deletion_protection = false}

When you place an in-place upgrade request, Cloud SQL first performs apre-upgrade check. If Cloud SQL determines that your instance isn'tready for an upgrade, then your upgrade request fails with a message suggesting howyou can address the issue. See alsoTroubleshoot a major versionupgrade.

When youview your list ofbackups, theupgrade backups are listed with typeOn-demand. Upgrade backups are labeled sothat you can identify them quickly.For example, if you're upgradingfrom SQL Server Enterprise 2017 to SQL Server Enterprise 2019 or SQL ServerEnterprise 2022, your pre-upgrade backup is labeled asPre-upgrade backup, SQLSERVER_2017_ENTERPRISE to SQLSERVER_2019_ENTERPRISE or SQLSERVER_2022_ENTERPRISE.and your post-upgrade backup is labeled asPost-upgrade backup, SQLSERVER_2019_ENTERPRISE or SQLSERVER_2022_ENTERPRISE from SQLSERVER_2017_ENTERPRISE.

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

Upgrade the database compatibility level

Thedatabase compatibilityleveldetermines how the database behaves with respect to the application it serves.The database compatibility level setting ensuresbackwardcompatibilitywith earlier versions of SQL Server and relates to Transact-SQL and QueryOptimizer changes. When a SQL Server instance database version is upgraded, thecompatibility levels of existing databases are preserved, such that theapplication can continue to operate on the more recent version of SQL Server.Upgrading the compatibility level helps you benefit from new features, queryprocessing improvements, and other changes.

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

1. Identify thecurrent compatibility levelof your database.

   For example, for SQL Server 2017, the default compatibility level is 140. Tocheck the current compatibility level for your database, run the followingcommand in Transact-SQL, after replacingDATABASE_NAME

   with the name of the database on your SQL Server instance.

   USEDATABASE_NAMEGOSELECTcompatibility_levelFROMsys.databasesWHEREname='DATABASE_NAME'

2. Determine the target compatibility level.

   Identify the default compatibility level designation for your upgradeddatabase version to determine the target compatibility level for yourdatabase. For example, for SQL Server 2022, the default compatibility levelis 160. See thetable mapping new versions of SQL Server with compatibilitylevels.

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

   Before you upgrade the compatibility level, study the differences in systembehavior between your current compatibility level and your targetcompatibility level. See thecomplete list of the differences betweencompatibilitylevels.

4. Collect a baseline of workload data.

   Before upgrading the compatibility level, collect a baseline of workloaddata by usingSQL Server QueryStore,so that you can later identify and address regressed queries. You use QueryStore to capture queries and plans for atypical businesscycleto establish a performance baseline. For a guided workflow, use theQueryTuningAssistantfeature in SQL Server Management Studio.

5. Upgrade the compatibility level.

   To change the compatibility level for the database, run the followingcommand in Transact-SQL, after replacingDATABASE_NAME

   with the name of the database on your SQL Server instance andTARGET_COMPATIBILITY_LEVELwith the target compatibility level.

   ALTERDATABASEDATABASE_NAMESETCOMPATIBILITY_LEVEL=TARGET_COMPATIBILITY_LEVEL;GO

6. Collect upgraded workload data.

   Collect upgraded workload data using Query Store for comparison andregression detection.

7. Address regressed queries.

   For the most part, Query Optimizer changes in upgraded compatibility levelsimprove performance. However, from time to time, certain queries mightregress in performance. Query Store'sRegressedQueriesfeature helps you identify the queries that have regressed and lets youforce the last known good query plan. SQL Server also offersautomaticplancorrection,which can automatically switch to the last known good plan in the event of aquery regression.

Complete the major version upgrade

After upgrading your primary instance, perform acceptance tests to make sure thatthe upgraded system performs as expected.

Troubleshoot a major version upgrade

Cloud SQL returns an error message if you attempt an invalid upgradecommand, for example, if your instance contains invalid database flags for thenew version.

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

View error logs

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

To view error logs, use the Google Cloudconsole::

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

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.

3. In theOperations and logs pane of the instanceOverview page, clicktheView SQL Server error logs link.

   TheLogs Explorer page opens.

4. View logs as follows:

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

   For more information on query filters, seeAdvanced queries.

   • To filter the upgrade error logs for a single instance, enter thefollowing query in theSearch all fields box, after replacingDATABASE_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 namedshopping-db running in the projectbuylots, use the following queryfilter:

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

   You can either review all logs reported within a given timeframe, or you can filter logs by severity. A common option for troubleshooting might include selecting the following filters:

   • Emergency
   • Alert
   • Critical
   • Error

Restore the primary instance to the previous major version

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

To restore a primary instance to the previous version, perform the following steps:

1. Identify your pre-upgrade backup.

   SeeAutomatic upgrade backups.

2. Create a recovery instance.

   Create a new Cloud SQLinstance using the major versionthat Cloud SQL was running when the pre-upgrade backup was made. Setthe sameflags andinstancesettings that the originalinstance uses.

3. Restore your pre-upgrade backup.

   Restoreyour pre-upgrade backup to the recovery instance. This might take severalminutes to complete.

4. Add your read replicas.

   If you're using read replicas, then add the read replicas individually.

5. Connect your application.

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

Limitations

This section lists limitations for an in-place major version upgrade.

• You can't perform an in-place major version upgrade on anexternal replica.

FAQs

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

Is my instance unavailable during an upgrade?

Yes. Your instance remains unavailable for a period of time while Cloud SQL performs the upgrade.

How long does an upgrade take?

Upgrading a single instance typically takes less than 10 minutes. If your instance configuration has a small number of vCPUs or memory, then your upgrade might take more time.

If your instance hosts too many databases or tables, or your databases are very large, then the upgrade might take hours or even time out because the total upgrade time corresponds to the number of objects in your databases. If you have multiple instances that need to be upgraded, then your upgrade time increases proportionately.

Can I monitor each step in my upgrade process?

While Cloud SQL lets you monitor whether an upgrade operation is still in progress, you can't 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, explicitly configured flag values, and user data. However, the default value of the system variables might change.

To learn more, seeConfigure database flags. If a certain flag or value is no longer supported in your target version, thenCloud SQL automatically removes the flag during the upgrade.

What's next

• Learn aboutoptions for connecting to an instance.
• Learn aboutimporting and exporting data.
• Learn more about [setting database flags][flags].