Using Data-tier Apps in Visual Studio 2010

BANGALORE, INDIA: 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.

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.

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.

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.

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”)

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