Posts Tagged ‘DAC’

Data Tier Applications – DAC / DACPAC

Posted: December 20, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , , , , ,
0

A data-tier application is an entity that contains all of the database and instance objects used by an application. It provides a single unit for authoring, deploying and managing the data tier objects, instead of having to manage them separately through scripts. A Dedicated Administrator Connection (DAC) allows tighter integration of data tier development and gives administrators an application level view of resource usage in their systems.

Data Tier Applications can be developed using Visual Studio, and then be deployed to a database using the Data Tier Deployment Wizard.

You can also Extract a Data-Tier Application for usage in Visual Studio, or easily deploy to a different instance of SQL Server, including a SQL Azure instance.


Let see a step by step example
A) Extracting a Data Tier Application

  1. Suppose First server named as VIRENDRA1_SQL2012, and DB Name is as VIRENDRATEST
  2. Now from SQL Server Management Studio Object Explorer, right click on VIRENDRATEST on VIRENDRA1_SQL2012
  3. Click Tasks |Extract Data-Tier Application
  4. As the Data Tier Application Wizard displays, click Next
  5. In the Set Properties Window specify C:\VIRENDRATEST.dacpac
    as the filename under Save to DAC package file
  6. If promped that the file is already existing, replace the existing file.
  7. Click Next
  8. On the Validation and Summary Page, click Next
  9. As the Data Extraction completes, click Finish.

You have now successfully exported a Database as a Data-Tier Application.

B) Deploying a Data Tier Application

  1. Suppose First server named as VIRENDRA2_SQL2012, and DB Name is as VIRENDRATEST
  2. In SQL Server Management Studio Object Explorer, Right-Click VIRENDRA2_SQL2012
  3. Select Deploy Data Tier Application
  4. As the Introduction Page displays, click Next
  5. In the Select Package Window Browse to C:\VIRENDRATEST.dacpac
  6. Select the Data Tier Application Package you previously exported
  7. Click Next
  8. As Package Validation completes, click Next on the Update Configuration Page
  9. Click Next on the Summary Page
  10. As Deployment completes, click Finish

You have now successfully deployed a Data Tier Application to a SQL Server Instance running on a different server.