SQL Azure V12 BACPAC import error: "The internal target platform type SqlAzureV12DatabaseSchemaProvider does not support schema file version '3.3'"

Question

Until a very few days ago I was able to import a V12 BACPAC from Azure to my local server with SQL Server 2014 SP1 CU6 (12.0.4449.0).

But now, when I try to import the BACPAC, my SQL Server Management Studio 2014 says:

"Internal Error. The internal target platform type SqlAzureV12DatabaseSchemaProvider does not support schema file version '3.3'. (File: D:\MyDB.bacpac) (Microsoft.Data.Tools.Schema.Sql)"

I think I've the latest SQL Server 2014 SP1 version with all the latest updates (build 12.0.4449.0) but still I get this error.

Please help!

Thanks

Chiu

Sorry, i little confusing. Am i right that you mean that you was able to import BACPAC one day and was not able to import BACPAC with the same version of SSMS another day? Are you sure that there were no updates? – Nidifugous

Exactly! I was able to import the following file: MyDB-2016-4-18-21-51.bacpac and two days later MyDB-2016-4-20-10-18.bacpac gave me the error! And there were no updates. Two days :) – Chiu

I tried using SSMS 2016 is got error which says the same with version 3.5. I get it fixed by using latest version of SSMS 17.9 – Gooch

Answer 1

Fix: To resolve, use the latest SSMS Preview which installs the most up to date DacFx version. This understands how to process the latest features, notably Database Scoped Configuration Options. Once this is installed you can Import inside SSMS or using SqlPackage from the “C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin” location if you prefer command line tools.

Alternatively, execute the following command on the Azure DB to set MaxDop value back to default since it appears the issue is that this has been changed to 1. Future exports should now produce bacpacs that can be understood by the 2014 client tools, assuming no other new Azure features have been added to the DB.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0

Root cause / why does this happen: The root cause is that your database have non-default values for 1 or more Database Scoped Configuration options. As these were only added very recently, older versions of the tools do not understand how to deploy them and so DacFx blocks. These are the only properties/objects with that high a schema version. Basically any time you see an error like “does not support schema file version '3.3'” it means you need to upgrade. One possible cause is if the database was migrated from AzureV1 -> AzureV12, which sets the MaxDop option to 1 from its default of 0.

Notes: It's strongly recommended that you use the latest SSMS and keep it up to date via the built-in update notifications if you're working with Azure. it will ensure that you avoid running into issues like this one. Generally if you only use the SQL Server 2014 surface area you should be able to use older tools when re-importing, but with the huge number of recent advancements in Azure SQL DB cases like this will crop up more and more often where the new tools are required in order to perform as expected.

For reference, I’m including the Database Scoped Configuration options and their default values below. If any of these properties are non-default on the DB when exporting the schema version gets bumped so that old tools do not break.

<!-- Database Scoped Configurations-->
<Property Name="MaxDop" Type="System.Int32" DefaultValue="0" />
<Property Name="MaxDopForSecondary" Type="System.Int32?" DefaultValue="null"/>
<Property Name="LegacyCardinalityEstimation" Type="System.Boolean" DefaultValue="false" />
<Property Name="LegacyCardinalityEstimationForSecondary" Type="System.Boolean?" DefaultValue="null" />
<Property Name="ParameterSniffing" Type="System.Boolean" DefaultValue="true" />
<Property Name="ParameterSniffingForSecondary" Type="System.Boolean?" DefaultValue="null" />
<Property Name="QueryOptimizerHotfixes" Type="System.Boolean" DefaultValue="false" />
<Property Name="QueryOptimizerHotfixesForSecondary" Type="System.Boolean?" DefaultValue="null" />

Vindicate

Thanks, but why would the Database Scoped Configuration options change from one day to the next? I have the same problem at a client site where backups up to 18 April where fine, but from 19 April they have started failing. We have made no schema changes in that time. – Rurik

@Rurik - that's a good question. I'm following up with the core product team that added this to see if anything is changing the settings automatically. For reference, the issue for another customer appears to be that MAXDOP is being set to 1 (default is 0). The only way this should happen right now is if the DB is upgraded from V11 -> V12 since this was the value of the flag in that version. I'll add a workaround to the answer in the case you don't want to upgrade client tools. – Vindicate

Thanks for the reply. I can confirm that we did upgrade from v11-12 but that was a couple of months ago now. I can also see from "select * from sys.database_scoped_configurations" that the value for MAXDOP is 1 which fits with your explanation above. – Rurik

I can confirm the ALTER sql statement works for me and we can now successfully import the bacpac without upgrading SSMS. Thanks a lot for the prompt replies – Rurik

just a way for MS to push latest products down your throat – Joseph

Answer 2

The simple "Alter" solution given by Kevin (ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0) seems to be the fast solution to resolve the crisis for anyone having customer-down issues. Never mind about installing the latest DAC or SQL Server 2016, it's not necessary to resolve the immediate issue, PLUS all that is in preview status (beta). Hardly something you want to introduce into a production environment right now

This apparently only happened to us if we had a v11 database pending auto update by MSFT set for this last weekend. For those database upgrades we canceled and applied the upgrade ourselves, the Max Degree Of Parallelism field appears not to have gotten set to 0, and this error occurred. We have about 300 db's and noticed this as the pattern

FYI: You can check for that problem value with this SQL query

    SELECT [dbscm].[value]                         AS [MaxDop],
    [dbscm].[value_for_secondary]           AS [MaxDopForSecondary],
    [dbscl].[value]                         AS [LegacyCardinalityEstimation],
    [dbscl].[value_for_secondary]           AS    
    [LegacyCardinalityEstimationForSecondary],
    [dbscp].[value]                         AS [ParameterSniffing],
    [dbscp].[value_for_secondary]           AS 
    [ParameterSniffingForSecondary],
    [dbscq].[value]                         AS [QueryOptimizerHotfixes],
    [dbscq].[value_for_secondary]           AS 
    [QueryOptimizerHotfixesForSecondary]  
    FROM [sys].[databases] [db] WITH (NOLOCK)
    LEFT JOIN [sys].[database_scoped_configurations] AS [dbscm] WITH
    (NOLOCK) ON [dbscm].[name] = N'MAXDOP'
    LEFT JOIN [sys].[database_scoped_configurations] AS [dbscl] WITH  
    (NOLOCK) ON [dbscl].[name] = N'LEGACY_CARDINALITY_ESTIMATION'
    LEFT JOIN [sys].[database_scoped_configurations] AS [dbscp] WITH
    (NOLOCK) ON [dbscp].[name] = N'PARAMETER_SNIFFING'
    LEFT JOIN [sys].[database_scoped_configurations] AS [dbscq] WITH 
    (NOLOCK) ON [dbscq].[name] = N'QUERY_OPTIMIZER_HOTFIXES'
    WHERE [db].[name] = DB_NAME(); 

Maudiemaudlin

Answer 3

I was facing the same issue while I was importing an export from azure to my local MSSQLLocalDB instance (for local debugging).

I did not want to touch the azure db neither wanted to download the latest preview.

So What I did was as follows On my local db:

1. Executed the alter query setting the value for MAXDOP to 1
   ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1

2. Imported the bacpac, which ran successfully.

3. Rest the value of MAXDOP to 0
   ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0

Hope it helps somebody in similar use case

Fredel