SQL Server 物件與版本的 DAC 支援DAC Support For SQL Server Objects and Versions

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

資料層應用程式 (DAC) 支援最常用的 Database EngineDatabase Engine 物件。A data-tier application (DAC) supports the most commonly used Database EngineDatabase Engine objects.

本主題內容In This Topic

重要

本文適用於 SQL Server 2012,但不適用於 SQL Server 2014 或更新版本。This article is valid for SQL Server 2012, but not for SQL Server 2014 or later. 如需 SQL 2012 和更新版本的 DAC 文章,請參閱下列連結:For DAC articles about SQL 2012 and earlier, see the following links:

支援的 SQL Server 物件Supported SQL Server Objects

在撰寫或編輯資料層應用程式時,只能在其中指定支援的物件。Only supported objects can be specified in a data-tier application as it is being authored or edited. 您無法從包含 DAC 不支援之物件的現有資料庫中擷取、註冊或匯入 DAC。You cannot extract, register, or import a DAC from an existing database that contains objects that are not supported in a DAC. SQL Server 2017SQL Server 2017 支援下列 DAC 中的物件。supports the following objects in a DAC.

DATABASE ROLEDATABASE ROLE FUNCTION:內嵌資料表值FUNCTION: Inline Table-valued
FUNCTION:多重陳述式資料表值FUNCTION: Multistatement Table-valued FUNCTION:純量FUNCTION: Scalar
INDEX:叢集INDEX: Clustered INDEX:非叢集INDEX: Nonclustered
INDEX:空間INDEX: Spacial INDEX:唯一INDEX: Unique
登入LOGIN 權限Permissions
角色成員資格Role Memberships SCHEMASCHEMA
統計資料Statistics STORED PROCEDURE:Transact-SQLSTORED PROCEDURE: Transact-SQL
同義字Synonyms TABLE:檢查條件約束TABLE: Check Constraint
TABLE:定序TABLE: Collation TABLE:資料行,包括計算資料行TABLE: Column, including computed columns
TABLE:條件約束,預設TABLE: Constraint, Default TABLE:條件約束,外部索引鍵TABLE: Constraint, Foreign Key
TABLE:條件約束,索引TABLE: Constraint, Index TABLE:條件約束,主索引鍵TABLE: Constraint, Primary Key
TABLE:條件約束,唯一TABLE: Constraint, Unique TRIGGER:DMLTRIGGER: DML
TYPE:HIERARCHYID、GEOMETRY、GEOGRAPHYTYPE: HIERARCHYID, GEOMETRY, GEOGRAPHY TYPE:使用者定義資料類型TYPE: User-defined Data Type
TYPE:使用者定義資料表類型TYPE: User-defined Table Type 使用者USER
VIEWVIEW

SQL Server 版本的資料層應用程式支援Data-tier Application Support by the Versions of SQL Server

SQL ServerSQL Server 各版本對 DAC 作業有不同的支援層級。The versions of SQL ServerSQL Server have different levels of support for DAC operations. SQL ServerSQL Server 版本支援的所有 DAC 作業,受到該版本的所有版本支援 (例如 Standard、Enterprise、Developer 或 Evaluation)。All of the DAC operations supported by a version of SQL ServerSQL Server are supported by all editions of that version.

Database EngineDatabase Engine 執行個體支援下列 DAC 作業:Instances of the Database EngineDatabase Engine support the following DAC operations:

  • 所有支援的 SQL ServerSQL Server版本都支援匯出和擷取。Export and extract are supported on all supported versions of SQL ServerSQL Server.

  • Azure SQL DatabaseAzure SQL Database 以及所有 SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2008 R2SQL Server 2008 R2版本都支援所有作業。All operations are supported on Azure SQL DatabaseAzure SQL Database and all versions of SQL Server 2014 (12.x)SQL Server 2014 (12.x), SQL Server 2012 (11.x)SQL Server 2012 (11.x), and SQL Server 2008 R2SQL Server 2008 R2.

  • SQL Server 2008SQL Server 2008 Service Pack 2 (SP2) 或更新版本以及 SQL Server 2005 (9.x)SQL Server 2005 (9.x) SP4 或更新版本都支援所有作業。All operations are supported on SQL Server 2008SQL Server 2008 Service Pack 2 (SP2) or later, and SQL Server 2005 (9.x)SQL Server 2005 (9.x) SP4 or later.

DAC Framework 包含用戶端工具,以建立和處理 DAC 封裝和匯出檔案。The DAC Framework comprises the client-side tools for building and processing DAC packages and export files. 下列產品包含 DAC FrameworkThe following products include the DAC Framework

  • SQL Server 2014 (12.x)SQL Server 2014 (12.x) SQL Server 2012 (11.x)SQL Server 2012 (11.x) 包含可支援所有 DAC 作業的 DAC Framework 3.0。and SQL Server 2012 (11.x)SQL Server 2012 (11.x) includes DAC Framework 3.0, which supports all DAC operations.

  • SQL Server 2008 R2SQL Server 2008 R2 SP1 和 Visual Studio 2010 SP1 包含 DAC Framework 1.1,它可支援不含匯出和匯入的所有 DAC 作業。SP1 and Visual Studio 2010 SP1 included DAC Framework 1.1, which supports all DAC operations except export and import.

  • SQL Server 2008 R2SQL Server 2008 R2 和 Visual Studio 2010 包含可支援不含匯出、匯入和就地升級之所有 DAC 作業的 DAC Framework 1.0。and Visual Studio 2010 included DAC Framework 1.0, which supports all DAC operations except export, import, and in-place upgrade.

  • 舊版 SQL Server 或 Visual Studio 的用戶端工具不支援 DAC 作業。The client tools from earlier versions of SQL Server or Visual Studio do not support DAC operations.

舊版 DAC Framework 無法處理使用其中一個 DAC Framework 版本所建立的 DAC 封裝或匯出檔案。A DAC package or export file built with one version of the DAC Framework cannot be processed by an earlier version of the DAC Framework. 例如,您無法使用 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 用戶端工具部署使用 SQL Server 2008 R2SQL Server 2008 R2 用戶端工具所擷取的 DAC 封裝。For example, a DAC package extracted using the SQL Server 2014 (12.x)SQL Server 2014 (12.x) client tools cannot be deployed using the SQL Server 2008 R2SQL Server 2008 R2 client tools.

任何新版 DAC Framework 都可以處理使用其中一個 DAC Framework 版本所建立的 DAC 封裝或匯出檔案。A DAC package or export file built with one version of the DAC Framework can be processed by any later version of the DAC Framework. 例如,您可以使用 SQL Server 2008 R2SQL Server 2008 R2 SP1 或新版用戶端工具部署使用 SQL Server 2008 R2SQL Server 2008 R2 用戶端工具所擷取的 DAC 封裝。For example, a DAC package extracted using the SQL Server 2008 R2SQL Server 2008 R2 client tools can be deployed using either the SQL Server 2008 R2SQL Server 2008 R2 SP1 or higher client tools.

資料部署限制Data Deployment Limitations

請注意在 SQL Server 2012 SP1 中 DAC Framework 資料部署引擎內的這些精確度限制。Note these fidelity limitations in the DAC Framework data deployment engine in SQL Server 2012 SP1. 這些限制適用於下列 DAC Framework 動作:部署或發行 .dacpac 檔案,以及匯入 .bacpac 檔案。The limitations apply to the following DAC Framework actions: deploy or publish a .dacpac file, and import a .bacpac file.

  1. 在某些情況下遺失中繼資料及 sql_variant 資料行內的基底類型。Loss of metadata for certain conditions and base types within sql_variant columns. 在受影響的案例中,您將會看見包含下列訊息的警告:由 DAC Framework 部署時,不會保留 sql_variant 資料行內所使用之特定資料類型的特定屬性。In the affected cases, you will see a warning with the following message: Certain properties on certain data types used within a sql_variant column are not preserved when deployed by the DAC Framework.

    • MONEY、SMALLMONEY、NUMERIC、DECIMAL 基底類型:不會保留有效位數。MONEY, SMALLMONEY, NUMERIC, DECIMAL base types: Precision is not preserved.

      • DECIMAL/NUMERIC 基底類型的有效位數為 38:"TotalBytes" sql_variant 中繼資料一定會設定為 21。DECIMAL/NUMERIC base types with precision 38: the "TotalBytes" sql_variant metadata is always set to 21.
    • 所有文字基底類型:所有文字都會套用資料庫的預設定序。All text base types: The database default collation is applied for all text.

    • BINARY 基底類型:不會保留最大長度屬性。BINARY base types: Max length property is not preserved.

    • TIME、DATETIMEOFFSET 基底類型:有效位數一律設定為 7。TIME, DATETIMEOFFSET base types: Precision is always set to 7.

  2. 在 sql_variant 資料行內遺失資料。Loss of data within sql_variant columns. 在受影響的案例中,您將會看見包含下列訊息的警告:當 sql_variant DATETIME2 資料行中小數位數大於 3 的值是由 DAC Framework 所部署時,將會發生資料遺失。DATETIME2 值在部署期間限制為小數位數等於 3。In the affected case, you will see a warning with the following message: There will be data loss when a value in a sql_variant DATETIME2 column with scale greater than 3 is deployed by the DAC Framework. The DATETIME2 value is limited to a scale equal to 3 during deployment.

    • 小數位數大於 3 的 DATETIME2 基底類型:小數位數限制為等於 3。DATETIME2 base type with scale greater than 3: scale is limited to equal 3.
  3. 部署作業會因為 sql_variant 資料行內的以下情況而失敗。Deployment operation fails for the following conditions within sql_variant columns. 在受影響的案例中,您將會看見包含下列訊息的對話方塊:由於 DAC Framework 中的資料限制,所以作業失敗。In the affected cases, you will see a dialog with the following message: Operation failed due to data limitations in the DAC Framework.

    • DATETIME2、SMALLDATETIME 和 DATE 基底類型:如果此值超出 DATETIME 範圍,例如年份小於 1753。DATETIME2, SMALLDATETIME and DATE base types: If the value is outside of DATETIME range - for example, the year is less than 1753.

    • DECIMAL、NUMERIC 基底類型:如果值的有效位數大於 28。DECIMAL, NUMERIC base type: when precision of the value is greater than 28.

