Chapter 3: Data-Tier Applications

Microsoft Press
Nov 30, 2010
22.5k
0
0
Article

This chapter is taken from book "Introducing Microsoft SQL Server 2008 R2" by Ross Mistry and Stacia Misner published for Microsoft Press.

Ask application developers or database administrators what it was like to work with data-driven applications in the past, and most probably do not use adjectives such as "easy," "enjoyable," or "wonderful" when they describe their experience. Indeed, the development, deployment, and even the management of data-driven applications in the past were a struggle. This was partly because Microsoft SQL Server and Microsoft Visual Studio were not really outfitted to handle the development of data-driven applications, the ability to create deployment policies did not exist, and application developers couldn't effortlessly hand off a single package to database administrators for deployment. After a data-driven application was deployed, developers and administrations found making changes to be a tedious process. Much later in the life cycle of data-driven applications, they came to the stark realization that there was no tool available to centrally manage a deployed environment. Obviously, many challenges existed throughout the life cycle of a data-driven application.

Introduction to Data-Tier Applications

With the release of Microsoft SQL Server 2008 R2, the SQL Server Manageability team addressed these struggles by introducing support for data-tier applications to help streamline the deployment, management, and upgrade of database applications. A data tier application, also referred to as a DAC, is a single unit of deployment that contains all the elements used by an application, such as the database application schema, instance level objects, associated database objects, files and scripts, and even a manifest defining the organization's deployment requirements.

The DAC improves collaboration between data-tier developers and database administrators throughout the application life cycle and allows organizations to develop, deploy, and manage data-tier applications in a much more efficient and effective manner than ever before, mainly because the DAC file functions as a single unit. Database administrators can now also centrally manage, monitor, deploy, and upgrade data-tier applications with SQL Server Management Studio and view DAC resource utilization across the SQL Server infrastructure in Utility Explorer at scale.

The Data-Tier Application Life Cycle

There are two common methods for generating a DAC. One is to author and build a DAC using a SQL Server data-tier application project in Microsoft Visual Studio 2010. In the second method, you can extract a DAC from an existing database by using the Extract Data-Tier Application Wizard in SQL Server Management Studio. Alternatively, a DAC can be generated with Windows PowerShell commands.

Figure 3-1 illustrates the data-tier application generation and deployment life cycle for both a new data-tier application project in Visual Studio 2010 and an extracted DAC created with the Extract Data-Tier Application Wizard in SQL Server Management Studio (SSMS). In the illustration, the DAC package is deployed to the same instance of SQL Server 2008 R2 in both methodologies.

FIGURE 3-1 The data-tier application life cycle

Data-tier developers using a data-tier application project template in Visual Studio 2010 first build a DAC and then deploy the DAC package to an instance of SQL Server 2008 R2. In contrast, database administrators using the Extract Data-Tier Application Wizard in SQL Server Management Studio generate a DAC from an existing database. The DAC package is then deployed to a SQL Server 2008 R2 instance. In both methods, the deployment creates a DAC definition that is stored in the msdb system database and a user database that stores the objects identified in the DAC definition. Finally, the applications connect to the database associated with the DAC. Database administrators use the Utility Control Point and Utility Explorer in SQL Server Management Studio to centrally manage and monitor data-tier applications at scale.

Common Uses for Data-Tier Applications

Data-tier applications are used in a multitude of ways to serve many different needs. For example, organizations may use data-tier applications when they need to

Deploy a data-tier application for test, staging, and production instances of the Database Engine.
Create DAC packages to tighten integration handoffs between data-tier developers and database administrators.
Move changes from development to production.
Upgrade an existing DAC instance to a newer version of the DAC by using the Upgrade Data-Tier Wizard.
Compare database schemas between two data-tier applications.
Upgrade database schemas from older versions of SQL Server to SQL Server 2008 R2-for example, to extract a data-tier application from SQL Server 2000 and then deploy the package on SQL Server 2008 R2.
Consider next generation development, which is achieved by importing an existing version of a DAC into Visual Studio and then modifying the schema, objects, or deployment strategies.
Author database objects by using source code control systems such as Team Foundation Server.
Integrate data-tier applications with Microsoft SQL Azure. Currently, it is possible to deploy, register, and delete. Upgrading of data-tier applications with SQL Azure is likely to be supported in future releases.

