SQL Server Mentalist

Hi Friends,

Continuing from my last post on DAC:BISQL # 43 : Introduction to DAC – Data-Tier Application {DAC Part – I} « SQL Server Mentalist

In a last post we understand what is DAC and some background related to it !!

Visual Studio 2010 Premium provides capabilities to manage application and database change.

Database Projects enable following

• Maintain your database code offline in Database Projects.
• Manage database artefacts using same/similar gestures as application code.
• Manage database code along with your application’s source code in a common source repository.
• Develop all your code in an integrated development environment.
• Reduce effort in integrating database change between source code and target databases.

Visual Studio 2010 specific database features

We have lot more future through DAC as shown in following diagram

Comparison with Different version

Lets Compare Functionality difference between Visual Studio 2010 Professional,Premium and Ultimate

Database Project Extensibility

Database Projects are an extensible database development ecosystem. The following extension points are available:

Database Schema Providers (DSP)

• SQL Server 2000/2005/2008 – In Visual Studio 2008 Team System Database Edition
• SQL Server 2005/2008/2008 R2 – In Visual Studio 2010
• Providers are publicly extensible in Visual Studio 2010

Database Project Build and Deployment Extensibility

• Build and Deployment Extensibility allow you to integrate external tools more tightly into your development process, producing new artefacts, and customizing deployment plans.
• Database Project Build and Deploy publicly extensible in Visual Studio 2010.

Database Project Feature Extensibility

• Contextual feature extensibility support per DSP provider.
• Project Feature publicly extensible in Visual Studio 2010.

Feature Extensibility

• Ability to extend existing tools and features
• Publicly extensible in Visual Studio 2008 Team System Database Edition GDR and later versions.

Project System and Feature Extensions

Here we can see extension at component level with different levels of version

Database Project Extensibility

Some Key points of Offline Schema Development

• The differences in development and deployment techniques between application and database development is largely attributed to the existence of data and operational state that inherently resides in all live databases
• Offline Schema Development enables a repeatable, flexible, process driven Database Development Life Cycle (DDLC) process
• Adoption of Visual Studio Database Projects may require a mental shift for some developers or at least a change in process or workflows.
• The source controlled version of your project represents the truth of the database schema and the "compiled" .dbschema file is a blueprint of your schema at a specific point in time.

Visual Studio Database Projects enables an Offline Schema Development environment by providing:

• A Database Project (.dbproj) designed to manage database development.
• Declarative SQL syntax support where the schema is defined in the domain language of the database. 
• Schema model representation of the database schema where source can be round-tripped from source to model and back again. 
• Schema model interfaces providing programmatic model interaction to database tooling and designers. 
• Interpretation and validation of SQL syntax and schema dependencies ensuring integrity of the source without executing it against a database. 
• Schema comparison engine that compares your source with a target database to generate an update script. 
• Compiled versions of database schema enabling deferred deployment and script generation (.dbschema file). 
• .dbschema files can be deployed to different environments that have different configurations.

Database Deployment Flow and Overview

We can deploy our project’s schema to multiple databases that have different schema versions to integrate the schema defined in our project. The following graphic depicts what happens at the time of deployment as shown in following diagram .

At the time of deployment, the schema model that is defined by your project and the schema model that is defined by the target database are compared. The deployment engine then creates a deployment plan based on the difference of the model comparison.

Hope this helps !!

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

· 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

· BISQL # 49 : SQL Server Denali Feature # 1–FileTables #1 – Introduction,Theory

Hope you will like this post on DAC- Database Project overview Part II.

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