Freigeben über

What features are not supported in Azure SQL Database?

  • Artikel

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

  • Anonymous
    July 17, 2016
    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.
  • Anonymous
    July 18, 2016
    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."
    • Anonymous
      July 19, 2016
      Thanks Wojciech - post updated to reflect this important clarification.
  • Anonymous
    July 25, 2016
    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).
    • Anonymous
      July 25, 2016
      The comment has been removed
  • Anonymous
    November 30, 2016
    Could get more details on implications of " Snapshot isolation is enabled and cannot be turned off at the Database level." ?