REAL WORLD

Organizations looking to accelerate and standardize deployment of database applications within their database environments should leverage data-tier applications included in SQL Server 2008 R2. By utilizing data-tier applications, an organization captures intent and produces a single deployment package, providing a more reliable and consistent deployment experience than ever before. In addition, data-tier applications facilitate streamlined collaboration between development and database administrator teams, which improves efficiency.

Supported SQL Server Objects

Every DAC contains objects used by the application, including schemas, tables, and views. However, some objects are not supported in data-tier applications. The following list can help you become acquainted with some of the SQL Server objects that are supported.

Database role
Function: Inline Table-valued
Function: Multistatement Table-valued
Function: Scalar
Index: Clustered
Index: Non-clustered
Index: Unique
Login
Schema
Stored Procedure: Transact-SQL
Table: Check Constraint
Table: Collation
Table: Column, including computed columns
Table: Constraint, Default
Table: Constraint, Foreign Key
Table: Constraint, Index
Table: Constraint, Primary Key
Table: Constraint, Unique
Trigger: DML
Type: User-defined Data Type
Type: User-defined Table Type
User
View

Database administrators do not have to worry about looking for unsupported objects. This laborious task is accomplished with the Extract Data-Tier Application Wizard. Unsupported objects such as DDL triggers, service broker objects, and full-text catalog objects are identified and reported by the wizard. Unsupported objects are identified with a red icon that represents an invalid entry. Database administrators must also pay close attention to objects with a yellow icon, because this communicates a warning. A yellow icon usually warns database administrators that although an object is supported, it is linked to and quite reliant on an unsupported object. Database administrators need to review and address all objects with red and yellow icons. The wizard does not create a DAC package until unsupported objects are removed. For a list of some common supported objects, review the topic "SQL Server Objects Supported in Data-tier Applications" at http://msdn.microsoft.com/en-us/library/ee210549(SQL.105).aspx.

Visual Studio 2010 and Data-Tier Application Projects

By leveraging the new project DAC template in Visual Studio 2010, data-tier developers can create new data-tier applications from scratch or edit existing data-tier applications by importing them directly into a project. Data-tier developers then add database objects such as tables, views, and stored procedures to the data-tier application project. Data-tier developers can also define specific deployment requirements for the data-tier application. When the data-tier application project is complete, the data-tier developer creates a single unit of deployment, known as a DAC file package, from within Visual Studio 2010. This package is delivered to a database administrator, who deploys it to one or more SQL Server 2008 R2 instances. Alternatively, database administrators can use the DAC package to upgrade an existing data-tier application that has already been deployed.

Launching a Data-Tier Application Project Template in Visual Studio 2010

The following steps describe how to launch a data-tier application project template in Visual Studio 2010:

Launch Visual Studio 2010.
In Visual Studio 2010, select File, and then select New Project.
In the Installed Templates list, expand the Database node, and then select SQL Server.
In the Project Template pane, select Data-Tier Application.
Specify the name, location, and solution name for the data-tier application, as shown in Figure 3-2, and click OK.

FIGURE 3-2 Selecting the Data-Tier Application project template in Visual Studio 2010
Select Project, and then click Add New Item to add and create a database object based on the Data-Tier Application project template. Some of the database objects included in the template are scalar-valued function, schema, table, index, login, stored procedure, user, user-defined table type, view, table-valued function, trigger, user-defined data type, database role, data generation plan, and inline function.

Figure 3-3 illustrates the syntax for creating a sample Employees table schema for a data-tier application in Visual Studio 2010. The Solution Explorer pane also includes the other schema objects-specifically the tables associated with the data-tier application.

FIGURE 3-3 The Create Table schema and the Solution Explorer pane in a Visual Studio 2010 DAC project

Importing an Existing Data-Tier Application Project into Visual Studio 2010

Instead of creating a DAC from the ground up in Visual Studio 2010, a data-tier developer can choose to import an existing data-tier application into Visual Studio 2010 and then either edit the DAC or completely reverse-engineer it. The following steps enable you to import objects from a data-tier application package to a data-tier application project in Visual Studio 2010:

