SQL Server 2008 R2 provides some new tools to help deploy database schemas.

Compiled code and database schemas are frequently dependent on one another and must therefore be deployed simultaneously to a given environment. If a developer writes code that accesses a new table, that table must be available when the code runs. If the code is deployed and run before the table is deployed to the database, a runtime error occurs and the user experience will be unsatisfactory.

In the past, Microsoft has released tools to help developers manage the deployment of database schema changes. Visual Studio Database Edition (aka “Data Dude”) was bundled into Visual Studio 2010. But these tools are geared toward developers – not to DBAs. Most DBAs don’t work in Visual Studio. And in large enterprise applications, it is the DBAs who are managing the deployment of the database schemas. Without tools, this coordination requires a lot of manual steps.

SQL Server 2008 R2 includes support for Data Tier Applications. A Data Tier Application (mysteriously abbreviated “DAC”) is a package containing instructions for deploying a database schema. DBAs and developers can use SQL Server Management Studio to create “DACPAC” files for a given database. They can then use that DACPAC file to create or update a database with an existing schema.

DAC does not support all database objects. Standard objects, such as tables, columns, views, indexes are supported. Security objects such as users roles and logins are supported. Extended stored procedures and full text searches are not supported. The list of supported items is similar to the list of items supported in SQL Azure.

To create a DAC in SQL Server 2008 R2, launch SQL Server Management Studio, connect to a server, and expand the Databases node. Right-click the name of the database you want to deploy and select Tasks | Extract Data Tier Application… from the context menu. The Extract Data Tier Application wizard launches and an introduction window (Figure 1) displays.

Figure 1

Click the Next button. The Set Properties screen (Figure 2) displays.

Figure 2

At the Application Name field, enter a name for this DAC.

At the Version field, enter a version number. SQL Server does not auto-increment version numbers, so you will need to manage this yourself. It’s a good idea to keep major and minor versions of database schemas in sync with the version of the application that consumes them.

At the Description field, you may optionally enter a brief description of the schema.

At the Save to DAC package file field, enter the full path (including file name) where you want to store the deployment file. You may click the Browse button to select a folder. If you enter the name of a file that already exists, you must check the Overwrite existing file checkbox in order to proceed.

Click the Next button to perform a validation. The validation checks if DAC can handle all objects in the database. When the validation is complete, the Validation screen (Figure 3) displays. This screen displays items up to three sets of items: Items that can be deployed with DAC are displayed beneath a green Check icon; items that are not supported are displayed under a red X icon; and items that are supported, but rely on unsupported items are displayed under a yellow Exclamation icon.

Figure 3

Supported items present no problem to DAC.

Unsupported items appear as errors. Unfortunately, you will not be allowed to proceed until these items are removed from the database. Fortunately, the wizard is non-modal, so you can minimize it and switch back to Management Studio to remove these items before re-running the Validation.

Click the Next button to start building the DAC package.

When complete, the Build Package screen (Figure 4) displays, indicating success or listing problems that occurred.

Figure 4

Click the Finish button to close the wizard. A file with the extension DACPAC is created.

Deploying the Schema in a DAC

After creating a DAC, you can use SQL Server Management Studio to deploy the DAC’s schema to a new database. To begin, launch SQL Server Management Studio, expand the Management node, and right-click the Data-tier applications node. From the context menu, select Deploy Data-tier application. The The Introduction screen (Figure 5) of the Deploy Data-tier application wizard displays.

Figure 5

Click the Next button to display the Select Package screen (Figure 6).

Figure 6

Click the Browse button and select the DACPAC file created above. Click Open. Click the Next button to display the Update Configuration screen (Figure 7).

Figure 7

At the Name field, enter a name for the database you wish to create. This field defaults to the name of the database from which the DAC originated. If you are running this deployment on the same server instance on which you created the DACPAC, this will cause a conflict and SQL Server will notify you with a red exclamation icon next to the Name field.

Click Next to begin the deployment. When the deployment completes, the Summary screen (Figure 8) displays.

You should notice the new database appears beneath the Database node and the DAC is registered and appears beneath the Management \ Data-tier applications node. You may need to refresh these nodes in order to view them.

Note that the new database contains no objects – only database objects, such as tables, views, stored procedures, and users.

In this article, we discussed Data-tier applications and described how to use SQL Server Management Studio to create and deploy a DACPAC file.