Visual Studio 2010 SQL Server Database Projects

Duke Kamstra, Program Manager, Microsoft Corporation

May 2010

This white paper describes the various project templates available in Visual Studio 2010 for implementing SQL Server databases and Data-tier Applications (DAC). After reading this white paper you will be able to:

• Describe the databases each project template is designed to support

• Choose the appropriate project template for your database design

Applies To

Visual Studio 2010

Introduction

Establish and support a Database Development Life Cycle (DDLC)

Integrate the DDLC into the rest of the Application Lifecycle

So which project type should I use?

Comparison of Features

Project Artifacts and Capabilities

Schema Definition

Schema Compare

Build

Deploy

Visual Studio 2010 delivers several new or enhanced database project templates. All of these project templates are available in the Professional, Premium and Ultimate editions of the product.

Project Type	Description
Database Project	Database project templates are available for SQL Server 2005 and SQL Server 2008. They may be used to define a user database (also known as a catalog). All objects implemented by the respective SQL Server version are supported in database projects.
Server Project	Server project templates are available for SQL Server 2005 and SQL Server 2008. They may be used to define server level objects as well as modifications to the SQL master database. Examples of server level objects are Logins and custom error messages.
Data-tier Application	Data-tier application (DAC) projects may be used to implement databases that will be deployed to SQL Server 2008 R2 or SQL Azure. A DAC is the new unit of deployment for Data-tier applications and contains the data-tier application and deployment requirements in a single package (.dacpac). Developers can declaratively specify deployment intent which is captured in the DAC and this makes it very easy to deploy, upgrade and uninstall Data-tier Applications using Visual Studio or SQL Server Management Studio. For more information about this new SQL technology, see the video Developing Data-tier Applications using Visual Studio 2010 and documentation in Understanding Data-tier Applications. If the target instance of SQL Server is enrolled into the SQL Server Utility as a managed instance then you can also monitor deployed Data-tier Applications in the Utility dashboard. Please see Managing the SQL Server Utility for additional details.

All of these project templates are designed to support a declarative database development methodology analogous to writing code in C++, C# or Visual Basic. MSDN documentation on database projects is available at this location: An Overview of Database and Server Projects.

Establish and support a Database Development Life Cycle (DDLC)

Implement schema changes by adding, modifying or deleting the definitions of objects in your project. There is no need to be connected to a server instance to design your database. Build your project to identify syntax errors. Create automated Data Generation plans to seed a test database. Implement Unit Tests to verify that the behavior of the database schema meets requirements. Deploy your project and the correct script will be generated to either create a new instance of the database or do an in place update to an existing database.

Use the same tools available to application developers when writing your Transact-SQL code:

• Refactoring – Rename an object and have all the uses of that object’s name changed throughout your database design.

• Unit Testing – Implement unit tests written in Transact-SQL to verify the behavior of your stored procedures and functions.

• Code Analysis – Run SQL code analysis rules to identify potential bugs in your Transact-SQL code.

• Source Code Control integration – For example use Team Foundation Server’s "Shelf Set" functionality to collaborate with the rest of your team before attempting to deploy the changes to production.

• Automate build and deployment to an integration test environment – For example, use Team Foundation Server’s Build Agent to automatically build and deploy database schema changes as well as any changes to the other tiers of your n-tier application. Then automatically run unit tests for each tier to identify any issues.

MSDN documentation on managing database change is available at this location: Managing Database Change.

Integrate the DDLC into the rest of the Application Lifecycle

By tightly integrating database development into Visual Studio the application development team and the folks that are responsible for building the database have a common platform enabling them to easily collaborate.

One Visual Studio solution may contain each tier of the application: the client, middle tier, and data tier. If a developer makes a change in one tier that affects another, for example adding a column in the database, he or she can make the respective changes to the middle tier without changing development tools. She can then deploy one project or the entire solution to a sandbox environment. She may then execute the unit tests implemented by her teammates to verify that the other tiers still function as expected.

So which project type should I use?

Use the DAC project if:

• You are designing a database that will be deployed to a SQL Utility you should use the DAC project.

Use the Database or Server project if:

• You are designing a database that requires SQL objects not supported by DAC.

• You are deploying a project to a version of SQL Server that is not supported by a DAC.

Project	Prerequisites	Supported SQL Objects
SQL Server Database Projects	The target SQL instance may be SQL Server 2005 or SQL Server 2008	All SQL Server 2005 and SQL Server 2008 object types are supported.
DAC Project	The target SQL instance version and project objects must be supported by a DAC	The list of SQL objects supported by DAC projects is available on MSDN: SQL Server Objects Supported in Data-tier Applications.

Comparison of Features

Features that are not addressed below behave the same for DAC and SQL Server Database projects.

Project Artifacts and Capabilities

Artifact	DAC Project	SQL Server Database Project
.sqlsettings	Configure database level SET properties. See Chart 1. These values are only used during design time. The properties are not set on the database when it is deployed.	Configure all database level properties. See Chart 3.
.sqlpolicy	The developer may specify the required configuration of the SQL server instance on which the DAC is deployed. See Chart 2.	Not Applicable
.sqlcmdvars	Not Applicable	Allows the developer to define SQLCMD variables.
.sqldeployment	Not Applicable	Allows the developer to configure the behavior of the deployment pipeline for the SQL Server Database project. See Chart 4.
Build output	Building a DAC project creates a .dacpac. Note that .dacpac files are limited in size to 50MB.	Building a SQL Server Database project creates a .dbschema file.
SQL Server Common Language Runtime Assembly References	Not supported	Supported
Database Project References	Not supported	Supported
XSD References	Not supported	Supported
Partial Projects	Not supported	Supported

Chart 1: DAC SQL Settings

Setting
SET ANSI NULLS
SET ANSI PADDING
SET ANSI WARNINGS
SET ARITHABORT
SET CONCAT_NULL_YIELDS_NULL
SET NUMERIC ROUNDABORT
SET QUOTED IDENTIFIER
Trustworthy

Chart 2: DAC .sqlpolicy

Setting
BuildNumber
Collation
Edition
IsCaseSensistive
Language
NamedPipesEnabled
OSVersion
Platform
TCPEnabled
VersionMajor
VersionMinor

Chart 3: SQL Server Database .sqlsettings

Setting
Database Collation
Default filegroup
Default filestream filegroup
Auto close
Auto update statistics
Auto create statistics
Auto update statistics asynchronously
Auto shrink
Close cursor on commit enabled
Default cursor
SET ANSI NULLS
SET ANSI PADDING
SET ANSI WARNINGS
SET ARITHABORT
SET CONCAT_NULL_YIELDS_NULL
SET NUMERIC ROUNDABORT
SET QUOTED IDENTIFIER
Trustworthy
Compatibility level
Service Broker options
Database state
Database access
Update options
Parameterization
Recursive triggers enabled
Database chaining
Enable full text search
Broker priority honored
Encryption enabled
Recovery
Page verify
Allow snapshot isolation
Read committed snapshot
Change tracking
Auto cleanup

Chart 4: SQL Server Database .sqldeployment

Settings
Deployment collation default	IgnoreFilegroupPlacement
Deploy database properties	IgnoreFileSize
Always re-create database	IgnoreFillFactor
Block incremental deployment if data loss might occur	IgnoreFullTextCatalogFilePath
Execute deployment script in single-user ode	IgnoreIdentitySeed
Back up database before deployment	IgnoreIncrement
Generate DROP statements for objects that are in the target database but that are not in the database project	IgnoreIndexOptions
Do not use ALTER ASSEMBLY statements to update CLR types	IgnoreIndexPadding
AbortOnFirstError	IgnoreKeywordCasing
CheckNewConstraints	IgnoreLockHintsOnIndexes
CommentOutSetVarDeclarations	IgnoreLoginSids
DisableAndReenabeDdlTriggers	IgnoreNotForReplication
DropConstraintsNotInSource	IgnoreObjectPlacementOnPartitionScheme
DropIndexesNotInSource	IgnorePermissions
EnforceMinimalDependencies	IgnoreQueueEventNotifications
GenerateDeployStateChecks	IgnoreQuotedIdentifiers
IgnoreAnsiNulls	IgnoreRoleMembership
IgnoreAuthorizer	IgnoreRouteLifetime
IgnoreBodyDependencies	IgnoreSemicolonBetweenStatements
IgnoreCollations	IgnoreStatisticsSample
IgnoreColumnOrder	IgnoreTableOptions
IgnoreComments	IgnoreUserSettingsObjects
IgnoreCryptographicProviderFilePath	IgnoreWhitespace
IgnoreDdlTriggerOrder	IgnoreWitNocheckOnForeignKeys
IgnoreDdlTriggerState	IncludeTransactionalScripts
IgnoreDefaultSchema	PreserveDependencyGraphs
IgnoreDmlTriggerOrder	TreatVerificationErrorsAsWarnings
IgnoreDmlTriggerState	UnmodifiableObjectWarnings
IgnoreExtendedProperties	VerifyDeployment
IgnoreFileAndLogFilePath

Schema Definition

Aside from the differences in Transact-SQL object type support, the most notable difference between DAC and SQL Server databases projects is the detection of invalid syntax. Both project types use the same design time validation engine and IntelliSense parser. As a result, SQL language that is allowed for a SQL Server Database project but is not allowed for a DAC project (that is ALTER TABLE) will not be reported as an error until you actually build the DAC project.

Capability	DAC Project	SQL Server Database Project
Importing an existing database schema	Using SSMS a .dacpac may be extracted from a SQL 2000, 2005 or 2008 database. This .dacpac may then be imported into a DAC project using Visual Studio.	SQL 2005 or 2008 database may be imported into a SQL Server Database project using Visual Studio.
Three part names	The database part of the three part name may only refer to the current database. Inter-database operations are not supported.	Supported.
Four part names	Not supported.	Supported.
ALTER	The ALTER keyword is not supported. All objects must be defined using the CREATE keyword.	Supported.
References to objects defined in the Master and MSDB databases	Supported.	Supported.
Refactoring	Supported.	Supported.
Schema View	Supported.	Supported.
Dependency Viewer	Supported.	Supported.
SQL Debugger	Supported.	Supported.
SQL IntelliSense	Supported.	Supported.
SQLCMD Variables & Syntax	Defining SQLCMD variables is not supported at the project level. SQLCMD syntax is supported in the Pre and Post Deployment scripts.	Supported.

Schema Compare

The matrix described in this MSDN topic describes which artifacts may be compared with each other and/or modified using Schema Compare. For more information, see Compare and Synchronize Database Schemas.

Build

From a Visual Studio, MSBuild or Team Foundation Server Build perspective the action(s) required to build DAC or SQL Server Database projects is the same. The differences are in how the schema definition is "compiled" and the artifacts that result from building the projects.

DAC Project

The project settings for building a DAC project are represented in Figure 1.

Figure 1: DAC build settings

A DAC project is built by piping the .SQL scripts to a DAC Compiler (dacc.exe). The DAC compiler serializes the schema definition and .sqlpolicy file into a .dacpac. The artifacts created by the build process are

• .dacpac

• Predeployment scripts

• Postdeployment scripts

SQL Server Database Project

The project settings for building a SQL Server Database project are represented in Figure 2.

Figure 2: SQL Server Database build settings

A SQL Server Database project is built by serializing the in-memory model of the schema definition into a .dbschema file. The artifacts created by the build process are

• .dbschema

• .deploymanifest

• .sqlcmdvars

• .sqldeployment

• .sqlsetttings

• Predeployment scripts

• Postdeployment scripts

Deploy

From a Visual Studio, MSBuild or Team Foundation Server (TFS) Build perspective the action(s) required to deploy DAC or SQL Server Database projects are the same. The implementation of the respective deployment pipelines is very different. One notable difference between the two projects is how changes to an existing database are deployed. Detailed information on deploying your database projects is available on MSDN in Build and Deploy Databases to an Isolated Development Environment.

When a DAC is deployed and an earlier version already exists a "side by side upgrade" is performed by

1. Renaming the existing database

2. Creating a new database using the schema definition in the .dacpac

3. Migrating the data from the renamed database to the new database

4. Optionally removing the earlier version of the database

For more details on DAC deployment refer to the Data-tier Applications in SQL Server 2008 R2 White Paper on MSDN.

When a SQL Server Database project is deployed and an earlier version of the database already exists an "incremental deployment" script is generated that modifies the existing database. This script looks similar to the script a database developer would write by hand to modify an existing database.

Another difference between DAC and SQL Server Database project deployment is that DAC projects can be deployed on SQL Azure cloud instances. This functionality is not supported for SQL Server Database projects.

DAC Project

The deployment pipeline for DAC projects does the following:

• Calls the DAC Framework API and passes the .dacpac to it.

• The DAC Framework:

  • Renames the existing DAC if an upgrade is being done.

  • Executes the CREATE DATABASE, etc. script to create the new version of the DAC.

  • Copies the data from the earlier version of the DAC if an upgrade is being done.

Note that the user must manually execute any pre or post deployment scripts defined in the project.

Alternatively the .dacpac may be deployed using SQL Server Management Studio (SSMS) or by running a PowerShell script similar to the following. Refer to the SQL Server 2008 R2 Books On Line for details.

$srv = get-item .
$sqlstoreconnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($srv.ConnectionContext.SqlConnectionObject)
$sqlstoreconnection.Connect()
$dacstore = New-Object Microsoft.SqlServer.Management.Dac.DacStore($sqlstoreconnection)

--Load a DacType from a dacpac file

$dacpacPath = "test.dacpac"
$fileStream = [System.IO.File]::Open($dacpacPath, [System.IO.FileMode]::OpenOrCreate)
$dacType = [Microsoft.SqlServer.Management.Dac.DacType]::Load($fileStream)

--subscribe for events

$dacstore.add_DacActionStarted({Write-Host `n`nStarting at $(get-date) :: $_.Description})
$dacstore.add_DacActionFinished({Write-Host Completed at $(get-date) :: $_.Description})

--Install

$dacName  = "TestDac"
$evaluateTSPolicy = $true
$deployProperties = New-Object Microsoft.SqlServer.Management.Dac.DatabaseDeploymentProperties($sqlStoreConnection.ServerConnection,$dacName)
$dacstore.Install($dacType, $deployProperties, $evaluateTSPolicy)
$fileStream.Close()

SQL Server Database

The deployment pipeline for SQL Server Database projects takes the .dbschema file and associated artifacts created during the build and does the following:

1. Deserializes the .dbschema file and associated build artifacts (the source) into an in-memory model of the schema definition.

2. Reads the schema definition of the database being changed (the target) into an in-memory model.

3. Compares the two in-memory models and creates a .SQL script that will alter the target to be equivalent to the source.

4. Prepends and appends the Pre and Post deployment scripts, respectively.

5. Saves the.SQL file to disk and optionally executes the "deployment script" on the target.

Alternatively, the .dbschema may be deployed using the command line utility VSDBCMD.EXE using a command similar to the following. Refer to the MSDN documentation for more details.

vsdbcmd.exe /a:Deploy /cs:"Server=MYSQLSERVER;Integrated Security=true;Pooling=false" /dsp:Sql /dd+ /model:"Northwind.dbschema"  /p:TargetDatabase="NewNorthwind"

MSDN documentation on using VSDBCMD.EXE is available here: How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE and Command-Line Reference for VSDBCMD.EXE (Deployment and Schema Import).

This white paper provides detailed information about the Database Project, Server Project, and Data-tier Application project templates so you can make an informed decision about which project type to use for your database design.

See Also

Concepts

Technical Articles for Visual Studio Application Lifecycle Management