Working with Data Application Components in SQL Server 2008 R2

Exclusive offer: get 50% off this eBook here
Learning SQL Server 2008 Reporting Services

Learning SQL Server 2008 Reporting Services — Save 50%

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

$29.99    $15.00
by Jayaram Krishnaswamy | February 2010 | .NET Microsoft

In the August CTP Microsoft introduced Data-Tier Applications and several new features were introduced in the Nov 2009 CTP. Registering, Viewing & comparing and upgrading Data-Tier applications were added. This article by Dr. Jayaram Krishnaswamy, author of Learning SQL Server 2008 Reporting Services, shows how you may extract a Data Application Component from a SQL Server 2008 and then deploy the component to an SQL Server 2008 R2 Server. The process is Wizard driven and very easy to understand. DAC files in the field of DATA are like *.msi files for applications. Just as you would install an application from an MSI file, you would install a database package with the DAC file with the extension DACPAC. This article explains how it is done.

(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

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

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


Oracle SQL Developer 2.1 Design and Develop Databases using Oracle SQL Developer and its feature-rich, powerful user-extensible interface with this book and eBook
Published: December 2009
eBook Price: $32.99
Book Price: $54.99
See more
Select your format and quantity:

About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

Books From Packt


Refactoring with Microsoft Visual Studio 2010
Refactoring with Microsoft Visual Studio 2010

Microsoft Silverlight 4 and SharePoint 2010 Integration
Microsoft Silverlight 4 and SharePoint 2010 Integration

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

.NET Compact Framework 3.5 Data Driven Applications
.NET Compact Framework 3.5 Data Driven Applications

Least Privilege Security for Windows 7, Vista and XP
Least Privilege Security for Windows 7, Vista and XP

Microsoft Dynamics GP 2010 Cookbook
Microsoft Dynamics GP 2010 Cookbook

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

Applied Architecture Patterns on the Microsoft Platform
Applied Architecture Patterns on the Microsoft Platform


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
W
W
G
X
p
P
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software