How to: Upgrade a Data-tier Application

In-place and Side-by-side Upgrades

There are two types of DAC upgrades:

• A side-by-side upgrade preserves a copy of the existing database while building a new database that has the schema defined in the new version of the DAC. It then copies data from the old database to the new database.

• An in-place upgrade alters the schema of the existing database to match the schema defined in the new DAC version.

SQL Server 2008 R2 and Microsoft Visual Studio 2010 included the DAC Framework 1.0, which supported only side-by-side upgrades. Side-by-side upgrades do not support SQL Azure. SQL Server 2008 R2 Service Pack 1 (SP1) and Visual Studio 2010 SP1 both include a new DAC Framework 1.1, which introduces in-place upgrades on both SQL Azure and instances of the Database Engine. For more information about DAC Framework 1.1, see DAC Support For SQL Server Objects and Versions

The original version of the DAC Upgrade Wizard used DAC Framework 1.0 to perform side-by-side upgrades. The version of the wizard in SQL Server 2008 R2 SP1 performs an in-place upgrade.

SQL Server 2008 R2 users can upgrade to both the new wizard and DAC Framework 1.1 by installing SQL Server 2008 R2 SP1.

Preparing for the Upgrade

It is prudent to take a full database backup before starting the upgrade. If an upgrade encounters an error and cannot roll back all of its updates, you may need to restore the backup.

There are several actions that you should take to validate the DAC package and the upgrade actions. For more information about how to perform these checks, see How to: Validate a DAC Package.

• We recommend that you do not upgrade by using 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 examine the code, such as stored procedures or other user-defined code.

• If changes have been made to the current database after the original DAC was deployed, some of the changes may prevent the successful completion of the upgrade, or be removed by the upgrade. You should first generate a review a report of any such changes made in the database.

• It is prudent to generate a list of the schema changes the update will perform, and review them for any problems.

Choose the DAC Upgrade Options

There are four upgrade options for an in-place upgrade:

Ignore Data Loss – If True, the upgrade will proceed even if some of the operations result in the loss of data. If False, these operations will terminate the upgrade. For example, if a table in the current database is not present in the schema of the new DAC, the table will be dropped if True is specified. The default setting is True.

Block on Changes - If True, the upgrade is terminated if the database schema is different than that defined in the previous DAC. If False, the upgrade continues even if changes are detected. The default setting is False.

Rollback On Failure - If True, the upgrade are enclosed in transactions, and if errors are encountered a rollback will be attempted. If False, all changes are committed as they are made and if errors occur you may have to restore a previous backup of the database. The default setting is False.

Skip Policy Validation - If True, the DAC server selection policy is not evaluated. If False, the policy is evaluated and the upgrade terminates if there is a validation error. The default setting is False.

Requirements

A DAC can only be upgraded 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 login must be the owner of the existing database. The built-in SQL Server system administrator account named sa can also launch the wizard.

The application name in the DAC package must match the application name of the currently deployed DAC. For example, if the current DAC has an application name of GeneralLedger, you can only upgrade by using a DAC package that also has an application name of GeneralLedger.

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.

Space Considerations

For an in-place upgrade, the primary space consideration is that there be enough transaction log space available to log all of the modifications.

A side-by-side upgrade retains the current database for the deployed DAC in addition to creating a new database and copying existing data to the new database. You should have available enough free disk space to hold a new database that is as large as the existing database. Before running a side-by-side upgrade, ensure that the amount of data in the current database is not too large to fit in the new database. The database created by the upgrade will have the default size settings from the CREATE DATABASE statement. The default is for one data file with an initial size of 3MB and an unlimited number of 1MB increments, and one log file having an initial size of 1MB and maximum size limit of 2GB.

Side-by-side Database Options

In a side-by-side upgrade, the database created during the deployment phase 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.

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.

In-place DAC Upgrade Using PowerShell

This example uses the DAC Framework 1.1 IncrementalUpgrade() method to perform an in-place DAC upgrade. This method will upgrade DACs on either SQL Server or SQL Azure. The recommended way to get the new framework is to install SQL Server 2008 R2 SP1.

