SQL Azure V12 BACPAC import error: “The internal t

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

Answer 1

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(); 

Answer 2

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" />

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