What features are not supported in Azure SQL Database?

  • Article

Azure SQL database is a fast growing service with several features being added each month hence its challenging to capture the latest however this blog is an effort to enlist features that are partially supported or not fully supported. For more comprehensive and latest information please refer to Azure SQL DB documentation.

  • Architecture differences: Unlike SQL Server where your Databases are the only ones on your server, Azure SQL may use a single physical server to host Databases from many different customers (much like a VM, Azure SQL is inherently isolated multi-tenant and needs to share physical resources among all clients of the service). Hence server level functionality is generally restricted/unsupported and many  diagnostic and monitoring features System Admins have been accustomed to, no longer apply and are not available. e.g. SQL Server Utility, SQL Trace, Profiler, Extended Events, Data Collector and many system tables. Instead, several views are available to provide diagnostic and health monitoring.
  • Transact SQL Differences (subset of T-SQL is supported)
    1. Few features have full parity with the SQL Server;
    2. Some have partial parity (some options are omitted, or added uniquely to support Azure SQL);
    3. There are data types, functions, operators, statements, procedures, and system tables/views that are not supported at all.
    4. Distributed transactions are not supported.
    5. Agented jobs using SQL Agent are not supported.
    6. Extremely large connections (>2GB logging) could be throttled or disconnected.
  • DDL Differences:  Being the same product, there are several feature that have full parity while a few features only exist in SQL server. Few such features are highlighted below:
    1. .NET CLR integration (eliminated since April 15th 2016, may be added in future).
    2. Extended stored procedures e.g. xp_sendmail are not supported.
    3. Table partitioning exists but does not scale out across disks (horizontal partitioning of  table/index data across multiple file groups to improve performance on large datasets) however consider premium storage to eliminate the need. A new feature called Federations is available to attain similar functionality.
    4. Prior to V12, a clustered index was required on all Azure SQL tables. This is no more a limitation.
    5. Snapshot isolation is enabled and cannot be turned off at the Database level.
  • DML Differences: All CRUD operations, Aggregate and ordering functions including Top, Except, Intersect, Union, CTEs, MARS, Hints are supported. MAXDOP defaulted to 1 while PAGLOCK, REMOTE are not supported. Largest different is that Full-Text search (Contains) is not supported by SQL Azure (in preview) while character-based searches (Like) are supported. Additionally, BULK INSERT is not supported. All programmatic features including tempdb, Cast, Convert, RaiseError, If/Else are fully supported.
  • Admin level differences: Change Data Capture (relies on log reader), Resource Governor, DB mirroring (provider readable secondary which may be better in certain situations), Service Broker and SQL Agent are not supported.
  • Other unsupported features
    Replication – use SQL Replication (SQL 2016 with onPrem), BCP or SSIS to move data to another database.
    Global temporary tables
    Trace flags
    Transact-SQL debugging
    SSIS instance, although can connect via On-premise SSIS.
    Server-scope or logon triggers
    SQL Server error log (Error log is available in Dynamic Management Views while Operation logs are available from the Azure Portal)