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.

Comments

• Anonymous
  February 18, 2013
  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?

• Anonymous
  February 19, 2013
  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

• Anonymous
  February 19, 2013
  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?

• Anonymous
  February 20, 2013
  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

• Anonymous
  May 23, 2013
  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.

• Anonymous
  June 10, 2013
  The comment has been removed

• Anonymous
  June 29, 2013
  Backup and Export info is pretty important? tank's

• Anonymous
  August 22, 2013
  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?

• Anonymous
  June 01, 2015
  I had another reason for the same error:

1. My database has Latin1_General_CS_AS collation  (case sensitive)
2. In View definition I select column "offset" when table contains column "Offset".