Azure SQL Database Managed Instance T-SQL differences from SQL Server

Azure SQL Database Managed Instance (preview) provides high compatibility with on-premises SQL Server Database Engine. Most of the SQL Server Database Engine features are supported in Managed Instance. Since there are still some differences in syntax and behavior, this article summarizes and explains these differences.

T-SQL differences from SQL Server

This section summarizes key differences in T-SQL syntax and behavior between Managed Instance and on-premises SQL Server Database Engine, as well as unsupported features.

Always-On availability

High availability is built into Managed Instance and cannot be controlled by users. The following statements are not supported:

Auditing

The key differences between SQL Audit in Managed Instance, Azure SQL Database, and SQL Server on-premises are:

  • In Managed Instance, SQL Audit works at the server level and stores .xel files on Azure blob storage account.
  • In Azure SQL Database, SQL Audit works at the database level.
  • In SQL Server on-premises / virtual machine, SQL Audit works at the server level, but stores events on files system/windows event logs.

XEvent auditing in Managed Instance supports Azure blob storage targets. File and windows logs are not supported.

The key differences in the CREATE AUDIT syntax for Auditing to Azure blob storage are:

  • A new syntax TO URL is provided and enables you to specify URL of the Azure blob Storage container where .xel files will be placed
  • The syntax TO FILE is not supported because Managed Instance cannot access Windows file shares.

For more information, see:

Backup

Managed Instance has automatic backups, and enables users to create full database COPY_ONLY backups. Differential, log, and file snapshot backups are not supported.

  • Managed Instance can back up a database only to an Azure Blob Storage account:
    • Only BACKUP TO URL is supported
    • FILE, TAPE, and backup devices are not supported
  • Most of the general WITH options are supported
    • COPY_ONLY is mandatory
    • FILE_SNAPSHOT not supported
    • Tape options: REWIND, NOREWIND, UNLOAD, and NOUNLOAD are not supported
    • Log-specific options: NORECOVERY, STANDBY, and NO_TRUNCATE are not supported