Create a PowerShell script (such as IncrementalUpgradeDAC.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 MyApplicationVNext.dacpac file

   ## Load the DAC package file.
   $dacpacPath = "C:\MyDACs\MyApplicationVNext.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 specify the DAC to be upgraded and the upgrade options, upgrade the DAC, and close the DAC package file:

   ## Upgrade the DAC and create the database.
   $dacName  = "MyApplication"
   ## Set the upgrade options.
   $upgradeProperties = New-Object Microsoft.SqlServer.Management.Dac.DacUpgradeOptions
   $upgradeProperties.blockonchanges = $true
   $upgradeProperties.ignoredataloss = $false 
   $upgradeproperties.rollbackonfailure = $true
   $upgradeProperties.skippolicyvalidation = $false
   ## Do not set IgnoreDrift, replaced by BlockOnChanges.
   
   ## Upgrade the DAC and create the database.
   $dacstore.IncrementalUpgrade($dacName, $dacType, $upgradeProperties)
   
   $fileStream.Close()
   

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

In-place DAC Upgrade Using a Wizard

In Management Studio, you can launch the Upgrade Data-tier Application Wizard by opening the Server node in Object Explorer, selecting the Management node, and then selecting the Data-tier Applications node. Right-click a data-tier application node in Object Explorer, and then select Upgrade Data-tier Application…

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

• Introduction Page

• Select Package Page

• Review Policy Page

• Detect Change Page

• Review the Upgrade Plan

• Summary Page

• Upgrade DAC Page

Introduction Page

This page describes the steps for upgrading 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 Package page.

Cancel - Terminates the wizard without upgrading the DAC.

Select Package Page

Use this page to specify the DAC package that contains the new version of the data-tier application. The page transitions through two 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 a DAC authored in Microsoft 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 evaluations of the conditions in the DAC server selection policy succeeded. The results of evaluating each condition are reported on a separate line.

Ignore policy violations - Use this check box to proceed with the upgrade 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 Detect Change page.

Cancel - Terminates the wizard without upgrading the DAC.

Detect Change Page

Use this page reports the results of the wizards check for changes made to the database that make it's schema different than the schema definition stored in the DAC metadata in msdb. For example, if CREATE, ALTER, or DROP statements have been used to add, change, or remove objects from the database after the DAC was originally deployed. The page first displays a progress bar, and then reports the results of the analysis.

Detecting change, this may take a few minutes - Displays a progress bar as the wizard checks for differences between the current schema of the database and the objects in the DAC definition.

Change detection results: - Indicates that the analysis has completed and the results are reported below.

The database DatabaseName has not changed - The wizard detected no differences in the objects defined in the database and their counterparts in the DAC definition.

The database DatabaseName has changed - The wizard detected changes between the objects in the database and their counterparts in the DAC definition.

Proceed despite possible loss of changes - Specifies that you understand some of the objects or data in the current database will not be present in the new database, and that you are willing to proceed with the upgrade. You should select this button only if you have analyzed the change report and understand the steps you must perform to manually transfer any objects or data required in the new database. If you are not sure, click the Save Report button to save the change report, then click Cancel. Analyze the report, plan how to transfer any required objects and data after the upgrade completes, then restart the wizard. For more information, see Upgrading Data-tier Applications.

Save Report - Click the button to save a report of the changes the wizard detected between the objects in the database and their counterparts in the DAC definition. You can then review the report to determine if you need to take actions after the upgrade completes to incorporate some or all of the objects listed in the report into the new database.

< Previous - Returns to the Select DAC Package page.

Next > - Proceeds to the Review the Upgrade Plan page.

Cancel - Terminates the wizard without deploying the DAC.

Review the Upgrade Plan Page

Use this page to do review the actions that will be taken by the upgrade process. Only proceed when you are confident the upgrade will not create problems.

The following actions will be used to upgrade the DAC. - Review the information displayed to ensure the actions taken will be correct. The Action column displays the actions, such as Transact-SQL statements, that will be run to perform the upgrade. The Data Loss column will contain a warning if the associated action could delete data.

Refresh – refreshes the action list.

Save Action Report – saves the contents of the action window to an HTML file.

Proceed despite possible loss of changes - Specifies that you understand some of the objects or data in the current database will not be present in the new database, and that you are willing to proceed with the upgrade. You should select this button only if you have analyzed the change report and understand the steps you must perform to manually transfer any objects or data required in the new database. If you are not sure, click the Save Action Report button to save the change report and the Save Scripts button to save the Transact-SQL script, then click Cancel. Analyze the report and script, and then plan how to transfer any required objects and data after the upgrade completes, then restart the wizard. For more information, see Upgrading Data-tier Applications.

Save Scripts – saves the Transact-SQL statements that will be used to perform the upgrade to a text file.

Restore Defaults - Returns the option to its default setting of false.

< Previous - Returns to the Detect Change 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 upgrading the DAC.

The following settings will be used to upgrade your DAC. - Review the information displayed to ensure the actions taken will be correct. The window displays the DAC you selected to be upgraded, and the DAC package containing the new version of the DAC. The window also displays whether the current version of the database is the same as the current DAC definition, or if the database has changed.

< Previous - Returns you to the Detect Change page.

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

Cancel - Terminates the wizard without deploying the DAC.

Upgrade DAC Page

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

Upgrading the DAC - Reports the success or failure of each action taken to upgrade 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 upgrade 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 a SQL Server Management Studio\DAC Packages folder in the Documents folder of your Windows account.

Finish - Terminates the wizard.

Side-by-side DAC Upgrade Using PowerShell

This example uses the DAC Framework 1.0 Upgrade() method to perform a side-by-side DAC upgrade. The Upgrade() method will be removed in the next version of SQL Server, and does not support SQL Azure. The preferred method is to use the DAC Framework 1.1 IncrementalUpgrade() method (covered above) to perform an in-place upgrade.

There are two upgrade options for side-by-side upgrades:

• IngoreDrift - If True, the upgrade continues even if changes are detected. If False, the upgrade is terminated if the database schema is different than that defined in the previous DAC.

• InvokeTSPolicy - If True, the DAC server selection policy is evaluated and the upgrade terminates if there is a validation error. If False, the policy is not evaluated.

To perform an in-place upgrade, create a PowerShell script (such as UpgradeDAC.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 MyApplicationVNext.dacpac file

   ## Load the DAC package file.
   $dacpacPath = "C:\MyDACs\MyApplicationVNext.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 upgrade the DAC and close the DAC package file:

   ## Upgrade the DAC and create the database.
   $dacName  = "MyApplication"
   $evaluateTSPolicy = $true
   $ignoreDriftOption = $true
   $upgradeProperties = New-Object Microsoft.SqlServer.Management.Dac.DacUpgradeOptions($evaluateTSPolicy, $ignoreDriftOption)
   $dacstore.Upgrade($dacName, $dacType, $upgradeProperties)
   $fileStream.Close()
   

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

See Also

Concepts

Change History