SQL Server 2008 R2: DAC
Posted by decipherinfosys on February 27, 2010
SQL Server 2008 R2 has a lot of new features and we will be covering some of them in the upcoming posts. One of those features is DAC – Data Tier Applications. This is not to be confused with the existing DAC acronym – Dedicated Admin Connection. Using DAC, one can script out the meta data information of a database and also some instance level objects that are required to support the database. It does not script out the data. Here is how BOL defines it:
“A data-tier application (DAC) is an entity that contains all of the database and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects instead of having to manage them separately. A DAC allows tighter integration of data-tier development with the development of the associated application code. It also gives administrators an application level view of resource usage in their systems.”
Let’s see this in action first and then look at it’s benefits and also some of the restrictions of DAC. For the sake of this post, we are going to create a DAC from an existing database(s) and then will look at how to deploy a DAC to an instance.
So, in order to get started open up SSMS, right click on the database for which you wish to extract the DAC:
The steps are straightforward. On the next screen, you can set the version, the name of the DAC package and it’s location. Key thing to note is that you do not get a choice of picking which objects you want to be scripted out. If there are certain objects that will prevent the creation of the package (filestream or a database audit specification for example), you will get an error.
Upon clicking next, it will do the validations to ensure that a DAC package can be created. If the DAC package cannot be created because it contains objects that are not supported by the DAC, it will list those objects out and you will need to remove them before you can use DAC. Once the file has been created in a location (this file will have an extension of *.dacpac), you can double click on it and it will unpack it’s contents. Upon unpacking, you will see these files: DacMetaData, PhysicalObjectStream and LogicalObjectStream files which are XML files and also a SQL file. The contents are self explanatory. So, how can these files or the dacpac package useful?
Let’s use this package to deploy this on an instance that does not have this database or say on the same instance but with a different database-name essentially creating a clone in terms of the structure (not data) and access rights. If you use an installer say a msi package, you can then choose to use the dacpac package as part of it and do the deployment that way. Alternatively, this can also be done via SSMS. In order to demo this, we have dropped our deciphertest database from the instance and are now going to re-create it using the DAC package. Do remember that this only clones the schema and the access rights and not the data. So, for the data you need to have it either scripted out (for configuration data, you can have it as part of a database object itself using a stored procedure which has canned insert statements) or use a SSIS package to transfer the data from one database to another.
You can deploy the package via SSMS by eihter right clicking on the instance or under the management node – both will spawn off the same wizard:
The steps are straightforward as well:
We can then select the name of the clone database in one of those steps:
This will create the new database on the instance and also will create the deployed DAC package (in prior CTP version, the DAC name and the DB name could have been separate but in the version that we are using – Nov. CTP, seems like both would be the same now).
Not all objects are supported in DAC. Here is the BOL link that lists out the supporting objects. We have just covered the basics of DAC and it’s usage. Take a look at the resources section below to learn more about this wonderful feature addition in SQL Server 2008 R2.