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.