部署資料層應用程式Deploy a Data-tier Application

適用於: 是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

您可以使用精靈或 PowerShell 指令碼,將 DAC 封裝中的資料層應用程式 (DAC) 部署到現有的 Database Engine 或 Azure SQL Database 執行個體。Deploy a data-tier application (DAC) from a DAC package to an existing instance of the database engine or Azure SQL Database using a wizard or a PowerShell script.

部署程序會將 DAC 定義儲存到 msdb 系統資料庫 (SQL DatabaseSQL Database 中則是 master) 來註冊 DAC 執行個體並建立資料庫,然後使用 DAC 內定義的所有資料庫物件來擴展該資料庫。The deployment process registers a DAC instance by storing the DAC definition in the msdb system database (master in SQL DatabaseSQL Database); creates a database, then populates that database with all the database objects defined in the DAC.

多次部署相同的 DAC 封裝Deploy the same DAC package multiple times

您可以將相同的 DAC 封裝部署到單一 Database EngineDatabase Engine 執行個體多次,但是一次只能執行一個部署。You can deploy the same DAC package to a single instance of the Database EngineDatabase Engine multiple times, but you must run the deployments one at a time. 針對每個部署指定的 DAC 執行個體名稱在 Database EngineDatabase Engine執行個體中必須是唯一的。The DAC instance name specified for each deployment must be unique within the instance of the Database EngineDatabase Engine.

如果您將 DAC 部署至資料庫引擎執行個體,下次從執行個體將公用程式收集組傳送到公用程式控制點時,部署的 DAC 就會合併至 SQL Server 公用程式If you deploy a DAC to an instance of the Database Engine, the deployed DAC is incorporated into the SQL Server Utility the next time the utility collection set is sent from the instance to the utility control point. 然後 DAC 會出現在 [公用程式總管] Management StudioManagement Studio [部署的資料層應用程式] 節點中,並在 詳細資料頁面中報告。The DAC will then be present in the Deployed Data-tier Applications node of the Management StudioManagement Studio Utility Explorer and reported in the Deployed Data-tier Applications details page.

資料庫選項和設定Database Options and Settings

根據預設,部署期間建立的資料庫將會擁有 CREATE DATABASE 陳述式中的所有預設值,但是以下項目除外:By default, the database created during the deployment will have all of the default settings from the CREATE DATABASE statement, except:

  • 資料庫定序和相容性層級設定為 DAC 封裝內所定義的值。The database collation and compatibility level are set to the values defined in the DAC package. 在 SQL Server Developer Tools 中從資料庫專案建立的 DAC 封裝會使用資料庫專案中所設定的值。A DAC package built from a database project in the SQL Server Developer Tools uses the values set in the database project. 從現有的資料庫中擷取的封裝會使用原始資料庫中的值。A package extracted from an existing database uses the values from the original database.

  • 您可以在 [更新組態] 頁面上調整某些資料庫設定,例如資料庫名稱和檔案路徑。You can adjust some of the database settings, such as database name and file paths, in the Update Configuration page. 部署至 SQL DatabaseSQL Database時,無法設定檔案路徑。You cannot set the file paths when deploying to SQL DatabaseSQL Database.

某些資料庫選項 (例如 TRUSTWORTHY、DB_CHAINING 和 HONOR_BROKER_PRIORITY) 無法在部署過程中調整。Some database options, such as TRUSTWORTHY, DB_CHAINING, and HONOR_BROKER_PRIORITY, cannot be adjusted as part of the deployment process. 實體屬性 (如檔案群組數目或檔案數目和大小) 無法在部署過程中更改。Physical properties, such as the number of filegroups, or the numbers and sizes of files cannot be altered as part of the deployment process. 部署完成之後,您可以使用 ALTER DATABASE 陳述式、 SQL Server Management StudioSQL Server Management StudioSQL ServerSQL Server PowerShell 來修改資料庫。After the deployment completes, you can use the ALTER DATABASE statement, SQL Server Management StudioSQL Server Management Studio, or SQL ServerSQL Server PowerShell to tailor the database.

限制事項Limitations and restrictions

DAC 可部署至 SQL DatabaseSQL Database或執行 Database EngineDatabase Engine Service Pack 4 (SP4) 或更新版本的 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 執行個體。A DAC can be deployed to SQL DatabaseSQL Database, or an instance of the Database EngineDatabase Engine running SQL Server 2005 (9.x)SQL Server 2005 (9.x) Service Pack 4 (SP4) or later. 如果您使用更新版本建立 DAC,則 DAC 可能會包含 SQL Server 2005 (9.x)SQL Server 2005 (9.x)不支援的物件。If you create a DAC using a later version, the DAC may contain objects not supported by SQL Server 2005 (9.x)SQL Server 2005 (9.x). 您無法將這些 DAC 部署至 SQL Server 2005 (9.x)SQL Server 2005 (9.x)執行個體。You cannot deploy those DACs to instances of SQL Server 2005 (9.x)SQL Server 2005 (9.x).

