SQL-Articles

Issues restoring SQL Azure bacpac due to QueryStoreStaleQueryThreshold

In our environment we have inhouse and SQL Azure databases. We will be refreshing the DEV environments weekly once from the production SQL Azure databases. As you know backups are taken care by Azure itself, so we can simply add the storage in our inhouse server and extract it as data tier application.

Last night when we tried to restore it we started getting an error message.

The Element or Annotation class SqlDatabaseOptions does not contain the Property class QueryStoreStaleQueryThreshold. (Microsoft.Data.Tools.Schema.Sql)

If you look at error it’s because of the query store  which is a upcoming feature in SQL Server 2016 however in SQL Azure they released this when they upgrade the Azure db to V12 (I believe on June 2015). I’m trying to import the database to SQL Server 2014. Since it’s an upcoming feature and not supported in SQL Server 2014 it was not able to create the database. I was trying to find out the actual root cause why this week it got failed and why it didnt failed last week.

I chekced create database script header information [model.xml] in last week bacpac I could see the below content in SQL database options

sql_azure_bacpac_querystore_2

When I checked the same in this week bacpac I could see the below options

sql_azure_bacpac_querystore_1

 

If you see it’s very clear that we have an entry QueryStoreStaleQueryThreshold in the database options which is why it’s failing this week.

I also checked sysdatabases in SQL Azure to verify whether QUERY_STORE is enabled but I dont find it enabled.

SELECT name, [is_query_store_on] FROM sys.databases
WHERE name='MyDB'
Workaround 1 — Easy, no need to take new bacpac from Azure
  • When you import the data tier from Azure using management studio, it will be copied to a temporary location. As shown below go to the temp path and copy the bacpac to a location

sql_azure_bacpac_querystore_3

  • Rename the extension of the bacpac to zip and extract the contents

sql_azure_bacpac_querystore_4

  • Now open the folder, you can find a file called model.xml. Open the file in notepad

sql_azure_bacpac_querystore_5

  • Now remove the below line from the file and save it
<Property Name="QueryStoreStaleQueryThreshold" Value="10" >
  • It’s not over. When Azure builds the bacpac it will generate a hash value for this file so when you modify obviosuly the hash value will be changed. So now we need to update the new has value.
    If we didnt update the hash value then we will get the below error, so it’s mandatory to complete the below steps
The calculated checksum for model.xml in the package C:\Temp\temp\Mydb_bacpac-20151028211314.bacpac is different from the saved checksum. (Microsoft.Data.Tools.Schema.Sql)
  • Open powershell, make sure you have powershell version is equal or greater than 4.0 or else below command won’t work
Get-FileHash '.\model.xml' -Algorithm SHA256
  • As shown below it will generate a new hash value for the file

sql_azure_bacpac_querystore_6

  • Now go to the extracted folder and open the file Origin.xml, you can find the hash value just replace the value from the one we generated above.
<Checksum Uri="/model.xml">C7CBAE8ADEF44F3B309E79489F6A3AEC46C1E88D8E1A143A9F8738C3EEBF4BA1</Checksum>

sql_azure_bacpac_querystore_7

  • Save the file.  Zip the entire folder. Now rename it from zip to bacpac and try to extract it using SSMS , choose to get the bacpac from disk.

Awesome you are done, you got the data from SQL Azure database !!!

Workaround 2

As you all do I searched in google to get a solution. I found one [https://social.msdn.microsoft.com/Forums/azure/en-US/ac9deff4-e0fe-4de0-98ec-9059e917e0bc/database-export-bacpacs-can-no-longer-be-imported-sqldatabaseoptions-querystoredesiredstate?forum=ssdsgetstarted] but it’s not an efficient method for me because the db size is huge and I dont want to generated one more bacpac

  • Connect to SQL Azure database and execute below command.
ALTER DATABASE [Database-Name] SET QUERY_STORE = on;
GO
ALTER DATABASE [Database-Name]
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY =
(STALE_QUERY_THRESHOLD_DAYS = 367)
);
GO
ALTER DATABASE [Database-Name] SET QUERY_STORE = off;
GO
  • Even though it’s not enabled, we are trying to just turn on and turn off so that in the bacpac it wont reflect
  • Now create one more bacpac from Azure
  • Now use this bacpac from your inhouse server

Posted

in

by

VidhyaSagar

Tags:

, , ,

Comments

3 responses to “Issues restoring SQL Azure bacpac due to QueryStoreStaleQueryThreshold”

  1. Karol avatar
    Karol

    In Workaround 1, on the last step that says :

    “Save the file. That’s it you are done with all the changes. Now rename it from zip to bacpac and try to extract it using SSMS , choose to get the bacpac from disk”

    Can you clarify what you mean by “rename it from zip to bacpac”? Do you mean that we need to rezip the contents of the extracted and edited bacpac folder, and then remove the zip extension on that?

    Thank you for your help.

    -K

    Reply
    1. VidhyaSagar avatar
      VidhyaSagar

      Yes Karol. You are right. Thanks for pointing this. I’ll modify it.

      Reply
  2. Ram avatar
    Ram

    Getting the below error in trying to import the updated .bacpac
    Multidisk ZIP format is not supported. (Microsoft.Data.Tools.Schema.Sql)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *