資料層應用程式Data-tier Applications

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

資料層應用程式 (DAC) 是邏輯資料庫管理實體,會定義與使用者資料庫相關聯的所有 SQL ServerSQL Server 物件,例如資料表、檢視表,以及包括登入的執行個體物件。A data-tier application (DAC) is a logical database management entity that defines all of the SQL ServerSQL Server objects - like tables, views, and instance objects, including logins - associated with a user's database. DAC 是 SQL ServerSQL Server 資料庫部署的自主單位,可讓資料層開發人員和資料庫管理員將 SQL ServerSQL Server 物件包裝為可攜式成品,稱為 DAC 封裝,又稱為 DACPAC。A DAC is a self-contained unit of SQL ServerSQL Server database deployment that enables data-tier developers and database administrators to package SQL ServerSQL Server objects into a portable artifact called a DAC package, also known as a DACPAC.

BACPAC 是相關的成品,會封裝資料庫結構描述,以及在資料庫中儲存的資料。A BACPAC is a related artifact that encapsulates the database schema as well as the data stored in the database.

資料層應用程式的優點Benefits of Data-tier Applications

大多數資料庫應用程式的開發週期包含開發人員和 DBA 共用及交換應用程式更新和維護活動的指令碼和特定整合注意事項。The lifecycle of most database applications involves developers and DBAs sharing and exchanging scripts and ad hoc integration notes for application update and maintenance activities. 雖然這對小量資料庫是可接受的,但是一旦資料庫數目、大小和複雜性等方面都增加時,這很快會變得無法擴充。While this is acceptable for a small number of databases, it quickly becomes unscalable once databases grow in number, size, and complexity.

DAC 是資料庫生命週期管理和生產力工具,讓宣告式資料庫開發可以簡化部署和管理。A DAC is a database lifecycle management and productivity tool that enables declarative database development to simplify deployment and management. 開發人員可以在 SQL Server Data Tools 資料庫專案中撰寫資料庫,然後建立資料庫成為 DACPAC 以遞交給 DBA。A developer can author a database in SQL Server Data Tool database project and then build the database into a DACPAC for handoff to a DBA. DBA 可以使用 SQL Server Management Studio,將 DAC 部署至 SQL ServerSQL Server 測試或實際執行的執行個體或 Azure SQL DatabaseAzure SQL DatabaseThe DBA can deploy the DAC using SQL Server Management Studio to a test or production instance of SQL ServerSQL Server or Azure SQL DatabaseAzure SQL Database. 或者,DBA 可以使用 DACPAC 來升級以前使用 SQL Server Management Studio 部署的資料庫。Alternatively, the DBA can use the DACPAC to upgrade a previously deployed database using SQL Server Management Studio. 為了完成開發週期,DBA 可以將資料庫擷取至 DACPAC 並將它遞交給開發人員,以反映測試或實際執行調整,或啟用進一步的資料庫設計變更以回應應用程式變更。To complete the lifecycle, the DBA can extract the database into a DACPAC and hand it off to a developer to either reflect test or production adjustments, or to enable further database design changes in response to changes in the application.

DAC 驅動部署優於指令碼驅動方法,因為此工具協助 DBA 識別及驗證不同來源和目標資料庫的行為。The advantage of a DAC-driven deployment over a script driven exercise is that the tool helps the DBA with identifying and validating behaviors from different source and target databases. 在升級期間,如果升級可能會導致資料遺失,工具會警告 DBA,而且還提供升級計畫。During upgrades, the tool warns the DBA if the upgrade might cause data loss, and also provide an upgrade plan. DBA 可以評估該計畫,然後利用該工具進行升級。The DBA can evaluate the plan and then utilize the tool to proceed with the upgrade.

DAC 還支援版本設定,協助開發人員和 DBA 維護和管理資料庫生命週期中的資料庫歷程。DAC's also support versioning to help the developer and the DBA maintain and manage the database lineage through its lifecycle.

DAC 概念DAC Concepts

