Windows Azure Import/Export Service and External References

Windows Azure Import/Export Service and External References

Rate This
  • Comments 8

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.

Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post
  • 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?

  • Hey Bruce,

    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

  • 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?

  • Bruce,

    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

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

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

  • Backup and Export info is pretty important? tank's

  • 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?

Page 1 of 1 (8 items)