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:
Important
Regardless of the configured update policy, all instances continue receiving updates and features that don't require changes to the SQL engine, such as the following features: zone redundancy, and instance stop and start.
SQL Server 2022 update policyThe SQL Server 2022 update policy aligns your database format with SQL Server 2022.
When you use the SQL Server 2022 update policy, consider the following points:
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 you use the Always-up-to-date update policy, consider the following points:
The following table lists all the features that are only available to instances with the designated update policy:
The following features are impacted by the configured update policy:
Unless you're relying on a specific feature that requires the SQL Server 2022 update policy, we recommend using the Always-up-to-date update policy. The Always-up-to-date update policy provides you with the latest features and benefits available to Azure SQL Managed Instance. While the latest features might not be directly relevant to you, there are often improvements to performance, security, and reliability that can benefit your workload.
If you're using the SQL Server 2022 update policy to copy databases from SQL Managed Instance to SQL Server for regulatory compliance, contractual obligations, or other reasons important to your business, you can often accomplish the same goals by using other features like database export/import, or transactional replication, or services like Azure Data Factory. Using one of these alternative methods allows you to use the Always-up-to-date update policy with SQL Managed Instance while still meeting your business requirements.
If you're not yet sure what requirements your solution needs, then take your time and start with the SQL Server 2022 update policy. You can always switch to the Always-up-to-date update policy later.
You can also use different update policies for different environments. For example, use the Always-up-to-date update policy in your development environment to take advantage of the latest features, while using the SQL Server 2022 update policy in your production environment to ensure compatibility with SQL Server 2022 for failover scenarios.
Existing instancesFor an existing instance, you can enable the Always-up-to-date update policy by using the Azure portal, PowerShell, the Azure CLI, or REST API.
Caution
The SQL Server 2022 update policy is enabled by default for all existing and new instances. When you change the update policy 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 you 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:
Go to your SQL managed instance resource in the Azure portal.
Select Maintenance and updates under Settings.
Select the bubble to enable the Always up-to-date update policy:
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
Set DatabaseFormat = AlwaysUpToDate when you update an existing SQL managed instance with the Set-AzSqlInstance PowerShell command to configure your instance to use the Always-up-to-date update policy.
Set --database-format = AlwaysUpToDate when you update an existing SQL managed instance with the az sql mi update Azure CLI command to configure your instance to use the Always-up-to-date update policy.
Set databaseFormat = AlwaysUpToDate when you update an existing SQL managed instance with the Managed Instances - Create Or Update REST API command to configure your instance to use the Always-up-to-date update policy.
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, or REST API.
Important
Make sure to add update policy configuration to your deployment templates, so that you don't rely on system defaults that might change over time.
To create a new SQL managed instance with the Always-up-to-date policy in the Azure portal, follow these steps:
Go to the Azure SQL page in the Azure portal and select + Create to open the Select SQL deployment option page.
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.
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:
Confirm the designated policy under Update policy on the Review + create tab before you create your new instance.
Set DatabaseFormat = AlwaysUpToDate when you create a new SQL managed instance with the New-AzSqlInstance PowerShell command to create a new instance with the Always-up-to-date update policy.
Set --database-format = AlwaysUpToDate when you create a new SQL managed instance with the az sql mi create Azure CLI command to create a new instance with the Always-up-to-date update policy.
Set databaseFormat = AlwaysUpToDate when you create a new SQL managed instance with the Managed Instances - Create Or Update REST API command to create a new instance with the Always-up-to-date 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. 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)RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4