Create a new data-tier application project in Visual Studio.
In the Visual Studio Solution Explorer pane, navigate to the node for the desired data tier application project.
Right-click the node for the desired data-tier application project, and then select the Import Data-Tier Application Wizard.
Review the information on the Welcome page, and then click Next.
On the Specify Import Options page, select the option that allows you to import from a data-tier application package.
Click the Browse button, and navigate to the folder in which you placed the .dacpac to import. Select the file, and then click Open. Click Next to continue.
Review the report that shows the status of the import actions, as illustrated in Figure 3-4, and then click Finish.

FIGURE 3-4 Reviewing the results when importing an existing DAC into Visual Studio 2010
In Schema View, navigate to the dbo schema, navigate to the Tables, Views, and Stored Procedures nodes, and verify that the objects created are now in the data-tier application.

Data-tier developers and database administrators interested in working in Visual Studio to initiate any of the actions mentioned in this section, such as importing or creating a data-tier application, can refer to the article "Creating and Managing Databases and Data-tier Applications in Visual Studio" at http://msdn.microsoft.com/en-us/library/dd193245(VS.100).aspx.

Extracting a Data-Tier Application with SQL Server Management Studio

The Extract Data-Tier Application Wizard is another tool that you can use for creating a new data-tier application. The wizard is in SQL Server 2008 R2 Management Studio. In this method, the wizard works its way into an existing SQL Server database, reads the content of the database and the logins associated with it, and ensures that the new data-tier application can be created. Finally, the wizard either creates a new DAC package or communicates all errors and issues that need to be addressed before one can be created. This approach comes with a big advantage. The extraction process can be applied to many versions of SQL Server, not just SQL Server 2008 R2. For example, database administrators can use the wizard to generate a DAC package from SQL Server 2000, SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2 databases.

IMPORTANT DAC definitions remain unregistered when you use the Extract Data-Tier Application Wizard. Database administrators must use the Register Data-Tier Application Wizard in SQL Server 2008 R2 Management Studio to register a DAC definition. For additional information on registering a DAC definition, see the "Registering a Data-Tier Application" section later in this chapter..

Follow these steps to extract a data-tier application:

In Object Explorer, connect to a SQL Server instance containing the database that houses the data-tier application to be extracted.
Expand the Database folder, and select a database to extract.
Invoke the Extract Data-Tier Application Wizard by right-clicking the desired database, selecting Tasks, and then selecting Extract Data-Tier Application.
Review the information on the Introduction page, and then click Next to begin the extraction process. Select the Do Not Show This Page Again check box if you do not want the Introduction page displayed in the future when using the wizard.
On the Set Properties page, illustrated in Figure 3-5, complete the DAC properties by typing in the application name, version, and description, as described here:
- Application name This refers to the name of the DAC. Although this name can be different from the DAC package file, it is recommended that you make it similar enough so that it still identifies the application.
- Version The DAC version identification helps developers manage changes when working in Visual Studio. In addition, the version information helps identify the DAC package version used during deployment. The DAC version information is stored in the msdb database and can be viewed in SQL Server Management Studio in the data-tier applications node.
- Description This property is optional. Use it to describe the DAC. If this section is completed, the information is saved in the msdb database under the data-tier applications node in Management Studio.
FIGURE 3-5 Specifying DAC properties when using the Extract Data-Tier Application Wizard
Next, indicate where the DAC package file is to be saved. Remember to use the appropriate extension, .dacpac. Alternatively, click the Browse button and identify the name and location for the DAC package file.
You also have the option to select the Overwrite Existing File check box to replace a DAC package with the same name. If you choose a name that already exists for a DAC package, the existing file is not automatically overwritten. Instead, an exclamation mark appears next to the Browse button. The Next button on the page is also disabled until you change the name you specified or select the Overwrite Existing File check box.
After you have entered all the DAC properties, click Next to continue.
On the Validation And Summary page, illustrated in Figure 3-6, review the information presented in the DAC properties summary tree because these settings are used to extract the DAC you specified. The wizard checks and validates object dependencies, confirms that the information is supported by the DAC, and displays DAC object issues, DAC object warnings, and DAC objects that are supported. If there are no issues, click Next to continue. You also have the option to click Save Report to capture the entire report.

FIGURE 3-6 The Extract Data-Tier Application Wizard's Validation And Summary page

NOTE The Next button is disabled on the Validation And Summary page if one or more objects are not supported by the DAC. These items need to be addressed before the wizard can proceed. You usually remedy these issues by removing the unsupported objects from the database and rerunning the wizard.
The Build Package page is the final screen and is used to monitor the status of the extraction and build process affiliated with the DAC package file. The wizard extracts a DAC from the selected database, creates the package in memory, and saves the file to the location specified in the previous steps. You can also click the links in the Result column to review the outcome and any additional corresponding steps if required, and then click Save to capture the entire report. Click Finish to complete the data-tier application extraction process.

Installing a New DAC Instance with the Deploy Data-Tier Application Wizard

After the DAC package has been created using the data-tier application project template in Visual Studio 2010, the Extract Data-Tier Application Wizard in SQL Server Management Studio, or Windows PowerShell commands, the next step is to deploy the DAC package to a Database Engine instance running SQL Server 2008 R2. This can be achieved by using the Deploy Data-Tier Application Wizard located in SQL Server Management Studio.

During the deployment process, the wizard registers a DAC instance by storing the DAC definition in the msdb system database, creates the new database, and then populates the database with all the database objects defined in the DAC. If a DAC is installed on a managed instance of the Database Engine, the Data-Tier Application is monitored by the SQL Server Utility. The DAC can be viewed in the Deployed Data-Tier Applications node of the Management Studio Utility Explorer and reported in the Deployed Data-Tier Applications details page.

NOTE Data-tier applications can be deployed only on Database Engine instances of SQL Server running SQL Server 2008 R2. Unfortunately, SQL Server 2008, SQL Server 2005, and SQL Server 2000 are not supported when you are deploying data-tier applications. However, upcoming SQL Server cumulative updates or service packs will include functionality for down-level support.

Follow these steps to deploy a DAC package to an existing SQL Server 2008 R2 Database Engine instance:

In Object Explorer, connect to the SQL Server instance in which you plan to deploy the Data-Tier Application.
Expand the SQL Server instance, and then expand the Management folder.
Right-click the Data-Tier Applications node, and then select Deploy Data-Tier Application to invoke the Deploy Data-Tier Application Wizard.
Review the information in the Introduction page, and then click Next to begin the deployment process. Select the Do Not Show This Page Again check box if you do not want the Introduction page displayed in the future when using the wizard.
On the Select Package page, specify the DAC package you want to deploy. Alternatively, use the Browse button to specify the location for the DAC package.
When the DAC package is selected, verify the DAC details, such as the application name, version number, and description in the read-only text boxes, as shown in Figure 3-7. Click Next to continue.

FIGURE 3-7 Specifying a DAC package to deploy with the Deploy Data-Tier Application Wizard
NOTE If a database with the same name already exists on the instance of SQL Server, the wizard cannot proceed.
The wizard then analyzes the DAC package to ensure that it is valid. If the DAC package is valid, the Update Configuration page is automatically invoked. Otherwise, an error is displayed. You need to address the error(s) and start over again.
On the Update Configuration page, specify the database deployment properties. The options include
- Log File Path Accept the default location or use the Browse button to specify the location and path where the transaction log file will reside.
The next page includes a summary of the settings that are used to deploy the data-tier application. Review the information displayed in the Summary page and DAC properties tree to ensure that the actions taken are correct, and then click Next to continue.
The Deploy DAC page, shown in Figure 3-8, includes results such as success or failure based on each action performed during the deployment process. These actions include preparing system tables in msdb, preparing deployment scripts, creating the database, creating schema objects affiliated with the database, renaming the database, and registering the DAC in msdb. Review the results for every action to confirm success. You can also click Save Report to capture the entire report. Then click Finish to complete the deployment.

FIGURE 3-8 Viewing the deployment and results page associated with deploying the DAC

NOTE Throughout this chapter, you can also use Windows PowerShell scripts in conjunction with data-tier applications to do many of the tasks discussed, such as

Creating data-tier applications.
Creating server objects.
Loading DAC packages from a file.
Upgrading data-tier applications.
Deleting data-tier applications.

If you are interested in learning more about building Windows PowerShell scripts for data-tier applications, you can find more information in the white paper "Data-tier Applications in SQL Server 2008 R2" at http://go.microsoft.com/fwlink/?LinkID=183214.

