Microsoft’s cloud-based platform Azure SQL Database is now supported by selected ApexSQL products, including ApexSQL Diff and ApexSQL Data Diff. These applications can compare “usual” data source (such as database, backup, snapshot or source control scripts) against SQL Azure, as well as two SQL Azure databases against each other.
In addition, a community version of ApexSQL Diff is released and offers Azure SQL Database support for FREE. We will do this for other products over time as well.
Currently, only Azure V11 is fully supported by ApexSQL Diff and ApexSQL Data Diff, with basic support for Azure V12. Work is in progress to implement full support for Azure V12, in both tools.
ApexSQL Diff
Azure SQL Database is seamlessly integrated to ApexSQL Diff and as the result there are no differences when comparing SQL Server databases and Azure SQL databases. In order to compare Azure SQL Database, a Database should be selected as the data source type from the Source/Destination drop down menu in the New project dialog. And finally, a database could be selected from the list of databases available on specified SQL Azure server.
Limitations of Azure SQL Database
Although Azure SQL Database contains a rich set of functions that SQL Server has, it still imposes some limitations on type of objects that could be compared and synchronized. The list of objects/features that are not supported in SQL Azure is as follows:
- Application Roles, Assemblies, Asymmetric Keys
- Certificates, Common Language Runtime (CLR) Types, Contracts
- Defaults
- Event Notifications, Extended Stored Procedures
- Full Text Catalogs, Full Text Indexes, Full Text stoplists
- Message types
- Partition functions, Partition schemes, Partitions / Data spaces
- Queues
- Remote service bindings, Routes, Rules
- Search property lists, Sequences, Services, Symmetric keys
- XML schema collections, XML indexes, XML documents
These objects cannot be created in Azure SQL Database. As the result if any (or both) of data sources being compared is Azure SQL Database, then the objects specified above are not compared and not available for synchronization.
Additionally, Azure SQL Database has a limitation for objects that are supported, such as: no data compression, no data encryption, no extended properties, no file groups, no filestreams and so on. In these cases unsupported features are not compared. (Please see List of unsupported Azure SQL Database options for ApexSQL Diff ).
For example, if the source database is located on SQL Server 2014 and has a table [Table1] with extended property [exprop1] defined, while target database is located on Azure SQL Database and has a table [Table1] without extended property defined (as extended properties cannot be created in Azure SQL Database), then these tables will be shown as equal after comparison and extended property cannot be synchronized from source to target database.
ApexSQL Diff informs users about unsupported options using warnings in Synchronization Wizard if something cannot be synchronized to Azure SQL Database and prevents from generating incorrect scripts. For example, ROWGUIDCOL columns are not supported in Azure SQL Database, so if a table [Table1] with column [Col1] that has ROWGUIDCOL column is being synchronized to Azure SQL Database, then ApexSQL Diff shows the following warning in Synchronization Wizard:
ROWGUIDCOL property for column [Col1] on table [Table1] is not supported in Azure SQL.
In this case column [Col1] will be scripted without ROWGUIDCOL property, so synchronization script will not fail on execution.
ApexSQL Data Diff
ApexSQL Data Diff fully supports Azure SQL Database. ApexSQL Data Diff compares and synchronizes data exactly the same way regardless of the database type.
The only exception in data management is that Azure SQL Database does not allow inserting data into tables without a clustered index. Meaning that table without a clustered index could be created in Azure SQL Database (for example, as result of synchronization using ApexSQL Diff), but when data is being inserted into this table (for example, as result of data synchronization using ApexSQL Data Diff) Azure SQL Database will fail with error. In order to prevent this situation, ApexSQL Data Diff shows high priority warning message while synchronization:
Destination table [Table1] does not have any clustered index. Azure SQL does not allow inserting data into table without clustered index.
In this case a clustered index should be manually created in a destination table and only after that data could be synchronized to the table.
December 18, 2013
