[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, Working with Data Application Components in SQL Server 2008 R2, to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
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.
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.
(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.
Click Unpack. This creates a folder with the following files shown.
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.
Right click Data-Tier Applications and from the drop-down list click on Deploy Data-Tier Application....
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.
Click Next. This opens the Select Package page of the wizard. Now browse to the location where you saved your *.dacpac file as shown.
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.
Click Next. The Summary page of the wizard gets displayed as shown. The details of deployment are shown here.
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.
Open the Management folder in SQL Server 2008 R2 and review the objects as shown.
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.
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.
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:
- Easy guide to understand WCF in Visual Studio 2008 SP1 and Visual Studio 2010 Express [Article]
- MySQL Linked Server on SQL Server 2008
- MySQL Data Transfer using SQL Server Integration Services (SSIS)
- Transferring Data from MS Access 2003 to SQL Server 2008
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.
Post new comment