Advertisment

Data-tier Apps in Visual Studio 2010

author-image
PCQ Bureau
New Update

A data-tier application contains the database and instance objects, like

stored procedures, tables, views, logins, etc. and resides in the data tier. SQL

Server developers today want to create a data-tier application they can test and

declaratively deploy across multiple instances while the system deals with

version upgrades. SQL Server DBAs, on the other hand, want to know about

deployment requirements ahead of time and they also want the system to perform

much of the mechanical upgrade operations instead of managing data-tier

applications as a bunch of scripts. With an increasing number of applications

going to the cloud enterprises want to deploy data-tier applications both on and

off premise. In general, enterprises face the following three areas of

challenges:

Advertisment

Direct Hit!

Applies To: Data-tier app developers and DBAs



USP: New features in VS 2010 simplify Data-tier app development
Primary Link:

http://tinyurl.com/ykd4ll3




Search Engine Keywords: Data-tier Application, DAC, Visual
Studio 2010

Development: Visual Studio didn't have a uniform database project

system and workflow to create data-tier applications and author Transact-SQL

code. Developers expressed the need for an ability to capture deployment

environment prerequisites declaratively at design time to be subsequently used

at deployment time.

Deployment: Data-tier developers and Database Administrators (DBAs)

continued to work in silos. It was difficult to define a clear containment for

an application after it was deployed. Upgrading or making changes to deployed

data-tier applications was a time consuming and error prone task.

Advertisment

Management: It was difficult to centrally control the deployed

environment. For example, deploying the same version of a data-tier application

in a database farm with several managed instances was still a big challenge.

Visual Studio 2010 and SQL Server 2008 R2 addressed these challenges with a

number of new enhancements . First, by introducing a new unit of deployment for

data-tier applications (.dacpac) to make the process of deployment and upgrade

of data-tier applications easier. Simply put, a .dacpac is a single package that

captures both the data-tier application and deployment policies that specify

environment prerequisites.

The new data-tier application project system is introduced in Visual Studio

2010 backed by a new Transact-SQL Editor experience which included an integrated

editor, debugger, offline and online IntelliSense, language services and policy

designer. Also added are static code analysis and build services  for

Transact-SQL. The build output of a data-tier application project is a .dacpac

which can be subsequently deployed (or upgraded) either from Visual Studio 2010

or from SQL Server Management Studio.

Advertisment
Conceptual diagram of a Data-tier Application Component.

SQL Server Management Studio 2008 R2 can be used to deploy, upgrade, delete,

register and extract .dacpacs and also monitor deployed data-tier applications.

For easier management of multiple SQL Server instances, the SQL Server

Control Point is introduced in SQL Server 2008 R2. DBAs can enroll SQL Server

instances in a SQL Server Control Point and monitor their performance and

configuration at an instance and data-tier application level. This helps you do

trend analysis to improve resources utilization and eventually reduce costs.

Advertisment

A lap around Data-tier Application projects



The focus of data-tier applications in Visual Studio 2010 and SQL Server

2008 R2 is what we refer to as departmental applications; these are typically

smaller, less sophisticated applications that are growing at a much faster rate

than DBAs can effectively manage them.

Setting the VersionMajor facet.

Visual Studio 2010 brings many new features that combine a first-class

Transact-SQL IDE with a new project system to produce a comprehensive model of

the objects, environment prerequisites and runtime resources required by a

data-tier application.

Advertisment

Create the project



Follow these steps to create a new data-tier application project in Visual

Studio 2010: In the main Visual Studio menu, click File, go to New > Project. In

the New Project dialog, click and expand the Database node in the Installed

Templates tree and click SQL Server. Select SQL Server data-tier application

from the list of available project templates and click on OK.

There are several ways to seed an empty data-tier application project: you

can create your application from scratch, import a Transact-SQL script from a

file, import an existing .dacpac or reverse engineer from an existing database.

Develop Deploy Manage Lifecycle.
Advertisment

Add new data-tier objects to the project



New objects can be easily added to your project from the context menu in

Solution Explorer. Follow these steps in order to add a new table to the

