Azure SQL Database Features

Azure SQL Database service shares a common code base with the latest stable version of SQL Server. Most of the standard SQL language, query processing, and database management features are identical in SQL Server and Azure SQL Database. The features that are common in SQL Server and all flavors of Azure SQL Database are:

Azure SQL Database manages your databases and guarantees their high-availability. Some features that might affect high-availability or cannot be used in PaaS world have limited functionalities in Azure SQL Database. In addition, some database features depend on the type of Azure SQL Database that you create. These features are described in the tables below. With Azure SQL Database, you can create a database as part of a managed instance, as a single database, or as part of an elastic pool. If you need more details about the differences, you can find them in the separate pages for Single database and Elastic pools or Managed Instance.

SQL features

The following table lists the major features of SQL Server and provides information about whether the feature is partially or fully supported in Managed Instance or Single Database and Elastic pools, with a link to more information about the feature.

SQL Feature Single databases and elastic pools Managed instances and instance pools
Always Encrypted Yes - see Cert store and Key vault Yes - see Cert store and Key vault
Always On Availability Groups 99.99-99.995% availability is guaranteed for every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database 99.99.% availability is guaranteed for every database and cannot be managed by user. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database. Use Auto-failover groups to configure secondary Always On Managed Instance in another region. Other SQL Server instances and Single databases cannot be used as secondaries for Managed Instance.
Attach a database No No
Auditing Yes Yes, with some differences
Azure Active Directory (AAD) authentication Yes. AAD users only. Yes. Including server-level AAD logins.
BACKUP command No, only system-initiated automatic backups - see Automated backups Yes, user initiated copy-only backups to Azure Blob Storage (automatic system backups cannot be initiated by user) - see Backup differences
Built-in functions Most - see individual functions Yes - see Stored procedures, functions, triggers differences
BULK INSERT statement Yes, but just from Azure Blob storage as a source. Yes, but just from Azure Blob Storage as a source - see differences.
Certificates and asymmetric keys Yes, without access to file system for BACKUP and CREATE operations. Yes, without access to file system for BACKUP and CREATE operations - see certificate differences.
Change data capture - CDC No Yes
Collation - server/instance No, default logical server collation SQL_Latin1_General_CP1_CI_AS is always used. Yes, can be set when the instance is created and cannot be updated later.
Columnstore indexes Yes - Premium tier, Standard tier - S3 and above, General Purpose tier, Business Critical, and HyperScale tiers Yes
Common language runtime - CLR No Yes, but without access to file system in CREATE ASSEMBLY statement - see CLR differences
Credentials Yes, but only database scoped credentials. Yes, but only Azure Key Vault and SHARED ACCESS SIGNATURE are supported see details
Cross-database/three-part name queries No - see Elastic queries Yes, plus Elastic queries
Cross-database transactions No Yes, within the instance. See Linked server differences for cross-instance queries.
Database mail - DbMail No Yes
Database mirroring No No
Database snapshots No No
DBCC statements Most - see individual statements Yes - see DBCC differences
DDL statements Most - see individual statements Yes - see T-SQL differences
DDL triggers Database only Yes
Distributed partition views No Yes
Distributed transactions - MS DTC No - see Elastic transactions No - see Linked server differences. Try to consolidate databases from several distributed SQL Server instances into one managed instance during migration.
DML triggers Most - see individual statements Yes
DMVs Most - see individual DMVs Yes - see T-SQL differences
Event notifications No - see Alerts No
Expressions Yes Yes
Extended events (XEvent) Some - see Extended events in SQL Database Yes - see Extended events differences
Extended stored procedures No No
Files and file groups Primary file group only Yes. File paths are automatically assigned and the file location cannot be specified in ALTER DATABASE ADD FILE statement.
Filestream No No
Full-text search (FTS) Yes, but third-party word breakers are not supported Yes, but third-party word breakers are not supported
Functions Most - see individual functions Yes - see Stored procedures, functions, triggers differences
In-memory optimization Yes - Premium and Business Critical tiers only Limited support for non-persistent In-Memory objects such as table types Yes - Business Critical tier only
Language elements Most - see individual elements Yes - see T-SQL differences
Linked servers No - see Elastic query Yes. Only to SQL Server and SQL Database without distributed transactions.
Linked servers that read from files (CSV, Excel) No. Use BULK INSERT or OPENROWSET as an alternative for CSV format. No. Use BULK INSERT or OPENROWSET as an alternative for CSV format. Track this requests on Managed Instance Feedback item
Log shipping High availability is included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database Natively built in as a part of DMS migration process. Not available as High availability solution, because other High availability methods are included with every database and it is not recommended to use Log-shipping as HA alternative. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database. Not available as a replication mechanism between databases - use secondary replicas on Business Critical tier, auto-failover groups, or transactional replication as the alternatives.
Logins and users Yes, but CREATE and ALTER login statements do not offer all the options (no Windows and server-level Azure Active Directory logins). EXECUTE AS LOGIN is not supported - use EXECUTE AS USER instead. Yes, with some differences. Windows logins are not supported and they should be replaced with Azure Active Directory logins.
Minimal logging in bulk import No, only Full Recovery model is supported. No, only Full Recovery model is supported.
Modifying system data No Yes
OLE Automation No No
OPENDATASOURCE No Yes, only to other Azure SQL Databases and SQL Servers. See T-SQL differences
OPENQUERY No Yes, only to other Azure SQL Databases and SQL Servers. See T-SQL differences
OPENROWSET Yes, only to import from Azure Blob storage. Yes, only to other Azure SQL Databases and SQL Servers, and to import from Azure Blob storage. See T-SQL differences
Operators Most - see individual operators Yes - see T-SQL differences
Polybase No. You can query data in the files placed on Azure Blob Storage using OPENROWSET function. No. You can query data in the files placed on Azure Blob Storage using OPENROWSET function.
Query Notifications No Yes
R Services Yes, in public preview No
Recovery models Only Full Recovery that guarantees high availability is supported. Simple and Bulk Logged recovery models are not available. Only Full Recovery that guarantees high availability is supported. Simple and Bulk Logged recovery models are not available.
Resource governor No Yes
RESTORE statements No Yes, with mandatory FROM URL options for the backups files placed on Azure Blob Storage. See Restore differences
Restore database from backup From automated backups only - see SQL Database recovery From automated backups - see SQL Database recovery and from full backups placed on Azure Blob Storage - see Backup differences
Restore database to SQL Server No. Use BACPAC or BCP instead of native restore. No, because SQL Server Database Engine used in Managed Instance has higher version than any RTM version of SQL Server used on-premises. Use BACPAC, BCP, or Transactional replication instead.
Semantic search No No
Service Broker No Yes, but only within the instance. If you are using remote Service Broker routes, try to consolidate databases from several distributed SQL Server instances into one managed instance during migration and use only local routes. See Service Broker differences
Server configuration settings No Yes - see T-SQL differences
Set statements Most - see individual statements Yes - see T-SQL differences
SQL Server Agent No - see Elastic jobs Yes - see SQL Server Agent differences
SQL Server Auditing No - see SQL Database auditing Yes - see Auditing differences
System stored functions Most - see individual functions Yes - see Stored procedures, functions, triggers differences
System stored procedures Some - see individual stored procedures Yes - see Stored procedures, functions, triggers differences
System tables Some - see individual tables Yes - see T-SQL differences
System catalog views Some - see individual views Yes - see T-SQL differences
TempDB Yes. 32GB size per core for every database. Yes. 24GB size per vCore for entire GP tier and limited by instance size on BC tier
Temporary tables Local and database-scoped global temporary tables Local and instance-scoped global temporary tables
Time zone choice No Yes, and it must be configured when the Managed Instance is created.
Trace flags No Yes, but only limited set of global trace flags. See DBCC differences
Transactional Replication Yes, Transactional and snapshot replication subscriber only Yes, in public preview. See the constraints here.
Transparent data encryption (TDE) Yes - General Purpose and Business Critical service tiers only Yes
Windows authentication No No
Windows Server Failover Clustering No. Other techniques that provide high availability are included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database No. Other techniques that provide high availability are included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database

