Free Trial *Internet Service Required
7 out of 9 rated this helpful - Rate this topic

Database Administration (Management Portal for SQL Database)

A data-tier application (DAC) defines all of the database objects - like tables, views, and instance objects associated with a database, including logins - required to support a multi-tier or client-server application. Each DAC operates as a single unit of management throughout the development, test, and production lifecycle of the associated application.

There are several ways to work with a DAC:

  • A DAC can be authored and built using a SQL Server Data-tier Application project in Microsoft Visual Studio 2010.

  • A DAC can be extracted from an existing database by using the Extract Data-tier Application Wizard in SQL Server Management Studio.

  • A DAC can be deployed, extracted, or upgraded using database and DAC management services for Windows Azure SQL Database. The Database Administration workspace in the Management Portal for SQL Database provides controls for creating new databases and dropping existing databases. It also provides controls for extracting the metadata of an existing database in a DAC package file, or creating a new database by deploying a DAC package.

Benefits of Data-tier Applications

Database developers can build their database projects into a DAC package file, which can be used to easily deploy the DAC to an instance of the Database Engine or SQL Database. DAC packages are versioned, so when a database developer builds a new version of their database project, a production database administrator can use that single DAC package to either deploy a new instance of the DAC, or to upgrade a previously deployed version of the DAC. The SQL Server Utility provides a Data-tier Application dashboard that administrators can use to quickly monitor the health of their deployed DACs.

DAC Concepts and Lifecycle

A DAC simplifies the development, deployment, and management of the data-tier elements that support an application:

  • A database developer uses a database project in SQL Server Developer Tools, code name “Juneau”, to define the database objects required by the application. The metadata defining the DAC and its objects is called a DAC definition.

  • When the application is finished, the database developer builds the database project to encapsulate the DAC definition into a DAC package (.dacpac) deployment file.

  • A database administrator the uses the DAC package to deploy an instance of the DAC on a production instance of the Database Engine or SQL Database. The deployment operation creates a database, populates the database with the objects defined in the DAC, and stores a copy of the DAC definition.

  • As the development team works on the next version of the application, the database developer updates the database project to reflect the database changes needed to support new features in the application.

  • When the new version of the application is finished, the database developer builds a new DAC package.

  • Database administrators can then use the DAC package to upgrade existing instances of the DAC to the new version. They can also use the package to deploy a new instance of the DAC that matches the new definitions.

  • A database administrator can use the DAC dashboard in the SQL Server Utility to easily monitor the health of the deployed DACs.

Data-tier applications can also be used for other operations:

  • A DAC extract operation creates a DAC package containing the definitions of the objects in the database. This package can then be imported into SQL Server Developer Tools to create a database project that contains the same objects that were present in the database.

  • A DAC export operation archives the metadata and data in a DAC to an export (.bacpac) file. The export file can then be used to migrate the DAC to another instance of the Database Engine or to SQL Database. The archive file can also be used to restore that version of the DAC if the current version is lost, forming a logical backup capability for SQL Database, or to keep an on-premise copy of a DAC deployed to SQL Database.

Differences between a DACPAC and a BACPAC

DACPAC and BACPAC are similar but they target different scenarios. A DACPAC is focused on capturing and deploying schema, including upgrading an existing database. The primary use case for a DACPAC is to deploy a tightly defined schema to development, test, and then production environments, and the reverse: capturing production’s schema and applying it to back to test and development environments.

A BACPAC, on the other hand, is focused on capturing schema and data. A BACPAC is the logical equivalent of a database backup and cannot be used to upgrade existing databases. The primary use case for a BACPAC is to move a database from one server to another - or from a local server to the cloud - and archiving an existing database in an open format.

If you want to deploy just your schema – that is, the DACPAC - captured with SQL 2008 R2 (SP1) to SQL Database, you can use either SQL Server Management Studio or the Management Portal for SQL Database.

If you want to deploy both the schema and the data – that is, the BACPAC - you can use Import/Export Wizard functionality in SQL Server 2012. There is also a command line tool available on CodePlex to capture the schema and data from your local instance of SQL Server. You can then deploy the BACPAC to SQL Database by using either the command line tool or the Import/Export service in the Management Portal for SQL Database.

Recommendations

Certain DAC operations, such as deploying a DAC are asynchronous, and can run some time for a large database. After using the Database Administration workspace to start a DAC operation, you can use the Task Status button in the ribbon to monitor progress. For more information, see View Task Status.

