Saturday, October 31, 2015

Creating a data-tier application definition in SQL Server 2012

Data-tier application in SQL Server defines the schema and objects that are required to support an application.It is really very simple.

There are two ways you can implement a DAC:
  • Using Microsoft Visual Studio 2010 with a SQL Server Data-tier application project type
  • Using the extraction utility in SQL Server's Extract Data-tier wizard.
In Microsoft Visual Studio 2015 Community there are no templates to do this although you can import one, if it exists in the community samples.

Here is how you extract using the SQL Server Management Studio in SQL Server 2012.

Connect to SQL Server on your computer. Here is the Object Explorer of a named instance of SQL Server 2012.


We now create a DAC using the Northwind database.
Right click Northwind to open the menu and the sub-menu as shown.


Click on Extract Data-Tier Application... to display the Introduction screen of the Wizard.


The above screen pretty well describes the actions we take. It has the three steps:
  • Set the DAC properties
  • Review object dependency and validation results
  • Build the DAC Package
Click Next to display the Set Properties page of the wizard. when you choose the databse the application name gets the database name by default and if it iss the first time the version is also set.


It appears that I have already created a file with that name and I will extract another with a different name NrthWnd.


Click Next. Displays the validation and summary page of the wizard.


Click Next. after a bit of creating and saving animation the process either succeeds or fails.


Click finish (after you get to see the Success of the operationb)  and the DAC  page is saved to the location indicated.


DAC files can be unpacked with programs shown.


PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...