Platform capabilities

Azure platform provides a number of PaaS capabilities that are added as an additional value to the standard Database features. There is a number of external services that can be used with Azure SQL Database service.

Platform Feature Single databases and elastic pools Managed instances and instance pools
Active geo-replication Yes - all service tiers other than hyperscale No, see Auto-failover groups as an alternative
Auto-failover groups Yes - all service tiers other than hyperscale Yes, see Auto-failover groups
Auto-scale Yes, but only in serverless model. In the non-serverless model, the change of service tier (change of vCore, storage, or DTU) is fast and online. The service tier change requires minimal or no downtime. No, you need to choose reserved compute and storage. The change of service tier (vCore or max storage) is online and requires minimal or no downtime.
Automatic backups Yes. Full backups are taken every 7 days, differential 12 hours, and log backups every 5-10 min. Yes. Full backups are taken every 7 days, differential 12 hours, and log backups every 5-10 min.
Automatic tuning (indexes) Yes No
Availability Zones Yes No
Azure Resource Health Yes No
Backup retention Yes. 7 days default, max 35 days. Yes. 7 days default, max 35 days.
Data Migration Service (DMS) Yes Yes
File system access No. Use BULK INSERT or OPENROWSET to access and load data from Azure Blob Storage as an alternative. No. Use BULK INSERT or OPENROWSET to access and load data from Azure Blob Storage as an alternative.
Geo-restore Yes - all service tiers other than hyperscale Yes - all service tiers other than hyperscale
Hyperscale architecture Yes No
Long-term backup retention - LTR Yes, keep automatically taken backups up to 10 years. Not yet. Use COPY_ONLY manual backups as a temporary workaround.
Pause/resume Yes, in serverless model No
Policy-based management No No
Public IP address Yes. The access can be restricted using firewall or service endpoints. Yes. Needs to be explicitly enabled and port 3342 must be enabled in NSG rules. Public IP can be disabled if needed. See Public endpoint for more details.
Point in time database restore Yes - all service tiers other than hyperscale - see SQL Database recovery Yes - see SQL Database recovery
Resource pools Yes, as Elastic pools Yes. A single managed instance can have multiple databases that share the same pool of resources. In addition, you can deploy multiple managed instances in instance pools(Preview) that can share the resources.
Scaling up or down (online) Yes, you can either change DTU or reserved vCores or max storage with the minimal downtime. Yes, you can change reserved vCores or max storage with the minimal downtime.
SQL Alias No, use DNS Alias No, use Clicongf to set up alias on the client machines.
SQL Analytics Yes Yes
SQL Data Sync Yes No
SQL Server Analysis Services (SSAS) No, Azure Analysis Services is a separate Azure cloud service. No, Azure Analysis Services is a separate Azure cloud service.
SQL Server Integration Services (SSIS) Yes, with a managed SSIS in Azure Data Factory (ADF) environment, where packages are stored in SSISDB hosted by Azure SQL Database and executed on Azure SSIS Integration Runtime (IR), see Create Azure-SSIS IR in ADF.

