This is one of the new feature released with SQL Server 2008 R2. I know it’s late to cover this topic. It has greater flexibility and easy of implementation for database perspective. I could say it’s a build which has all database & instance level objects for implementation, just run the build and get the database work done, just simple like that. I’m not sure why Microsoft chosen same acronym for Data tier applications as DAC is also know as Dedicated Administrator Connection, lets not dig into it, only Microsoft can answer!!
Which Versions of SQL it’s supported?
*) SQL Server 2008 R2 RTM
*) SQL Server 2008 SP2 onwards
*) SQL Server 2005 SP4 onwards
*) SQL Azure
You need to use SQL 2008 R2 client tools (SSMS) to work with SQL 2008 and SQL 2005. SQL 2008 & SQL 2005 client tools won’t support DAC.
What does it do?
DAC is a entity which ease development and deployment. Let’s say you are going to create a db, tables, user, logins etc. All these objects are packed into an single object for deployment. The same package can be build again for updates or modification in the objects and then used for deployment, i.e it simplifies the process of upgrading database objects.
Elements of DAC
*) DAC Properties — You can provide details about the package. What application it is, which version description about the DAC project.
*) Definition of all database objects which need to be deployed
*) Definition of all server level objects which need to be deployed.
*) SQL Server Selection Policy — Name itself describes you, you can create filters on instance level or on the server configuration. Lets say your deployment requires mixed authentication mode, then you can create a policy to validate that.
*) Files and Scripts — Now you can add all your application dependent files, pre-release and post-release scripts etc to the DAC package itself.
Below screenshot is taken from Visual Studio 2010 to show the elements of DAC
What are the objects supported in DAC?
Below are the list of objects that are supported in DAC package
FUNCTION: Inline Table-valued
FUNCTION: Multistatement Table-valued
STORED PROCEDURE: Transact-SQL
TABLE: Check Constraint
TABLE: Column, including computed columns
TABLE: Constraint, Default
TABLE: Constraint, Foreign Key
TABLE: Constraint, Index
TABLE: Constraint, Primary Key
TABLE: Constraint, Unique
TYPE: User-defined Data Type
TYPE: User-defined Table Type
I have faced only one limitation practically which is the first point, rest of them I read it from internet, not sure where I read those points, just wanted to share it.
*) It appears the DAC will also not support cross-database stored procedures. If you have a procedure pointing table in another database then that object can’t be added to DAC package.
*) Non-primary file groups are not supported in data-tier applications.
*) DAC doesn’t appear to support XML indexes on extract to data-tier.
*) DAC doesn’t appear to support XML nodes alias of the form Table(Column) on extract to data-tier.
*) DAC also does not support filestream objects.