驗證 DAC 封裝Validate a DAC Package

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

最好先檢閱 DAC 封裝的內容,再將它部署至實際執行環境,以及先驗證升級動作,再升級現有 DAC。It is a good practice to review the contents of a DAC package before deploying it in production, and to validate the upgrade actions before upgrading an existing DAC. 當您部署的封裝之前不是在組織內開發時,特別會是這個情況。This is especially true when deploying packages that were not developed in your organization.

  1. 開始之前: 必要條件Before you begin: Prerequisites

  2. 若要升級 DAC,請使用下列方式: 檢視 DAC 內容檢視資料庫變更檢視升級動作比較 DACTo upgrade a DAC, using: View the Contents of a DAC, View Database Changes, View Upgrade Actions, Compare DACs

必要條件Prerequisites

建議您不要部署來源不明或來源不受信任的 DAC 封裝。We recommend that you do not deploy a DAC package from unknown or untrusted sources. 這類 DAC 可能包含惡意程式碼,因此可能會執行非預期的 Transact-SQLTransact-SQL 程式碼,或是修改結構描述而造成錯誤。Such DACs could contain malicious code that might execute unintended Transact-SQLTransact-SQL code or cause errors by modifying the schema. 使用來源不明或來源不受信任的 DAC 之前,請先將它部署到 Database EngineDatabase Engine 的隔離測試執行個體,並在資料庫上執行 DBCC CHECKDB (Transact-SQL),然後檢查資料庫中的程式碼,例如預存程序或其他使用者定義的程式碼。Before you use a DAC from an unknown or untrusted source, deploy it on an isolated test instance of the Database EngineDatabase Engine, run DBCC CHECKDB (Transact-SQL) on the database, and also examine the code, such as stored procedures or other user-defined code, in the database.

檢視 DAC 內容View the Contents of a DAC

有兩個機制可檢視資料層應用程式 (DAC) 封裝的內容。There are two mechanisms for viewing the contents of a data-tier application (DAC) package. 您可以在 SQL Server Developer Tools 中將 DAC 封裝匯入 DAC 專案。You can import the DAC package to a DAC project in SQL Server Developer Tools. 您可以將封裝的內容解除封裝到資料夾中。You can unpack the contents of the package to a folder.

在 SQL Server Developer Tools 中檢視 DACView a DAC in SQL Server Developer Tools

  1. 開啟 [檔案] 功能表,選取 [開新檔案] ,然後選取 [專案...] 。Open the File menu, select New, and then select Project....

  2. 選取 [SQL Server] 專案範本,並指定 [名稱][位置][方案名稱]Select the SQL Server project template, and specify a Name, Location, and Solution name.

  3. 在 [方案總管] 中,以滑鼠右鍵按一下專案節點,然後選取 [屬性...] 。In Solution Explorer, right click the project node and select Properties....

  4. 在 [專案設定] 索引標籤的 [輸出類型] 區段中,選取 [資料層應用程式 (.dacpac 檔案)] 核取方塊,然後關閉屬性對話方塊。On the Project Settings tab, in the Output Types section, select the Data-tier Application (.dacpac File) check box, and then close the properties dialog.

  5. 在 [方案總管] 中,以滑鼠右鍵按一下專案節點,然後選取 [匯入資料層應用程式...] 。In Solution Explorer, right click the project node and select Import Data-tier Application....

  6. 使用方案總管 開啟 DAC 中的所有檔案,例如伺服器選取原則和部署前後指令碼。Use Solution Explorer to open all of the files in the DAC, such as the server selection policy and the pre- and post-deployment scripts.

  7. 使用 [結構描述檢視] 檢閱結構描述中的所有物件,特別是檢閱函數或預存程序這類物件中的程式碼時。Use the Schema View to review all of the objects in the schema, particularly reviewing the code in objects such as functions or stored procedures.

檢視資料夾中的 DACView a DAC in a Folder

  • 遵循 Unpack a DAC Package中的指示,將 DAC 封裝解壓縮至資料夾。Unpack the DAC package into a folder by following the instructions in Unpack a DAC Package.

  • Transact-SQLTransact-SQL 的 [ Database EngineDatabase Engine 查詢編輯器] 中開啟 Management StudioManagement Studio指令碼以檢視其內容。View the contents of the Transact-SQLTransact-SQL scripts by opening them in the Database EngineDatabase Engine Query Editor in Management StudioManagement Studio.

  • 使用記事本這類工具檢視文字檔的內容。View the contents of the text files in tools such as notepad.

