Skip to content

dac-support-for-sql-server-objects-and-versions.md

Latest commit

 

History

History
119 lines (80 loc) · 8.85 KB

dac-support-for-sql-server-objects-and-versions.md

File metadata and controls

119 lines (80 loc) · 8.85 KB
title DAC Support For SQL Server Objects and Versions | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
dbe-data-tier-apps
ms.tgt_pltfrm
ms.topic article
helpviewer_keywords
data-tier application [SQL Server], supported objects
objects [SQL Server], data-tier applications
ms.assetid b1b78ded-16c0-4d69-8657-ec57925e68fd
caps.latest.revision 19
author JennieHubbard
ms.author jhubbard
manager jhubbard

DAC Support For SQL Server Objects and Versions

A data-tier application (DAC) supports the most commonly used [!INCLUDEssDE] objects.

In This Topic

Supported SQL Server Objects

Only supported objects can be specified in a data-tier application as it is being authored or edited. You cannot extract, register, or import a DAC from an existing database that contains objects that are not supported in a DAC. [!INCLUDEssCurrent] supports the following objects in a DAC.
DATABASE ROLE FUNCTION: Inline Table-valued
FUNCTION: Multistatement Table-valued FUNCTION: Scalar
INDEX: Clustered INDEX: Non-clustered
INDEX: Spacial INDEX: Unique
LOGIN Permissions
Role Memberships SCHEMA
Statistics STORED PROCEDURE: Transact-SQL
Synonyms TABLE: Check Constraint
TABLE: Collation TABLE: Column, including computed columns
TABLE: Constraint, Default TABLE: Constraint, Foreign Key
TABLE: Constraint, Index TABLE: Constraint, Primary Key
TABLE: Constraint, Unique TRIGGER: DML
TYPE: HIERARCHYID, GEOMETRY, GEOGRAPHY TYPE: User-defined Data Type
TYPE: User-defined Table Type USER
VIEW

Data-tier Application Support by the Versions of SQL Server

The versions of [!INCLUDEssNoVersion] have different levels of support for DAC operations. All of the DAC operations supported by a version of [!INCLUDEssNoVersion] are supported by all editions of that version.

Instances of the [!INCLUDEssDE] support the following DAC operations:

  • Export and extract are supported on all supported versions of [!INCLUDEssNoVersion].

  • All operations are supported on [!INCLUDEssSDSFull] and all versions of [!INCLUDEssSQL14], [!INCLUDEssSQL11], and [!INCLUDEssKilimanjaro].

  • All operations are supported on [!INCLUDEssKatmai] Service Pack 2 (SP2) or later, and [!INCLUDEssVersion2005] SP4 or later.

The DAC Framework comprises the client-side tools for building and processing DAC packages and export files. The following products include the DAC Framework

  • [!INCLUDEssSQL14] and [!INCLUDEssSQL11] includes DAC Framework 3.0, which supports all DAC operations.

  • [!INCLUDEssKilimanjaro] SP1 and Visual Studio 2010 SP1 included DAC Framework 1.1, which supports all DAC operations except export and import.

  • [!INCLUDEssKilimanjaro] and Visual Studio 2010 included DAC Framework 1.0, which supports all DAC operations except export, import, and in—place upgrade.

  • The client tools from earlier versions of SQL Server or Visual Studio do not support DAC operations.

A DAC package or export file built with one version of the DAC Framework cannot be processed by an earlier version of the DAC Framework. For example, a DAC package extracted using the [!INCLUDEssSQL14] client tools cannot be deployed using the [!INCLUDEssKilimanjaro] client tools.

A DAC package or export file built with one version of the DAC Framework can be processed by any later version of the DAC Framework. For example, a DAC package extracted using the [!INCLUDEssKilimanjaro] client tools can be deployed using either the [!INCLUDEssKilimanjaro] SP1 or higher client tools.

Data Deployment Limitations

Note these fidelity limitations in the DAC Framework data deployment engine in SQL Server 2012 SP1. The limitations apply to the following DAC Framework actions: deploy or publish a .dacpac file, and import a .bacpac file.

  1. Loss of metadata for certain conditions and base types within sql_variant columns. In the affected cases, you will see a warning with the following message: Certain properties on certain data types used within a sql_variant column are not preserved when deployed by the DAC Framework.

    • MONEY, SMALLMONEY, NUMERIC, DECIMAL base types: Precision is not preserved.

      • DECIMAL/NUMERIC base types with precision 38: the “TotalBytes” sql_variant metadata is always set to 21.

    • All text base types: The database default collation is applied for all text.

    • BINARY base types: Max length property is not preserved.

    • TIME, DATETIMEOFFSET base types: Precision is always set to 7.

  2. Loss of data within sql_variant columns. In the affected case, you will see a warning with the following message: There will be data loss when a value in a sql_variant DATETIME2 column with scale greater than 3 is deployed by the DAC Framework. The DATETIME2 value is limited to a scale equal to 3 during deployment.

    • DATETIME2 base type with scale greater than 3: scale is limited to equal 3.

  3. Deployment operation fails for the following conditions within sql_variant columns. In the affected cases, you will see a dialog with the following message: Operation failed due to data limitations in the DAC Framework.

    • DATETIME2, SMALLDATETIME and DATE base types: If the value is outside of DATETIME range – for example, the year is less than 1753.

    • DECIMAL, NUMERIC base type: when precision of the value is greater than 28.

Additional Considerations for Deployment Actions

Note the following considerations for DAC Framework data deployment actions:

  • Extract/Export - On actions that use the DAC Framework to create a package from a database – for example, extract a .dacpac file, export a .bacpac file - these limitations do not apply. The data in the package is a full-fidelity representation of the data in the source database. If any of these conditions are present in the package, the extract/export log will contain a summary of the issues via the messages noted above. This is to warn the user of potential data deployment issues with the package they created. The user will also see the following summary message in the log: These limitations do not affect the fidelity of the data types and values stored in the DAC package created by the DAC Framework; they only apply to the data types and values resulting from deploying a DAC package to a database. For more information about the data that is affected and how to work around this limitation, seethis topic.

  • Deploy/Publish/Import - On actions that use the DAC Framework to deploy a package to a database, like to deploy or publish a .dacpac file, and import a .bacpac file, these limitations do apply. The data that results in the target database may not contain a full-fidelity representation of the data in the package. The Deploy/Import log will contain a message, noted above, for every instance the issue is encountered. The operation will be blocked by errors – see category 3 above - but will proceed with the other warnings.

    For more information about the data that is affected in this scenario and how to work around this limitation for deploy/publish/import actions, see this topic.

  • Workarounds – Extract and export operations will write full-fidelity BCP data files into the .dacpac or .bacpac files. To avoid limitations, use the SQL Server BCP.exe command line utility to deploy full-fidelity data to a target database from a DAC package.

See Also

Data-tier Applications