Data-Tier Applications (DAC) is the newest addition in SQL Server 2008 R2 data management and Visual Studio 2010 development system. The DAC helps users to develop, deploy, and manage the data-tier portion of the applications in a flexible manner and more efficiently than ever before. The key output of DAC is automation and facilitation of database objects and SQL Server instance level objects within the lifecycle of database systems.
Database objects that fall under this category include:
SQL Server instance-level objects in this category include:
Linked servers
SQL jobs
Certificates
Endpoints
Credential objects
User-defined policies and conditions
Role membership (mappings between logins and server roles)
This recipe gives an insight of the DAC process, how to install it, and use it to deploy within the network.
The self-contained unit of deployment is the first step for the DAC process that enables the Developers and Database Administrators (DBA) to package the SQL Server objects, such as database and instance objects into a single-entity called Data-Tier Applications (DAC) package.
The official representation of DAC is represented by Microsoft as follows:
For the ease of understanding the Production deployment and Development extract process, it is represented with numbers—P1 for production and D1 for Development.
The build process of the DAC package can be accomplished using Data-Tier application project system in Visual Studio application. The data management process of the DAC package can be accomplished by using SQL Server Management Studio (SSMS).The DAC deployment installs a new database on the instance, creates the database objects, and creates the logins associated with the users of the database. If a previous version of the DAC is already available, then the DAC package can be used to upgrade the existing DAC instance to a newer version.
The following are few of the best practice items provided for DAC package deployment and extraction that are directed for Independent Software Vendors (ISV) and Internet (Web hosting) Service Providers (ISP):
The database should contain up to a few gigabytes of data (for example, up to 10 GB).
There are certain limitations and support restrictions for data-tier applications, such as the common database objects. They are:
Objects marked for deprecation, including defaults, rules, and numbered stored procedures
CLR objects and data types (such as Spatial, Geography, Geometry, Hierarchy ID data types, SQL assemblies, CLR stored procedures, and functions)
User-defined aggregates and user-defined CLR types
Partition schemes and partition functions
XML schema collections, XML indexes, and spatial indexes
Service broker objects
FileStream columns
Symmetric keys, asymmetric keys, and certificates
DDL triggers
Application roles
Full-text catalog objects
Extended stored procedures
Encrypted objects (for example, encrypted stored procedures, views, functions, and triggers)
Objects containing cross-database dependencies and linked server references
Extended properties
Linked Servers
Synonyms
The Data-Tier Application is wrapped with schema and metadata.
The schema consists of tables, views, constraints, stored procedures, users, logins, and file group
The metadata consists of management policies and failover policies that map a database having a direct relationship with the relevant
.dacpacdefinition file
There are no specific hardware or software requirements for data-tier applications. However, to manage the data-tier applications through the SQL Server Utility, users must consider the limitations imposed by the utility and managed instances.
Though the data-tier applications can be used to work with existing databases, or implement them as new releases, we need to know how to create data-tier applications in existing systems to register each database as a data-tier application.
The build process of the DAC package can be accomplished by using Data-Tier application project system in Visual Studio application.
The data management process of the DAC package can be accomplished by using SQL Server Management Studio (SSMS). The DAC deployment installs a new database on the instance, creates the database objects, and creates the logins associated with the users of the database.
If a previous version of the DAC is already available, the DAC package can be used to upgrade the existing DAC instance to a newer version.
The Extract Data-tier application process creates a DAC package from a database. To extract a DAC package, perform the following steps:
1. Register the instance of SQL Server. In the Object Explorer pane, click on Connect and follow the instructions in the Connect to Server dialog box.
2. In Object Explorer, select the node of the instance from which you want to extract a data-tier application and expand its Databases node.
3. Select the database for the extraction. Right-click the databases node, point to Tasks, and then click Extract Data-tier Application to launch the Extract Data-tier Application Wizard.
4. On the Set Properties page (shown in the next screenshot), review or change the properties and then click on Next. These properties are displayed in Visual Studio and in SQL Server Management Studio and are used as follows:
Application Name: It identifies the application. For example, if a database called
FinanceDBserves the Finance application, the application name should be set to Finance. The application name is used when a data-tier application is upgraded. In order to upgrade a DAC V1 to a DAC V2, the application names of V1 and V2 must be identical.Version: It's the version of the data-tier application. By default, the version number is 1.0.0.0.
Description: Optional.
Save to DAC package file: It specifies the filename and path for the DAC package file. This file must end with the
.dacpacextension.
5. This is where the retrieval of database objects and relevant logins with checks of DAC package can be created with a validation process.
6. The validation process ensures that all database objects are supported by a DAC, and all dependent objects are available in the database. For example, if a view depends on a table and the latter was dropped from the database, a DAC cannot be extracted.
7. When the validation completes, all issues and errors that prevent the creation of a DAC package appear in the summary tree (refer to the next screenshot). If there are no issues with the validation report, then click Next to create and save the DAC package file.
8. On the Build Package page, review the status of building the DAC package file. When the build is finished, click Finish to close the wizard. Finally, verify that a new DAC package file was created in the path you specified in step 4.
9. The extracted DAC package file can now be imported to a Data-tier Application project in Visual Studio. In addition, the DAC package can also be deployed to create a new data-tier application on an instance of SQL Server, or to upgrade an older version of an existing data-tier application.
Note
In an ISV-based environment, if the referred
.dacpacfile is received from a third-party or an unknown source, then it is essential to review the file contents that contain the pre and post schema change scripts. The default practice must include verification of the package file contents with an anti-virus scanner.
There are three key components of a data-tier application project initiation—SQL Server objects, a server selection policy, and custom scripts (sometimes referred to as additional or extra files).
In order to get started, define the schema of an application. Developers can import a DAC package into the project system, or they can select an instance of SQL Server and import objects from an existing database. Then, users can create and modify database objects and SQL Server instance objects that are associated with the database.
Programming users can utilize advanced code editing features (such as IntelliSense technology, auto-completion, code snippets, and the Transact-SQL compiler) to write Transact-SQL code for stored procedures, functions, users, and logins.
After adding tables to the data-tier application projects, developers can create a DAC package and deploy the project to a target instance of SQL Server to test the release. For the control deployments, a server selection policy enables developers to set prerequisites and conditions on the target instance of SQL Server where the data-tier application is deployed. This process helps to dictate prerequisite conditions on target instances.
The server selection policy helps developers to express and dictate deployment intents. Each time a DAC package is deployed, the policy is evaluated against the target to ensure that the instance meets the application requirements.
Custom deployment actions and data manipulation operations are also available in the data-tier application project through custom scripts. Users can provide Transact-SQL scripts and include them in the DAC package. While the system does not automatically execute or reason over custom scripts, users can manually execute these scripts during DAC deployment or upgrade.
The unpack functionality is provided to let users view the content of a DAC package and generate the Transact-SQL script that corresponds to the deployment or upgrade of a data-tier application. The best practice is to use the unpack feature before you deploy or upgrade a DAC package in production, to review the code and compare or identify changes.
In order to simplify the management of SQL Server environments, the health of DAC resources (for example, CPU, and disk space) across multiple computers and instances can be viewed in a central interface called the Utility Explorer in SQL Server Management Studio.
The Utility Explorer displays aggregated data and information, enabling DBAs to easily obtain utilization reports and statistics for their SQL Server installations. Furthermore, in order to customize CPU and disk space performance reports, DBAs can use Utility Health Policies to tune and control views showing the DAC resource consumption.