Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Developer and DBA both sometime have their own priority and preferences when it is about database

  • Developer and DBA both sometime have their own priority and preferences when it is about database designs. If you are DBA or Developer you must learn cross domain expertise features. One of the similar features is DACPAC. What is the use of DACPAC?

    Posted on 01-12-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

7  Answers  

Subscribe to Notifications
  • Score
    7

    Microsoft has combined SQL Server 2008 R2 and Visual Studio 2010 to give us a new feature called data-tier applications, or DACs. Deployment of DACs from Visual Studio into SQL Server is done through a data-tier application package, also known as a DACPAC.

    Data-Tier Application Components

    • Single file filename.dacpac
    • Combines logical & Physical objects along with deployment policies
    • Simpler deployment options
      • Install / Uninstall /Upgrade
      • Repair (in future release)

    What is a data-tier application?

    • .dacpacs are a zip file, containing multiple XML files
    • Database object definitions
    • Server-selection policies
    • Application properties (versions, App name…)
    • Instance-level objects (logins, users...)
    • pre & post-deployment scripts

    This initial release is designed for simple, departmental systems with a database size up to a few gigabytes

    Supported SQL Server Objects

    • DATABASE ROLE
    • FUNCTION: Inline Table-valued
    • FUNCTION: Multistatement Table-valued
    • FUNCTION: Scalar
    • INDEX: Clustered
    • INDEX: Non-clustered
    • INDEX: Unique
    • LOGIN
    • SCHEMA
    • STORED PROCEDURE: Transact-SQL
    • 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: User-defined Data Type
    • TYPE: User-defined Table Type
    • USER
    • VIEW

    SQL Server not supported Objects

    • 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, 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
    • Synonyms

    Others

    • Filegroups are not stored in the DAC package; when you deploy you get one filegroup with a single file
    • Passwords for SQL logins are not stored in the DAC package; they will be created during deployment (or update) but disabled; you have to manually enable them and set the password
    • A Data-Tier application can be extracted from any version of SQL Server 2000 or later; however, a DAC package can only be deployed to SQL Server 2008 R2
    • The length of the database name used to extract, register or deploy the data-tier application cannot exceed 87 characters.

    Several database and SQL Server instance-level objects cannot be bundled into a DAC package (when a DAC in is built or imported in Visual Studio or when a DAC is extracted in SQL Server Management Studio). When a DAC is registered or extracted, these instance-level items are not captured in the DAC definition and are not stored in msdb or in the DAC package, respectively. However, these objects do not impose any other limitations or prevent the use of DACs. In other words, users can extract, register, deploy, and upgrade DACs on instances containing such items.

    Database objects that fall under this category include:

    • Object permissions
    • Role membership (mappings between users and database roles)
    • Extended properties
    • Statistics
    • Diagrams
    • Plan guides

    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)

    SQL Server 2008 SP2 and SQL Server 2005 SP4 introduced support for DAC operations only in the Database Engine. You must use the SQL Server 2008 R2 client tools and wizards, such as SQL Server Management Studio, to perform DAC operations on SQL Server 2008 SP2 or SQL Server 2005 SP4. The SQL Server 2008 and SQL Server 2005 tools do not perform DAC operations, even after service packs have been applied.

    http://msdn.microsoft.com/en-us/library/ff381683.aspx

    Example
    http://www.mssqltips.com/sqlservertip/2100/introduction-to-data-tier-applications-in-sql-server-2008-r2/

    The good

    DACPACs are great for small shops that are deploying minor application changes to SQL Server. It allows the code to be kept within the existing source control and gives developers an easy way to edit SQL Server objects from within the already familiar Visual Studio environment. This means that developers can work against their development database for all their coding, and then package up all the changes into a single DACPAC for release to production. The DACPAC can also be handed off to a DBA for release into a production or quality assurance (QA) environment.

    The DACPAC handles versioning of the database through data-tier editing, providing the developer with an easy way to use the database editing system. The .NET developer has the ability to edit the tables, procedures, views, and functions of the database.

    The bad

    Data-tier applications have a few issues in this first release. The first problem is that not all features of the SQL Server engine are supported by DACPACs. This includes the SQL Server Service Broker, CLR Objects, and most importantly, SQL Server security.

    Now all these features can be supported by using a post-release script. This is not the best solution, however, as developers need to know all the appropriate T-SQL commands in order to create and manage the objects and security.

    Currently DACPACs can only be pushed to SQL Server 2008 R2, and they must be developed using Visual Studio 2010.

    The ugly

    The biggest problem with DACPACs has to do with the way a data-tier application is released to push version changes from the DAC into SQL Server. This is done by creating a new database with a temporary name, generating the new objects in the database, and then moving all the data from the existing database to the new one. After all the data has been transferred and the post-release scripts run, the existing database is dropped and the new database is given the correct name.

    This release technique causes the database to require at least twice as much data space as it is in size, as well as enough log space to hold at least the largest object within the transaction log of the destination database. For example, if your database is 5 GB in size with the largest table being 500 MB, you will need room on the disk to hold both 5 GB databases with enough space for the transaction log to hold the entire 500 MB table.

    There are a few problems with this technique. First of all, your transaction logs become useless. This is because the database is being renamed and you can't restore the transaction logs over the course of the database upgrade.

    Another problem is created if you are using SQL Server Service Broker within your database, as any messages that were in queue during the upgrade process will be lost. The same goes for any data changes made to tables after the release but before it's completed; they will be lost as well.

    Now you can make a DACPAC out of an existing database so it doesn't need to be used for only new projects, but not all databases can be successfully turned into DACPACs. As of this writing, if you try to turn the sample database pubs into a DACPAC (a database which has been around since the days of SQL 6.x), you'll get an error from Visual Studio when it tries to create the DACPAC.

    OK, so why use data-tier applications?

    After reading all of this, the first question is probably going to be, why use DACPACs? The answer here is simple -- SQL Azure. As you may have noticed, the features that are supported by DACPACs line up with the features that SQL Azure Database supports in its current version. Because of the small amount of data that can be fit into a database through Azure (1 GB or 10 GB as of this writing, depending on the database size you have purchased) this release technique will probably work OK. With SQL Azure, you don't have to worry about backups since they are handled through the redundancy of the solution.

    While data-tier applications were designed for SQL Azure Database, they are perfectly capable of being used against your local in-house databases -- provided you are able to work within the limits of the DACPAC platform.

    Because of the release technique used by the DACPAC system, it is recommended that you not use DACPACs for your Tier 1 applications or apps larger than the 10 GB database size supported by SQL Azure. Doing so will require longer downtimes during upgrades as the data is moved between the old and new databases.

    Obviously Microsoft hasn't made any announcements about DACPAC version 2, which will hopefully support the rest of the SQL Server 2008 R2 feature set and allow DACPACs to be released to older versions of Microsoft SQL Server.

    http://www.test104.com/en/tech/6701.html

    Replied on Jan 12 2012 3:45AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    5

    About DACPAC

    DACPAC, or Data-tier Application Component Packages, is a much touted feature that allows your developers to package database changes into a single file in Visual Studio and send it to the DBAs for deployment. This is a significant improvement over how changes are being distributed now. Today they're either sent as a series of .SQL files with deployment instructions, or as a set of Team Foundation Server paths for the DBA to fetch with the same deployment document. Either way, the process leads to deployment problems because there's just too much human interaction and opportunity for misinterpretation.

    With DACPAC, DBAs get a single deployment file from the developers that contains all the changes; there's nothing to misunderstand or forget. But in this first incarnation DACPAC has some problems. For starters, to make even minor changes in the database, DACPAC completely re-creates the database and all of its objects, then moves all of the data over to the duplicated structures. In the final step, it drops the original database and gives the new one the proper name. You can easily see that creating an entirely new copy of the database for a small code change won't fit into most situations. Further, DACPAC doesn't copy user permissions or work with service broker or replication objects. DACPAC is a great idea, but is currently suitable only for very small databases with limited structures. However, keep an eye on this feature; I suspect many of these limitations will be ironed out in future versions. DACPAC should only get better and better

    So before I will show an alternative way to achieve application lifecycle management let’s get a shortlist of what tools you need to even begin to use the Data-tier Applications. You must have:

    •SQL Server 2008 R2 •Visual Studio 2010 Ultimate or Premium (Professional has limited power here)

    So, by just using SQL Server 2008 R2 and Visual Studio 2010 Ultimate can I achieve the same as this awesome thing called a DACPAC


    Why DACPAC?

    “Microsoft’s investments in application and multi-server management will help reduce the complexity around developing, deploying, and managing applications. A core concept of application and multi-server management in SQL Server is the data-tier application (DAC). A DAC, introduced in the Microsoft® SQL Server® 2008 R2 data management software and the Microsoft Visual Studio® 2010 development system, is a self-contained unit of deployment that defines and bundles database objects, SQL Server instance objects that are associated with the database, and deployment requirements of an application. Using DACs, developers and database administrators can automate and facilitate common operations and practices in the lifecycle of database systems.

    A data-tier application (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® SQL Server® objects, including database and instance objects, into a single entity called a DAC package (.dacpac),


    Uses On SQL Side

    • Cross database store procedure Execution
    • Hand reconcile upgrade scripts
    • Schema/data portability
    • Security/containment
    • Management at scale
    • A data-tier application (DAC) supports the most commonly used Database Engine objects.

    Here is the list

    • DATABASE ROLE

    • FUNCTION: Inline Table-valued

    • FUNCTION: Multistatement Table-valued

    • FUNCTION: Scalar

    • INDEX: Clustered

    • INDEX: Non-clustered

    • INDEX: Unique

    • LOGIN

    • SCHEMA

    • STORED PROCEDURE: Transact-SQL

    • 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: User-defined Data Type

    • TYPE: User-defined Table Type

    • USER

    • VIEW

    Uses On Developer Side - C# (As i am C# & SQL developer)

    The Microsoft.SqlServer.Management.Dac namespace contains classes that represent the Dac objects. The top-level instance is called DacStore and provides the connection to SQL Server. The Dac objects can be used to create and manage a DAC package, which contains all the information required to re-create a data-tier application.

    The Microsoft.SqlServer.Management.Dac namespace contains the DAC objects. The DAC objects are used together with the Microsoft.SqlServer.Managment.Smo, Microsoft.SqlServer.Managment.Dmf, and Microsoft.SqlServer.Managment.Utility namespaces to create DAC packages. The DAC packages can be installed on an instance of SQL Server using the DAC object model, or deployed onto the SQL Server Utility using the SQL Server Utility object model. In Design mode, SMO is not connected to an instance of SQL Server. The SMO objects are used to design a data-tier application structure which can be saved into the DAC package. A DAC package can also include a limited amount of reference data and some miscellaneous files. Policy information is stored for each SMO instance class, and for the overall instance of SQL Server using DMF conditions. After it is created, the DAC package can be used to install the data-tier application on an instance of SQL Server that meets the specified DMF conditions. In connected mode, the SMO Server object represents an instance of SQL Server.


    By using the Microsoft.SqlServer.Management.Dac namespace, you can do the following:

    Create an object graph that represents the data-tier application structure.

    Add reference data.

    Add miscellaneous files

    Add deployment conditions.

    Add configuration conditions.

    Install the DAC package.

    Upgrade a DAC package.

    Create a DAC package.

    Load and save a DAC package from file (serialize and deserialize).

    # Uses for Develpoer#

    SQL expertise

    Maintain script libraries

    Versioned deployments

    Keeping development/test/production synchronized

    Building and debugg SQL Files

    Adding views and stored procedures to the project and building the solution

    Creating a server selection policy to declare deployment intent

    Adding a post-deployment script to the project

    Configuring a data-tier application project for deployment

    Deploying a data-tier application

    Upgrading a data-tier application

    Added some refernces below please look

    Vedio Link http://channel9.msdn.com/Blogs/elisaj/Developing-Data-tier-Applications-using-Visual-Studio-2010

    Refernce 1 http://msdn.microsoft.com/en-us/library/ff718691.aspx

    Refernce 2 http://sqlblog.hartskeerl.nl/category/dacpac/

    All study Links are present here http://msdn.microsoft.com/en-us/library/ff718926.aspx

    This is really great SQL R2 improvement

    Good Question for the day i learnt a lot because of this question

    Thanks

    Yogesh

    Replied on Jan 12 2012 7:47AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    2

    DAC (Data Tier Applications) introduced in SQL Server 2008 R2 release, in collaboration with Microsoft Visual Studio 2010 it allows Developers & DBAs to develop and deploy Data Tier Applications in self-contained units (dacpacs)

    Using DACs, developers and database administrators can now automate common operations and design practices during their database lifecycles in controlled environments.

    Replied on Jan 13 2012 12:24PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    4

    What is DACPAC

    With Visual Studio 2010 a new project type named` SQL server Data-Tier application’ is introduced also called as ‘DAC application’. This new project type is for use with SQL Server 2008 R2. It creates special unit of deployment called DACPAC (DAC package). DAC stands for Data tier applications. The ‘dacpac’ can be handed over to DBA by a developer who will later take care of deploying the application. DAC application is targeted towards small applications (departmental applications). It contains all the database and instance objects used by the application. Note: the ‘dacpac’ file contains schema but no data, so the file size is considerably small. Important: Data –Tier applications can only be deployed on a computer where SQL Server 2008 R2 is installed and can be created with Visual Studio 2010.

    ".DACPAC" is a .zip file which contains multiple XML files and does not contain any user data.

    Supported Objects are;

    1. Tables
    2. Views
    3. Stored Procedures
    4. Indexes
    5. Functions
    6. DML Triggers
    7. Logins
    8. Users
    9. Roles

    User Defined Table Types

    Non-Supported objects;

    1. CLR
    2. Service Broker Objects
    3. Encryption Keys
    4. Event Notifications
    5. XML Schema collections
    6. Fulltext Indexes
    7. Synonyms
    Replied on Jan 15 2012 11:09PM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    5

    DACPAC

    SQL Server 2008 R2 with Visual Studio 2010 has introduced a new feature called Data-Tier Applications that allows the database developer to author the database, build it and hand the DAC package to the database administrator for deployment.

    The DAC package is a single unit of deployment, much the same way an executable file is a unit of deployment for application code. A DAC package is usually built in conjunction with an application executable file. When the system administrators deploy the executable file to the application tier or client computers, the database administrators deploy the associated DAC package to an instance of the Database Engine.

    For example, a development team is developing a 3-tier application named Finance. Build 1.0.1.123 of the application passes all acceptance testing and is ready to deploy to production. The application developers hand off a version 1.0.1.123 Finance.exe file to production system administrators for deployment to the production application servers. The database developers hand off a version 1.0.1.123 Finance.dacpac file to the production database administrators for deployment to a production instance of the Database Engine.

    A DAC Package contains the following elements:

    • .dacpacs zip file, containing multiple XML files
    • DAC properties that define characteristics of the DAC. For example, each DAC has an application name property and a version property whose values typically relate to the name and version number of the associated application.
    • Definitions of all the database objects used by the application, such as schemas, tables, views, and stored procedures.
    • Definitions of the instance-level objects, such as logins, associated with the database objects and used by the application.
    • A server selection policy that defines the pre-requisite conditions an instance of the Database Engine should have to host the DAC. The policy is defined using the Server Selection facet and can evaluate conditions such as the edition and default collation of the instance.
    • Files and scripts that can be embedded in the DAC definition when it is authored in Visual Studio. Examples are application documents, data-generation plans that specify how to create meaningful test data, or pre- and post-deployment scripts.

    We can create a DAC package by either using any of the following options, - Visual Studio 2010 for a new database or for any existing database - SQL Server 2008 R2 Management Studio for any existing database - PowerShell cmdlets for any existing database

    Uses of DACPAC

    .Using DACPAC, developers and database administrators can automate and facilitate common operations and practices in the lifecycle of database system.DAC Package can deployed to an instance of SQL server to create a new instance.DAC deployment install a new database on the instance, create the database objects, and creates the login associate with the users of the database.

    In order to simplify the management of SQL Server environments, the health of DAC resources ( for example CPU, disk space) across multiple computers and instance 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 DBA to easily obtain utilization report and statistics for their SQL server installation. Furthermore in order to customize CPU and disk performance report, DBA can use Utility Health Policies to tune and control views showing the DAC resource consumption.

    To Facilitate and automate life cycle of database projects, DACPAC offers following facilities.

    • Author Data tier applications
    • Move changes from development to production and view the DAC content.
    • Upgrade an existing data-tier application.
    • Extract DAC package from an existing database.
    • Compare a DAC package to a database.
    • Move data tier application between instances of SQL server.

    Limitations

    • This initial release is designed for simple, departmental systems with a database size up to a few gigabytes
    • There are a number of object types that are not supported; e.g. Service Broker objects, DDL triggers, Filestream columns, etc. wecannot create a Data-Tier application if your database includes these
    • Filegroups are not stored in the DAC package; when you deploy you get one filegroup with a single file
    • Passwords for SQL logins are not stored in the DAC package; they will be created during deployment (or update) but disabled; we have to manually enable them and set the password
    • A Data-Tier application can be extracted from any version of SQL Server 2000 or later; however, a DAC package can only be deployed to SQL Server 2008 R2

    Reference

    http://msdn.microsoft.com/en-us/library/ee240739.aspx

    download.microsoft.com/.../Data-tierAppsInSQLServer2008R2.docx

    http://www.mssqltips.com/sqlservertip/2100/introduction-to-data-tier-applications-in-sql-server-2008-r2/

    Replied on Jan 20 2012 2:59AM  . 
    Sineetha
    106 · 2% · 492
  • Score
    7

    What is the use of DACPAC?

    A data-tier application (DAC) is an entity that contains all of the Microsoft SQL Server database and instance objects, such as tables and views, used by an application. A DAC provides a single unit for developing, deploying, and managing the data-tier objects instead of having to manage them as sets of separate scripts and objects. 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. A DAC can be deployed to either SQL Server 2008 R2 or SQL Azure.

    Importing Objects Into Data-tier Applications

    When working on new applications, data-tier objects are created using the DAC project user interface. There are several methods for using the database objects from an existing application to create a data-tier application project for further development:

    The production database administrator can use the Extract Data-tier Application Wizard in Management Studio to extract a DAC package that contains the definitions of all the objects in the existing database. The database developer can then import that DAC package into a DAC project in Visual Studio. DAC packages can be extracted from databases in SQL Server 2008 R2, SQL Azure, SQL Server 2008, and SQL Server 2005.

    The database developer can use the Import Objects wizard in the data-tier application project to import objects from the database.

    If there is a Transact-SQL script for creating the existing database, the database developer can import the script into a DAC project.

    How will you be able to use DACPAC with SQL Azure?

    In SQL Azure, the process of data import and export has been one of the very interesting features.

    In Azure Platform, we can see Import and Export CTP ribbon.

    Export Option: With this option you can export the data and objects from a SQL Azure database to a single file. This file will be stored on Windows Azure Storage. You need to select the Database (Export is highlighted and can be clicked), then enter the credentials and Windows Azure storage location and hit the finish button. At that point you get a pop-up telling you your request will be processed.If you give it some time, depending on the size of the database of course etc. And if you go to the location on Windows Azure storage, you will see the file is there.

    If it is not then you click on the status button in the ribbon. You enter your credentials and look at the logging entries and find out what went wrong.

    Import Option: The file is on Windows Azure storage and you specify where the data has to imported in. We can do this option for new database as well as existing database, the data and objects are replaced by the content of the file.It almost looks like a backup facility. But it gets better.

    Supported SQL Server Objects

    1. DATABASE ROLE
    2. FUNCTION: Inline Table-valued
    3. FUNCTION: Multistatement Table-valued
    4. FUNCTION: Scalar
    5. INDEX: Clustered
    6. INDEX: Non-clustered
    7. INDEX: Unique
    8. LOGIN
    9. SCHEMA
    10. STORED PROCEDURE: Transact-SQL
    11. TABLE: Check Constraint
    12. TABLE: Collation
    13. TABLE: Column, including computed columns
    14. TABLE: Constraint, Default
    15. TABLE: Constraint, Foreign Key
    16. TABLE: Constraint, Index
    17. TABLE: Constraint, Primary Key
    18. TABLE: Constraint, Unique
    19. TRIGGER: DML
    20. TYPE: User-defined Data Type
    21. TYPE: User-defined Table Type
    22. USER
    23. VIEW

    SQL Server not supported Objects

    1. Objects marked for deprecation, including defaults, rules and numbered stored procedures
    2. CLR objects and data types (such as Spatial, Geography, Geometry, Hierarchy ID data types, SQL assemblies, CLR stored procedures and functions)
    3. User-defined aggregates and user-defined (CLR) types
    4. Partition schemes and partition functions
    5. XML schema collections, XML indexes and spatial indexes
    6. Service broker objects
    7. Filestream columns
    8. Symmetric keys, asymmetric keys, certificates
    9. DDL triggers
    10. Application roles
    11. Full-text catalog objects
    12. Extended stored procedures
    13. Encrypted objects (for example, encrypted stored procedures, views, functions, and triggers)
    14. Objects containing cross-database dependencies and linked server references
    15. Extended properties
    16. Synonyms

    SQL Server 2012

    However in SQL Server 2012, see the Additional supported objects: Permissions, Role Memberships, Synonym, CLR System types: Hierarchy ID, Geometry, and Geography, Spatial Index, and Statistics

    SQL Server Code Name “Denali” and SQL Azure will offer ‘anywhere support’ for moving a DAC database between SQL Server databases across server, private cloud or SQL Azure. This enables customers to build once and deploy and manage anywhere which leads to unprecedented flexibility across IT and dev. Import and export services in the DAC framework enable archiving and migration scenarios between on-premise and cloud database servers. The new export service in the DAC framework will extract database schema as well as data and serialize it into a new logical and open archive format – a .bacpac file. Users will then be able to import the .bacpac archive into another database server

    Replied on Jan 31 2012 11:09PM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    6

    Microsoft has combined SQL Server 2008 R2 and Visual Studio 2010 to give us a new feature called data-tier applications, or DACs. Deployment of DACs from Visual Studio into SQL Server is done through a data-tier application package, also known as a DACPAC.

    Data-Tier Application Components

    • Single file filename.dacpac
    • Combines logical & Physical objects along with deployment policies
    • Simpler deployment options
      • Install / Uninstall /Upgrade
      • Repair (in future release)

    What is a data-tier application?

    • .dacpacs are a zip file, containing multiple XML files
    • Database object definitions
    • Server-selection policies
    • Application properties (versions, App name…)
    • Instance-level objects (logins, users...)
    • pre & post-deployment scripts
    • This initial release is designed for simple, departmental systems with a database size up to a few gigabytes

    Supported SQL Server Objects

    • DATABASE ROLE
    • FUNCTION: Inline Table-valued
    • FUNCTION: Multistatement Table-valued
    • FUNCTION: Scalar
    • INDEX: Clustered
    • INDEX: Non-clustered
    • INDEX: Unique
    • LOGIN
    • SCHEMA
    • STORED PROCEDURE: Transact-SQL
    • 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: User-defined Data Type
    • TYPE: User-defined Table Type
    • USER
    • VIEW

    SQL Server not supported Objects

    • 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, 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
    • Synonyms

    Others

    • Filegroups are not stored in the DAC package; when you deploy you get one filegroup with a single file
    • Passwords for SQL logins are not stored in the DAC package; they will be created during deployment (or update) but disabled; you have to manually enable them and set the password
    • A Data-Tier application can be extracted from any version of SQL Server 2000 or later; however, a DAC package can only be deployed to SQL Server 2008 R2
    • The length of the database name used to extract, register or deploy the data-tier application cannot exceed 87 characters.

    Database objects that fall under this category include:

    • Object permissions
    • Role membership (mappings between users and database roles)
    • Extended properties
    • Statistics
    • Diagrams
    • Plan guides

    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)

    http://msdn.microsoft.com/en-us/library/ff381683.aspx

    Example
    http://www.mssqltips.com/sqlservertip/2100/introduction-to-data-tier-applications-in-sql-server-2008-r2/

    Replied on Feb 4 2012 3:02AM  . 
    Mitesh Modi
    18 · 10% · 3080

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.