Synchronizing to Microsoft Azure SQL databases

With Schema Compare, you can compare and synchronize Microsoft Azure SQL databases.

List of unsupported object types

Object Oblect
Application Roles Partition Schemes
Assemblies Queues
Asymmetric Keys Remote Synonyms
Certificates Routes
Contracts Rules
Defaults Services
Event Notifications Service Bindings
Extended Stored Procedures Symmetric Keys
Full Text Catalogs System Tables
Message Types User-Defined Types
Numbered Stored Procedures XML Schema Collections
Partition Functions  

If you try to synchronize unsupported objects to a Microsoft Azure SQL database, the synchronization script will fail.

Unsupported functions:

  • WITH ENCRYPTION option is not supported;
  • CLR scalar and table-valued functions are not supported.

Unsupported Indexes:

  • ON keyword is not supported (for partition_schema_name, filegroup name, default);
  • Relational index options that are not supported:

    • PAD_INDEX
    • FILLFACTOR
    • SORT_IN_TEMPDB
    • ALLOW_ROW_LOCKS
    • ALLOW_PAGE_LOCKS
    • MAXDOP
    • DATA_COMRESSION

Synonyms’ support

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].

Unsupported Stored Procedures

  • EXTERNAL NAME (CLR-procedure) option is not supported.
  • WITH ENCRYPTION option is not supported.

Tables’ support

Azure does not support the following options for tables:

  • ON keyword { partition_schema_name | filegroup | ‘default’ }
  • 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
  • Azure does not support FileTables;

Triggers’ support

  • Azure does not support the following options for triggers:
    • 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 not support CLR-triggers

Unsupported Types:

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

Users’ support

  • Azure does not support users based on Windows Logins;

  • User can be created only for some specific login, while logins can be created only in a master database. So user creation logic is as follows: create a login in a master database (if not exist yet) and then create a user in a custom database for this login;

Views’ support

  • WITH ENCRYPTION is not supported;

T-SQL and SQL Server feature support

Microsoft Azure SQL databases have some other limitations that can lead to Schema Compare synchronization failure. For instance, Microsoft Azure SQL databases don’t support encryption, data compression, and SQL Server replication.