檢視資料庫變更View Database Changes

將 DAC 的目前版本部署至實際執行環境之後,直接對相關聯資料庫進行的變更可能會與新版 DAC 中所定義的結構描述衝突。After the current version of a DAC was deployed to production, changes may have been made directly to the associated database that might conflict with the schema defined in a new version of the DAC. 升級至新版 DAC 之前,請確認是否已經對資料庫進行這類變更。Before upgrading to a new version of the DAC, check to see if such changes have been made to the database.

使用精靈檢視資料庫變更View Database Changes by Using a Wizard

  1. 執行 [升級資料層應用程式精靈] ,同時指定目前部署的 DAC 以及含有新版 DAC 的 DAC 封裝。Run the Upgrade Data-tier Application wizard, specifying the currently deployed DAC and the DAC package containing the new version of the DAC.

  2. [偵測變更] 頁面上,檢閱已對資料庫進行之變更的報表。On the Detect Change page, review the report of the changes that have been made to the database.

  3. 如果您不想要繼續升級,請選取 [取消]Select Cancel if you do not want to continue with the upgrade.

  4. 如需使用精靈的詳細資訊,請參閱 升級資料層應用程式For more information on using the wizard, see Upgrade a Data-tier Application.

使用 PowerShell 檢視資料庫變更View Database Changes by Using PowerShell

  1. 建立 SMO Server 物件,並將它設定為包含要檢視之 DAC 的執行個體。Create a SMO Server object and set it to the instance that contains the DAC to be viewed.

  2. 開啟 ServerConnection 物件,並連接到相同的執行個體。Open a ServerConnection object and connect to the same instance.

  3. 指定變數中的 DAC 名稱。Specify the DAC name in a variable.

  4. 使用 GetDatabaseChanges() 方法擷取 ChangeResults 物件,並將該物件以管道傳送至文字檔以產生新的、已刪除和已變更之物件的簡單報表。Use the GetDatabaseChanges() method to retrieve a ChangeResults object, and pipe the object to a text file to generate a simple report of new, deleted, and changed objects.

檢視資料庫變更範例 (PowerShell)View Database Changes Example (PowerShell)

檢視資料庫變更範例 (PowerShell)View Database Changes Example (PowerShell)

下列範例報告在已部署的 DAC (名稱為 MyApplicaiton) 中所做的任何資料庫變更。The following example reports any database changes that have been made in a deployed DAC named MyApplicaiton.

## Set a SMO Server object to the default instance on the local computer.  
CD SQLSERVER:\SQL\localhost\DEFAULT  
$srv = get-item .  
  
## Open a Common.ServerConnection to the same instance.  
$serverconnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($srv.ConnectionContext.SqlConnectionObject)  
$serverconnection.Connect()  
$dacstore = New-Object Microsoft.SqlServer.Management.Dac.DacStore($serverconnection)  
  
## Specify the DAC instance name.  
$dacName  = "MyApplication"  
  
## Generate the change list and save to file.  
$dacChanges = $dacstore.GetDatabaseChanges($dacName) | Out-File -Filepath C:\DACScripts\MyApplicationChanges.txt  

檢視升級動作View Upgrade Actions

使用新版 DAC 封裝升級從舊版 DAC 封裝部署的 DAC 之前,可以產生一份報表,其中包含會在升級期間執行的 Transact-SQLTransact-SQL 陳述式,然後檢閱這些陳述式。Before using a new version of a DAC package to upgrade a DAC that was deployed from an earlier DAC package, you can generate a report that contains the Transact-SQLTransact-SQL statements that will be run during the upgrade, and then review the statements.

