Creating Data-Tier Applications in SQL Server 2008 R2 - Page 2

November 4, 2010

If your source is SQL Server database (and not DAC package), it might contain certain objects, which are not supported by Data Tier Applications; the third screen also gives an objects list, which cannot be imported into project, if it finds any, as shown below. For a complete list of supported objects can be found on MSDN.

Figure 7 - Importing Data Tier Application 5

When you click on the Finish button, the import process starts and the status is displayed on the screen as shown below. Click on the Finish button to close the wizard after import.

Figure 8 - Importing Data Tier Application 6

Once the project has been imported, a data tier application project structure will be created as shown below. You can browse through the imported objects using the Solution Explorer or Schema View window. You can change the definition of existing objects or author new objects in the project.

Figure 9 - Data Tier Application Project Structure

Once the data tier application project has been created, you can change the project -- the location where the DAC package will be created after build, the name of the DAC package, version of the DAC package, when it will be built, etc. Right click on the project in the Solution Explorer and click on the Properties menu; a screen similar to that shown below will appear. In the Project Settings page you can specify the version, description etc.

Figure 10 - Data Tier Application Properties - Project Settings

In the build page, you can specify default collation, which will be used in the generated DAC package during deployment, location where DAC package will be created, and name of the DAC package as shown below.

Figure 11 - Data Tier Application Properties - Build

In the Build Events page you can specify commands to be executed before and after the project is built.

Figure 12 - Data Tier Application Properties - Build Events

In the Deploy page, you specify a connection string if you want to build and deploy the project. Next you also specify the name of the database/application to be created on the specified connection string during deployment.

Figure 13 - Data Tier Application Properties - Deploy

Code Analysis is new feature, which analyzes the SQL code for any violation and reports it as a warning or error during build. On this page, you can select which rules you want to consider during building the project for analyzing.

Figure 14 - Data Tier Application Properties - Code Analysis

Data tier application has a cool new feature (also a much awaited feature) to declare deployment intent, which means you can check the pre-requisite for your application deployment even before deployment and if it satisfies the specified criteria/policy then deploy it. For example, I want to deploy my data tier application on SQL Server 2008 (Version: 10) and higher and this is what I have declared below. To define deployment intent, double click on the ServerSelection.sqlpolicy file under Properties in Solution Explorer, which will bring up a page like this.

Figure 15 - Deployment Intent Declaration

To build your project simply hit F6 or press CTRL+SHIFT+B or go to the Build menu and click either on Build Solution or Rebuild Solution menu as shown below. This will create a DAC package (with *.dacpack extension, you can watch the Output window for detail) in the specified location.

Figure 16 - Building Data Tier Application