To compare the SSIS features in SQL Database server and Managed Instance, see Compare an Azure SQL Database single database, elastic pool, and managed instance.
Yes, with a managed SSIS in Azure Data Factory (ADF) environment, where packages are stored in SSISDB hosted by Managed Instance and executed on Azure SSIS Integration Runtime (IR), see Create Azure-SSIS IR in ADF.

To compare the SSIS features in SQL Database and Managed Instance, see Compare an Azure SQL Database single database, elastic pool, and managed instance.
SQL Server Reporting Services (SSRS) No - see Power BI No - see Power BI
Query Performance Insights (QPI) Yes No. Use built-in reports in SQL Server Management Studio and Azure Data Studio.
VNet Partial, it enables restricted access using VNet Endpoints Yes, Managed Instance is injected in customer's VNet. See subnet and VNet
VNet Service endpoint Yes No
VNet Global peering Yes, using Private IP and service endpoints No, Managed Instance is not supported due to Load balancer constraint in VNet global peering.

Tools

Azure SQL database supports various data tools that can help you to manage your data.

Tool Single databases and elastic pools Managed instances and instance pools
Azure portal Yes Yes
Azure CLI Yes Yes
Azure Data Studio Yes Yes
Azure Powershell Yes Yes
BACPAC file (export) Yes - see SQL Database export Yes - see SQL Database export
BACPAC file (import) Yes - see SQL Database import Yes - see SQL Database import
Data Quality Services (DQS) No No
Master Data Services (MDS) No No
SMO Yes Yes version 150
SQL Server Data Tools (SSDT) Yes Yes
SQL Server Management Studio (SSMS) Yes Yes version 18.0 and higher
SQL Server PowerShell Yes Yes
SQL Server Profiler No - see Extended events Yes
System Center Operations Manager (SCOM) Yes Yes, in preview

Migration methods

You can use different migration methods to move your data between SQL Server, Single Database, and Managed Instance databases. Some methods are Online and picking-up all changes that are made on the source while you are running migration, while in Offline methods you need to stop your workload that is modifying data on the source while the migration is in progress.

Source Single database and elastic pool Managed Instance and instance pools
SQL Server (on-prem, AzureVM, Amazon RDS) Online: Data Migration Service (DMS), Transactional Replication
Offline: BACPAC file (import), BCP
Online: Data Migration Service (DMS), Transactional Replication
Offline: Native backup/restore, BACPAC file (import), BCP, Snapshot replication
Single database Offline: BACPAC file (import), BCP Offline: BACPAC file (import), BCP
Managed Instance Online: Transactional Replication
Offline: BACPAC file (import), BCP, Snapshot replication
Online: Transactional Replication
Offline: Cross-instance point-in-time restore (Azure PowerShell or Azure CLI), Native backup/restore, BACPAC file (import), BCP, Snapshot replication

Next steps

Microsoft continues to add features to Azure SQL Database. Visit the Service Updates webpage for Azure for the newest updates using these filters:

For more information about the Azure SQL Database flavors, see: