Problem
I’m looking at the new features in SQL Server 2008 R2 and I see one called a Data-Tier application. Can you provide an overview of this feature?
Solution
SQL Server 2008 R2 has a set of features called Application and Multi-Server Management that focus on reducing the complexity of managing databases and applications. The Data-Tier application is one of these features. At a high level the Data-Tier application provides development, deployment, and update capabilities for an application database. The unit of deployment is a DAC package which is a file that contains everything needed to deploy (or update) the application database. The DAC package can be created from a Visual Studio 2010 Data-Tier project or from SQL Server 2008 R2 Management Studio (SSMS). In addition SQL Server 2008 R2 includes monitoring and dashboard reporting on key metrics for the Data-Tier application.
Before digging in to the details of the Data-Tier application, please note the following guidance as offered in the MSDN white paper Data-Tier Applications in SQL Server 2008 R2 (check the white paper for additional details):
- This initial release is designed for simple, departmental systems with a database size up to a few gigabytes
- There are a number of object types that are not supported; e.g. Service Broker objects, DDL triggers, Filestream columns, etc. You cannot create a Data-Tier application if your database includes these
- Filegroups are not stored in the DAC package; when you deploy you get one filegroup with a single file
- Passwords for SQL logins are not stored in the DAC package; they will be created during deployment (or update) but disabled; you have to manually enable them and set the password
- A Data-Tier application can be extracted from any version of SQL Server 2000 or later; however, a DAC package can only be deployed to SQL Server 2008 R2
Creating a Data Tier Application with SQL Server Management Studio
You can create a DAC package from an existing database with the SQL Server 2008 R2 version of Management Studio. Navigate to the database in the Object Explorer then right click it and select Tasks, Extract Data-tier Application from the context menu as shown below:
The Extract Data-tier Application wizard will be launched. Fill in the Properties dialog as shown below:
Note that the output of the wizard is a DAC package that you will import into a SQL Server Data-tier Application project in Visual Studio 2010. After filling in the above dialog, the next wizard step checks the database to make sure it can be supported by the current version of the data tier application. The final wizard step creates the DAC package which in this case is “C:\temp\Chinook.dacpac”.
Note from the Tasks context menu above that you could register a SQL Server 2008 R2 database as a Data-tier application. I’m going to proceed with how to do it from scratch in Visual Studio 2010, how to import a DAC package, and how to deploy.
Creating a Data-Tier Application with Visual Studio 2010
Beginning with Visual Studio 2005 there has been a Database project that you could use to create, maintain and deploy a SQL Server database. Visual Studio 2010 Premium and Ultimate versions include a new project template for a Data-Tier application.
To begin click File, New, Project then select SQL Server Data-tier Application as shown below:
After creating the new project the Solution Explorer has the folder layout shown below:
Like the database project in earlier versions of Visual Studio, the SQL Server Data-tier Application is used to organize each individual database object into its own .SQL file. For example to create a table, right click on Tables, then click Add, Table from the context menu and type in the name the table. A new source file will be added to the project with a stubbed out script; e.g.:
Use the text editor to complete the table schema. If you want to create a project from an existing database there are two options available to do that. Right click on the Project in the Solution Explorer and you will see the choices Import Script and Import Data-tier Application. Import Script allows you to process one or more files that contain T-SQL commands to create database objects. Import Data-tier Application will read a DAC package and create the database objects in the project. If you manually add database objects to the project, the import data-tier application will be disabled.
To import the DAC package that we created earlier, select Import Data-tier Application and navigate to the DAC package:
The wizard will continue and create the database objects in the project from the DAC package. The Solution Explorer now shows tables, indexes, etc.:
Data-Tier Application Project Settings
There are a number of project settings that you can customize to suit your needs. Right click Properties under the Project in Solution Explorer then select Open from the context menu to display the Project Settings:
Note the Name and Version; when you deploy changes make sure to increment the Version. Click Build to display the Build properties:
The Visual Studio paradigm is that you create an individual script file for tables, indexes, etc. then perform a Build which creates the DAC package. Specify the name of the DAC package and folder in the above dialog. Click Build Events to display the Build Events properties:
The Pre-build and Post-build event commands are available to specify commands that will be automatically executed when you Build. These commands could be just anything that you need to do right before the Build and right after the Build completes. Click Deploy to view the Deploy properties:
You can enable deployment directly to an instance of SQL Server 2008 R2 by specifying the connection string. Click Code Analysis to view the Code Analysis properties:
Code Analysis provides a number of built-in rules that you can enable for your project. You can specify if violating these rules will be treated as an error.
You can also specify database settings and server selection policies; expand Properties in the Solution Explorer underneath the project. There you will find Database.sqlsettings and ServerSelection.sqlpolicy. Dabtabase.sqlsettings is shown below:
ServerSelection.sqlpolicy is shown below:
The above settings will be stored in the DAC package and enforced on deployment. The Facet properties above allow you to specify things like SQL Server edition, version, etc. When you click a checkbox a dialog will be displayed allowing you to select a value from a dropdown list (e.g. True or False) or enter a value (e.g. a SQL Server major version).
Deploying a Data-Tier Application
There are two steps to deploying your Data-Tier application to an instance of SQL Server 2008 R2. First you right click on the project in Solution Explorer and select Build (or Rebuild) from the context menu. This will create the DAC package in the folder you specified in the Project Build settings dialog. The second step is to deploy the DAC package; you can right click on the project in the Solution Explorer and select Deploy from the context menu or you can deploy with SQL Server Management Studio.
To deploy using the Deploy command in the project you need to specify a connection string for the destination database on the Project Deploy properties page. To deploy using SQL Server Management Studio, navigate to the Management node in Object Explorer then right click on Deploy Data-tier Application as shown below:
The Deploy Data-tier Application wizard will be launched. Select the DAC package to deploy:
Click Next to proceed to the Update Configuration step where you can specify the database name, data file path and log file path:
Click Next to display a summary then click Next again to perform the deployment. You will see output similar to the following on completion:
Note that the database name that is initially created has a globally unique identifier (GUID) appended to it. After the process completes successfully, the database is renamed to what you specified.
Monitoring Data-Tier Applications
There is a new feature in SQL Server 2008 R2 called a Utility Control Point which provides monitoring of database instances and Data-tier applications. I will cover the setup in a future article, but right now I want to give you a preview of what you get for a data-tier application:
The above screen shot was taken from the Utility Explorer in SQL Server 2008 R2 Management Studio. Since I just created the data-tier application there is no data available yet. You get a dashboard view with CPU Utilization, Storage Utilization, Policy Details and Property Details. The only requirement is to run through the Utility Control Point setup process which I will do in a future article.
Updating a Data-Tier Application
After you deploy a data-tier application, you will undoubtedly want to make changes; e.g. add or change tables, stored procedures, views, etc. You can use the Visual Studio 2010 Data-tier project to do this and deploy your changes in the same way we did in the section above. Make your changes and be sure to increment the Version in the Project Settings dialog, then performed a Build (or Rebuild) which creates an updated DAC package. To deploy the update, go to SQL Server Management Studio, right click on the Data-tier application, then select Upgrade Data-tier Application:
The familiar wizard will be displayed; navigate to the DAC package and note that the Version has been incremented:
At a high level the wizard will create a new database, deploy everything in the DAC package to the new database, generate scripts that will copy the data from the current database to the new database, then rename the current and new databases appropriately. This is a very brief overview of the upgrade process; you can get additional details from Upgrading Data-Tier Applications in SQL Server Books on Line. I will be creating a tip focused on upgrade in the near future.
Next Steps
- The Data-tier Application is an interesting new feature in SQL Server 2008 R2 that may be able to save time when deploying and upgrading departmental databases.
- For additional details on data-tier applications take a look at this white paper on MSDN.
- Take a look at the SQL Server 2008 R2 Update for Developers Training Kit (June 2010 Update) for Presentations, Demos, Hands on Labs, and Videos. There is a wealth of material on Data-tier Applications.
- You can download the sample code used in this tip here.
