Export (0) Print
Expand All

Visual Studio 2010 SQL Server Database Projects

Visual Studio 2010

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

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.

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.

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.

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.

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

Ff678491.VS_2010_SqlDB_1(en-us,VS.100).png

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

Ff678491.VS_2010_SqlDB_2(en-us,VS.100).png

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.

Community Additions

ADD
Show:
© 2014 Microsoft