Registering a Data-Tier Application

There may be situations in which a database administrator needs to create a data-tier application based on an existing database and then register and store the newly created DAC definition for the database in the msdb system database. This execution, often referred to as creating a DAC in place, is achieved by using either the Register Data-Tier Application Wizard or Windows PowerShell. Unlike the Extract Data-Tier Application Wizard, which creates a .dacpac file from an existing database, the Register Data-Tier Application Wizard creates a DAC in place by registering the DAC definition and metadata in the msdb system database. A DAC registration can be performed only on a Database Engine instance running SQL Server 2008 R2.

Use the following steps to register a data-tier application from an existing database by using the Register Data-Tier Application Wizard in Management Studio:

In Object Explorer, connect to a SQL Server instance containing the database you want to register as a data-tier application.
Expand the SQL Server instance, and then expand the Databases folder.
Invoke the Register Data-Tier Application Wizard by right-clicking the desired database, selecting Tasks, and then selecting Register As Data-Tier Application.
Review the information on the Introduction page, and then click Next to begin the registration process. Select the Do Not Show This Page Again check box if you do not want the Introduction page displayed in the future when using the wizard.
On the Set Properties page, complete the DAC properties by typing in the application name, version, and description, as described here:
- Description This property is optional. Use it to describe the DAC. If this section is completed, the information is saved in the msdb database under the Data-Tier Applications node in Management Studio.
On the Validation And Summary page, review the information presented in the DAC properties summary tree because these settings are used to register the specified DAC. The wizard checks and validates SchemaName, ObjectName, and object dependencies, and it confirms that the information is supported by the DAC. Review the summary. It displays DAC object issues, DAC object warnings, and the DAC objects supported. If there are no issues, click Next to continue. You can also click Save Report to capture the entire report.
The Register DAC screen indicates whether or not the DAC was successfully registered in the msdb system database. Review the success and failure of each action, and then click Finish to conclude the registration process.

The data-tier application can now be viewed under the Data-Tier Applications node in SQL Server Management Studio. Moreover, if a database resides on a utility-managed instance, resource utilization associated with the data-tier application can be viewed in Utility Explorer after you connect to a Utility Control Point.

Deleting a Data-Tier Application

Database administrators may encounter occasions when they need to delete a data-tier application from an instance of SQL Server. This is accomplished by using the Delete Data-Tier Application Wizard in SQL Server Management Studio. Database administrators should be aware that they will be prompted by the wizard to choose one of three predefined options for handling the database linked to the application before the DAC is deleted. The three options are

Delete Registration This method keeps the associated database and login in place while deleting the DAC metadata from the instance.
Detach Database This method detaches the associated database and removes the DAC metadata. Detaching the associated database means that although the data files, log files, and logins remain in place, the database can no longer be referenced by an instance of the Database Engine.
Delete Database The DAC metadata and the associated database are dropped. The data and log files are deleted. Logins are not removed.

To delete the DAC, follow these steps:

In Object Explorer, connect to a SQL Server instance containing the data-tier application you plan to delete.
Expand the SQL Server instance, and then expand the Management folder.
Expand the Data-Tier Applications node, right-click the data-tier application you want to delete, and then select Delete Data-Tier Application.
Review the information in the Introduction page, and then click Next to begin the deletion process. Select the Do Not Show This Page Again check box if you do not want the Introduction page displayed in the future when using the wizard.
On the Choose Method page, specify the method you want to use to delete the data-tier application, as illustrated in Figure 3-9. The options are Delete Registration, Detach Database, and Delete Database. Click Next to continue.

FIGURE 3-9 Choosing the method with which to delete the DAC with the Delete Data-Tier Application Wizard
Review the information displayed in the Summary page, as shown in Figure 3-10.

FIGURE 3-10 Viewing the Summary page when deleting a DAC

Ensure that the application name, database name, and delete method are correct. If the information is correct, click Next to continue.
On the Delete DAC page, take a moment to review the information. This page communicates which actions failed or succeeded. Unsuccessful actions have a link next to them in the Result column. Click the link for detailed information about the error. In addition, you can click Save Report to save the results on the Delete DAC page to an HTML file. Click Finish to complete the deletion process and close the wizard.

