Creating Data-Tier Applications in SQL Server 2008 R2
November 4, 2010
SQL Server 2008 R2 with Visual Studio 2010 has introduced a new feature called Data-Tier Applications that allows the database developer to author the database, build it and hand the DAC package to the database administrator for deployment. Read on to learn more...
The collaboration between developer and database administrator has always been a challenge in the application development life cycle. Visual Studio 2005 Database Edition and Visual Studio 2008 Database Edition with GDR bridged this gap to a great extent but it still lacked flexibility and streamlined collaboration between the developer and database administrator for deployment. With these releases both the developer and database administration have to work together closely during deployment; in a nutshell even though development became easier, the deployment was a real challenge because of being iterative in nature and because several steps have to be performed manually.
SQL Server 2008 R2 with Visual Studio 2010 has introduced a new feature called Data-Tier Applications (called DAC for short), which makes database development, deployment and management much easier. Although the first version of DAC is aimed at departmental applications (applications which are not bigger in size), future versions will have support for enterprise scale applications.
When you create a Data Tier Applications project (it shares the same file extension as database projects i.e. dbproj) and build it, it creates a self-contained unit of deployment called the DAC package (which has a dacpack extension) that contains SQL Server instance objects, which are associated with the database, database objects and deployment intent (also called pre-requisite checks or deployment requirements of the application). In other words, the database developer can author the database, build it and can hand over the created DAC package to the database administrator for deployment. The database administrator then simply goes and deploys the DAC package on the required SQL instance or automates the process of deployment using PowerShell.
You can create a DAC package by either using any of the following options, although in this article I will be talking about the first two options:
Though a Data Data-Tier application can be extracted from SQL Server 2000 or later versions, a DAC package can be deployed to SQL Server 2008 SP2, SQL Server 2008 R2 and SQL Azure.
Creating Data Tier Applications using Visual Studio 2010
Visual Studio 2010 Professional, Premium and Ultimate editions allow you to create Data Tier Applications; you can find a list features by edition at Microsoft Visual Studio.
To create Data Tier Applications, go to Visual Studio 2010 IDE (Integrated Development Environment) and create a new project as shown below. On the New Project Template page, select SQL Server under the Database node on the left and in the detail pane, select the SQL Server Data Tier Application template. Next, specify the name of the project and the location where you want to create the project.
When you click on the OK button, a new Data Tier Application will be created and the structure of this project will be similar to that shown below. You can view this project in either Solution Explorer or Schema View like this:
So far we have just created an empty Data Tier Application; it does not contain any object so far. To continue from here there are three options, first start creating required objects one by one in the IDE, second if you have already created a script file then import it into the project and third import a database from a SQL Server instance as a Data Tier Application as I am going to demonstrate next. Right click on the project node in the Solution Explorer and click on Import Data-tier Application menu as shown below:
The first screen of the Import Data-tier Application wizard is a Welcome screen and it explains the process of importing from a SQL Server database or from any already available DAC package.
The second screen of the wizard lets you specify from where you want to import; if you are importing from a SQL Server database you need to specify the connection string to connect to that database or if you want to import from a DAC package then you need to specify the location and name of the DAC package. You can choose either of these two options, though for demonstration purposes I am going to import from the SQL Server database as shown below.
The third screen of the wizard gives you a list of objects, which will be imported from the specified source to the project as shown below.