A Lowdown on Data-tier Applications in SQL Server and Their Key Benefits

This article addresses a deeper understanding of Data-tier application, while also underlying its key benefits.

DAC (Data-tier application) can be defined as a logical database management system, which defines SQL Server objects, which are associated with the selected user’s database like, views, instance objects, table, and logins.

Data-tier Applications in SQL Server and Their Key Benefits

A DAC acts as a self-contained unit comprised in a SQL Server database used for deployment that enables developers of data-tier and database administrators to form packages of SQL Server objects, in a portable artifact which is known as a DAC package, or DACPAC. A BACPAC is also a related artifact which can encapsulate database schema and then store data in the database.

Data-tier Applications Benefits

Most database applications involve a lifecycle where DBAs and developers share and exchange ad-hoc integration notes and scripts for maintenance activities and application updates. While this can be easily managed with a few databases, it can quickly escalate and become un-scalable as the database grows in complexity, number, and size.

A DAC is a productivity tool that ensures to provide declarative database development throughout the lifecycle of database management by simplifying its deployment. Users can use its tool to help DBA in validating and identifying the behaviors of different sources and target databases. During upgrades user will be warned if there is any data loss risk to his database, while also offering an extended upgrade plan. This helps DBAs in evaluating the plan properly and then utilizing this tool to proceed further with the upgrade.

DAC Concepts

DAC is known for simplifying the deployment, management, and development of data-tier elements which are supported by an application.

In order for an SQL database to appear as a DAC, it needs to be registered in the system, either by user option or by DAC operations. Once the database is registered, DAC version along with its properties is recorded as a metadata of the whole database. Conversely, users can also unregister their database along with its DAV properties.

DACPAC is considered as a Windows file which comes with an extension of .dacpac. This is an open support file with several XML sections, which represent the details of DACPAC origin and its other characteristics. Users with programming knowledge can unpack these files using DacUnpack.exe, which will allow the user to inspect each section more closely.

DAC Operations

A DAC is capable of supporting the following operations:

•    EXTRACT – User can use DACPAC to extract a particular data from any database.

•    DEPLOY – User can use DACPAC to deploy it to a host server. Note that if deployment is done using a manageability tool such as Management Portal for SQL Azure or SQL Server Management Studio, the resulting database becomes the host server and is registered under the data-tier application.

•    REGISTER – it can also be used for registering database in the form of data-tier application.

•    UNREGISTER – This operation allows the user to undo his/her registration by unregistering the DAC.

•    UPGRADE – Users can upgrade their database using DACPAC.

Upgrade operation is supported for even those databases which have not been previously registered as a data-tier application, but this upgrade has a consequence, which implicitly registers the database. Before engaging in any large data operation, always keep an mdf repair tool handy.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com

Leave a Reply

Your email address will not be published. Required fields are marked *