none
Database export .bacpacs can no longer be imported: SqlDatabaseOptions ... QueryStoreDesiredState

    Question

  • Since yesterday my Scheduled SQL Azure backups (.bacpacs) and on-demand exports can no longer be imported to a local SQL Database (12.0.4213).

    The error message is:

    "Could not load schema from package

    The Element or Annotation class SqlDatabaseOptions does not contain the Property class QueryStoreDesiredState."

    Monday, October 26, 2015 9:50 AM

Answers

  • Update 2015-10-29

    If you are using SQL2016 CTP the recently released DACFx October Preview should fix this, just install both the 32 and 64bit versions.  Not sure if this will fix it for SQL 2014 though, i suspect not as the query store has its own property tab in SSMS 2016.

    For people who are getting similar error messages to this one but mention a different property, you need to reset its value to the default one (so that its excluded from the database options element in the model.xml file found in the *.bacpac)

    Original Post

    Spent the morning on the phone to MS support and have manged to get a workaround for this problem.  The issue is caused by the new Query Store feature of SQL server, which i believe may have been inadvertently enabled by us, by using the "Index adviser" feature or if you have enrolled in the "Query Performance Insight" preview in the Azure portal.

    The fix for us was to reset the "Stale Query Threshhold" value back to its default, and to then disable the "Query Store" on the affected database.  Once done i was able to manually export the database to a file and import it locally.

    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

    You will also need to have the latest versions of DACFx (both the 64 and 32bit versions) installed, as well as SQL 2014 SP1 CU3 installed locally.


    • Proposed as answer by Tim Chubb Monday, October 26, 2015 3:25 PM
    • Marked as answer by Neil Thompson [WLD] Tuesday, October 27, 2015 9:15 AM
    • Edited by Tim Chubb Thursday, October 29, 2015 11:39 AM Update to DACFx has been released
    Monday, October 26, 2015 2:26 PM