使用精靈來報告升級動作Report Upgrade Actions by Using a Wizard

  1. 執行 [升級資料層應用程式精靈] ,同時指定目前部署的 DAC 以及含有新版 DAC 的 DAC 封裝。Run the Upgrade Data-tier Application wizard, specifying the currently deployed DAC and the DAC package containing the new version of the DAC.

  2. [摘要] 頁面上,檢閱升級動作的報表。On the Summary page, review the report of the upgrade actions.

  3. 如果您不想要繼續升級,請選取 [取消]Select Cancel if you do not want to continue with the upgrade.

  4. 如需使用精靈的詳細資訊,請參閱 升級資料層應用程式For more information on using the wizard, see Upgrade a Data-tier Application.

使用 PowerShell 來報告升級動作Report Upgrade Actions by Using PowerShell

  1. 建立 SMO Server 物件,並將它設定為包含已部署之 DAC 的執行個體。Create a SMO Server object and set it to the instance that contains the deployed DAC.

  2. 開啟 ServerConnection 物件,並連接到相同的執行個體。Open a ServerConnection object and connect to the same instance.

  3. 使用 System.IO.File 以載入 DAC 封裝檔案。Use System.IO.File to load the DAC package file.

  4. 指定變數中的 DAC 名稱。Specify the DAC name in a variable.

  5. 使用 GetIncrementalUpgradeScript() 方法以取得升級要執行的 Transact-SQL 陳述式清單,並將該清單以管道傳送至文字檔。Use the GetIncrementalUpgradeScript() method to get a list of the Transact-SQL statements an upgrade would run, and pipe the list to a text file.

  6. 關閉用來讀取 DAC 封裝檔案的檔案資料流。Close the file stream used to read the DAC package file.

檢視升級動作範例 (PowerShell)View Upgrade Actions Example (PowerShell)

檢視升級動作範例 (PowerShell)View Upgrade Actions Example (PowerShell)

下列範例報告 Transact-SQL 陳述式,可執行以將 DAC (名稱為 MyApplicaiton) 升級至 MyApplication2017.dacpac 檔案中所定義的結構描述。The following example reports the Transact-SQL statements that would be run to upgrading a DAC named MyApplicaiton to the schema defined in a MyApplication2017.dacpac file.

## Set a SMO Server object to the default instance on the local computer.  
CD SQLSERVER:\SQL\localhost\DEFAULT  
$srv = get-item .  
  
## Open a Common.ServerConnection to the same instance.  
$serverconnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($srv.ConnectionContext.SqlConnectionObject)  
$serverconnection.Connect()  
$dacstore = New-Object Microsoft.SqlServer.Management.Dac.DacStore($serverconnection)  
  
## Load the DAC package file.  
$dacpacPath = "C:\MyDACs\MyApplication2017.dacpac"  
$fileStream = [System.IO.File]::Open($dacpacPath,[System.IO.FileMode]::OpenOrCreate)  
$dacType = [Microsoft.SqlServer.Management.Dac.DacType]::Load($fileStream)  
  
## Specify the DAC instance name.  
$dacName  = "MyApplication"  
  
## Generate the upgrade script and save to file.  
$dacstore.GetIncrementalUpgradeScript($dacName, $dacType) | Out-File -Filepath C:\DACScripts\MyApplicationUpgrade.sql  
  
## Close the filestream to the new DAC package.  
$fileStream.Close()  

Compare DACsCompare DACs

在升級 DAC 之前,最好先檢閱目前 DAC 與新 DAC 之間的資料庫和執行個體層級物件的差異。Before upgrading a DAC, it is a good practice to review the differences in the database and instance-level objects between the current and new DACs. 如果您沒有目前 DAC 封裝的複本,您可以從目前的資料庫擷取封裝。If you do not have a copy of the package for the current DAC, you can extract a package from the current database.

如果您在 SQL Server Developer Tools 中將這兩個 DAC 封裝匯入至 DAC 專案,則可以使用結構描述比較工具來分析這兩個 DAC 的差異。If you import both DAC packages into DAC projects in SQL Server Developer Tools, you can use the Schema Compare tool to analyze the differences between the two DACs.

您也可以將 DAC 解除封裝至不同的資料夾。Alternatively, unpack the DACs into separate folders. 然後您可以使用差異工具 (如 WinDiff 公用程式) 來分析差異。You can then use a difference tool, such as the WinDiff utility, to analyze the differences.

另請參閱See Also

資料層應用程式 Data-tier Applications
部署資料層應用程式 Deploy a Data-tier Application
升級資料層應用程式Upgrade a Data-tier Application