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
Renaming the existing database
Creating a new database using the schema definition in the .dacpac
Migrating the data from the renamed database to the new database
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:
Deserializes the .dbschema file and associated build artifacts (the source) into an in-memory model of the schema definition.
Reads the schema definition of the database being changed (the target) into an in-memory model.
Compares the two in-memory models and creates a .SQL script that will alter the target to be equivalent to the source.
Prepends and appends the Pre and Post deployment scripts, respectively.
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