What features are not supported in Azure SQL Database?


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)
Comments (7)

  1. robert_gale says:

    Under DDL differences above, number 1 says:
    “.NET CLR integration available now in latest release.” – really? Since Microsoft’s abrupt removal of CLR from Azure, I can’t find any references to a specific timetable for its reinstatement.

    1. Gaurav Hind says:

      Thanks Robert for your feedback – you are right. In an effort to protect the security of customers, starting April 15th 2016, Microsoft has proactively suspended the use of user-defined SQL CLR in Azure SQL Database.While no exploits have been known till date, this is attributed to a security risk. It is likely that the feature will be introduced in near future however the timelines are not yet published. I have corrected the post. Below official document updated on June 13th confirms the same.
      https://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information/#features-not-supported-in-sql-database

  2. I haven’t used/checked all of these but the one below is not true since introducing V12 as far as I remember.

    “DDL Differences (…)
    4. A clustered index is required on all Azure SQL tables. No support for heap tables and INSERT operations will fail until a clustered index is created.”

    1. Gaurav Hind says:

      Thanks Wojciech – post updated to reflect this important clarification.

  3. mrdenny says:

    Few corrections suggested.

    “Azure SQL may use a single physical server to host Databases from many different customers” We don’t care about the physical layer. I’d reword this so that it doesn’t sound like my SQL databases may be hosted on the same instance as yours.

    SQL Replication where SQL DB is a subscriber is supported as of SQL 2016 on prem. I believe that SQL ERRORLOG type info is available in the DMVs.

    Table partitioning is there, but you can’t scale out across disks for performance. With the premium storage behind the SQL DB instances you shouldn’t need this anyway. You can get performance through partition elimination.

    DB Mirroring is available through the readable secondary’s (it’s not DB mirroring, it’s better).

    1. Gaurav Hind says:

      Thank you for your feedback Mr. Denny, please keep such great inputs coming. I have taken some of your feedback and reflected in the post for the benefit of readers. Please follow specific inputs to your comments as below:

      1. Azure SQL DB multi-tenancy – We as “users” of the database absolutely need to know about sharing that “physical” layer and factoring that into the architecture/design while designing for performance and isolation. I have updated the post to suggest “isolated” multi-tenancy as different VMs could be hosted on the same physical infrastructure and resources are shared down to the fiber as much as it is for any VM in the cloud.

      2. Table partition across disks is not supported (updated the post) however considering this feature is of high value for very large datasets it may pose challenge and users need to know about this. Granted, this can be circumvented by using premium disks however that adds to the cost hence yet another design and cost consideration.

      3. DB mirroring – Readable secondary may have few pros over DB mirroring in certain situations (updated the post to reflect that).

      Thanks,
      Gaurav

  4. Ashu2107 says:

    Could get more details on implications of ” Snapshot isolation is enabled and cannot be turned off at the Database level.” ?

Skip to main content