Upgrading a Data-Tier Application

Let us recall the past for a moment, when updating changes to existing database schemas and database applications was a noticeably challenging task. Database administrators usually created scripts that included the new or updated database schema changes to be deployed. The other option was to use third-party tools. Both processes could be expensive, time consuming, and challenging to manage from a release or build perspective. Today, with SQL Server 2008 R2, database administrators and developers can upgrade their existing deployed data-tier applications to a new version of the DAC by simply building a new DAC package that contains the new or updated schema and properties.

The upgrade can be accomplished by using Windows PowerShell commands or the Upgrade Data-Tier Application Wizard in SQL Server Management Studio. The tools are intended to upgrade a deployed DAC to a different version of the same application. For example, an organization may want to upgrade the Accounting DAC from version 1.0 to version 2.0. The upgrade wizard first preserves the database that will be upgraded by making a copy of it. It then creates a new database that includes the schema and objects of the new version of the DAC. The original database's mode is then set to read-only, and the data is copied to the new version. After the data transfer is complete, the new DAC assumes the original database name. The renamed DAC remains on the SQL Server instance.

There are a few actions that data-tier developers and database administrators should always perform before a data-tier application upgrade. First, the schema associated with the original DAC should be compared to the new DAC. Second, database administrators must confirm that the amount of data held in the existing DAC does not exceed the size limit of the new DAC database. To upgrade a data-tier application by using the Upgrade Data-Tier Application Wizard, follow these steps:

In Object Explorer, connect to a SQL Server instance containing the DAC you want to upgrade.
Expand the SQL Server instance, and then expand the Management folder.
Expand the data-tier applications tree and select the data-tier application that you want to upgrade.
Right-click the data-tier application, and select Upgrade Data-Tier Application. This starts the Upgrade Data-Tier Application Wizard.
Review the information on the Introduction page, and then click Next to begin the upgrade process. Select the Do Not Show This Page Again check box if you do not want the Introduction page displayed in the future when using the wizard.
On the Select Package page, specify the DAC package that contains the new DAC version to upgrade to. Alternatively, you can use the Browse button to specify the location of the DAC package. When the DAC package is selected, you can verify the DAC details, such as the application name, version number, and description in the read-only text boxes.
IMPORTANT Ensure that the DAC package and the original DAC have the same name.
When invoked, the Detect Change page starts off by displaying a progress bar while the wizard verifies differences between the current schema of the database and the objects in the DAC definition. The change detection results indicate whether the database objects have changed or remain the same. If the database has changed, you are warned that there may be data loss if you proceed with the upgrade, as illustrated in Figure 3-11. Select the Proceed Despite Possible Loss Of Changes check box, and click Next to continue.

FIGURE 3-11 The Detect Change page of the Upgrade Data-Tier Application Wizard

NOTE If the database has changed, it is a best practice to review the potential data losses before you proceed and verify that this is the outcome you want for the upgraded database. However, the original database is still preserved, renamed, and maintained on the SQL Server instance. Any data changes can be migrated from the original database to the new database after the upgrade is complete.
The next page includes a summary of the settings that will be used to upgrade the data-tier application. Review the information displayed in the Summary page and the DAC properties tree to ensure that the actions to be taken are correct, and then click Next to continue.
The Upgrade DAC page, shown in Figure 3-12, includes results, such as the success or failure of each action performed during the upgrade process. Some of the actions tested include
- Validating the upgrade.
- Preparing system tables in msdb.
- Preparing the deployment script.
- Creating the new database.
- Creating schema objects in the database.
- Setting the source database as read-only.
- Disconnecting users from the existing source database.
- Preparing scripts to copy data from the database.
- Disabling constraints on the database.
- Setting the database to read/write.
- Renaming the database.
- Upgrading the DAC metadata in msdb to reflect the new DAC version.
Review the result for every action. You can also click Save Report to capture the entire report. Then click Finish to complete the upgrade.

FIGURE 3-12 Reviewing the result information on the Upgrade DAC page

NOTE Data-tier applications are a large and intricate subject. See the following sources for more information:
- "Data-tier Applications in SQL Server 2008 R2" white paper at http://go.microsoft.com/fwlink/?LinkID=183214