T-SQL differences between SQL Server & Azure SQL Managed Instance
Applies to:
Azure SQL Managed Instance
This article summarizes and explains the differences in syntax and behavior between Azure SQL Managed Instance and SQL Server.
SQL Managed Instance provides high compatibility with the SQL Server database engine, and most features are supported in a SQL Managed Instance.
There are some PaaS limitations that are introduced in SQL Managed Instance and some behavior changes compared to SQL Server. The differences are divided into the following categories:
- Availability includes the differences in Always On Availability Groups and backups.
- Security includes the differences in auditing, certificates, credentials, cryptographic providers, logins and users, and the service key and service master key.
- Configuration includes the differences in buffer pool extension, collation, compatibility levels, database mirroring, database options, SQL Server Agent, and table options.
- Functionalities include BULK INSERT/OPENROWSET, CLR, DBCC, distributed transactions, extended events, external libraries, FILESTREAM and FileTable, full-text Semantic Search, linked servers, PolyBase, Replication, RESTORE, Service Broker, stored procedures, functions, and triggers.
- Environment settings such as VNets and subnet configurations.
Most of these features are architectural constraints and represent service features.
Temporary known issues that are discovered in SQL Managed Instance and will be resolved in the future are described in What's new?.
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
Availability
Always On availability groups
High availability is built into SQL Managed Instance and can't be controlled by users. The following statements aren't supported:
- CREATE ENDPOINT … FOR DATABASE_MIRRORING
- CREATE AVAILABILITY GROUP
- ALTER AVAILABILITY GROUP
- DROP AVAILABILITY GROUP
- The SET HADR clause of the ALTER DATABASE statement
Backup
Azure SQL Managed Instance has automatic backups, so users can create full database COPY_ONLY
backups. Differential, log, and file snapshot backups aren't supported.
- With a SQL Managed Instance, you can back up an instance database only to an Azure Blob storage account:
- Only
BACKUP TO URL
is supported. FILE
,TAPE
, and backup devices aren't supported.
- Only
- Most of the general
WITH
options are supported.COPY_ONLY
is mandatory.FILE_SNAPSHOT
andCREDENTIAL
aren't supported.- Tape options:
REWIND
,NOREWIND
,UNLOAD
, andNOUNLOAD
aren't supported. - Log-specific options:
NORECOVERY
,STANDBY
, andNO_TRUNCATE
aren't supported.
Limitations:
With a SQL Managed Instance, you can back up an instance database to a backup with up to 32 stripes, which is enough for databases up to 4 TB if backup compression is used.
You can't execute
BACKUP DATABASE ... WITH COPY_ONLY
on a database that's encrypted with service-managed Transparent Data Encryption (TDE). Service-managed TDE forces backups to be encrypted with an internal TDE key. The key can't be exported, so you can't restore the backup. Use automatic backups and point-in-time restore, or use customer-managed (BYOK) TDE instead. You also can disable encryption on the database.Native backups taken on a SQL Managed Instance can be restored to a SQL Server 2022 instance only. This is because SQL Managed Instance has higher internal database version compared to other versions of SQL Server. For more information, review Restore a SQL Managed Instance database backup to SQL Server 2022.
To back up or restore a database to/from an Azure storage, you can authenticate using either managed identity or shared access signature (SAS) which is an URI that grants you restricted access rights to Azure Storage resources Learn more on this. Using Access keys for these scenarios isn't supported.
The maximum backup stripe size by using the
BACKUP
command in SQL Managed Instance is 195 GB, which is the maximum blob size. Increase the number of stripes in the backup command to reduce individual stripe size and stay within this limit.Tip
To work around this limitation, when you back up a database from either SQL Server in an on-premises environment or in a virtual machine, you can:
- Back up to
DISK
instead of backing up toURL
. - Upload the backup files to Blob storage.
- Restore into SQL Managed Instance.
The
Restore
command in SQL Managed Instance supports bigger blob sizes in the backup files because a different blob type is used for storage of the uploaded backup files.- Back up to
For information about backups using T-SQL, see BACKUP.
Security
Auditing
The key differences between auditing in Microsoft Azure SQL and in SQL Server are:
- With SQL Managed Instance, auditing works at the server level. The
.xel
log files are stored in Azure Blob storage. - With Azure SQL Database, auditing works at the database level. The
.xel
log files are stored in Azure Blob storage. - With SQL Server, on-premises or in virtual machines, auditing works at the server level. Events are stored on file system or Windows event logs.
XEvent auditing in SQL Managed Instance supports Azure Blob storage targets. File and Windows logs aren't supported.
The key differences in the CREATE AUDIT
syntax for auditing to Azure Blob storage are:
- A new syntax
TO URL
is provided to specify the URL of the Azure Blob storage container where the.xel
files are placed. - The syntax
TO FILE
isn't supported because SQL Managed Instance can't access Windows file shares.
For more information, see:
Certificates
SQL Managed Instance can't access file shares and Windows folders, so the following constraints apply:
- The
CREATE FROM
/BACKUP TO
file isn't supported for certificates. - The
CREATE
/BACKUP
certificate fromFILE
/ASSEMBLY
isn't supported. Private key files can't be used.
See CREATE CERTIFICATE and BACKUP CERTIFICATE.
Workaround: Instead of creating backup of certificate and restoring the backup, get the certificate binary content and private key, store it as .sql file, and create from binary:
CREATE CERTIFICATE
FROM BINARY = asn_encoded_certificate
WITH PRIVATE KEY (<private_key_options>);
Credential
Managed identity, Azure Key Vault and SHARED ACCESS SIGNATURE
identities are supported. Windows users aren't supported.
See CREATE CREDENTIAL and ALTER CREDENTIAL.
Cryptographic providers
SQL Managed Instance can't access files, so cryptographic providers can't be created:
CREATE CRYPTOGRAPHIC PROVIDER
isn't supported. See CREATE CRYPTOGRAPHIC PROVIDER.ALTER CRYPTOGRAPHIC PROVIDER
isn't supported. See ALTER CRYPTOGRAPHIC PROVIDER.
Logins and users
SQL logins created by using
FROM CERTIFICATE
,FROM ASYMMETRIC KEY
, andFROM SID
are supported. See CREATE LOGIN. Server principals (logins) are created at the server level, and users (database principals) are created at the database level. Microsoft Entra logins created with the CREATE LOGIN syntax and Microsoft Entra users created with the CREATE USER FROM LOGIN syntax are supported. When creating a user and specifyingFROM LOGIN
, that user is associated to the login, and inherits the server roles and permissions assigned to it.SQL Managed Instance supports creating contained database users based on Microsoft Entra identities with the syntax
CREATE USER [AADUser/AAD group] FROM EXTERNAL PROVIDER
. Users created this way aren't associated to server principals, even if a server principal with the same name exists in themaster
database.Windows logins created with the
CREATE LOGIN ... FROM WINDOWS
syntax aren't supported. Use Microsoft Entra logins and users.The Microsoft Entra admin for the instance has unrestricted admin privileges.
Some features do not support using Microsoft Entra logins in cross-instance interactions, but only within a single SQL Managed Instance, such as SQL Server replication for example. Linked server feature though supports cross-instance authentication using Microsoft Entra server principals (logins).
Setting a Microsoft Entra login mapped to a Microsoft Entra group as the database owner isn't supported. A member of the Microsoft Entra group can be a database owner, even if the login hasn't been created in the database.
Impersonation of Microsoft Entra server-level principals by using other Microsoft Entra principals is supported, such as the EXECUTE AS clause. EXECUTE AS limitations are:
EXECUTE AS USER isn't supported for Microsoft Entra users when the name differs from the login name. An example is when the user is created through the syntax
CREATE USER [myAadUser] FROM LOGIN [john@contoso.com]
and impersonation is attempted throughEXEC AS USER = myAadUser
. When you create a USER from a Microsoft Entra login, specify the user_name as the same login_name from LOGIN.Only SQL Server-level logins that are part of the
sysadmin
role can execute the following operations that target Microsoft Entra principals:- EXECUTE AS USER
- EXECUTE AS LOGIN
To impersonate a user with EXECUTE AS statement, the user needs to be mapped directly to Microsoft Entra login. Users that are members of Microsoft Entra groups mapped into Microsoft Entra server principals can't effectively be impersonated with EXECUTE AS statement, even though the caller has the impersonate permissions on the specified user name.
Database export/import using bacpac files are supported for Microsoft Entra users in SQL Managed Instance using either SSMS V18.4 or later, or SqlPackage.
- The following configurations are supported using database bacpac file:
- Export/import a database between different manage instances within the same Microsoft Entra domain.
- Export a database from SQL Managed Instance and import to SQL Database within the same Microsoft Entra domain.
- Export a database from SQL Database and import to SQL Managed Instance within the same Microsoft Entra domain.
- Export a database from SQL Managed Instance and import to SQL Server (version 2012 or later).
- In this configuration, all Microsoft Entra users are created as SQL Server database principals (users) without logins. The type of users is
SQL
and is visible asSQL_USER
insys.database_principals
. Their permissions and roles remain in the SQL Server database metadata and can be used for impersonation. However, they can't be used to access and sign in to the SQL Server using their credentials.
- In this configuration, all Microsoft Entra users are created as SQL Server database principals (users) without logins. The type of users is
- The following configurations are supported using database bacpac file:
Only the server-level principal login, which is created by the SQL Managed Instance provisioning process, members of the server roles, such as
securityadmin
orsysadmin
, or other logins with ALTER ANY LOGIN permission at the server level can create Microsoft Entra server principals (logins) in themaster
database for SQL Managed Instance.SQL auth-based logins must be assigned the
sysadmin
role to create logins for Microsoft Entra identities.The login must be a member of the same Microsoft Entra tenant that the Azure SQL Managed Instance is hosted in.
Microsoft Entra server principals (logins) are visible in Object Explorer starting with SQL Server Management Studio 18.0 preview 5.
A server principal with sysadmin access level is automatically created for the Microsoft Entra admin once it's enabled on an instance.
During authentication, the following sequence is applied to resolve the authenticating principal:
- If the Microsoft Entra account is directly mapped to a Microsoft Entra login, which is present in
sys.server_principals
as type "E," grant access and apply permissions of that login. - If the Microsoft Entra account is a member of a group that's mapped to a Microsoft Entra login, which is present in
sys.server_principals
as type "X," grant access and apply permissions of that login. - If the Microsoft Entra account exists as directly mapped to a Microsoft Entra user in a database, which is present in
sys.database_principals
as type "E," grant access and apply permissions of the Microsoft Entra database user. - If the Microsoft Entra account is a member of a Microsoft Entra group that's mapped to a Microsoft Entra user in a database, which is present in
sys.database_principals
as type "X," grant access and apply permissions of the Microsoft Entra group user.
- If the Microsoft Entra account is directly mapped to a Microsoft Entra login, which is present in
Service key and service master key
- Master key backup isn't supported (managed by SQL Database service).
- Master key restore isn't supported (managed by SQL Database service).
- Service master key backup isn't supported (managed by SQL Database service).
- Service master key restore isn't supported (managed by SQL Database service).
Configuration
Buffer pool extension
- Buffer pool extension isn't supported.
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION
isn't supported. See ALTER SERVER CONFIGURATION.
Collation
The default instance collation is SQL_Latin1_General_CP1_CI_AS
and can be specified as a creation parameter. See Collations.
Compatibility levels
- Supported compatibility levels are 100, 110, 120, 130, 140, 150 and 160.
- Compatibility levels below 100 aren't supported.
- The default compatibility level for new databases is 150. For restored databases, the compatibility level remains unchanged if it was 100 and above.
See ALTER DATABASE Compatibility Level.
Database mirroring
Database mirroring isn't supported.
ALTER DATABASE SET PARTNER
andSET WITNESS
options aren't supported.CREATE ENDPOINT … FOR DATABASE_MIRRORING
isn't supported.
For more information, see ALTER DATABASE SET PARTNER and SET WITNESS and CREATE ENDPOINT … FOR DATABASE_MIRRORING.
Database options
- Multiple log files aren't supported.
- In-memory objects aren't supported in the General Purpose service tier.
- There's a limit of 280 files per General Purpose instance, which implies a maximum of 280 files per database. Both data and log files in the General Purpose tier are counted toward this limit. The Business Critical tier supports 32,767 files per database.
- The database can't contain filegroups that contain FILESTREAM data. Restore fails if
.bak
containsFILESTREAM
data. - Every file is placed in Azure Blob storage. IO and throughput per file depend on the size of each individual file.
CREATE DATABASE statement
The following limitations apply to CREATE DATABASE
:
Files and filegroups can't be defined.
A memory-optimized filegroup and file are automatically added and are called XTP.
The
CONTAINMENT
option isn't supported.WITH
options aren't supported.Tip
As a workaround, use
ALTER DATABASE
afterCREATE DATABASE
to set database options to add files or to set containment.The
FOR ATTACH
option isn't supported.The
AS SNAPSHOT OF
option isn't supported.
For more information, see CREATE DATABASE.
ALTER DATABASE statement
Some file properties can't be set or changed:
- A file path can't be specified in the
ALTER DATABASE ADD FILE (FILENAME='path')
T-SQL statement. RemoveFILENAME
from the script because SQL Managed Instance automatically places the files. - A file name can't be changed by using the
ALTER DATABASE
statement. - Altering XTP file or filegroup isn't allowed.
The following options are set by default and can't be changed:
MULTI_USER
ENABLE_BROKER
AUTO_CLOSE OFF
The following options can't be modified:
AUTO_CLOSE
AUTOMATIC_TUNING(CREATE_INDEX=ON|OFF)
AUTOMATIC_TUNING(DROP_INDEX=ON|OFF)
DISABLE_BROKER
EMERGENCY
ENABLE_BROKER
FILESTREAM
HADR
NEW_BROKER
OFFLINE
PAGE_VERIFY
PARTNER
READ_ONLY
RECOVERY BULK_LOGGED
RECOVERY_SIMPLE
REMOTE_DATA_ARCHIVE
RESTRICTED_USER
SINGLE_USER
WITNESS
Some ALTER DATABASE
statements (for example, SET CONTAINMENT) might transiently fail, for example during the automated database backup or right after a database is created. In this case ALTER DATABASE
statement should be retried. For more information on related error messages, see the Remarks section.
For more information, see ALTER DATABASE.
SQL Server Agent
- Enabling and disabling SQL Server Agent is currently not supported in SQL Managed Instance. SQL Agent is always running.
- Job schedule trigger based on an idle CPU isn't supported.
- SQL Server Agent settings are read only. The procedure
sp_set_agent_properties
isn't supported in SQL Managed Instance. - Jobs
- T-SQL job steps are supported.
- The following replication jobs are supported:
- Transaction-log reader
- Snapshot
- Distributor
- SSIS job steps are supported.
- Other types of job steps aren't currently supported:
- The merge replication job step isn't supported.
- Queue Reader isn't supported.
- Command shell isn't yet supported.
- SQL Managed Instance can't access external resources, for example, network shares via robocopy.
- SQL Server Analysis Services isn't supported.
- Notifications are partially supported.
- Email notification is supported, although it requires that you configure a Database Mail profile. SQL Server Agent can use only one Database Mail profile, and it must be called
AzureManagedInstance_dbmail_profile
.- Pager isn't supported.
- NetSend isn't supported.
- Alerts aren't yet supported.
- Proxies aren't supported.
- EventLog isn't supported.
- User must be directly mapped to the Microsoft Entra server login to create, modify, or execute SQL Agent jobs. Users that aren't directly mapped, for example, users that belong to a Microsoft Entra group that has the rights to create, modify or execute SQL Agent jobs, will not effectively be able to perform those actions. This is due to SQL Managed Instance impersonation and EXECUTE AS limitations.
- The Multi Server Administration feature for master/target (MSX/TSX) jobs aren't supported.
For information about SQL Server Agent, see SQL Server Agent.
Tables
The following table types aren't supported:
- FILESTREAM
- FILETABLE
- EXTERNAL TABLE (except PolyBase)
- MEMORY_OPTIMIZED (not supported only in General Purpose tier)
For information about how to create and alter tables, see CREATE TABLE and ALTER TABLE.
Functionalities
BULK INSERT / OPENROWSET
SQL Managed Instance can't access file shares and Windows folders, so the files must be imported from Azure Blob storage:
DATASOURCE
is required in theBULK INSERT
command while you import files from Azure Blob storage. See BULK INSERT.DATASOURCE
is required in theOPENROWSET
function when you read the content of a file from Azure Blob storage. See OPENROWSET.OPENROWSET
can be used to read data from Azure SQL Database, Azure SQL Managed Instance, or SQL Server instances. Other sources such as Oracle databases or Excel files aren't supported.
CLR
A SQL Managed Instance can't access file shares and Windows folders, so the following constraints apply:
- Only
CREATE ASSEMBLY FROM BINARY
is supported. See CREATE ASSEMBLY FROM BINARY. CREATE ASSEMBLY FROM FILE
isn't supported. See CREATE ASSEMBLY FROM FILE.ALTER ASSEMBLY
can't reference files. See ALTER ASSEMBLY.
Database Mail (db_mail)
sp_send_dbmail
can't send attachments using @file_attachments parameter. Local file system and external shares or Azure Blob Storage aren't accessible from this procedure.- See the known issues related to
@query
parameter and authentication.
DBCC
Undocumented DBCC statements that are enabled in SQL Server aren't supported in SQL Managed Instance.
- Only a limited number of Global Trace flags are supported. Session-level
Trace flags
aren't supported. See Trace flags. - DBCC TRACEOFF and DBCC TRACEON work with the limited number of global trace-flags.
- DBCC CHECKDB with options REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, and REPAIR_REBUILD can't be used because database can't be set in
SINGLE_USER
mode - see ALTER DATABASE differences. Potential database corruption is handled by the Azure support team. Contact Azure support if there is any indication of database corruption.
Distributed transactions
T-SQL and .NET based distributed transactions across managed instances are generally available. Other scenarios, such as XA transactions, distributed transactions between managed instances and other participants and more, are supported with DTC for Azure SQL Managed Instance, which is available in public preview.
Extended Events
Some Windows-specific targets for Extended Events (XEvents) aren't supported:
- The
etw_classic_sync
target isn't supported. Store.xel
files in Azure Blob storage. See etw_classic_sync target. - The
event_file
target isn't supported. Store.xel
files in Azure Blob storage. See event_file target.
External libraries
In-database R and Python external libraries are supported in limited public preview. See Machine Learning Services in Azure SQL Managed Instance (preview).
FILESTREAM and FileTable
- FILESTREAM data isn't supported.
- The database can't contain filegroups with
FILESTREAM
data. FILETABLE
isn't supported.- Tables can't have
FILESTREAM
types. - The following functions aren't supported:
GetPathLocator()
GET_FILESTREAM_TRANSACTION_CONTEXT()
PathName()
GetFileNamespacePat)
FileTableRootPath()
For more information, see FILESTREAM and FileTables.
Full-text Semantic Search
Semantic Search isn't supported.
Linked servers
Linked servers in SQL Managed Instance support a limited number of targets:
- Supported targets are SQL Managed Instance, SQL Database, Azure Synapse SQL serverless and dedicated pools, and SQL Server instances.
- Targets that aren't supported are files, Analysis Services, and other RDBMS. Try to use native CSV import from Azure Blob Storage using
BULK INSERT
orOPENROWSET
as an alternative for file import, or load files using a serverless SQL pool in Azure Synapse Analytics.
Operations:
sp_dropserver
is supported for dropping a linked server. See sp_dropserver.- The
OPENROWSET
function can be used to execute queries only on SQL Server instances. They can be either managed, on-premises, or in virtual machines. See OPENROWSET. - The OPENDATASOURCE function can be used to execute queries only on SQL Server instances. They can be either managed, on-premises, or in virtual machines. An example is
SELECT * FROM OPENDATASOURCE('SQLNCLI', '...').AdventureWorks2022.HumanResources.Employee
. Only theSQLNCLI
,SQLNCLI11
,SQLOLEDB
, andMSOLEDBSQL
values are supported as a provider. The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 and SQL Server Management Studio 19 (SSMS). The SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) aren't recommended for new development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server or the latest Microsoft ODBC Driver for SQL Server going forward. - Linked servers can't be used to read files (Excel, CSV) from the network shares. Try to use BULK INSERT, OPENROWSET that reads CSV files from Azure Blob Storage, or a linked server that references a serverless SQL pool in Synapse Analytics. Track this requests on SQL Managed Instance Feedback item
Linked servers on Azure SQL Managed Instance support SQL authentication and Microsoft Entra authentication.
PolyBase
Data virtualization with Azure SQL Managed Instance enables you to execute Transact-SQL (T-SQL) queries against data from files stored in Azure Data Lake Storage Gen2 or Azure Blob Storage, and combine it with locally stored relational data using joins. Parquet and delimited text (CSV) file formats are directly supported. The JSON file format is indirectly supported by specifying the CSV file format where queries return every document as a separate row. It's possible to parse rows further using JSON_VALUE
and OPENJSON
. For general information about PolyBase, see PolyBase.
Further, CREATE EXTERNAL TABLE AS SELECT (CETAS) allows you to export data from your SQL managed instance into an external storage account. You can use CETAS to create an external table on top of Parquet or CSV files Azure Blob storage or Azure Data Lake Storage (ADLS) Gen2. CETAS can also export, in parallel, the results of a T-SQL SELECT statement into the created external table.
Replication
- Snapshot and Bi-directional replication types are supported. Merge replication, Peer-to-peer replication, and updatable subscriptions aren't supported.
- Transactional Replication is available for SQL Managed Instance with some constraints:
- All types of replication participants (Publisher, Distributor, Pull Subscriber, and Push Subscriber) can be placed on SQL Managed Instance, but the publisher and the distributor must be either both in the cloud or both on-premises.
- SQL Managed Instance can communicate with the recent versions of SQL Server. For more information, see the supported versions matrix.
- Transactional Replication has some additional networking requirements.
For more information about configuring transactional replication, see the following tutorials:
- Replication between a SQL MI publisher and SQL MI subscriber
- Replication between an SQL MI publisher, SQL MI distributor, and SQL Server subscriber
RESTORE statement
- Supported syntax:
RESTORE DATABASE
RESTORE FILELISTONLY
RESTORE HEADERONLY
RESTORE LABELONLY
RESTORE VERIFYONLY
- Unsupported syntax:
RESTORE LOGONLY
RESTORE REWINDONLY
- Source:
FROM URL
(Azure Blob storage) is the only supported option.FROM DISK
/TAPE
/backup device isn't supported.- Backup sets aren't supported.
WITH
options aren't supported. Restore attempts includingWITH
likeDIFFERENTIAL
,STATS
,REPLACE
, and so on, will fail.
A database restore operation is asynchronous and retryable in Azure SQL Managed Instance. You might get an error in SSMS if the connection fails or a time-out expires. Azure SQL Managed Instance keeps trying to restore the database in the background, and you can track the progress of the restore process by using the sys.dm_exec_requests and sys.dm_operation_status dynamic management views.
The following database options are set or overridden and can't be changed later:
NEW_BROKER
if the broker isn't enabled in the .bak file.ENABLE_BROKER
if the broker isn't enabled in the .bak file.AUTO_CLOSE=OFF
if a database in the .bak file hasAUTO_CLOSE=ON
.RECOVERY FULL
if a database in the .bak file hasSIMPLE
orBULK_LOGGED
recovery model.- A memory-optimized filegroup is added and called XTP if it wasn't in the source .bak file.
- Any existing memory-optimized filegroup is renamed to XTP.
SINGLE_USER
andRESTRICTED_USER
options are converted toMULTI_USER
.
Limitations:
- Backups of the corrupted databases might be restored depending on the type of the corruption, but automated backups aren't taken until the corruption is fixed. Make sure that you run
DBCC CHECKDB
on the source SQL Managed Instance and use backupWITH CHECKSUM
in order to prevent this issue. - Restore of
.BAK
file of a database that contains any limitation described in this document (for example,FILESTREAM
orFILETABLE
objects) can't be restored on SQL Managed Instance. .BAK
files that contain multiple backup sets can't be restored..BAK
files that contain multiple log files can't be restored.- Backups that contain databases bigger than 8 TB, active in-memory OLTP objects, or number of files that would exceed 280 files per instance can't be restored on a General Purpose instance.
- Backups that contain databases bigger than 4 TB or in-memory OLTP objects with the total size larger than the size described in resource limits can't be restored on Business Critical instance. For information about restore statements, see RESTORE statements.
Important
The same limitations apply to built-in point-in-time restore operation. As an example, General Purpose database greater than 4 TB cannot be restored on Business Critical instance. Business Critical database with In-memory OLTP files or more than 280 files cannot be restored on General Purpose instance.
Service broker
Cross-instance service broker message exchange is supported only between Azure SQL Managed Instances:
CREATE ROUTE
: You can't useCREATE ROUTE
withADDRESS
other thanLOCAL
or DNS name of another SQL Managed Instance. Port is always 4022.ALTER ROUTE
: You can't useALTER ROUTE
withADDRESS
other thanLOCAL
or DNS name of another SQL Managed Instance. Port is always 4022.
Transport security is supported, dialog security isn't:
CREATE REMOTE SERVICE BINDING
isn't supported.
Service broker is enabled by default and can't be disabled. The following ALTER DATABASE options aren't supported:
ENABLE_BROKER
DISABLE_BROKER
Stored procedures, functions, and triggers
NATIVE_COMPILATION
isn't supported in the General Purpose tier.- The following sp_configure options aren't supported:
allow polybase export
allow updates
filestream_access_level
remote access
remote data archive
remote proc trans
scan for startup procs
- The following sp_configure options are ignored and have no effect:
Ole Automation Procedures
sp_execute_external_scripts
is only supported for Machine Learning Services for SQL MI, otherwisesp_execute_external_scripts
isn't supported for SQL Managed Instance. See sp_execute_external_scripts.xp_cmdshell
isn't supported. See xp_cmdshell.Extended stored procedures
aren't supported, and this includessp_addextendedproc
andsp_dropextendedproc
. This functionality won't be supported because it's on a deprecation path for SQL Server. For more information, see Extended Stored Procedures.sp_attach_db
,sp_attach_single_file_db
, andsp_detach_db
aren't supported. See sp_attach_db, sp_attach_single_file_db, and sp_detach_db.
System functions and variables
The following variables, functions, and views return different results:
SERVERPROPERTY('EngineEdition')
returns the value 8. This property uniquely identifies a SQL Managed Instance. See SERVERPROPERTY.SERVERPROPERTY('InstanceName')
returns NULL because the concept of instance as it exists for SQL Server doesn't apply to SQL Managed Instance. See SERVERPROPERTY('InstanceName').@@SERVERNAME
returns a full DNS "connectable" name, for example,my-managed-instance.wcus17662feb9ce98.database.windows.net
. See @@SERVERNAME.SYS.SERVERS
returns a full DNS "connectable" name, such asmyinstance.domain.database.windows.net
for the properties "name" and "data_source." See SYS.SERVERS.@@SERVICENAME
returns NULL because the concept of service as it exists for SQL Server doesn't apply to SQL Managed Instance. See @@SERVICENAME.SUSER_ID
is supported. It returns NULL if the Microsoft Entra login isn't insys.syslogins
. See SUSER_ID.SUSER_SID
isn't supported. The wrong data is returned, which is a temporary known issue. See SUSER_SID.
Environment constraints
Subnet
- You can't place any other resources (for example virtual machines) in the subnet where you have deployed your SQL Managed Instance. Deploy these resources using a different subnet.
- Subnet must have sufficient number of available IP addresses. Minimum is to have at least 32 IP addresses in the subnet.
- The number of vCores and types of instances that you can deploy in a region have some constraints and limits.
- There is a networking configuration that must be applied on the subnet.
Virtual network
- Virtual network can be deployed using Resource Model. Classic Model doesn't support virtual network (VNet) deployment.
- After a SQL managed instance is created, moving the SQL managed instance or VNet to another resource group or subscription isn't supported.
- For SQL managed instances hosted in virtual clusters that are created before September 22, 2020, VNet global peering isn't supported. You can connect to these resources via ExpressRoute or VNet-to-VNet through VNet Gateways.
Failover groups
System databases aren't replicated to the secondary instance in a failover group. Therefore, scenarios that depend on objects from the system databases are impossible on the secondary instance unless the objects are manually created on the secondary.
tempdb
- The maximum file size of the
tempdb
system database can't be greater than 24 GB per core on a General Purpose tier. The maximumtempdb
size on a Business Critical tier is limited by the SQL Managed Instance storage size.tempdb
log file size is limited to 120 GB on General Purpose tier. Some queries might return an error if they need more than 24 GB per core intempdb
or if they produce more than 120 GB of log data. tempdb
is always split into 12 data files: 1 primary, also calledmaster
, data file, and 11 non-primary data files. The file structure can't be changed and new files can't be added totempdb
.- Memory-optimized TempDB metadata, a new SQL Server 2019 in-memory database feature, isn't supported.
- Objects created in the
model
database can't be auto-created intempdb
after a restart or a failover becausetempdb
doesn't get its initial object list from themodel
database. You must create objects intempdb
manually after each restart or a failover.
msdb
The following schemas in the msdb
system database in SQL Managed Instance must be owned by their respective predefined roles:
- General roles
- TargetServersRole
- Fixed database roles
- SQLAgentUserRole
- SQLAgentReaderRole
- SQLAgentOperatorRole
- DatabaseMail roles:
- DatabaseMailUserRole
- Integration services roles:
- db_ssisadmin
- db_ssisltduser
- db_ssisoperator
Important
Changing the predefined role names, schema names and schema owners by customers will affect the normal operation of the service. Any changes made to these will be reverted back to the predefined values as soon as detected, or at the next service update at the latest to ensure normal service operation.
Error logs
SQL Managed Instance places verbose information in error logs. There are many internal system events that are logged in the error log. Use a custom procedure to read error logs that filters out some irrelevant entries. For more information, see SQL Managed Instance – sp_readmierrorlog or SQL Managed Instance extension(preview) for Azure Data Studio.
Changing the number of retained error logs is unsupported.