How to: Deploy a Data-tier Application

Before You Begin

You can deploy a DAC to an instance of the Database Engine from SQL Server 2005 Service Pack 4 (SP4) or later, or to SQL Azure. You must use the client tools from SQL Server 2008 R2, and possibly the DAC Framework 1.1. For more information, see DAC Support For SQL Server Objects and Versions.

For a managed 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. The DAC will then be present in the Deployed Data-tier Applications node of the Management Studio Utility Explorer and reported in the Deployed Data-tier Applications details page.

For more information about creating a DAC package, see Implementing Data-tier Applications.

Requirements

When connected to SQL Server 2005 SP4 or later, a DAC can only be deployed by members of the sysadmin or serveradmin fixed server roles, or by logins that are in the dbcreator fixed server role and have ALTER ANY LOGIN permissions. The built-in SQL Server system administrator account named sa can also deploy a DAC. Deploying a DAC with logins to SQL Azure requires membership in the loginmanager or serveradmin roles. Deploying a DAC without logins to SQL Azure requires membership in the dbmanager or serveradmin roles.

Security Note

We recommend that you do not deploy a DAC package from unknown or untrusted sources. Such DACs could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema. Before you use a DAC from an unknown or untrusted source, unpack the DAC and also examine the code, such as stored procedures or other user-defined code. For more information about viewing the contents of a DAC, see How to: Validate a DAC Package.

Login Passwords

To improve security, SQL Server Authentication logins are stored in a DAC package without any password. When the package is deployed or upgraded, the login is created as a disabled login with a generated password. To enable the logins, log in using a login that has ALTER ANY LOGIN permission and use ALTER LOGIN to enable the login and assign a new password that can be communicated to the user. This is not needed for Windows Authentication logins as their passwords are not managed by SQL Server.

Database Options and Settings

By default, the database created during the deployment will have all of the default settings from the CREATE DATABASE statement, except:

• The database collation and compatibility level are set to the values defined in the DAC package. A package built from a DAC project in Visual Studio uses the values set in the DAC 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. You cannot set the file paths when deploying to SQL Azure.

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. After the deployment completes, you can use the ALTER DATABASE statement, SQL Server Management Studio, or SQL Server PowerShell to tailor the database. For more information, see Modifying a Database.

Multiple Deployments

The same DAC package can be deployed to a single instance of the Database Engine multiple times, however the deployments must be run one at a time. The DAC instance name specified for each deployment must be unique within the instance of the Database Engine.

Deploy a DAC Using PowerShell

Create a PowerShell scipt (.ps1) file containing the following code.

1. Add code to create a SMO Server object and set it to the instance containing the database from which you want to extract a DAC. This example sets a Server object to the default instance on the local computer:

   ## Set a SMO Server object to the default instance on the local computer.
   CD SQLSERVER:\SQL\localhost\DEFAULT
   $srv = get-item .
   

2. Add code to open a ServerConnection object and connect to the same instance.

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

3. Add code to load the DAC package file. This example loads a MyApplication.dacpac file

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

4. Add code to subscribe to the DAC deployment events.

   ## 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})
   

5. Add code to deploy the DAC, create the database, and close the DAC package file:

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

Run DeployDAC.ps1 from either a PowerShell session in which you have loaded the SQL Server PowerShell snapins, or by using the sqlps command prompt utility.

Using the Deploy Data-tier Application Wizard

In Management Studio, you can launch the Deploy Data-tier Application Wizard by navigating to the Management node under a server in Object Explorer, right-click the Data-tier Applications node, then select Deploy Data-tier Application…

The wizard deploys the DAC to the instance of the Database Engine associated with the node you selected in the Object Explorer hierarchy. For example, if you launch the wizard by right-clicking on the server node for an instance named ProductionServer01/Accounting, the DAC will be deployed to that instance of the Database Engine.

Click on a link in the list below to navigate to details for a page in the wizard:

• Introduction Page

• Select DAC Package Page

• Review Policy Page

• Update Configuration Page

• Summary Page

• Deploy Page

Introduction Page

This page describes the steps for deploying a data-tier application.

Do not show this page again. - Click the check box to stop the page from being displayed in the future.

Next > - Proceeds to the Select DAC Package page.

Cancel - Terminates the wizard without deploying a DAC.

Select DAC Package Page

Use this page to specify the DAC package that contains the data-tier application to be deployed. The page transitions through three states.

Select the DAC Package

Validating the DAC Package

Review Policy Page

Use this page to review the results of evaluating the DAC server selection policy, if the DAC has a policy. The DAC server selection policy is optional, and is assigned to the DAC when it is created in Visual Studio. The policy uses the server selection policy facets to specify conditions an instance of the Database Engine should meet to host the DAC.

Evaluation results of policy conditions - A read-only report showing whether the conditions of the DAC deployment policy succeeded. The results of evaluating each condition are reported on a separate line.

The following server selection policies always evaluate to false when deploying a DAC to SQL Azure: 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. Only select this option if you are sure that all of the conditions which failed will not prevent the successful operation of the DAC.

< Previous - Returns to the Select Package page.

Next > - Proceeds to the Update Configureation page.

Cancel - Terminates the wizard without deploying the DAC.

Update Configuration Page

Use this page to 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. The default is the name of the source database the DAC was extracted from. The name must be unique within the instance of the Database Engine and comply with the rules for Database Engine identifiers.

If you change the database name, the names of the data file and log files will change to match the new value.

The database name is also used as the name of the DAC instance. 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.

The following options do not apply to SQL Azure, and are not displayed when deploying to SQL Azure.

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

< Previous - Returns to the Select DAC Package page.

Next > - Proceeds to the Summary page.

Cancel - Terminates the wizard without deploying the DAC.

Summary Page

Use this page to review the actions the wizard will take when deploying the DAC.

The following settings will be used to deploy your DAC. - Review the information displayed to ensure the actions taken will be correct. The window displays the DAC package you selected, and the name you selected for the deployed DAC instance. The window also displays the settings that will be used when creating the database associated with the DAC.

< Previous - Returns you to the Update Configuration page to change your selections.

Next > - Deploys the DAC and displays the results in the Deploy DAC page.

Cancel - Terminates the wizard without deploying the DAC.

Deploy Page

This page reports the success or failure of the deploy operation.

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.

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. The default folder is the SQL Server Management Studio\DAC Packages folder in the Documents folder of your Windows account.

Finish - Terminates the wizard.

See Also

Concepts

Change History