Update policy in Azure SQL Managed Instance

  • Article

Applies to: Azure SQL Managed Instance

This article describes the update policy for Azure SQL Managed Instance, and how to modify it. The update policy is an instance setting that controls access to the latest SQL engine features in Azure.

Azure SQL Managed Instance offers the following two update policies:

  • SQL Server 2022 update policy: The instance can only use SQL engine features available in SQL Server 2022 as the internal database format is aligned with SQL Server 2022.
  • Always-up-to-date update policy: The instance has access to all SQL engine features as soon as they're available in Azure. The internal database format is no longer aligned with the latest version of SQL Server, and instead evolves with each newly introduced feature.

Important

Regardless of the configured update policy, all instances will continue receiving updates and features that don't require changes to the SQL engine, such as the following features: zone redundancy, instance stop and start, and fast provisioning.

SQL Server 2022 update policy

The SQL Server 2022 update policy aligns your database format with SQL Server 2022.

When using the SQL Server 2022 update policy, consider the following:

  • The SQL Server 2022 update policy is the default update policy for all existing and newly deployed instances.
  • Your internal database format remains aligned with SQL Server 2022.
  • You receive all the latest updates available for SQL Server 2022.
  • You can restore your database to SQL Server 2022 from Azure SQL Managed Instance.
  • You can configure a link for real-time data replication, bidirectional failover, and disaster recovery between SQL Server 2022 and Azure SQL Managed Instance.
  • You might not have access to some of the latest SQL engine features and benefits available to Azure SQL Managed Instance with the Always-up-to-date update policy.
  • The SQL Server 2022 update policy is available until the end of mainstream support of SQL Server 2022, at which point, the update policy for instances using the SQL Server 2022 update policy are automatically updated to the update policy that corresponds to the latest SQL Server major release available at that time.

Always-up-to-date update policy

The Always-up-to-date update policy configures your instance to receive all the latest features and updates available to Azure SQL Managed Instance.

When using the Always-up-to-date update policy, consider the following:

  • You're able to use all the new features and benefits available to Azure SQL Managed Instance.
  • Once the Always-up-to-date policy is enabled, you can't go back to the SQL Server 2022 update policy.
  • You lose some of the benefits provided by database format alignment with SQL Server 2022, such as the ability to restore your database to SQL Server 2022, and bidirectional failover between your instance and SQL Server 2022 with the link feature.

Feature comparison

The following table lists all the features that are only available to instances with the designated update policy:

SQL Server 2022 update policy Always-up-to-date update policy
- Restore database to SQL Server 2022
- Link with bidirectional failover and disaster recovery		 JSON data type

The following features are impacted by the configured update policy:

  • Automated backups and copy-only backups: Database backups taken from instances configured with the SQL Server 2022 update policy can be restored to instances configured with either the SQL Server 2022 or Always-up-to-date update policy. Database backups taken from instances configured with the Always-up-to-date update policy can only be restored to instances also configured with the Always-up-to-date update policy.
  • Managed Instance link: Establishing a link from SQL Managed Instance to SQL Server 2022, or failing back from SQL Server 2022 to SQL Managed Instance is only available to instances with the SQL Server 2022 update policy.
  • Database copy and move: A database from an instance configured with the Always-up-to-date update policy can't be copied or moved to an instance configured with the SQL Server 2022 update policy.
  • Failover groups: Instances in a failover group must have matching update policies.

Existing instances

For an existing instance, you can enable the Always-up-to-date update policy by using the Azure portal, PowerShell, the Azure CLI, and REST API.

Caution

The SQL Server 2022 update policy is enabled by default for all existing and new instances. Once the update policy is changed to Always-up-to-date, the internal database format is upgraded permanently. You can't change the update policy back to SQL Server 2022 and can no longer use the features and benefits that require the SQL Server 2022 update policy.

To change the update policy for an existing instance in the Azure portal, follow these steps:

  1. Go to your SQL managed instance resource in the Azure portal.

  2. Select Maintenance and updates under Settings.

  3. Select the bubble to enable the Always up-to-date update policy:

    Screenshot of the SQL Managed Instance page in the Azure portal, with update policy selected.

  4. Select Yes on the Confirm update policy change popup to save your changes. Once the Always-up-to-date update policy is enabled, the SQL Server 2022 update policy is no longer available

New instances

Although the SQL Server 2022 update policy is enabled by default, you can choose the Always-up-to-date policy when you create your instance by using the Azure portal, PowerShell, Azure CLI and REST API.

To create a new SQL managed instance with the Always-up-to-date policy in the Azure portal, follow these steps:

  1. Go to the Azure SQL page in the Azure portal and select + Create to open the Select SQL deployment option.

  2. In the SQL managed instances tile, select Single instance from the dropdown, and then select Create to open the Create Azure SQL Managed Instance page.

  3. On the Create Azure SQL Managed Instance page, fill out details for your instance. On the Additional settings tab, under SQL engine updates, choose the Always-up-to-date policy:

    Screenshot of the Create Azure SQL Managed Instance page of the Azure portal with update policy selected.

  4. You can confirm the designated policy under Update policy on the Review + create tab before you create your new instance.

Check update policy

You can check the current update policy by using the Azure portal, or Transact-SQL (T-SQL).

To check the current update policy in the Azure portal, go to your SQL managed instance resource, and then check the Update policy field under Updates and maintenance in the resource menu.

You can also use the serverproperty T-SQL command:

select serverproperty('ProductUpdateType')

The following values for ProductUpdateType indicate the update policy for the current instance:

  • CU: Updates are deployed via cumulative updates (CUs) for the corresponding major SQL Server release (SQL Server 2022 update policy)
  • Continuous: New features are brought to Azure SQL Managed Instance as soon as they're available, independent of the SQL Server release cadence (Always-up-to-date update policy)

Related content