DAC 會簡化支援應用程式之資料層元素的開發、部署與管理:A DAC simplifies the development, deployment, and management of data-tier elements that support an application:

  • 資料層應用程式 (DAC) 是邏輯資料庫管理實體,會定義與使用者資料庫相關聯的所有 SQL Server 物件,例如資料表、檢視表和執行個體物件。A data-tier application (DAC) is a logical database management entity that defines all SQL Server objects - such as tables, views, and instance objects - associated with a user's database. 它是 SQL Server 資料庫部署的自主單位,可讓資料層開發人員和 DBA 將 SQL Server 物件包裝為可攜式成品,稱為 DAC 封裝或 .dacpac 檔案。It is a self-contained unit of SQL Server database deployment that enables data-tier developers and DBAs to package SQL Server objects into a portable artifact called a DAC package, or .dacpac file.

  • SQL Server 資料庫若要被視為 DAC,必須註冊,無論是透過使用者作業明確註冊,或其中一個 DAC 作業隱含註冊。For a SQL Server database to be treated as a DAC, it must be registered - either explicitly by a user operation, or implicitly by one of the DAC operations. 當資料庫註冊時,就會記錄 DAC 版本和其他屬性做為資料庫中繼資料的一部分。When a database is registered, the DAC version and other properties are recorded as part of the metadata of the database. 相反地,資料庫也可以取消註冊並移除其 DAC 屬性。Conversely, a database can also be unregistered and have its DAC properties removed.

  • DAC 工具通常能夠讀取舊版 SQL Server DAC 工具所產生的 DACPAC 檔案,也可以將 DACPAC 部署至舊版 SQL Server。In general, DAC tools are capable of reading DACPAC files generated by DAC tools from previous SQL Server versions, and can also deploy DACPAC's to previous versions of SQL Server. 不過,舊版 DAC 工具無法讀取新版 DAC 工具所產生的 DACPAC 檔案。However, DAC tools from earlier versions cannot read DACPAC files generated by DAC tools from later versions. 具體來說:Specifically:

    • DAC 作業是在 SQL Server 2008 R2 中導入。DAC operations were introduced in SQL Server 2008 R2. 除了 SQL Server 2008 R2 資料庫之外,工具還支援 SQL Server 2008、SQL Server 2005 和 SQL Server 2000 資料庫所產生的 DACPAC 檔案。In addition to SQL Server 2008 R2 databases, the tools support generation of DACPAC files from SQL Server 2008, SQL Server 2005 and SQL Server 2000 databases.

    • 除了 SQL 2016 資料庫之外,SQL Server 2016 隨附的工具還可以讀取 SQL Server 2008 R2 或 SQL Server 2012 隨附的 DAC 工具所產生的 DACPAC 檔案。In addition to SQL 2016 databases, the tools shipped with SQL Server 2016 can read DACPAC files generated by DAC tools shipped with SQL Server 2008 R2 or SQL Server 2012. 這包含 SQL Server 2014、2012、2008 R2、2008 和 2005 資料庫,但不包含 SQL Server 2000 資料庫。This includes databases from SQL Server 2014, 2012, 2008 R2, 2008, and 2005, but not SQL Server 2000.

    • SQL Server 2008 R2 的 DAC 工具無法讀取 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017 工具所產生的 DACPAC 檔案。DAC tools from SQL Server 2008 R2 cannot read DACPAC files generated by tools from SQL Server 2012 (11.x)SQL Server 2012 (11.x) or SQL Server 2017SQL Server 2017.

  • DACPAC 是副檔名為 .dacpac 的 Windows 檔案。A DACPAC is a Windows file with a .dacpac extension. 檔案支援開放式格式,包含代表 DACPAC 來源詳細資料、資料庫的物件和其他特性的多個 XML 區段。The file supports an open format consisting of multiple XML sections representing details of the DACPAC origin, the objects in the database, and other characteristics. 進階使用者可以使用產品隨附的 DacUnpack.exe 公用程式來解除封裝檔案,更仔細檢查每個區段。An advanced user can unpack the file using the DacUnpack.exe utility that ships with the product to inspect each section more closely.

  • 使用者必須是 dbmanager 角色的成員或被指派 CREATE DATABASE 權限,才能建立資料庫,包括部署 DAC 封裝來建立資料庫。The user must be a member of the dbmanager role or assigned CREATE DATABASE permissions to create a database, including creating a database by deploying a DAC package. 使用者必須是 dbmanager 角色的成員或被指派 DROP DATABASE 權限,才能卸除資料庫。The user must be a member of the dbmanager role, or have been assigned DROP DATABASE permissions to drop a database.

DAC 工具DAC Tools

