Lesson 1: Creating a DAC in Visual Studio

Open Microsoft Visual Studio.

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

In the New Project dialog box, under Installed Templates, expand the Database node, and then click 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.

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

Select the Build tab. If you wish 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.dacpack file for deploying the DAC.

Select the Build Events tab. Observe the available settings, but do not change them.

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, fill in the connection information for an instance of the Database Engine running SQL Server 2008 Service Pack 2 or later. 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.

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.

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

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 that the Table template is selected, and change the value in the Name box at the bottom of the dialog box 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.

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.

If the instance of the Database Engine where you will upgrade the DAC is on a separate computer, copy the SampleDAC.dacpac file to a location that can be accessed from the other computer.

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 Import Data-tier Application Wizard in SQL Server Management Studio to deploy the DAC to an instance of the Database Engine.