安全性和權限Security and permissions

驗證登入會儲存在 DAC 封裝中,而且沒有密碼。Authentication logins are stored in a DAC package without a password. 當您部署或升級此封裝時,此登入會建立為停用的登入,而且會產生密碼。When the package is deployed or upgraded, the login is created as a disabled login with a generated password. 若要啟用登入,請使用具有 ALTER ANY LOGIN 權限的登入進行登入,並使用 ALTER LOGIN 來啟用登入,然後指派可以傳達給使用者的新密碼。To enable the logins, log in using a login with the ALTER ANY LOGIN permission and use ALTER LOGIN to enable the login and assign a new password that can be communicated to the user. Windows 驗證登入不需要這項處理,因為這類登入的密碼不是由 SQL Server 所管理。This is not required for Windows Authentication logins because their passwords are not managed by SQL Server.

只有下列項目的成員才能部署 DAC:sysadminserveradmin 固定伺服器角色,或是具有 dbcreator 固定伺服器角色及擁有 ALTER ANY LOGIN 權限的登入。A DAC can only be deployed by members of the sysadmin or serveradmin fixed server roles, or by logins in the dbcreator fixed server role with ALTER ANY LOGIN permissions. 內建的 SQL ServerSQL Server 系統管理員帳戶 (名稱為 sa ) 也可以部署 DAC。The built-in SQL ServerSQL Server system administrator account named sa can also deploy a DAC.

將具有登入的 DAC 部署至 SQL DatabaseSQL Database ,需要 loginmanager 或伺服器管理員 (serveradmin) 角色的成員資格。Deploying a DAC with logins to SQL DatabaseSQL Database requires membership in the loginmanager or serveradmin roles. 將不具有登入的 DAC 部署至 SQL DatabaseSQL Database ,需要 dbmanager 或伺服器管理員 (serveradmin) 角色的成員資格。Deploying a DAC without logins to SQL DatabaseSQL Database requires membership in the dbmanager or serveradmin roles.

使用精靈部署 DACDeploy a DAC using the wizard

  1. [物件總管] 中,展開您要部署 DAC 之執行個體的節點。In Object Explorer, expand the node for the instance to which you want to deploy the DAC.

  2. 以滑鼠右鍵按一下 [資料庫] 節點,然後選取 [部署資料層應用程式…] Right-click the Databases node, then select Deploy Data-tier Application...

  3. 完成精靈對話方塊,然後按一下 [完成]。Complete the wizard dialogs and click finish.

進一步了解下面的某些精靈頁面:More about some of the wizard pages below:

選取 DAC 封裝頁面Select DAC Package Page

指定包含要部署之資料層應用程式的 DAC 封裝。Specify the DAC package that contains the data-tier application to be deployed. 此頁面會在三種狀態之間轉換。The page transitions through three states.

選取 DAC 封裝Select the DAC Package

選擇要部署的 DAC 封裝。Choose the DAC package to deploy. DAC 封裝必須是有效的 DAC 封裝檔案,而且必須有 .dacpac 副檔名。The DAC package must be a valid DAC package file and must have a .dacpac extension.

DAC 封裝 - 指定包含要部署之資料層應用程式的 DAC 封裝的路徑和檔案名稱。DAC Package - Specify the path and file name of the DAC package that contains the data-tier application to be deployed. 您可以選取方塊右邊的 [瀏覽] 按鈕,瀏覽到 DAC 封裝的位置。You can select the Browse button at the right of the box to browse to the location of the DAC package.

應用程式名稱 - 當撰寫 DAC 或是從資料庫擷取 DAC 時,顯示指派之 DAC 名稱的唯讀方塊。Application Name - A read-only box that displays the DAC name assigned when the DAC was authored or extracted from a database.

版本 - 當撰寫 DAC 或是從資料庫擷取 DAC 時,顯示指派之版本的唯讀方塊。Version - A read-only box that displays the version assigned when the DAC was authored or extracted from a database.

描述 - 當撰寫 DAC 或是從資料庫擷取 DAC 時,顯示撰寫之描述的唯讀方塊。Description - A read-only box that displays the description written when the DAC was authored or extracted from a database.

驗證 DAC 封裝Validating the DAC Package