DACPAC 可以橫跨 SQL Server 2017SQL Server 2017 隨附的多個工具緊密地使用。A DACPAC can be seamlessly used across multiple tools that ship with SQL Server 2017SQL Server 2017. 這些工具將 DACPAC 做為互通性單位,解決不同使用者角色的需求。These tools address the requirements of different user personas using a DACPAC as the unit of interoperability.

  • 應用程式開發人員:Application Developers:

    • 可以使用 SQL Server Data Tools 資料庫專案來設計資料庫。Can use a SQL Server Data Tools database project to design a database. 此專案成功建立會導致產生包含在 .dacpac 檔案中的 DACPAC。A successful build of this project results in the generation of a DACPAC contained in a .dacpac file.

    • 可以將 DACPAC 匯入資料庫專案中並繼續進行資料庫設計。Can import a DACPAC into a database project and continue to design the database.

      SQL Server Data Tools 還支援 Local DB,以進行未連接的用戶端資料庫應用程式開發。SQL Server Data Tools also supports a Local DB for unconnected, client-side database application development. 開發人員可以取得此本機資料庫的快照集,以建立包含在 .dacpac 檔案中的 DACPAC。The developer can take a snapshot of this local database to create DACPAC contained in a .dacpac file.

    • 開發人員可以獨立地將資料庫專案直接發行至資料庫,甚至不需產生 DACPAC。Independently, the developer can publish a database project directly to a database without even generating a DACPAC. 發行作業遵循類似於其他工具部署作業的行為。The publish operation follows similar behavior as the deploy operation from other tools.

  • 資料庫管理員:Database Administrators:

    • 可以使用 SQL Server Management Studio 從現有的資料庫中擷取 DACPAC,也可以執行其他 DAC 作業。Can use SQL Server Management Studio to extract a DACPAC from an existing database, and also perform other DAC operations.

    • 此外,SQL DatabaseSQL Database 的 DBA 可以使用 SQL Azure 管理入口網站執行 DAC 作業。In addition, the DBA for a SQL DatabaseSQL Database can use the Management Portal for SQL Azure for DAC operations.

  • 獨立軟體廠商:Independent Software Vendors:

    • SQL Server 主機服務和其他資料管理產品可以使用 DACFx API 執行 DAC 作業。Hosting services and other data management products for SQL Server can use the DACFx API for DAC operations.
  • IT 系統管理員:IT Administrators:

    • IT 系統整合人員和管理員可以使用 SqlPackage.exe 命令列工具執行 DAC 作業。IT systems integrators and administrators can use the SqlPackage.exe command line tool for DAC operations.

DAC 作業DAC Operations

DAC 支援下列作業:A DAC supports the following operations:

  • EXTRACT - 使用者可將資料庫擷取到 DACPAC。EXTRACT - the user can extract a database into a DACPAC.

  • DEPLOY - 使用者可將 DACPAC 部署到主機伺服器。DEPLOY - the user can deploy a DACPAC to a host server. 從管理功能工具 (如 SQL Server Management Studio 或 SQL Azure 管理入口網站) 完成部署後,主機伺服器上所產生的資料庫會隱含註冊為資料層應用程式。When the deployment is done from a manageability tool like SQL Server Management Studio or the Management Portal for SQL Azure, the resulting database in the host server is implicitly registered as a data-tier application.

  • REGISTER - 使用者可將資料庫註冊為資料層應用程式。REGISTER - the user can register a database as a data-tier application.

  • UNREGISTER - 以前註冊為 DAC 的資料庫可以取消註冊。UNREGISTER - a database previously registered as a DAC can be unregistered.

  • UPGRADE - 可以使用 DACPAC 來升級資料庫。UPGRADE - a database can be upgraded using a DACPAC. 即使以前未註冊為資料層應用程式的資料庫也支援升級,但因為升級,資料庫會隱含註冊。Upgrade is supported even on databases that are not previously registered as data-tier applications, but as a consequence of the upgrade, the database will be implicitly registered.

BACPACBACPAC

BACPAC 是副檔名為 .bacpac 的 Windows 檔案,可封裝資料庫的結構描述和資料。A BACPAC is a Windows file with a .bacpac extension that encapsulates a database's schema and data. BACPAC 的主要使用案例是將資料庫從某個伺服器移至另一個伺服器 (或將資料庫從本機伺服器移轉至雲端),以及以開放式格式封存現有資料庫。The primary use case for a BACPAC is to move a database from one server to another - or to migrate a database from a local server to the cloud - and archiving an existing database in an open format.

類似於 DACPAC,BACPAC 檔案格式是開放式;BACPAC 的結構描述內容與 DACPAC 的結構描述內容相同。Similar to the DACPAC, the BACPAC file format is open - the schema contents of the BACPAC are identical to that of the DACPAC. BACPAC 中的資料是以 JSON 格式儲存。The data in a BACPAC is stored in JSON format.

DACPAC 和 BACPAC 相似,但它們以不同的案例為目標。DACPAC and BACPAC are similar but they target different scenarios. DACPAC 專注於擷取及部署架結構描述,包括升級現有資料庫。A DACPAC is focused on capturing and deploying schema, including upgrading an existing database. DACPAC 的主要使用案例是將嚴格定義的結構描述部署至開發、測試,最後至實際執行環境。The primary use case for a DACPAC is to deploy a tightly defined schema to development, test, and then to production environments. 也以及反向:擷取實際執行的結構描述並將它反向套用至測試和開發環境。And also the reverse: capturing production's schema and applying it back to test and development environments.

