Windows Azure Import/Export Service and External References

The Windows Azure Import/Export Service powered by the SQL Server 2012 Data-Tier Application Framework (DACFx V3) provides a cloud service for logical backup/restore and migration of Windows Azure SQL Databases. This functionality is available via an HTTP endpoint, as well as through the Windows Azure Management Portal.

We have recently released an update to the service that brings an enhancement to the level of validation carried out against a database during the Export operation. This improved level of validation ensures Exported BACPACs can be Imported (restored) to a new database in Azure. However, due to this improved validation, folks may see an increase in Export operation failures, particularly around invalid self-referencing external (three-part) names in object definitions. More details on the issue are below.

Scenario:

  1. You attempt to Export a Windows Azure SQL Database using the Import/Export Service via the HTTP Endpoint, or through the Windows Azure Management Portal.

Symptom:

  1. Export operation fails with error message similar to the following:

“Exception Microsoft.SqlServer.Management.Dac.Services.ServiceException: Error encountered during the service operation. Inner exception Microsoft.SqlServer.Dac.DacServicesException: Validation of the schema model for data package failed. Error SQL71562: Procedure: [dbo].[SampleProcedure] has an unresolved reference to object [MyDB].[dbo].[TestTable]. External references are not supported when creating a package from this platform.”

Issues:

1. Improved validation blocks Exports of databases containing fully qualified three-part names in object definitions.

Why:

DACFx must block Export when object definitions (views, procedures, etc.) contain external references, as Azure SQL Database does not allow cross-database external references. This includes blocking Export for databases with three-part references to themselves – if these references were successfully Exported, Importing the resulting BACPAC to a database with a different name will always fail, as the three-part name references would no longer be self-referencing.

Resolution:

1. Modify your database schema, removing all of the self-referencing three-part name references, reducing them to a 2 part name.

There are many tools/mechanisms by which you can accomplish fixing your schema to remove these external references. One option is to use SQL Server Data Tools (SSDT). In SSDT, you can create a database project from your Azure database, setting the target platform of the resulting project to “SQL Azure”. This will enable Azure-specific validation of your schema which will flag all three-part name/external references as errors. Once all of the external reference errors identified in the Error List have been remedied, you can publish your project back to your Azure database and resume usage of the Import/Export Service.