Working with Data Application Components in SQL Server 2008 R2

A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008

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

Working with DAC in SQL Server 2008 R2

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.

Working with DAC in SQL Server 2008 R2

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.

Working with DAC in SQL Server 2008 R2

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.

Working with DAC in SQL Server 2008 R2

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.

Working with DAC in SQL Server 2008 R2

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.

Working with DAC in SQL Server 2008 R2

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.

Working with DAC in SQL Server 2008 R2

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.

Working with DAC in SQL Server 2008 R2

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.

Working with DAC in SQL Server 2008 R2

(For more resources on Microsoft, see here.)

Unpacking the file

Let us see what we get when we Unpack. Click on Unpack to open the following page.

Working with DAC in SQL Server 2008 R2

Click Unpack. This creates a folder with the following files shown.

Working with DAC in SQL Server 2008 R2

The MetaData file is shown in the following listing.

Listing 1

<?xml version="1.0"?>
<DacType>
<Name>pubs_DAC</Name>
<Version>1.0.0.0</Version>
<Description>Demo Application</Description>
</DacType>

The PhysicalObjectStream file is an XML file that shows the details of how the database objects were serialized including all relationships, indexes etc. Similarly the LogicalObjectStream is also an XML document. The Pubs_DAC.sql is the script file from which the database can be generated.

Deploying the DAC Package

Now that we have created a DAC package, we will see how it can deployed to another server. In the following we deploy the pub_DAC package to SQL Server 2008 R2 NOV-CTP.

Connect to the SQL Server 2008 R2 Nov-CTP server as shown. Expand nodes and click on Management to open the Management folder as shown.

Working with DAC in SQL Server 2008 R2

Right click Data-Tier Applications and from the drop-down list click on Deploy Data-Tier Application....

Working with DAC in SQL Server 2008 R2

This brings up the Deploy Data-Tier application wizard as shown. Read the notes on this window. This wizard will deploy a DAC package file to an instance of SQL Server.

Working with DAC in SQL Server 2008 R2

Click Next. This opens the Select Package page of the wizard. Now browse to the location where you saved your *.dacpac file as shown.

Working with DAC in SQL Server 2008 R2

Click Next. This brings up the Update Configuration page of the wizard. The Data and Log file paths are chosen for the server to deploy them to a named database (the same name as the package) as shown.

Working with DAC in SQL Server 2008 R2

Click Next. The Summary page of the wizard gets displayed as shown. The details of deployment are shown here.

Working with DAC in SQL Server 2008 R2

Click Next. The deployment begins and you can see the process steps as shown. In the final step the DAC is registered in the MSDB database. You can also save a copy of the deployment report.

Working with DAC in SQL Server 2008 R2

Open the Management folder in SQL Server 2008 R2 and review the objects as shown.

Working with DAC in SQL Server 2008 R2

Now expand the Databases node in the R2 Server and refresh the node. You will see that the pubs_DAC is now in the R2 Server as shown.

Working with DAC in SQL Server 2008 R2

Once you create a DAC you can also Upgrade or delete it. You essentially follow the wizard steps after invoking these actions by right clicking the DAC in the Management studio a shown.

Working with DAC in SQL Server 2008 R2

Summary

The new feature in Microsoft SQL Server 2008 R2 November CTP that integrates the database objects into a single entity called Data Application Component was described with an example. Creating a DAC from a SQL Server and deploying it to another SQL Server was also described.


Further resources on this subject:


Books to Consider

comments powered by Disqus