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'
<Property Name="QueryStoreStaleQueryThreshold" Value="10" >
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)
Get-FileHash '.\model.xml' -Algorithm SHA256
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
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