Feature comparison: Azure SQL Database versus SQL Server
Azure SQL Database shares a common code base with SQL Server. Most of the standard SQL 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:
- Language features - Control of flow language keywords, Cursors, Data types, DML statements, Predicates, Sequence numbers, Stored procedures, and Variables.
- Database features - Automatic tuning (plan forcing), Change tracking, Database collation, Contained databases, Contained users, Data compression, Database configuration settings, Online index operations, Partitioning, and Temporal tables (see getting started guide).
- Security features - Application roles, Dynamic data masking (see getting started guide), Row Level Security, and Threat detection - see getting started guides for single database and elastic pools and managed instance.
- Multi-model capabilities - Graph processing, JSON data (see getting started guide), OPENXML, Spatial, OPENJSON, and XML indexes.
There are the database features that 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 |
|---|---|---|
| Always Encrypted | Yes - see Cert store and Key vault | Yes - see Cert store and Key vault |
| Always On Availability Groups | High availability is included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database | High availability is included with every database and cannot be managed by user. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database |
| 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, and Business Critical 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 |
| 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. 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 |
|---|---|---|
| Active geo-replication | Yes - all service tiers other than hyperscale | No, see Auto-failover groups(preview) as an alternative |
| Auto-failover groups | Yes - all service tiers other than hyperscale | Yes, in public preview |
| 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 - using Azure PowerShell. |
| 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 | No. A single managed instance can have multiple databases that share the same pool of resources. Managed instances cannot share 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 | Yes, see DNS Alias | No |
| 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 Azure SQL Database single databases/elastic pools 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 Azure SQL Database single databases/elastic pools 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 |
Tools
Azure SQL database supports various data tools that can help you to manage your data.
| Tool | Single databases and elastic pools | Managed instances |
|---|---|---|
| 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 | No |
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 |
|---|---|---|
| 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:
- Filtered to the SQL Database service.
- Filtered to General Availability (GA) announcements for SQL Database features.
For more information about the Azure SQL Database flavors, see:
Feedback
Loading feedback...