Limitations:

  • Managed Instance can back up a database to a backup with up to 32 stripes, which is enough for the databases up to 4 TB if backup compression is used.
  • Max backup stripe size is 195 GB (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 on-premises, backup to DISK instead of backup to URL, upload backup file to blob, then restore. Restore supports bigger files because a different blob type is used.

For information about backups using T-SQL, see BACKUP.

Buffer pool extension

Bulk insert / openrowset

Managed Instance cannot access file shares and Windows folders, so the files must be imported from Azure blob storage.

  • DATASOURCE is required in BULK INSERT command while importing files from Azure blob storage. See BULK INSERT.
  • DATASOURCE is required in OPENROWSET function when you read a content of a file from Azure blob storage. See OPENROWSET.

Certificates

Managed Instance cannot access file shares and Windows folders, so the following constraints apply:

  • CREATE FROM/BACKUP TO file is not supported for certificates
  • CREATE/BACKUP certificate from FILE/ASSEMBLY is not supported. Private key files cannot be used.

See CREATE CERTIFICATE and BACKUP CERTIFICATE.

Tip

Workaround: script certificate/private key, store as .sql file and create from binary:

CREATE CERTIFICATE  
 FROM BINARY = asn_encoded_certificate    
WITH PRIVATE KEY ( <private_key_options> ) 

CLR

Managed Instance cannot access file shares and Windows folders, so the following constraints apply:

Compatibility levels

  • Supported compatibility levels are: 100, 110, 120, 130, 140
  • Compatibility levels below 100 are not supported.
  • The default compatibility level for new databases is 140. For restored databases, compatibility level will remain unchanged if it was 100 and above.

See ALTER DATABASE Compatibility Level.

Credential

Only Azure Key Vault and SHARED ACCESS SIGNATURE identities are supported. Windows users are not supported.

See CREATE CREDENTIAL and ALTER CREDENTIAL.

Cryptographic providers

Managed Instance cannot access files so cryptographic providers cannot be created:

Collation

Server collation is SQL_Latin1_General_CP1_CI_AS and cannot be changed. See Collations.

Database options

  • Multiple log files are not supported.
  • In-memory objects are not supported in the General Purpose service tier.
  • There is a limit of 280 files per instance implying max 280 files per database. Both data and log files are counted toward this limit.
  • Database cannot contain filegroups containing filestream data. Restore will fail if .bak contains FILESTREAM data.
  • Every file is placed in Azure Premium storage. IO and throughput per file depend on the size of each individual file, in the same way as they do for Azure Premium Storage disks. See Azure Premium disk performance

CREATE DATABASE statement

The following are CREATE DATABASE limitations:

  • Files and filegroups cannot be defined.
  • CONTAINMENT option is not supported.
  • WITHoptions are not supported.

    Tip

    As workaround, use ALTER DATABASE after CREATE DATABASE to set database options to add files or to set containment.

  • FOR ATTACH option is not supported

  • AS SNAPSHOT OF option is not supported

For more information, see CREATE DATABASE.

ALTER DATABASE statement

Some file properties cannot be set or changed:

  • File path cannot be specified in ALTER DATABASE ADD FILE (FILENAME='path') T-SQL statement. Remove FILENAME from the script because Managed Instance automatically places the files.
  • File name cannot be changed using ALTER DATABASE statement.

The following options are set by default and cannot be changed:

  • MULTI_USER
  • ENABLE_BROKER ON
  • AUTO_CLOSE OFF

The following options cannot 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

Modify name is not supported.

For more information, see ALTER DATABASE.

Database mirroring

Database mirroring is not supported.

  • ALTER DATABASE SET PARTNER and SET WITNESS options are not supported.
  • CREATE ENDPOINT … FOR DATABASE_MIRRORING is not supported.

For more information, see ALTER DATABASE SET PARTNER and SET WITNESS and CREATE ENDPOINT … FOR DATABASE_MIRRORING.

DBCC

Undocumented DBCC statements that are enabled in SQL Server are not supported in Managed Instance.

Extended Events

Some Windows-specific targets for XEvents are not supported:

  • etw_classic_sync target is not supported. Store .xel files on Azure blob storage. See etw_classic_sync target.
  • event_file targetis not supported. Store .xel files on Azure blob storage. See event_file target.

External libraries

In-database R and Python external libraries are not yet supported. See SQL Server Machine Learning Services.

Filestream and Filetable

  • filestream data is not supported.
  • Database cannot contain filegroups with FILESTREAM data
  • FILETABLE is not supported
  • Tables cannot have FILESTREAM types
  • The following functions are not supported:
    • GetPathLocator()
    • GET_FILESTREAM_TRANSACTION_CONTEXT()
    • PathName()
    • GetFileNamespacePath()
    • FileTableRootPath()

For more information, see FILESTREAM and FileTables.

Semantic Search is not supported.

Linked servers

Linked servers in Managed Instance support limited number of targets:

  • Supported targets: SQL Server and SQL Database
  • Not supported targets: files, Analysis Services, and other RDBMS.

Operations

  • Cross-instance write transactions are not supported.
  • sp_dropserver is supported for dropping a linked server. See sp_dropserver.
  • OPENROWSET function can be used to execute queries only on SQL Server instances (either managed, on-premises, or in Virtual Machines). See OPENROWSET.
  • OPENDATASOURCE function can be used to execute queries only on SQL Server instances (either managed, on-premises, or in virtual machines). Only SQLNCLI, SQLNCLI11, and SQLOLEDB values are supported as provider. For example: SELECT * FROM OPENDATASOURCE('SQLNCLI', '...').AdventureWorks2012.HumanResources.Employee. See OPENDATASOURCE.

Logins / users

  • SQL logins created FROM CERTIFICATE, FROM ASYMMETRIC KEY, and FROM SID are supported. See CREATE LOGIN.
  • Windows logins created with CREATE LOGIN ... FROM WINDOWS syntax are not supported.
  • Azure Active Directory (Azure AD) user who created the instance has unrestricted admin privileges.
  • Non-administrator Azure Active Directory (Azure AD) database-level users can be created using CREATE USER ... FROM EXTERNAL PROVIDER syntax. See CREATE USER ... FROM EXTERNAL PROVIDER

Polybase

External tables referencing the files in HDFS or Azure blob storage are not supported. For information about Polybase, see Polybase.

Replication

Replication is not yet supported. For information about Replication, see SQL Server Replication.

RESTORE statement

  • Supported syntax
    • RESTORE DATABASE
    • RESTORE FILELISTONLY ONLY
    • RESTORE HEADER ONLY
    • RESTORE LABELONLY ONLY
    • RESTORE VERIFYONLY ONLY
  • Unsupported syntax
    • RESTORE LOG ONLY
    • RESTORE REWINDONLY ONLY
  • Source
    • FROM URL (Azure blob storage) is only supported option.
    • FROM DISK/TAPE/backup device is not supported.
    • Backup sets are not supported.
  • WITH options are not supported (No DIFFERENTIAL, STATS, etc.)
  • ASYNC RESTORE - Restore continues even if client connection breaks. If your connection is dropped, you can check sys.dm_operation_status view for the status of a restore operation (as well as for CREATE and DROP database). See sys.dm_operation_status.

The following database options are set/overridden and cannot be changed later:

  • NEW_BROKER (if broker is not enabled in .bak file)
  • ENABLE_BROKER (if broker is not enabled in .bak file)
  • AUTO_CLOSE=OFF (if a database in .bak file has AUTO_CLOSE=ON)
  • RECOVERY FULL (if a database in .bak file has SIMPLE or BULK_LOGGED recovery mode)
  • Memory optimized filegroup is added and called XTP if it was not in the source .bak file
  • Any existing memory optimized filegroup is renamed to XTP
  • SINGLE_USER and RESTRICTED_USER options are converted to MULTI_USER
    Limitations:
  • .BAK files containing multiple backup sets cannot be restored.
  • .BAK files containing multiple log files cannot be restored.
  • Restore will fail if .bak contains FILESTREAM data.
  • Backups containing databases that have active In-memory objects cannot currently be restored.
  • Backups containing databases where at some point In-Memory objects existed cannot currently be restored.
  • Backups containing databases in read-only mode cannot currently be restored. This limitation will be removed soon.

For information about Restore statements, see RESTORE Statements.

Service broker

  • Cross-instance service broker is not supported
    • sys.routes - Prerequisite: select address from sys.routes. Address must be LOCAL on every route. See sys.routes.
    • CREATE ROUTE - you cannot CREATE ROUTE with ADDRESS other than LOCAL. See CREATE ROUTE.
    • ALTER ROUTE cannot ALTER ROUTE with ADDRESS other than LOCAL. See ALTER ROUTE.

Service key and service master key

Stored procedures, functions, triggers

SQL Server Agent

  • SQL Agent settings are read only. Procedure sp_set_agent_properties is not supported in Managed Instance.
  • Jobs - only T-SQL job steps are currently supported (more steps will be added during public preview).
    • SSIS is not yet supported.
    • Replication is not yet supported
    • Transaction-Log reader is not yet supported.
    • Snapshot is not yet supported.
    • Distributor is not yet supported.
    • Merge is not supported.
    • Queue Reader is not supported.
    • Command shell is not yet supported.
    • Managed Instance cannot access external resources (for example, network shares via robocopy).
    • PowerShell is not yet supported.
    • Analysis Services are not supported.
  • Notifications are partially supported.
    • Email notification is supported, requires configuring a Database Mail profile. There can be only one database mail profile and it must be called AzureManagedInstance_dbmail_profile in public preview (temporary limitation).
    • Pager is not supported.
    • NetSend is not supported.
    • Alerts are not yet not supported.
    • Proxies are not supported.
  • Eventlog is not supported.

The following features are currently not supported but will be enabled in future:

  • Proxies
  • Scheduling jobs on idle CPU
  • Enabling/disabling Agent
  • Alerts

For information about SQL Server Agent, see SQL Server Agent.

Tables

The following are not supported:

  • FILESTREAM
  • FILETABLE
  • EXTERNAL TABLE
  • MEMORY_OPTIMIZED

For information about creating and altering tables, see CREATE TABLE and ALTER TABLE.

Behavior changes

The following variables, functions, and views return different results:

  • SERVERPROPERTY('EngineEdition') returns value 8. This property uniquely identifies Managed Instance. See SERVERPROPERTY.
  • SERVERPROPERTY('InstanceName') returns the short instance name, for example, 'myserver'. See SERVERPROPERTY('InstanceName').
  • @@SERVERNAME returns full DNS 'connectable' name, for example, my-managed-instance.wcus17662feb9ce98.database.windows.net. See @@SERVERNAME.
  • SYS.SERVERS - returns full DNS 'connectable' name, such as myinstance.domain.database.windows.net for properties 'name' and 'data_source'. See SYS.SERVERS.
  • @@SERVERNAME returns full DNS 'connectable' name, such as my-managed-instance.wcus17662feb9ce98.database.windows.net. See @@SERVERNAME.
  • SYS.SERVERS - returns full DNS 'connectable' name, such as myinstance.domain.database.windows.net for properties 'name' and 'data_source'. See SYS.SERVERS.
  • @@SERVICENAME returns NULL, as it makes no sense in Managed Instance environment. See @@SERVICENAME.
  • SUSER_ID is supported. Returns NULL if AAD login is not in sys.syslogins. See SUSER_ID.
  • SUSER_SID is not supported. Returns wrong data (temporary known issue). See SUSER_SID.
  • GETDATE() and other built-in date/time functions always returns time in UTC time zone. See GETDATE.

Known issues and limitations

TEMPDB size

tempdb is split into 12 files each with max size 14 GB per file. This maximum size per file cannot be changed and new files cannot be added to tempdb. This limitation will be removed soon. Some queries might return an error if they need more than 168 GB in tempdb.

Exceeding storage space with small database files

Each Managed Instance has up to 35 TB storage reserved for Azure Premium Disk space, and each database file is placed on a separate physical disk. Disk sizes can be 128 GB, 256 GB, 512 GB, 1 TB, or 4 TB. Unused space on disk is not charged, but the total sum of Azure Premium Disk sizes cannot exceed 35 TB. In some cases, a Managed Instance that does not need 8 TB in total might exceed the 35 TB Azure limit on storage size, due to internal fragmentation.

For example, a Managed Instance could have one file with 1.2 TB size that uses a 4 TB disk, and 248 files with 1 GB each that are placed on 248 disks with 128 GB size. In this example, the total disk storage size is 1 x 4 TB + 248 x 128 GB = 35 TB. However, total reserved instance size for databases is 1 x 1.2 TB + 248 x 1 GB = 1.4 TB. This illustrates that under certain circumstance, due to a very specific distribution of files, a Managed Instance might reach Azure Premium Disk storage limit where you might not expect it to.

There would be no error on existing databases and they can grow without any problem if new files are not added, but the new databases cannot be created or restored because there is not enough space for new disk drives, even if the total size of all databases does not reach the instance size limit. The error that is returned in that case is not clear.

Incorrect configuration of SAS key during database restore

RESTORE DATABASE that reads .bak file might be constantly retrying to read .bak file and return error after long period of time if Shared Access Signature in CREDENTIAL is incorrect. Execute RESTORE HEADERONLY before restoring a database to be sure that SAS key is correct. Make sure that you remove leading ? from the SAS key generated using Azure portal.

Tooling

SQL Server Management Studio and SQL Server Data Tools might have some issues while accessing Managed Instance. All tooling issues will be addressed before General Availability.

Incorrect database names

Managed Instance might show guid value instead of database name during restore or in some error messages. These issues will be corrected before General Availability.

Database mail profile

There can be only one database mail profile and it must be called AzureManagedInstance_dbmail_profile. This is a temporary limitation that will be removed soon.

Next steps