將進度列顯示為確認選定檔案為有效 DAC 封裝的精靈。Displays a progress bar as the wizard confirms that the selected file is a valid DAC package. 如果此 DAC 封裝已經驗證,此精靈會繼續回到最後一版的 [選取封裝] 頁面,您可以在此頁面上檢閱驗證的結果。If the DAC package is validated, the wizard proceeds to the final version of the Select Package page where you can review the results of the validation. 如果檔案不是有效的 DAC 封裝,精靈會停留在 [選取 DAC 封裝] 上。If the file is not a valid DAC package, the wizard remains on the Select DAC Package. 請選取另一個有效的 DAC 封裝,或是取消精靈並產生新的 DAC 封裝。Either select another valid DAC package or cancel the wizard and generate a new DAC package.

檢閱原則頁面Review Policy Page

檢閱評估 DAC 伺服器選取原則的結果 (如果使用的話)。Review the results of evaluating the DAC server selection policy (if used). DAC 伺服器選取原則為選擇性,而且當它在 Visual Studio 中建立時會指派給 DAC。The DAC server selection policy is optional, and is assigned to the DAC when it is created in Visual Studio. 此原則會使用伺服器選取原則 Facet 來指定 Database EngineDatabase Engine 執行個體主控 DAC 所應該符合的條件。The policy uses the server selection policy facets to specify conditions an instance of the Database EngineDatabase Engine should meet to host the DAC.

原則條件的評估結果 - 顯示 DAC 部署原則的條件是否成功。Evaluation results of policy conditions - Shows whether the DAC deployment policy conditions succeeded. 評估每個條件的結果會在個別行上報告。The results of evaluating each condition are reported on a separate line.

將 DAC 部署至 SQL DatabaseSQL Database時,下列選取伺服器原則永遠評估為 false:作業系統版本、語言、具名管道已啟用、平台和 tcp 已啟用。The following server selection policies always evaluate to false when deploying a DAC to SQL DatabaseSQL Database: operating system version, language, named pipes enabled, platform, and tcp enabled.

忽略違反原則 - 使用這個核取方塊可在一個或多個原則條件失敗時繼續部署。Ignore policy violations - Use this check box to proceed with the deployment if one or more of the policy conditions failed. 只有當您確定所有失敗的條件都不會阻礙 DAC 作業的成功時,才選取此選項。Only select this option if you are sure that all of the conditions which failed will not prevent the successful operation of the DAC.

更新組態頁面Update Configuration Page

指定部署作業所建立之部署的 DAC 執行個體和資料庫名稱,並設定資料庫選項。Specify the names of the deployed DAC instance and the database created by the deployment, and to set database options.

資料庫名稱: - 指定部署作業所要建立的資料庫名稱。Database Name: - Specify the name of the database to be created by the deployment. 預設值是擷取 DAC 的來源資料庫名稱。The default is the name of the source database the DAC was extracted from. 此名稱在 Database EngineDatabase Engine 執行個體內必須是唯一的,且必須符合 Database EngineDatabase Engine 識別碼的規則。The name must be unique within the instance of the Database EngineDatabase Engine and comply with the rules for Database EngineDatabase Engine identifiers.

如果您變更資料庫名稱,則資料檔和記錄檔的名稱也會變更,以符合新的值。If you change the database name, the names of the data file and log files will change to match the new value.

資料庫名稱也會當做 DAC 執行個體的名稱使用。The database name is also used as the name of the DAC instance. 執行個體名稱會顯示在物件總管中 [資料層應用程式] 節點或是公用程式總管中 [部署的資料層應用程式] 節點底下的 DAC 節點上。The instance name is displayed on the node for the DAC under the Data-tier Applications node in Object Explorer, or the Deployed Data-tier Applications node in the Utility Explorer.

下列選項不適用於 SQL DatabaseSQL Database,也不會在部署至 SQL DatabaseSQL Database 時顯示。The following options do not apply to SQL DatabaseSQL Database, and are not displayed when deploying to SQL DatabaseSQL Database.

使用預設資料庫位置 - 選取此選項,可在 Database EngineDatabase Engine執行個體的預設位置中建立資料庫資料檔和記錄檔。Use the default database location - Select this option to create the database data and log files in the default location for the instance of the Database EngineDatabase Engine. 檔案名稱將會使用資料庫名稱來建置。The file names will be built using the database name.

指定資料庫檔案 - 選取此選項可針對資料檔和記錄檔指定不同的位置或名稱。Specify database files - Select this option to specify a different location or name for the data and log files.

資料檔路徑和名稱: - 為資料檔指定完整路徑和檔案名稱。Data file path and name: - Specify the full path and file name for the data file. 此方塊中會填入預設路徑和檔案名稱。The box is populated with the default path and file name. 在此方塊中編輯字串來變更預設值,或使用 [瀏覽] 按鈕導覽至放置資料檔的資料夾。Edit the string in the box to change the default, or use the Browse button to navigate to the folder where the data file is to be placed.