部署動作的其他考量Additional Considerations for Deployment Actions

請注意,DAC Framework 資料部署動作有下列考量:Note the following considerations for DAC Framework data deployment actions:

  • 擷取/匯出 - 使用 DAC Framework 從資料庫建立套件的動作 (例如擷取 .dacpac 檔案、匯出 .bacpac 檔案),這些限制都不適用。Extract/Export - On actions that use the DAC Framework to create a package from a database - for example, extract a .dacpac file, export a .bacpac file - these limitations do not apply. 封裝中的資料為來源資料庫中資料的不失真表示法。The data in the package is a full-fidelity representation of the data in the source database. 如果封裝中有上述的任一情況,則擷取/匯出記錄將會透過上述的訊息包含問題摘要。If any of these conditions are present in the package, the extract/export log will contain a summary of the issues via the messages noted above. 這是為了警告使用者,他們所建立的封裝中可能會發生資料部署問題。This is to warn the user of potential data deployment issues with the package they created. 使用者也會在記錄中看到下列摘要訊息:這些限制不會影響由 DAC Framework 建立之 DAC 套件中所儲存資料類型和值的精確度,而只適用於將 DAC 套件部署到資料庫所產生的資料類型和值。如需受影響的資料以及如何解決這個限制的詳細資訊,請參閱這個主題The user will also see the following summary message in the log: These limitations do not affect the fidelity of the data types and values stored in the DAC package created by the DAC Framework; they only apply to the data types and values resulting from deploying a DAC package to a database. For more information about the data that is affected and how to work around this limitation, seethis topic.

  • 部署/發行/匯入 - 使用 DAC Framework 將封裝部署到資料庫的動作,例如部署或發行 .dacpac 檔案以及匯入 .bacpac 檔案,這些限制都適用。Deploy/Publish/Import - On actions that use the DAC Framework to deploy a package to a database, like to deploy or publish a .dacpac file, and import a .bacpac file, these limitations do apply. 目標資料庫中產生的資料可能不包含封裝中資料的不失真表示法。The data that results in the target database may not contain a full-fidelity representation of the data in the package. 部署/匯入記錄將會在每個執行個體遇到問題時包含一則訊息 (如上所述)。The Deploy/Import log will contain a message, noted above, for every instance the issue is encountered. 錯誤將封鎖此作業 (請參閱上面的類別目錄 3),但在其他警告的情況下將會繼續。The operation will be blocked by errors - see category 3 above - but will proceed with the other warnings.

    如需此案例中受影響的資料以及如何解決部署/發行/匯入動作之這項限制的詳細資訊,請參閱 這個主題For more information about the data that is affected in this scenario and how to work around this limitation for deploy/publish/import actions, see this topic.

  • 因應措施 - 擷取和匯出作業會將不失真的 BCP 資料檔案寫入 .dacpac 或 .bacpac 檔案中。Workarounds - Extract and export operations will write full-fidelity BCP data files into the .dacpac or .bacpac files. 為了避免限制,請使用 SQL Server BCP.exe 命令列公用程式,將不失真的資料從 DAC 封裝部署到目標資料庫。To avoid limitations, use the SQL Server BCP.exe command line utility to deploy full-fidelity data to a target database from a DAC package.

另請參閱See Also

資料層應用程式Data-tier Applications