Share via

  • Article

How to: Validate a DAC Package

It is a good practice to review the contents of a data-tier application (DAC) package before deploying it in production, and to validate the upgrade actions before using a DAC package to upgrade an existing DAC. This is especially true when deploying packages that were not developed in your organization.

Viewing the Contents of a DAC

There are two mechanisms for viewing the contents of a DAC package. You can create a DAC project in Microsoft Visual Studio 2010 and import the DAC package to the project. You can unpack the contents of the package to a folder.

Security noteSecurity 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, deploy it on an isolated test instance of the Database Engine, run DBCC CHECKDB on the database, and also examine the code, such as stored procedures or other user-defined code, in the database.

One way to view the contents of a DAC package is to create a DAC project in Microsoft Visual Studio 2010, and import the package into the project. You can then 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. You can 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.

If you do not have access to Visual Studio 2010, you can unpack the contents of the DAC to a folder. You can then open the Transact-SQL scripts in the Database Engine Query Editor in SQL Server Management Studio, and the files in tools such as notepad. For instructions, see How to: Unpack a DAC Package.

Validating an Upgrade Using a Wizard

Before upgrading a DAC, it is good practice to first check for changes that have been made to the database after the initial DAC deployment, and to review a script of the changes the upgrade will make. You can use the Upgrade Data-tier Application wizard to view this information; you can view the database changes on the Detect Change page, and the upgrade actions on the Summary page. Click Cancel on the Summary page if you do not want to perform the upgrade. For more information, see How to: Upgrade a Data-tier Application.

View Database Changes Using PowerShell

Before using a new version of a DAC package to upgrade a DAC that was deployed from an earlier DAC package, check to see if changes have been made to the database that might affect the upgrade.

Note

The PowerShell examples in this topic use methods introduced in the DAC Framework 1.1 Feature Pack. You can download the Feature Pack from this Web page.

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

  1. Add code to create a SMO Server object and set it to the instance containing the DAC to be upgraded. 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 specify the previously deployed DAC, then create a ChangeResults object and pipe it to a text file to generate a simple report of new, deleted, and changed objects.

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

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

Generate an Upgrade Script Using PowerShell

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 script file that contains the Transact-SQL statements that will be run during the upgrade, and then review the script. Create a PowerShell script (.ps1) file containing the following code:

  1. Add code to create a SMO Server object and set it to the instance containing the DAC to be upgraded. 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 specify the previously deployed DAC, then create a Transact-SQL script and pipe it to a .sql file. Close the filestream used to read the new DAC package.

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

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

Comparing DACs

Before upgrading a DAC, you can also review the differences in the database and instance-level objects between the current and new DACs. If you do not have a copy of the package for the current DAC, you can extract a package from the current database.

If you import both DAC packages into DAC projects in Visual Studio 2010, you can use the Schema Compare tool to analyze the differences between the two DACs.

If you do not have access to Visual Studio 2010, unpack the DACs into separate folders. You can then use a difference tool, such as the WinDiff utility, to analyze the differences.