Walkthrough: Creating a New Data-tier Application Project

Article
08/08/2012

Important

To create and manage databases using the latest version of Data Tier Applications (DACPAC), install the most recent release of SQL Server Data Tools from Get Started with Microsoft SQL Server Data Tools.

In this walkthrough, you create and configure a data-tier application (DAC) project, add objects, then build and deploy the DAC to a test instance of the SQL Server Database Engine.

Tasks illustrated in this walkthrough include:

How to create and configure a DAC project.
How to configure a server selection policy for the DAC.
How to add objects to a DAC project.
How to build the DAC project to create a DAC package file that can be deployed to SQL Server 2008 R2.
How to deploy the DAC package to an instance of the Database Engine.

Prerequisites

You must have installed Microsoft Visual Studio 2010 Beta 2 and SQL Server 2008 R2. DAC projects can only be created in Visual Studio 2010. DAC packages can only be deployed to an instance of the Database Engine running SQL Server 2008 R2. The SQL Server DAC wizards are only present in the client utilities for SQL Server 2008 R2.

To create a database project

On the File menu, point to New, and click Project.

The New Project dialog box appears.
Under Installed Templates, expand the Database node, and then select the SQL Server node.
In the list of templates, click Data-tier Application.
In Name, type SampleDAC.
Accept the default Location and Solution Name.
Select the Create directory for solution check box if it is not already selected.
Clear the Add to Source Control check box if it is not already cleared, and click OK.

The SampleDAC project is created.

Next, you will edit the project properties.

To configure your project

View the Solution Explorer to confirm that the SampleDAC project has been created and is open.
View the Schema View to confirm that the SampleDAC project is available there.
In Solution Explorer, expand the SampleDAC node, right-click the Properties node, and select Open.
On the Project Settings tab, observe the default settings, but do not change them for this walkthrough.
Select the Build tab. To change the collation of the database created when you deploy the DAC, select the new collation in the Database collation box. Observe the other settings, but do not change them. With these settings, a build of the project creates a SampleDAC.dacpac file for deploying the DAC.
Select the Build Events tab. Observe the available settings, but do not change them for this walkthrough.
Select the Deploy tab. These settings control the actions if you deploy the DAC from Visual Studio. Click the Edit button beside the Destination connection string box. This opens a Connect to SQL Server dialog box. In the connection dialog box, fill in the connection information for an instance of the Database Engine running SQL Server 2008 R2. When you click OK, the dialog box builds a connection string and places it in the Destination connection string box on the Deploy tab.
Select the Code Analysis tab. Observe the set of rules you can enable if you perform a code analysis of the DAC project, but do not change them for this walkthrough.
Close the project properties dialog box.

You have now created a SampleDAC project and configured its properties.

Next, you will configure the server selection policy.

Configure the Server Selection Policy

Data-tier applications include a server selection policy that defines criterie that should be met by any instance of the Database Engine where the DAC is deployed. The database administrator can choose to ignore the policy evaluation results when they deploy the DAC. For more information, see Configuring the Server Selection Policy.

To configure a server selection policy

In Solution Explorer, expand the SampleDAC node, and then expand the Properties node. Right-click Serverselection.sqlpolicy, and then click Open.
In the Facet properties pane, select IsCaseSensitive.
In the Edit values dialog, set the Value property to either True or False, to match the default collation of the instance of the Database Engine where the DAC is to be deployed.
Click the OK button.

You have now configured the server selection policy to test whether the default collation of an instance of the Database Engine is case sensitive.

Next, you will add a table to the project.

Adding a Table and Building the DAC

Now that the DAC project is configured, the following steps illustrate adding objects to the project, then building and deploying the DAC package.

To add a table to the project

In Schema View, expand the SampleDAC node, expand the Schemas node, and expand the node for the dbo schema.
Right click the Tables node, select Add, and then Table.
In the Add New Item dialog, ensure Table template is selected, and change the value in the Name box at the bottom of the dialog to read Customer. Click the Add button. A Transact-SQL editor window is now open for a file named SampleTable.table.sql.

In the Transact-SQL editor window, edit the CREATE TABLE statement to read:

CREATE TABLE [dbo].[Customer] (
    [CustomerID]   INT           PRIMARY KEY,
    [CustomerName] NVARCHAR (40) NOT NULL,
    [YTDOrders]    INT           NOT NULL,
    [YTDSales]     INT           NOT NULL
);

Close the Transact-SQL editor, saving your changes. In Schema View, you should now see a Customer table under the dbo node. In Solution Explorer, you should now see a Customer.table.sql file.

You have now added a table to the SampleDAC project, and can build the project.

To build the project

In Solution Explorer, right-click the SampleDAC node, and select Build.
In the Output window, review the report of the build actions. One of the items reported is the path and name of the SampleDAC.dacpac file created by the build.

You have now built the SampleDAC project. You can hand off the .dacpac file to a developer or database administrator, who can then use the Deploy Data-tier Application Wizard in SQL Server Management Studio to deploy the DAC to an instance of the Database Engine.

Deploying the DAC

There are two ways to deploy a DAC, either using Visual Studio 2010 or SQL Server Management Studio in SQL Server 2008 R2. You can only deploy a DAC to an instance of the Database Engine running SQL Server 2008 R2.

To deploy the package by using Visual Studio

In Solution Explorer, right-click the SampleDAC node, and select Deploy.
In the Output window, review the report of the deploy actions. The deploy operation first builds the DAC, and then deploys it to the instance of the Database Engine you specified on the Deploy tab of the DAC project properties.

You have now deployed the SampleDAC package. The next procedure illustrates an alternative way to deploy a DAC package.

To deploy the package by using SQL Server Management Studio

Copy the SampleDAC.dacpac file from the folder where it was created (reported in the Output window of the build step) to the computer running the instance of the Database Engine where you plan to deploy the DAC.
Run SQL Server Management Studio from SQL Server 2008 R2.
In the connection dialog box, connect to the instance of the Database Engine where you plan to deploy the DAC.
In Object Explorer, expand the node for your instance of the Database Engine, then expand the Management node. Right click the Data-tier Application node and select Deploy Data-tier Application… to launch the Deploy Data-tier Application Wizard.
In the Deploy Data-tier Application Wizard, click Next to proceed after reading the introductory page.
On the Select Package page, use the Browse button to find and select the SampleDAC.dacpac file you copied to the computer. Click Next to proceed.
A progress bar is displayed as the wizard loads the package and validates the contents.
On the Review Policies page, review the results from evaluating the server selection policy criteria on the current instance of the Database Engine. Click Next to proceed.
On the Update Configuration page, review the properties that could be configured, but do not configure any for this walkthrough. Click Next to proceed.
On the Summary page, review the settings that will be made when the DAC is deployed. Click Next to deploy the DAC.
The Deploy DAC page displays the status of the actions taken to deploy the DAC as they occur. When the DAC has been deployed, click the Save Report button to save a report of the actions taken. Click Finish to complete the deployment.
In Object Explorer, expand the Data-tier Application node. You will now see a node for the deployed SampleDAC.
In Object Explorer, expand the node for your instance of the Database Engine, then expand the Databases node. You will now see a node for the SampleDAC database created by the deployment of the SampleDAC data-tier application.

You have now deployed the SampleDAC project.

Next Steps

You have successfully created, configured, built, and deployed a data-tier application. You can now start testing the DAC on the instance of the Database Engine where it was deployed.