SQL Azure V12 BACPAC导入错误：“内部目标平台类型SqlAzureV12DatabaseSchemaProvider不支持模式文件版本'3.3'”

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).

直到几天前，我才能使用SQL Server 2014 SP1 CU6（12.0.4449.0）将Azure中的V12 BACPAC导入到本地服务器。

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

但现在，当我尝试导入BACPAC时，我的SQL Server Management Studio 2014说：

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

“内部错误。内部目标平台类型SqlAzureV12DatabaseSchemaProvider不支持模式文件版本'3.3'。（文件：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.

我想我最新的SQL Server 2014 SP1版本包含所有最新更新（版本12.0.4449.0），但我仍然收到此错误。

Please help!

请帮忙！

Thanks

谢谢

3 个解决方案

#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.

修复：要解决此问题，请使用安装最新DacFx版本的最新SSMS预览版。这了解如何处理最新功能，尤其是数据库范围配置选项。安装完成后，如果您更喜欢命令行工具，可以在SSMS内部导入或使用“C：\ Program Files（x86）\ Microsoft SQL Server \ 130 \ DAC \ bin”位置中的SqlPackage。

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.

或者，在Azure DB上执行以下命令以将MaxDop值设置回默认值，因为它似乎已经更改为1.未来的导出现在应该生成可以被2014客户端工具理解的bacpac，假设没有其他新的Azure功能已添加到数据库中。

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.

根本原因/为什么会发生这种情况：根本原因是您的数据库具有一个或多个Database Scoped Configuration选项的非默认值。由于这些仅在最近才添加，因此旧版本的工具无法理解如何部署它们，因此DacFx会阻止。这些是具有高模式版本的唯一属性/对象。基本上每当你看到“不支持模式文件版本''3.3'这样的错误时，就意味着你需要升级。一个可能的原因是数据库是从AzureV1迁移 - > AzureV12，它将MaxDop选项从默认值0设置为1。

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.

注意：如果您正在使用Azure，强烈建议您使用最新的SSMS并通过内置更新通知使其保持最新。它将确保您避免遇到类似这样的问题。通常，如果您只使用SQL Server 2014表面区域，则应该能够在重新导入时使用较旧的工具，但是随着Azure SQL DB中的大量最新进展，这样的情况会越来越频繁地出现在新工具中是为了按预期执行所必需的。

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 Configuration选项及其默认值。如果导出架构版本时数据库中的任何这些属性都是非默认属性，那么旧工具就不会中断。

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

#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

凯文（ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0）给出的简单“Alter”解决方案似乎是解决危机问题的快速解决方案。没关系安装最新的DAC或SQL Server 2016，没有必要解决即时问题，PLUS所有处于预览状态（测试版）。您现在几乎不想将其引入生产环境

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

这显然只发生在我们身上，如果我们有一个v11数据库，等待上周末MSFT设置的自动更新。对于我们取消并自行应用升级的数据库升级，Max Degree Of Parallelism字段似乎没有设置为0，并且发生了此错误。我们有大约300分贝，并注意到这是模式

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

仅供参考：您可以使用此SQL查询检查该问题值

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

#3

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

当我从azure导入导出到我的本地MSSQLLocalDB实例（用于本地调试）时，我遇到了同样的问题。

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

我不想触摸azure db也不想下载最新的预览版。

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

   执行alter query设置MAXDOP的值为1 ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1

2. Imported the bacpac, which ran successfully.

   进口成功运行的bacpac。

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

   将MAXD​​OP的值保持为0 ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0

Hope it helps somebody in similar use case

希望它可以帮助类似用例的人