project:

  • Right-click the project node in Solution Explorer and then click on Add.

    While commonly used objects are available directly in this context menu, you

    can also choose New Item or Existing Item as required.
  • From the context menu, click Table and specify a table name in the Add New

    Item dialog and click on OK. Experiment by adding other objects; follow the

    same steps as above but choose View and Stored Procedure to add them to your

     project.

Pervasive IntelliSense enhances productivity



We have invested heavily in IntelliSense for Transact-SQL. You will see

almost immediately that Visual Studio 2010 provides IntelliSense and statement

completion as you type Transact-SQL constructs in your data-tier objects. If you

are connected to a database, IntelliSense provides suggestions from the

connected instance otherwise it provides suggestions based on the objects in the

project.

Advertisment

Application Lifecycle Management (ALM) features



Users can leverage Application Lifecycle Management (ALM) features they are

already familiar with while working with Transact-SQL in Visual Studio 2010. The

data-tier application project system incorporates refactoring (e.g. Fully

Qualify Names, Expand Wildcards and Refactor with Preview), unit testing, pre

and post deployment scripts, Source Code Control integration and integration

with Team Foundation Server for automated builds.

For example, Visual Studio developers typically use Static

Code Analysis to analyze, control and improve code quality and identify design,

naming and performance issues among others. Static Code Analysis support for

Transact-SQL and many commonly used rules are now included in Visual Studio

2010. Code analysis warnings are shown in the Error List and you can

double-click on a warning to navigate to the Transact-SQL statement or construct

that violated the rule.

Data-tier Application project.

Declaratively specify environment prerequisites at

design time



The data-tier application project system lets developers declaratively

specify policies that describe environment prerequisites at design time. These

policies are incorporated into the resulting .dacpac when the project is built

and subsequently used at deployment time.

As an example, follow these steps to add an environment

prerequisite so that the data-tier application can only be deployed to instances

of SQL Server with a specific version number.

In Solution Explorer double-click on the

ServerSelection.sqlpolicy file located under the Properties folder.

  • Scroll down the Facet Properties list and double-click

    on  the VersionMajor property.

  • In the Edit Values dialog, set the Value text box to “9”

    and the Comparison operator drop-down list to “is greater than or equal to”

    and click on OK.

  • Click on 'Save All' in the toolbar or press CTRL+SHIFT+S.

Build and deploy to staging



Building the data-tier application project produces a .dacpac file as the

project output.

Use the 'Deploy' tab in the project properties dialog to

set the destination connection string for the project before you deploy the

data-tier application to a SQL Server instance. This connection string is only

used at design time and is most useful in iterative development scenarios.

Select Deploy in the Solution Explorer context menu to deploy the data-tier

application to the SQL Server instance. The deployment will succeed only if the

SQL Server. The deployment will succeed only if the SQL Server instance meets

the policies declared earlier (i.e. version major >= “9”)

Offline IntelliSense.

Deploy on premise or off premise



SQL Server 2008 R2 and Visual Studio 2010 deliver the ability for DBAs and

Developers to deploy data-tier application packages directly to SQL Azure. SQL

Azure provides a scalable relational database platform that is a self-managed,

highly available cloud service. Customers can utilize this flexibility to

further optimize resources across their environment based on application needs

and IT bandwidth.

Debug a deployed Data-tier Application



Users can easily debug stored procedures, functions and scripts from the

Transact-SQL Editor using standard debugging tools in Visual Studio (Call

Stacks, Locals, Watch, etc.) and the more traditional Transact-SQL debugging

tools like a graphical ShowPlan. Add a new Script to the project like you added

Tables and Views earlier. Click on the Connect button in the Transact-SQL

toolbar and specify the server name and credentials. The Transact-SQL Editor

provides online IntelliSense based on the instance you are connected to as you

write code. Set breakpoints in the T-SQL script and press F5 to start debugging.

Alternately, you can click on the Execute SQL button to execute the script

without debugging.

You can use features that were traditionally only available

in SQL Server Management Studio (e.g. Include Actual Execution Plan and Include

Client Statistics, etc.) as part of your debugging cycle.

Sanjay Nagamangalam, Principal Program Manager, SQL Server

Advertisment