[MS-DACPAC]:
Data-Tier Application Schema File Format
Intellectual Property Rights Notice for Open Specifications Documentation
§ Technical Documentation. Microsoft publishes Open Specifications documentation for protocols, file formats, languages, standards as well as overviews of the interaction among each of these technologies.
§ Copyrights. This documentation is covered by Microsoft copyrights. Regardless of any other terms that are contained in the terms of use for the Microsoft website that hosts this documentation, you may make copies of it in order to develop implementations of the technologies described in the Open Specifications and may distribute portions of it in your implementations using these technologies or your documentation as necessary to properly document the implementation. You may also distribute in your implementation, with or without modification, any schema, IDL’s, or code samples that are included in the documentation. This permission also applies to any documents that are referenced in the Open Specifications.
§ No Trade Secrets. Microsoft does not claim any trade secret rights in this documentation.
§ Patents. Microsoft has patents that may cover your implementations of the technologies described in the Open Specifications. Neither this notice nor Microsoft's delivery of the documentation grants any licenses under those or any other Microsoft patents. However, a given Open Specification may be covered by Microsoft Open Specification Promise or the Community Promise. If you would prefer a written license, or if the technologies described in the Open Specifications are not covered by the Open Specifications Promise or Community Promise, as applicable, patent licenses are available by contacting .
§ Trademarks. The names of companies and products contained in this documentation may be covered by trademarks or similar intellectual property rights. This notice does not grant any licenses under those rights. For a list of Microsoft trademarks, visit www.microsoft.com/trademarks.
§ Fictitious Names. The example companies, organizations, products, domain names, email addresses, logos, people, places, and events depicted in this documentation are fictitious. No association with any real company, organization, product, domain name, email address, logo, person, place, or event is intended or should be inferred.
Reservation of Rights. All other rights are reserved, and this notice does not grant any rights other than specifically described above, whether by implication, estoppel, or otherwise.
Tools. The Open Specifications do not require the use of Microsoft programming tools or programming environments in order for you to develop an implementation. If you have access to Microsoft programming tools and environments you are free to take advantage of them. Certain Open Specifications are intended for use in conjunction with publicly available standard specifications and network programming art, and assumes that the reader either is familiar with the aforementioned material or has immediate access to it.
Revision Summary
Date / Revision History / Revision Class / Comments /06/04/2010 / 0.1 / Major / First release.
09/03/2010 / 0.1.1 / Editorial / Changed language and formatting in the technical content.
02/09/2011 / 0.2 / Minor / Clarified the meaning of the technical content.
07/07/2011 / 1.0 / Major / Significantly changed the technical content.
11/03/2011 / 2.0 / Major / Significantly changed the technical content.
01/19/2012 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
02/23/2012 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
03/27/2012 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
05/24/2012 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
06/29/2012 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
07/16/2012 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
10/08/2012 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
10/23/2012 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
03/26/2013 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
06/11/2013 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
08/08/2013 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
12/05/2013 / 2.0 / No change / No changes to the meaning, language, or formatting of the technical content.
02/11/2014 / 3.0 / Major / Significantly changed the technical content.
05/20/2014 / 3.0 / No change / No changes to the meaning, language, or formatting of the technical content.
2/2
[MS-DACPAC] — v20140520
Data-Tier Application Schema File Format
Copyright © 2014 Microsoft Corporation.
Release: Tuesday, May 20, 2014
Contents
1 Introduction 6
1.1 Glossary 6
1.2 References 6
1.2.1 Normative References 6
1.2.2 Informative References 7
1.3 Overview 8
1.3.1 Data-Tier Application XML Parts 8
1.3.2 Document Structure 8
1.3.2.1 XML Namespace 8
1.4 Relationship to Protocols and Other Structures 9
1.5 Applicability Statement 9
1.6 Versioning and Localization 9
1.7 Vendor-Extensible Fields 9
2 Structures 10
2.1 Management Model (MM) 10
2.1.1 Instances 10
2.1.2 Reference 12
2.1.3 Key 12
2.1.4 ReferenceKey 13
2.1.5 KeyPatternType 13
2.1.6 InstancesType 13
2.1.7 ReferenceType 13
2.1.8 ReferencesType 14
2.2 Relational Engine (RE) 14
2.2.1 CheckConstraint 14
2.2.2 Column 15
2.2.3 Database 16
2.2.4 DatabaseRole 16
2.2.5 DefaultConstraint 17
2.2.6 DmlTrigger 17
2.2.7 ForeignKeyColumn 18
2.2.8 ForeignKeyConstraint 19
2.2.9 IndexedColumn 19
2.2.10 Login 20
2.2.11 PrimaryKeyConstraint 20
2.2.12 RelationalIndex 20
2.2.13 ScalarParameter 22
2.2.14 ScalarValuedFunction 22
2.2.15 Schema 23
2.2.16 SpatialIndex 23
2.2.17 Statistics 24
2.2.18 StoredProcedure 24
2.2.19 Synonym 25
2.2.20 Table 25
2.2.21 TableParameter 26
2.2.22 TableValuedFunction 26
2.2.23 UniqueConstraint 27
2.2.24 User 27
2.2.25 UserDefinedDataType 28
2.2.26 UserDefinedTableType 28
2.2.27 View 29
2.2.28 ActivationOrder 29
2.2.29 BooleanType 29
2.2.30 CompatibilityLevelEnumeration 30
2.2.31 DMLActionEnumeration 30
2.2.32 ExecuteAsEnumeration 31
2.2.33 GridDensity 31
2.2.34 LoginTypeEnumeration 31
2.2.35 PermissionStateEnum 32
2.2.36 PermissionTypeEnum 32
2.2.37 SortOrderEnumeration 34
2.2.38 UserTypeEnumeration 34
2.2.39 FillFactorType 34
2.2.40 MaxDopType 34
2.2.41 BaseSystemDataType 35
2.2.42 CollationType 35
2.2.43 ComputedColumnType 35
2.2.44 DatabasePermission 36
2.2.45 DataType 36
2.2.46 ExecutionContextType 37
2.2.47 IdentityType 37
2.2.48 Permissions 37
2.2.49 ScalarDataType 38
2.2.50 SqlDataType 38
3 Structure Examples 39
3.1 Pubs database (simplified) 39
3.2 Logical object sample 39
3.3 Physical object sample 41
4 Security Considerations 44
5 Appendix A: XML Schema 45
5.1 Management Model XML Schema for Version 2009/08 45
5.2 Relational Engine XML Schema for Version 2009/08 48
5.3 Management Model XML Schema for Version 2010/11 59
5.4 Relational Engine XML Schema for Version 2010/11 61
5.5 Management Model XML Schema for Version 2011/03 72
5.6 Relational Engine XML Schema for Version 2011/03 74
6 Appendix B: Product Behavior 129
7 Change Tracking 130
8 Index 131
2/2
[MS-DACPAC] — v20140520
Data-Tier Application Schema File Format
Copyright © 2014 Microsoft Corporation.
Release: Tuesday, May 20, 2014
1 Introduction
The data-tier application (DAC) schema file format provides XML Schema definitions for XML parts in a DAC package (a .dacpac file). A DAC is a self-contained unit for developing, deploying, and managing data-tier objects.
A DAC enables data-tier developers and database administrators (DBAs) to package Microsoft SQLServer objects, including database and instance objects, into a single entity called a DAC package, as specified in [MSDN-UNDERDAC]. A DAC package consists of multiple XML parts that represent metadata of the DAC and SQL Server object schema.
Note that the XML Schema definition [XMLSCHEMA1] (XSD) in Appendix A of this document is supplemental to the data portability scenarios that are described in [MS-DPDACPAC].
Sections 1.7 and 2 of this specification are normative and can contain the terms MAY, SHOULD, MUST, MUST NOT, and SHOULD NOT as defined in RFC 2119. All other sections and examples in this specification are informative.
1.1 Glossary
The following terms are defined in [MS-GLOS]:
database object
XML schema (XSD)
The following terms are specific to this document:
MIME type: A method that is used by protocol clients to associate files of a certain type with applications that can open or access files of that type.
MAY, SHOULD, MUST, SHOULD NOT, MUST NOT: These terms (in all caps) are used as described in [RFC2119]. All statements of optional behavior use either MAY, SHOULD, or SHOULD NOT.
1.2 References
References to Microsoft Open Specifications documentation do not include a publishing year because links are to the latest version of the documents, which are updated frequently. References to other documents include a publishing year when one is available.
1.2.1 Normative References
We conduct frequent surveys of the normative references to assure their continued availability. If you have any issue with finding a normative reference, please contact . We will assist you in finding the relevant information.
[RFC2119] Bradner, S., "Key words for use in RFCs to Indicate Requirement Levels", BCP 14, RFC 2119, March 1997, http://www.rfc-editor.org/rfc/rfc2119.txt
[XML1.0] Bray, T., Paoli, J., Sperberg-McQueen, C.M., and Maler, E., Eds., "Extensible Markup Language (XML) 1.0 (Second Edition)", W3C Recommendation, October 2000, http://www.w3.org/TR/2000/REC-xml-20001006
[XMLSCHEMA1] Thompson, H.S., Beech, D., Maloney, M., and Mendelsohn, N., Eds., "XML Schema Part 1: Structures", W3C Recommendation, May 2001, http://www.w3.org/TR/2001/REC-xmlschema-1-20010502/
1.2.2 Informative References
[MS-GLOS] Microsoft Corporation, "Windows Protocols Master Glossary".
[MS-DPDACPAC] Microsoft Corporation, "Data-Tier Application Data Portability Overview".
[MSDN-CDTS] Microsoft Corporation, "Create Database (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms176061.aspx
[MSDN-CFTS] Microsoft Corporation, "Create Function (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms186755.aspx
[MSDN-CHKCNST] Microsoft Corporation, "CHECK Constraints", http://msdn.microsoft.com/en-us/library/ms188258.aspx
[MSDN-CITS] Microsoft Corporation, "Create Index (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms188783.aspx
[MSDN-CLTS] Microsoft Corporation, "Create Login (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms189751.aspx
[MSDN-CNSTS] Microsoft Corporation, "Constraints", http://msdn.microsoft.com/en-us/library/ms189862.aspx
[MSDN-COLUMNPROPERTY] Microsoft Corporation, "COLUMNPROPERTY (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms174968.aspx
[MSDN-CPRTS] Microsoft Corporation, "Create Procedure (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms187926.aspx
[MSDN-CRTS] Microsoft Corporation, "Create Role (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms187936.aspx
[MSDN-CSTS] Microsoft Corporation, "Create Schema (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms189462.aspx
[MSDN-CTGTS] Microsoft Corporation, "Create Trigger (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms189799.aspx
[MSDN-CTTS] Microsoft Corporation, "Create Table (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms174979.aspx
[MSDN-CTYTS] Microsoft Corporation, "Create Type (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms175007.aspx
[MSDN-CUTS] Microsoft Corporation, "Create User (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms173463.aspx
[MSDN-CVTS] Microsoft Corporation, "Create View (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms187956.aspx
[MSDN-DACAPI] Microsoft Corporation, "Microsoft.SqlServer.Management.Dac Namespace", http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.dac(SQL.105).aspx
[MSDN-DACSUPOB] Microsoft Corporation, "DAC Support For SQL Server Objects and Versions", http://msdn.microsoft.com/en-us/library/ee210549.aspx
[MSDN-DTTS] Microsoft Corporation, "Data Types (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms187752.aspx
[MSDN-EATS] Microsoft Corporation, "Execute As (Transact-SQL)", http://msdn.microsoft.com/en-us/library/ms181362.aspx
[MSDN-TVPDE] Microsoft Corporation, "Use Table-Valued Parameters (Database Engine)", http://msdn.microsoft.com/en-us/library/bb510489.aspx
[MSDN-TVUDF] Microsoft Corporation, "Table-Valued User-Defined Functions", http://msdn.microsoft.com/en-us/library/ms191165.aspx
[MSDN-UDTT] Microsoft Corporation, "User-Defined Table Types", http://msdn.microsoft.com/en-us/library/bb522526.aspx
[MSDN-UNDERDAC] Microsoft Corporation, "Understanding Data-tier Applications", http://msdn.microsoft.com/en-us/library/ee240739(SQL.105).aspx
[MSFT-REDACPAC200908] Microsoft Corporation, "Relational Engine XML Schema for Version 2009/08", http://schemas.microsoft.com/sqlserver/RelationalEngine/Serialization/2009/08
[MSFT-REDACPAC201011] Microsoft Corporation, "Relational Engine XML Schema for Version 2010/11", http://schemas.microsoft.com/sqlserver/RelationalEngine/Serialization/2010/11
[MSFT-REDACPAC201103] Microsoft Corporation, "Relational Engine XML Schema for Version 2011/03", http://schemas.microsoft.com/sqlserver/RelationalEngine/Serialization/2011/03
[MSFT-SSPS] Microsoft Corporation, "Microsoft SQL Server Community Projects & Samples", http://sqlserversamples.codeplex.com/
1.3 Overview
1.3.1 Data-Tier Application XML Parts
A data-tier application consists of the following two kinds of information to represent database and instance objects:
§ Logical object definition
§ Physical object definition
This information is specified in the XML format, as specified in [XML1.0], that complies with the XSD that is specified in this specification.
1.3.2 Document Structure
The root element of a data-tier application XML is an Instances element. Subelements of the Instances element can appear in any order. A collection can have multiple instances of a subelement.
1.3.2.1 XML Namespace
The namespace URIs for a data-tier application XML are:
http://schemas.microsoft.com/sqlserver/RelationalEngine/Serialization/yyyy/mm
http://schemas.microsoft.com/sqlserver/ManagementModel/Serialization/yyyy/mm
The date component (yyyy/mm) indicates the release date of particular version of data-tier application XML. The standard file name extension for XML parts in a data-tier application is *.xml. The MIME type to use for XML files in a data-tier application is text/xml.
1.4 Relationship to Protocols and Other Structures
The data-tier application schema definition in this document supplements the data-portability scenarios that are described in [MS-DPDACPAC].
1.5 Applicability Statement
This format is applicable for use as XML parts of a .dacpac file or for use as a user’s reference.
1.6 Versioning and Localization
§ The XSD version 2009/08 is released in Microsoft SQL Server 2008 R2.
§ The XSD version 2010/11 is released in the Microsoft SQL Server 2008 R2 DAC out-of-band release.
§ The XSD version 2011/03 is released in Microsoft SQL Server 2012.
§ The XSD versions for Management Model and Relational Engine are specified in Appendix A.
§ The data-tier application file format contains localization-independent structures.
1.7 Vendor-Extensible Fields
The XML schema definition and file structure of a .dacpac file is based on the design and implementation of Microsoft SQL Server 2008 R2 Data-Tier Application Framework [MSDN-DACAPI]. An extension of the XML schema in this document can result in unexpected behavior that is not supported by SQL Server 2008 R2, Microsoft SQL Server 2012, or Microsoft SQL Server 2014.
2 Structures
This section specifies the XML schema model, the Management Model (MM) and Relation Engine (RE) Model, of a data-tier application.
2.1 Management Model (MM)
Management Model (MM) is the logical structure definition of a data-tier application instance in XML. MM specifies instances of server and database objects. The logical structure and attributes of each server and database object is specified in the Relational Engine (RE) model. Instance elements in MM are designed to reference RE elements.
2.1.1 Instances
MM:Instances is the root element of a data-tier application. The MM:Instances element contains the subelements that are listed in the following table.
Subelements /CheckConstraint
Column
Database
DatabaseRole
DefaultConstraint
DmlTrigger
ForeignKeyColumn
ForeignKeyConstraint
IndexedColumn
Login
PrimaryKeyConstraint
RelationalIndex
ScalarParameter
ScalarValuedFunction
Schema
SpatialIndex <1>
Statistics <2>
StoredProcedure
Synonym <3>
Table
TableParameter
TableValuedFunction
UniqueConstraint
User
UserDefinedDataType
UserDefinedTableType
View
The following is the XML definition of the MM:Instances element for version 2009/08.
<xs:element name="Instances"
xmlns:MM="http://schemas.microsoft.com/sqlserver/ManagementModel/Serialization/2009/08" xmlns:RE="http://schemas.microsoft.com/sqlserver/RelationalEngine/Serialization/2009/08">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element ref="RE:Database" maxOccurs="1" />
<xs:element ref="RE:CheckConstraint" />
<xs:element ref="RE:Column" />