Automated Azure SQL DB export fails - indexing for document type .xml is not supported on MS Azure SQL DB v12

1
votes

I'm using Azure SQL DB automated export feature. Everything worked like a charm, but recently it started failing:

Automated SQL Export failed for XXX:XXX at 8/2/2015 4:11:22 AM. The temporary database copy was made, but this copy could not be exported to the .bacpac file.

I've checked "Import/Export History" of my Azure SQL Server and saw following error details:

Error encountered during the service operation. Indexing for document type .xml is not supported on Microsoft Azure SQL Database v12.

I have no idea what it is. Probably it's something linked with FTS feature that I'm using, but I've done nothing to my database since my last successful automated exports, so I guess this must be some Azure issue.

  1. Do you know how to fix this?
  2. Do you know if it affects backups made by default by Azure SQL DB service?
2
sql-serverazureazure-sql-database

2 Answers

1
votes

Disclosure: I work on the SQL Server team responsible for Import/Export. A fix for this issue during automated export will be added in the future, but an immediate workaround is to use SqlPackage.exe to export locally. The solution is to set the VerifyFullTextDocumentTypesSupported flag to be false as mentioned in Satya's answer.

Exact steps to use SqlPackage.exe:

  1. Install the latest July DacFramework.msi release, or ideally install the latest SSMS Preview which has support for the most recent Azure SQL DB features. Support for Full Text Search and this config option was added this year, hence the need to update SqlPackage.exe. The version you were using to Export was out of date and hence did not know about Full Text Search support.
  2. Run the following command to export using SqlPackage.exe.

For version 120 (this is the July RTM update):

“C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe” /a:export /ssn:(localdb)\ProjectsV12 /sdn:myDB /tf:my.bacpac /p:VerifyFullTextDocumentTypesSupported=false

For version 130 (Installed by SSMS Preview release):

“C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe” /a:export /ssn:(localdb)\ProjectsV12 /sdn:myDB /tf:my.bacpac /p:VerifyFullTextDocumentTypesSupported=false

Finally, to answer your question #2 this will not affect backups or Point in Time Restore (PITR). It is a configuration issue with the Import/Export service and the issue is localized to this feature.

5
votes

If you have full-text index on a table in the database you may see this. Quick solution is to drop the index and recreate after import into Azure DB. Azure DB taken backups are not impacted by this and you can do PITR if needed.

   1. Open a CMD Window
2. Run the following command to export using SqlPackage.exe:
“C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe” /a:export /ssn:(localdb)\ProjectsV12 /sdn:myDB /tf:my.bacpac /p:VerifyFullTextDocumentTypesSupported=false

The VerifyFullTextDocumentTypesSupported flag is set to false, which allows export
to continue without any verification. The reason this is not allowed by default
is to avoid unexpected behavior upon migration to Azure Sql Database.