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:

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:

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:

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.

Regarding the statement: "This improved level of validation ensures Exported BACPACs can be Imported"; I guessing that this means that the export does not have to be done on a copy of the database, that the export will result in a viable/restoreable backup potentially missing only a few database operations that may have occured while the export was in progress. Is this correct?

The comment was geared at the Azure-specific validation of the model/package not at the transactional consistency of the Export operation. At Export time, DACFx validates the model to ensure that the schema is compatible with and can be deployed to another Azure SQL Database. The Export operation is not transactionally consistent. To ensure that it is transactionally consistent, you have several options including creating a copy of the database and Exporting from the copy, or setting the database to Read-Only, and Exporting while the database is in a Read-Only state.

Hope this helps!

Thanks,

Adam

Bruce

20 Feb 2013 3:46 AM

Adam:

Thanks for the response. What would happend if an attempt was made to restore a transactional inconsistent backup file? Would most of the database restore excepting the few updates that occured during the backup process, or could you be left with a totally useless result?

There are multiple failure modes for transactional inconsistencies in the event that the inconsistency does indeed cause a failure, as these inconsistencies may happen during different phases of the operation. The type of failure and the result, if it fails, is not deterministic.

Thanks,

Adam

Paul

23 May 2013 1:37 PM

Thanks for this information, very useful !

Will all this sort of information be put together in one place so it is easily found from the main Azure page ? Backup and Export info is pretty important and I think that it should all be gathered in one place. I end up searching all over to try and get answers.

Eric VanWieren

10 Jun 2013 10:36 AM

I have been using this tool, and am trying to find if it is a supported tool. The problem is that I do not want to write an application that uses this web service to perform a backup, then have the service pulled.

On that note, I am more than willing to use my own application from soup to nuts, but have not been able to find an example on how to use the DAC services to export directly to a blob. The problem is that the dac export call requires a stream to do the export, and so does the file upload function for blob storage.

Nice tool though.

john

29 Jun 2013 9:15 AM

Backup and Export info is pretty important? tank's

Matthew Jackson

22 Aug 2013 8:35 AM

I am receiving this error when extracting from a SQL2008 instance (10.0.5826). Why would this platform not be able to handle external references?