Data-tier Apps in Visual Studio 2010

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:

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.

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.

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.

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.

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.

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.

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