另一方面,BACPAC 專注於擷取支援兩個主要作業的結構描述和資料:A BACPAC, on the other hand, is focused on capturing schema and data supporting two main operations:

  • EXPORT - 使用者可將資料庫的結構描述和資料匯出至 BACPAC。EXPORT- The user can export the schema and the data of a database to a BACPAC.

  • IMPORT - 使用者可以將結構描述和資料匯入主機伺服器中的新資料庫。IMPORT - The user can import the schema and the data into a new database in the host server.

下列資料庫管理工具支援這兩個功能:SQL Server Management Studio、Azure 入口網站和 DACFx API。Both these capabilities are supported by the database management tools: SQL Server Management Studio, the Azure Portal, and the DACFx API.

權限Permissions

您必須是 dbmanager 角色的成員或被指派 CREATE DATABASE 權限,才能建立資料庫,包括部署 DAC 封裝來建立資料庫。You must be a member of the dbmanager role or assigned CREATE DATABASE permissions to create a database, including creating a database by deploying a DAC package. 您必須是 dbmanager 角色的成員或被指派 DROP DATABASE 權限,才能卸除資料庫。You must be a member of the dbmanager role, or have been assigned DROP DATABASE permissions to drop a database.

資料層應用程式工作Data-tier Application Tasks

工作Task 主題連結Topic Link
描述如何使用 DAC 封裝檔案來建立新的 DAC 執行個體。Describes how to use a DAC package file to create a new DAC instance. 部署資料層應用程式Deploy a Data-tier Application
描述如何使用新的 DAC 封裝檔案,將執行個體升級為新版的 DAC。Describes how to use a new DAC package file to upgrade an instance to a new version of the DAC. 升級資料層應用程式Upgrade a Data-tier Application
描述如何移除 DAC 執行個體。Describes how to remove a DAC instance. 您可以選擇同時卸離或卸除相關聯的資料庫,或讓資料庫保持完整。You can choose to also detach or drop the associated database, or leave the database intact. 刪除資料層應用程式Delete a Data-tier Application
描述如何使用 SQL Server 公用程式來檢視目前已部署 DAC 的健全狀態。Describes how to view the health of currently deployed DACs by using the SQL Server Utility. 監視資料層應用程式Monitor Data-tier Applications
描述如何建立包含在 DAC 中的資料和中繼資料之封存的 .bacpac 檔案。Describes how to create a .bacpac file that contains an archive of the data and metadata in a DAC. 匯出資料層應用程式Export a Data-tier Application
描述如何使用 DAC 封存檔案 (.bacpac) 執行 DAC 的邏輯還原,或將 DAC 移轉至另一個 Database EngineDatabase EngineSQL DatabaseSQL Database 的執行個體。Describes how to use a DAC archive file (.bacpac) to either perform a logical restore of a DAC, or to migrate the DAC to another instance of the Database EngineDatabase Engine or SQL DatabaseSQL Database. 匯入 BACPAC 檔案以建立新的使用者資料庫Import a BACPAC File to Create a New User Database
描述如何匯入 BACPAC 檔案,在 SQL ServerSQL Server 執行個體內建立新的使用者資料庫。Describes how to import a BACPAC file to create a new user database within an instance of SQL ServerSQL Server. 從資料庫中擷取 DACExtract a DAC From a Database
描述如何將現有的資料庫升級為 DAC 執行個體。Describes how to promote an existing database to be a DAC instance. DAC 定義會建立並儲存在系統資料庫中。A DAC definition is built and stored in the system databases. 將資料庫註冊為 DACRegister a Database As a DAC
描述如何先檢閱 DAC 封裝的內容以及 DAC 升級要執行的動作,再於實際執行系統中使用該封裝。Describes how to review the contents of a DAC package and the actions a DAC upgrade will perform before using the package in a production system. 驗證 DAC 套件Validate a DAC Package
描述如何先將 DAC 封裝的內容放入資料庫管理員可以檢閱 DAC 作用的資料夾,再將它部署至實際伺服器。Describes how to place the contents of a DAC package into a folder where a database administrator can review what the DAC does before deploying it to a production server. 解除封裝 DAC 套件Unpack a DAC Package
描述如何使用精靈來部署現有的資料庫。Describes how to use a wizard to deploy an existing database. 精靈會使用 DAC 來執行這種部署。The wizard uses DACs to perform the deployment. 使用 DAC 部署資料庫Deploy a Database By Using a DAC

另請參閱See also

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