SQL Server Mentalist

Hi Friends,

Rather having daily bored SQL server Script I think of giving something new thought to my reader

In DAC series you find even if you are ASP.NET programmer you can mange entire backend through DAC full capability like SQL Server.So if you are interested do read every post for DAC

DAC stands for Data-tier Application project

What is DAC?

In general words when we maintain backend every thing through Visual studio like code project nothing but DAC

Starting with Visual Studio 2010, a new project type, the SQL Server Data-tier Application project (DAC), was introduced along with the Database Projects. Although the purpose of both project types is to deploy a database schema to a server, they are different in semantics and handling. This guidance will cover only the Database Projects, not the functionality of DAC Projects. To make it easier for you to choose the appropriate project, depending on the scenario you have, we have outlined the application lifecycle for a DAC project and summarized the major differences between DAC and Database Projects.

So lifecycle of DAC can be summarized as

• The DAC can be managed by Visual Studio 2010 or SQL Server Management Studio.
• After finishing the development, the developer creates a DAC package, which is a portable XML file.
• DAC Package defines all of the objects and support server selection policies that specify conditions that an instance of SQL Server must meet to host a DAC.
• The database administrator deploys the DAC package, using SSMS, to an instance of SQL Server that is running SQL Server 2008 R2, or to SQL Azure.
• A copy of the DAC package is stored in the MSDB database for further reference and archiving purposes.
• When changes happen to the schema of the DAC project and a new DAC package is created, it is again deployed to the server. If data already existed in the database with the old schema, it has to be migrated and imported into the new schema of the database.

  Following are key features of DAC

• DAC packages can be only deployed to SQL Server instances that are running SQL Server 2008 R2, or to SQL Azure
• A DAC only supports a subset of the SQL Server objects which can be found in the MSDN reference “Features Supported in Data-tier Applications” DAC support following SQL Server Object

• A DAC package cannot upgrade an existing database; it will always deploy a new database with the changed schema. When you have deployed the new database schema to the server, you will have to make sure that the old data is migrated to the new database.
• DAC projects directly include specific instance level objects like Server principal that typically adhere to the requirement of the consuming application.
• DAC projects support server selection policies that specify conditions that an instance of SQL Server must meet to host a DAC.
• When DAC projects are deployed to the server, they form a DAC instance which can be easily controlled through a special functionality in SQL Server Management Studio (SSMS).

How to create new DAC Application

We have DAC Application under DATABASE tab in SQL Server Sub Tab as shown in following diagram

After Creating New DAC project we have following option in DAC project

Common scenario for the database lifecycle

Consider the following diagram, which depicts this fictitious organization and shows the relationship between their environments, actors, and process flow.

DAC main categories of functionality :

1. Manage Database Change
2. Manage Database Quality
3. Integration of Database Development Life Cycle (DDLC) with Application Lifecycle (ALM)

Now lets understand each every aspects for the same

Manage Database Change

• Versioning of your database schema – Full fidelity offline development of your database schema within a full featured project system with source control integration (SCC)
• Incremental Deployment – Deploy your database schema to multiple databases updating only the schema that differs from your source code.
• Data and Schema Compare – Compare schemas and data between databases, database projects, and dbschema (primary output artefact of building a database project).
• Schema Refactoring – Make common iterative changes to your database schema within the development cycle with more confidence.

Manage Database Quality

• Dependency validation and visualization – Validate your project during design and at build to ensure consistency and integrity of your database schema.
• Static Code Analysis – Find design, performance, and naming issues in your database schema code.
• Unit Testing and Data Generation – Create Database Unit Tests similar to what is available to the application developer, but with Data Generation capabilities to test and verify your deployed database schema with data.
  

  Integration of Database Development Life Cycle (DDLC) with Application Lifecycle (ALM) 

   

• A single IDE – The process and developer experience for Database Projects are consistent and similar to other Visual Studio projects bringing the data tier developer into the team based development lifecycle.
• Project Build integration with MS Build and Team Build
• Source Control integration with Microsoft Team Foundation Server

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 44 : DAC-Database Project Overview {DAC Part – II}

· BISQL # 45 : How to Rename .MDF File and .LDF file ? (i.e – .mdf , .ldf)

· BISQL # 46 : Information of SQL Server Database Services and Startup account

· BISQL # 47 : How to find Details of Any Column in Entire Database in One Query

· BISQL # 48 : How to Generate SELECT script for all tables in a Database

Hope you will like this post on DAC- data tier application Part I.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

              For More information related to BI World visit my Mentalist Blog

                       Link Resource Blog >> Daily Interesting links

                       SQL Server Mentalist >> SQL Learning Blog

                       Business Intelligence Mentalist >> BI World

                                     Connect With me on

             | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                 Copyright © 2011 – 2012 Vishal Pawar