SQL Server 2008 R2 Feature - Data-tier applications

Downloaded and Installed SQL 2008 R2 Bits. Link
Data Tier Application - data-tier application (DAC) defines the SQL Server Database Engine schemas and objects that are required to support an application. Link. All the elements of Database - Schema, Procs, Triggers, Views can be extracted into a Data Tier App Project. This is same as reverse engineer feature we have in VSDB 2008 where we can extract all objects from a database in a database project.

DAC is available from SQL 2008 R2 onwards. VSDB project supports from SQL 2005, 2008, R2 versions.
Created a Test Database and created few tables and Procs
--STEP 1
CREATE TABLE TESTTable1
( Id INT Identity(1,1),
Name VARCHAR(50))

INSERT INTO TESTTable1(Name)
SELECT 'Test'

CREATE PROC TestProc
(@Id Int)
AS
BEGIN
 SELECT * FROM TESTTable1 (NOLOCK) WHERE Id = @Id
END

--STEP 2
Extracting Data Tier Application. Right click on Database->Tasks->Extract Data Tier Application

After this provide properties and save the Package

Created a Datatier App in VSTS 2010

Import the Created Data Tier Application from the package

After Successful Import, You Can view the tables, procedures created in it.

Now if you want to deploy this in a database. Choose Project->Properties->Under Deploy Options->Specify the connection to directly deploy it. I am able to successfully build it. On trying to deploy I got the error Invalid object name 'msdb.dbo.sysdac_instances'. I am trying to deploy of SQL 2008 Instance. It need to be R2/CTP3 to support Data Tier Deployment.

More Reads

Microsoft SQL Server Management: Developing Managed Data-Tier Applications


Happy Learning!!!