Working with Data Application Components in SQL Server 2008 R2

(For more resources on Microsoft, see here.)

A Data Application Component is an entity that integrates all data tier related objects used in authoring, deploying and managing into a single unit instead of working with them separately. Programmatically DACs belong to classes that are found in The Microsoft.SqlServer.Management.Dac namespace. DACs are stored in a DacStore and managed centrally. Dacs can be authored and built using SQL Server Data-Tier Application templates in VS2010 (now in Beta 2) or using SQL Server Management Studio. This article describes creating DAC using SQL Server 2008 R2 Nov-CTP(R2 server in this article), a new feature in this version.

Overview of the article

In order to proceed working with this example you need to download SQL Server 2008 R2 Nov-CTP. The ease with which this installs depend on the Windows OS on your machine. I had encountered problems installing it on my Windows XP SP3 where only partial files were installed. On Windows 7 Ultimate it installed very easily. This article uses the R2 Server installed on Windows 7 Ultimate. You can download the R2 Server from this link after registering at the site. Download the x 32 version, a 1.2 GB file.

In order to work with Data Tier Applications in Visual Studio you need to install Visual Studio 2010 now in Beta 2. If you have installed Beta 1, take it out (use Add/Remove programs) before you install Beta 2. You would create a Database Project as shown in the next figure.

In the following sections we will look at how to extract a DAC from a existing Database using tools in SSMS and R2 Server. This will be followed by deploying the DAC to a SQL Server 2008 (before R2 Version). In a future article we will see how to create and work with the DACs in Visual Studio.

Extracting a DAC

We will use the Extract a Data-Tier Application wizard to create a DAC file. Connect to the SQL Server 2008 Server in the Management Studio as shown. We will create a DAC package that will create a DAC file for us on completing this task.

Right click the Pubs database and click on Tasks | Extract Data-Tier Appplication… You may also use any other database for working with this exercise.

This brings up the Wizard as shown in the next figure. Read the notes on this window and review the database icons on this window.

Click Next. The Set Properties page of the wizard gets displayed. The Application name will be the database name with which you started. You can change it if you like. The package file name will reflect the application name. The version is set at 1.0.0.0., but you may specify any version you like. You can create different DACs with different version numbers.

Click Next. The program cranks up and after validation the Validation & Summary page gets displayed as shown. The file path of the package, the name of the package and the DAC objects that got into the package are all shown here. All database objects (Tables, Views, Stored Procedures etc) are included in the package.

Click Save Report button to save the packaging info to a file. This saves the HTML file ExtractDACSummary_HODENTEK3_ pubs_20100125 to the SQL Server Management Studio folder. This report shows what objects were validated during this process as shown.

Click Next. The Build the Package opens up and after the build process is completed you will be able to save the package as shown in the next picture.

At the package location shown earlier you will see a package object as shown. This file can be Unpacked to a destination as well as opened with Microsoft SQL Server DAC Package File Unpack wizard. These actions can be accessed by making a right click on this package file.