Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 4, 2010

Creating Data-Tier Applications in SQL Server 2008 R2

By Arshad Ali

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...

Introduction

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:

  • Visual Studio 2010 for a new database or for any existing database
  • SQL Server 2008 R2 Management Studio for any existing database
  • PowerShell cmdlets for any existing database

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.

Data Tier Application Project Template
> Figure 1 - Data Tier Application Project Template

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:

Exploring Data Tier Application
Figure 2 - Exploring Data Tier Application

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:

Importing Data Tier Application 1
Figure 3 - Importing Data Tier Application 1

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.

Importing Data Tier Application 2
Figure 4 - Importing Data Tier Application 2

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.

Importing Data Tier Application 3
Figure 5 - Importing Data Tier Application 3

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.

Importing Data Tier Application 4
Figure 6 - Importing Data Tier Application 4



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date