ApexSQL support for Microsoft Azure SQL Database V12

Intro

The document is divided into the following three parts: “Azure fundamentals”, “Scripter/GrammarParser module” and “ApexSQL applications”.

Azure fundamentals

Microsoft Azure SQL Database V12 (Azure later on) is a cloud-based database that is (as declared by the Microsoft) a subset of the Microsoft SQL Server 2014. Currently, Azure supports most of the Microsoft SQL Server 2014 functionality (much more than Azure V11 supported from the Microsoft SQL Server 2012). Current version of the service is “Microsoft Azure 12.0”.

Azure support of database objects can be divided into two groups: supported (fully or partially) and not supported objects. The level of support is “measured” comparing to the Microsoft SQL Server 2014. For all “supported” objects basic functionality is supported, but in some cases “extra” features are not supported.

Most of not supported objects are related to:

• data storage logic (file groups, data compression, fulltext indexes and so on);
• server-side objects (jobs, alerts, endpoints, linked servers and so on);
• non-data related logic (message types, routes, contracts, and so on);

The full list of not supported objects is listed in “Not supported objects” part.

ApexSQL tools support schedule for Azure 12

ApexSQL will be taking a 2 phased approach to Azure 12 support

First, we plan to make sure all tools work on Azure 12, without errors, even if not all new datatypes/features are supported

Secondly, we will add full support for all of the new datatypes/features in Azure 12

The first phase should be accomplished in the next week or two (mid to late March). The following phase will be concluded by mid-April. This should be reflected in our product roadmap

Supported objects

Functions

Limitations

• WITH ENCRYPTION is supported (not supported in Azure V11)
• *CLR scalar and table-valued functions are supported (not supported in Azure V11)

Indexes*

Limitations

• ON keyword is not supported (for filegroup name, default) (same as Azure V11, while partition schema is supported);
• Relational index options are supported (not supported Azure V11):
  • PAD_INDEX
  • FILLFACTOR
  • SORT_IN_TEMPDB
  • ALLOW_ROW_LOCKS
  • ALLOW_PAGE_LOCKS
  • MAXDOP
  • DATA_COMRESSION

• Backward compatible relational indexes are not supported (not supported Azure V11);
• COLUMNSTORE indexes are supported (not supported Azure V11);

Roles

Limitations

• ALTER ROLE is fully supported (ALTER ROLE for adding/dropping members is not supported in Azure V11);

Schemas

Limitations

• No limitations are found;

Statistics

Limitations

• No limitations are found;

Stored Procedures

Limitations

• FOR REPLICATION option is supported (not supported in Azure V11);
• EXTERNAL NAME (CLR-procedure) option is supported (not supported in Azure V11);
• WITH ENCRYPTION option is supported (not supported in Azure V11);

Synonyms

Limitations

• Synonyms cannot be created for objects on different server; i.e. Azure supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #. It means that Azure does not support synonyms, that reference objects using four-part names in form: [server_name].[database_name].[schema_name].[object_name]; (same in Azure V11)

Tables*

Limitations

• Azure now supports the following options for tables (not supported in Azure V11):
  • ON keyword
  • TEXTIMAGE_ON
  • FILESTREAM_ON
  • <column_definition>
    • FILESTREAM
    • NOT FOR REPLICATION
    • ROWGUIDCOL
  • <data type>
    • CONTENT
    • DOCUMENT
    • xml_schema_collection
  • <column_constraint>, <computed_column_definition>, <table_constraint>
    • FILLFACTOR
    • ON
    • NOT FOR REPLICATION
  • <index_option>
    • PAD_INDEX
    • FILLFACTOR
    • ALLOW_ROW_LOCKS
    • ALLOW_PAGE_LOCKS
    • DATA_COMPRESSION
    • ON PARTITIONS

• Not actual anymore (was actual in Azure V11). Table must have clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is performed on the table (otherwise INSERT will fail).
• Azure does not support FileTables (same as Azure V11);

Triggers*

Limitations

• Azure supports the following options for triggers (not supported in Azure V11):
  • ENCRYPTION
  • WITH APPEND
  • NOT FOR REPLICATION
  • EXTERNAL NAME option (there is no external method support)
  • ALL SERVER Option (DDL Trigger)
  • Trigger on a LOGON event (Logon Trigger)
• Azure does support CLR-triggers (not supported in Azure V11):

User Defined Types (UDTs)*

Limitations

• Azure does support CLR-types (having option EXTERNAL NAME assembly_name [ .class_name ]) (not supported in Azure V11);
• Azure does support ROWGUIDCOL types (not supported in Azure V11);
• Azure types do support CONTENT, DOCUMENT and xml_schema_collection options (not supported in Azure V11);

Users*

Limitations

• Users do not support Certificates and Asymmetric Keys (and as the result users cannot be created based on these credentials);
• Azure does not support users based on Windows Logins;
• Users do not support specification of DEFAULT_LANGUAGE;
• User can be created only for some specific login, while logins can be created only in the master database. So, the user creation logic is as follows: create a login in the master database (if it doesn’t exist yet) and then create a user in a custom database for this login;

Views

Limitations

• WITH ENCRYPTION is supported (not supported in Azure V11);

Supported objects

These objects are supported in Azure V12 but were not supported in Azure V11.

Application Roles, Assemblies

Common Language Runtime (CLR), Change Data Capture (CDC) / Track Data Changes

Defaults,

Extended Properties,

Partition Functions, Partition Schemes, Partitions / Data Spaces

Rules,

Sequences

XML Indexes, XML Schema Collections, XML Documents

Not supported objects*

Not supported objects are the objects that have no level of support (not defined) in Azure. These objects are:

Agents, Asymmetric Keys

Certificates, Contracts

Data Compression, Data Encryption

Event Notifications, Extended Stored Procedures

FileGroups, FILESTREAM Data, Full Text Catalogs, Full Text Indexes, Full Text Stoplists

Jobs

Message Types

Queues

Remote Service Bindings, Remote Synonyms (usual synonyms are still available), Routes

Search Property Lists, Services, Symmetric Keys