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
When I checked the same in this week bacpac I could see the below options
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
- Rename the extension of the bacpac to zip and extract the contents
- Now open the folder, you can find a file called model.xml. Open the file in notepad
- 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
- 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.
- 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 !!!
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