All replies

  • I to have this exact problem.

    The last backup i have that works was from midweek last week.

    I have installed all the latest sql2014 SP1 hotfixes (CU3 i believe) the September DACFx Preview and the September SSDT preview and still no joy.

    Can find no trace of the missing class in the published MSDN documentation, so im guessing MS have somehow managed to publish an update that breaks existing installs.

    Going to try the SQL 2016 CTP to see if that fixes it now, really unacceptable that something can be published that breaks a business critical feature such as backups!

    EDIT:

    I can confirm the date of my last working backup was on the 22nd of October @ 3am (GMT)


    • Edited by Tim Chubb Monday, October 26, 2015 10:53 AM
    Monday, October 26, 2015 10:25 AM
  • Update 2015-10-29

    If you are using SQL2016 CTP the recently released DACFx October Preview should fix this, just install both the 32 and 64bit versions.  Not sure if this will fix it for SQL 2014 though, i suspect not as the query store has its own property tab in SSMS 2016.

    For people who are getting similar error messages to this one but mention a different property, you need to reset its value to the default one (so that its excluded from the database options element in the model.xml file found in the *.bacpac)

    Original Post

    Spent the morning on the phone to MS support and have manged to get a workaround for this problem.  The issue is caused by the new Query Store feature of SQL server, which i believe may have been inadvertently enabled by us, by using the "Index adviser" feature or if you have enrolled in the "Query Performance Insight" preview in the Azure portal.

    The fix for us was to reset the "Stale Query Threshhold" value back to its default, and to then disable the "Query Store" on the affected database.  Once done i was able to manually export the database to a file and import it locally.

    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

    You will also need to have the latest versions of DACFx (both the 64 and 32bit versions) installed, as well as SQL 2014 SP1 CU3 installed locally.


    • Proposed as answer by Tim Chubb Monday, October 26, 2015 3:25 PM
    • Marked as answer by Neil Thompson [WLD] Tuesday, October 27, 2015 9:15 AM
    • Edited by Tim Chubb Thursday, October 29, 2015 11:39 AM Update to DACFx has been released
    Monday, October 26, 2015 2:26 PM
  • Just came back to this as was going to post on @AzureSQLSupport  to try and get this issue moving. Tim you are a total star - I have also used the Index Advisor (months ago) so hopefully this fix will work for me as well. MS should publish this issue pronto - presumably this affects lots of other people too.


    Monday, October 26, 2015 2:50 PM
  • No worries Neil.

    Glad its helped caused a stressful Monday here lol :)

    This appears to be a new issue for MS from what the support agent said to me so i expect there will either be an updated CTP for sql 2016 or a new hotfix for SQL 2014 in due course.  Until then the workaround will suffice for us.

    Monday, October 26, 2015 3:29 PM
  • Yes having this issue also starting today.   Turning the Query_Store = Off did the trick.  Thanks for sharing this solution!

    FYI:  I DID NOT have to apply the CU3 Hotfix.    Try the solution WITHOUT the Hotfix.  if not needed I would avoid it and wait for the next SP


    Mike


    • Proposed as answer by SpicyMikey Monday, October 26, 2015 8:45 PM
    • Edited by SpicyMikey Monday, October 26, 2015 8:46 PM
    • Unproposed as answer by SpicyMikey Tuesday, October 27, 2015 12:56 PM
    Monday, October 26, 2015 8:42 PM
  • +1 did not need the hotfix
    Tuesday, October 27, 2015 9:15 AM
  • I have a similar problem which I have not been able to solve following the steps set out here.

    On attempting to import an Azure DB generated bacpac on my local machine, I get this error:

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

    The bacpac was exported after running the sql script provided here.

    Our Azure DB server is v12. My local sql server is 2014. I have *attempted* to apply all relevant updates though it is *very* confusing to find all the right bits:

    SSMS is 12.0.4100.1

    MS Sql server data tier application framework (x64) is 13.0.3047.1

    MS Sql server data tier application framework (x86) is 13.0.3086.1

    MS Sql server data tools is 14.0.50927.0

    I'd be grateful for any suggestions.


    JamesFM

    Tuesday, October 27, 2015 2:43 PM
  • Running the script provided above on the offending database solved the issue for me.  Nothing else was necessary.   When you ran the sql script did it complete successfully? 

    It seems this QUERY_STORE setting gets turned ON when you do certain things to the database.  Make sure you don't have processes that are forcing this back on.


    Mike

    Tuesday, October 27, 2015 3:10 PM
  • Thanks for the speedy response Mike.

    I ran the script which did complete successfully. I have also upgraded to SSMS 2016 CTP 2, and can see this in the database properties:

    I re-exported the database, but hit the same error again.


    JamesFM

    Tuesday, October 27, 2015 3:43 PM
  • Are you running the script on the SQL Server (destination side)?  You need to be running this on the SQL Azure (source side).   The problem is in trying to import an Azure database that has this configuration.    So run the script on the database you wish to import, then do the import again. 

    BTW: I tested it on SQL 2014 SP1.  I can't personally confirm it works with 2016, although I read that it should


    Mike


    • Edited by SpicyMikey Tuesday, October 27, 2015 4:07 PM
    Tuesday, October 27, 2015 4:04 PM
  • Hi Mike

    Thanks for this.

    Yes, the script was run on the source DB. The screenshot above shows the db property sheet for the source (Azure) db from which I am exporting the bacpac.


    JamesFM


    • Edited by JamesFM Tuesday, October 27, 2015 4:08 PM
    Tuesday, October 27, 2015 4:08 PM
  • Import export team is on top of this issue and found that DACFx on a client machine also needs to be updated to a matching version that is deployed to IE service. I will post the download link of DACFx within the next 24hours or sooner.

    This issue impacts the scenario of importing .bacpac from Azure SQL DB to a SQL Server instances running on-prem or VM. Please note that importing the same bacpac to Azure SQL Database using IE service is not impacted by it.

    Update: The update version of DACFx is released today. Please download and install this version of DACFx to resolve this issue.

    DACFX download page

    *Please note that DACFx has 4 dependent components which is listed on System Requirements >> Dependencies section. Download and install SqlSysClrTypes.MSI and SQLDOM.MSI both x64 and x86 on a X64 machine along with DACFx.MSI

    Tools version information (added on 11/2)

    Sqlpackage.exe - a commandline tools for DACFx tasks including import and export bacpac. DACFx Oct 2015 update installs a matching version of sqlpackage.exe under <your installation drive>:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe

    SSMS Preview October 2015 update - You can download SSMS October 2015 Preview from here.


    Tuesday, October 27, 2015 9:10 PM
  • Hi Eric

    Thank you so much for this response. I'm glad the issue is under investigation.

    You can see the version(s) of DACFx I have installed in my response above. I have to say it is quite confusing to know what you have to find and install to get this working.

    I appreciate the help with this issue.


    JamesFM

    Tuesday, October 27, 2015 9:52 PM
  • I apologize all the inconveniences this incident may have caused. We will ensure to prevent this type of issue in the future updates. It might be still necessary to update DACFx on a client machine but the update notification should be more clear and the client version of DACFx should be available to download before an update on the IE service.


    Wednesday, October 28, 2015 3:39 PM
  • Hi Eric

    Thank you for your efforts to resolve this issue.

    I downloaded the newer DACFx (October preview) from the link and installed both x86 and x64 versions, and rebooted.

    The process got a little further before hitting this error:

    Could not load type 'Microsoft.SqlServer.TransactSql.ScriptDom.SecurityPolicyOption' from assembly 'Microsoft.SqlServer.TransactSql.ScriptDom, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (Microsoft.SqlServer.Dac)

    How do I resolve this? I was unable to find a dll with this version online.

    Many thanks

    James


    JamesFM

    Wednesday, October 28, 2015 6:03 PM
  • That doesn't seem at all related to the issue at hand. Did this procedure you have ever work or is this a new one?  I'm wondering if you just hit this QUERY_STORE issue (which just occurred) when trying to write a new procedure. If so, you maybe past this problem and now just be dealing with Powershell issues (or whatever script/program/IDE you are using.

    Mike

    Wednesday, October 28, 2015 6:22 PM
  • Hi Mike

    Thanks for your response.

    I'm not writing a procedure of any sort.

    I'm attempting to import a bacpac into SSMS 2016 to create a new database. The bacpac was exported from Azure sql db version 12.

    I'm using the bacpac import wizard built into SSMS to do this. The import fails with the error message above.

    FWIW my guess is that the new DACFx bits reference Microsoft.SqlServer.Dac version 13, but somehow that dll was not provided in the install. But that's just a guess.


    JamesFM

    Wednesday, October 28, 2015 6:55 PM
  • Just been informed by MS Support that the DACFx october preview is out, you need SQL2016 installed for it to work.

    http://www.microsoft.com/en-us/download/details.aspx?id=49500

    If you are still on SQL2014 then just use the answer i posted i guess

    Thursday, October 29, 2015 11:23 AM
  • Hi James

    The issue seems to be caused by having running values that differ from the default values for the various DatabaseOptions properties relating to querystore, i had to enable querystore, reset values to default then disable query store again to get it running.

    try installing the October DACFx (both 32 and 64bit versions) preview, http://www.microsoft.com/en-us/download/details.aspx?id=49500 which i have been informed should resolve the issues using SQL2016 CTP, but NOT if u you are using SQL2014

    Hope that helps

    Thursday, October 29, 2015 11:28 AM
  • Eric, We still use SQL 2014. Comments from others, and our own testing, confirms the Oct DAC does not fix the issue for SQL 2014. What is the plan to give your customers still using 2014 a solution besides the script workaround. The workaround is good, but as I understand it, this will be undone if you perform certain functions. So the workaround is not a long term solution from a maintenance standpoint.


    Mike

    Thursday, October 29, 2015 1:00 PM
  • I updated the download and install note. Please download and install dependent components - SqlSysClrtypes and SQLdom MSIs. It will resolve this issue.
    Thursday, October 29, 2015 5:40 PM
  • @Tim and Mike,

    DACFx Oct 2015 update covers the scenario. DACFx checks a target server version and ignores QDS settings and property if the server version does not support the feature. This was tested scenario before release so please let me know the issue you are encountering.

    I updated my answer in this thread for the DACFx installation dependency. If your case is not related to that then we will investigate and resolve it in a fast track manner.

    My email is erickang@microsoft.com If you would like, please drop me an email with the issue you are encountering, I will follow up with our team quickly. 

    Thursday, October 29, 2015 5:55 PM
  • Eric,

    I saw your updated notes and went to the DACFx (Oct) download page.    

    Are the links on that page pointing to versions of these dependencies that are new and go as a set with DACFx (Oct)?  We are already using and deploying the latest SMO, DMO, etc., for SQL Server SP1.


    Mike

    Thursday, October 29, 2015 6:14 PM
  • Mike,

    The linked SQLSysClrTypes and SQLDom components are versioned for SQL Server 2016 CTP3.0. These components support side by side installation with the same components released in SQL Server 2014 or lower.

    The basic concept is that a component supports side by side installation if the major version is different. It is in-place upgrade if the major version is the same. In ARP, you will see multiple entries of SQLSysClrTypes and SQLDom installed per major version. This is one of fundamental release criteria.

    To make it 100% safe, install DACFx and its dependent components on a client machine or test machine where you are NOT running a production SQL server and check if the new client tools resolve the importing issue in your environment.

    *Please note that DACFx and its dependent components are client tools components. Your SQL Server does not need these components on its machine to resolve this issue.
    Thursday, October 29, 2015 7:11 PM
  • Hi Eric

    Many thanks for providing the link to the dependencies alongside the new DACFx which addresses this issue.

    I can confirm that, with the October preview of DACFx installed, along with both dependencies (and all these in both x86 and x64 versions) I am able to import my Azure bacpac locally, using a CTP of SSMS 2016.

    What a relief! Thank you :)


    JamesFM

    Saturday, October 31, 2015 11:01 AM
  • JamesFM,

    Thank you very much for the information. I have updated my posting with more specific information about the tools version which goes hand-in-hand with DACFx October 2015 update.

    Cheers,

    Eric

    Monday, November 02, 2015 6:02 PM