記錄檔路徑和名稱: - 為記錄檔指定完整路徑和檔案名稱。Log file path and name: - Specify the full path and file name for the log file. 此方塊中會填入預設路徑和檔案名稱。The box is populated with the default path and file name. 在此方塊中編輯字串來變更預設值,或使用 [瀏覽] 按鈕導覽至放置記錄檔的資料夾。Edit the string in the box to change the default, or use the Browse button to navigate to the folder where the log file is to be placed.

摘要頁面Summary Page

使用此頁面來檢閱部署 DAC 時,精靈將會採取的動作。Use this page to review the actions the wizard will take when deploying the DAC.

將使用以下設定部署您的 DACThe following settings will be used to deploy your DAC. - 檢閱顯示的資訊,以確保採取的動作將會是正確的。- Review the information displayed to ensure the actions taken will be correct. 此視窗會顯示您所選取的 DAC 封裝以及您針對部署的 DAC 執行個體所選取的名稱。The window displays the DAC package you selected, and the name you selected for the deployed DAC instance. 此視窗也會顯示當您建立與 DAC 相關聯的資料庫時,將要使用的設定。The window also displays the settings that will be used when creating the database associated with the DAC.

部署頁面Deploy Page

此頁面會報告部署作業成功或失敗。This page reports the success or failure of the deploy operation.

正在部署 DAC - 報告為了部署 DAC 所採取的每個動作成功或失敗。Deploying the DAC - Reports the success or failure of each action taken to deploy the DAC. 檢閱資訊以判斷每個動作成功或失敗。Review the information to determine the success or failure of each action. 發生錯誤的所有動作在 [結果] 資料行中都會有一個連結。Any action that encountered an error will have a link in the Result column. 選取連結來檢視該動作的錯誤報告。Select the link to view a report of the error for that action.

儲存報表 - 選取此按鈕可以將部署報告儲存到 HTML 檔案。Save Report - Select this button to save the deployment report to an HTML file. 此檔案會報告每個動作的狀態,包括所有動作所產生的所有錯誤。The file reports the status of each action, including all errors generated by any of the actions. 預設資料夾為 Windows 帳戶之文件資料夾中的 SQL Server Management Studio\DAC Packages 資料夾。The default folder is the SQL Server Management Studio\DAC Packages folder in the Documents folder of your Windows account.

使用 PowerShellUsing PowerShell

  1. 建立 SMO Server 物件,並將它設為您要部署 DAC 的執行個體。Create a SMO Server object and set it to the instance to which you want to deploy the 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. 使用 add_DacActionStartedadd_DacActionFinished 訂閱 DAC 部署事件。Use add_DacActionStarted and add_DacActionFinished to subscribe to the DAC deployment events.

  5. 設定 DatabaseDeploymentPropertiesSet the DatabaseDeploymentProperties.

  6. 您可以使用 DacStore.Install 方法來部署 DAC。Use the DacStore.Install method to deploy the DAC.

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

下列範例使用 MyApplication.dacpac 封裝中 DAC 定義來部署 Database EngineDatabase Engine之預設執行個體上名為 MyApplication 的 DAC。The following example deploys a DAC named MyApplication on a default instance of the Database EngineDatabase Engine, using a DAC definition from a MyApplication.dacpac package.

## Set a SMO Server object to the default instance on the local computer.  
CD SQLSERVER:\SQL\localhost\DEFAULT  
$server = Get-Item .  
  
## Open a Common.ServerConnection to the same instance.  
$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server.ConnectionContext.SqlConnectionObject)  
$serverConnection.Connect()  
$dacStore = New-Object Microsoft.SqlServer.Management.Dac.DacStore($serverConnection)  
  
## Load the DAC package file.  
$dacpacPath = "C:\MyDACs\MyApplication.dacpac"  
$fileStream = [System.IO.File]::Open($dacpacPath,[System.IO.FileMode]::OpenOrCreate)  
$dacType = [Microsoft.SqlServer.Management.Dac.DacType]::Load($fileStream)  
  
## Subscribe to the DAC deployment events.  
$dacStore.add_DacActionStarted({Write-Host `n`nStarting at $(Get-Date) :: $_.Description})  
$dacStore.add_DacActionFinished({Write-Host Completed at $(Get-Date) :: $_.Description})  
  
## Deploy the DAC and create the database.  
$dacName  = "MyApplication"  
$evaluateTSPolicy = $true  
$deployProperties = New-Object Microsoft.SqlServer.Management.Dac.DatabaseDeploymentProperties($serverConnection,$dacName)  
$dacStore.Install($dacType, $deployProperties, $evaluateTSPolicy)  
$fileStream.Close()  

詳細資訊More information

資料層應用程式 Data-tier Applications
從資料庫中擷取 DAC Extract a DAC From a Database
資料庫識別碼Database Identifiers