Prerequisites

To manage databases and data-tier applications, you must have a Windows Azure subscription and a SQL Database server. For more information about how to get started with Windows Azure and SQL Database, see the Windows Azure Platform Management Portal.

Permissions

You must be a member of the dbmanager role or assigned CREATE DATABASE permissions to create a database, including creating a database by deploying a DAC package. You must be a member of the dbmanager role, or have been assigned DROP DATABASE permissions to drop a database.

DAC Support on Federated Databases

DAC operations are not supported on federated databases in this release. DAC operations will support the federated database schema in a future release.

Data-tier Application Export and Import

The DAC export and import functions are implemented in the Windows Azure Management Portal. For more information, see How to: Import and Export a Database (Windows Azure SQL Database) and How to: Import a Data-tier Application (Windows Azure SQL Database).

Database Administration Tasks

Use the Administration workspace to perform these tasks:

[Top]

Create a New Database by Deploying a DAC Package

To create a database that contains all of the objects defined in a DAC package:

  1. Select the Deploy button on the Database Administration ribbon.

  2. Fill in the requested information in the Deploy Data-tier Application dialog.

    1. Data-tier Application File – click the Browse Local button to launch a file Open dialog. Navigate to the DAC package file, which will have a .dacpac file extension. Select the file and click Open.

    2. Name of the Database – This name must be unique on the SQL Database server and comply with the SQL Server rules for identifiers. For more information, see Identifiers.

    3. Edition – select the drop-down entry that specifies whether the database is a Web or Business database.

    4. Maximum Size – select the size from the drop-down list. The list only specifies the values supported by the Edition you have selected.

  3. Click Submit. The Submit button is greyed out until the required information has been specified.

A DAC deploy is an asynchronous operation. You can use the Task Status button in the ribbon to monitor progress. For more information, see View Task Status.

Database Administration Tasks

Extract a Database Schema to a DAC Package

To extract the definitions of the objects in a database to a DAC package file:

  1. Open the Database Administration workspace.

  2. In the Databases pane, select the database from which the objects are to be extracted.

  3. Select the Extract button on the Database Administration ribbon.

  4. Fill in the requested information in the Extract Data-tier Application file for DatabaseName dialog:

    1. Application Name –

    2. Version (use xx.xx.xx.xx where x is a number) – specify a numeric value that identifies the version of the DAC. Administrators use the version to identify whether the version of the DAC in the package is newer or later than the version deployed on their server. Developers use the version to identify whether the DAC definition in the package matches the version of the application they are working on.

    3. Description – Optional. Describes the purpose of the DAC, such as which application it is associated with.

  5. Click Save. This opens a file Save As dialog. Specify the name of the file with a .dacpac extension, and the folder to hold the file. Click Save.

A DAC extract is an asynchronous operation. You can use the Task Status button in the ribbon to monitor progress. For more information, see View Task Status.

Database Administration Tasks

Drop an Existing Database

To drop an existing database:

  1. Open the Database Administration workspace.

  2. In the Databases pane, select the database to be dropped.

  3. Select the inline Drop button for the database.

  4. In the line that asks Are you sure you want to drop DatabaseName?, select OK to drop the database, and Cancel to keep the database.

Database Administration Tasks

View Task Status

To view the progress of asynchronous tasks, such as DAC operations:

  1. Open the Database Administration workspace.

  2. Click the Tasks pane. The Tasks pane displays a summary of the status of asynchronous operations using these columns:

    • Activity ID – an identifier that uniquely identifies the operation.

    • Name – lists the type of operation, such as a DAC deploy or extract.

    • Database Name – lists the database that was the source or destination of the operation.

    • Date Created – lists the date and time the operation was started.

    • Created By – lists the login from the connection that started the operation.

    • Date Modified – lists the data and time at which this status entry was last updated.

    • Error – lists the error message returned by a failed operation.

  3. To view the details of the steps that make up a task, click the right-arrow at the left end of the summary line for the task. This will display a detail line for each step, reporting the action taken and whether it completed successfully.

  4. Click Refresh to update the data in the report.

Database Administration Tasks

See Also

Did you find this helpful?
(1500 characters remaining)

Community Additions

ADD
© 2013 Microsoft. All rights reserved.
facebook page visit twitter rss feed newsletter