Visual Studio 2010 SQL Database and DAC Projects (DBPROJ) – Project Properties

This post describes all the properties that may be configured in SQL Server, SQL Database and Data-tier Application Component (DAC) projects and how to go about setting them. Specifically, the properties that are defined and persisted in the DBPROJ file and the msbuild target files it references as well as the DBPROJ.USER file. People that need to manually modify the DBPROJ files or use msbuild to execute the targets will find this information useful.

The properties defined by database projects can be categorized as

• Project level properties – These properties apply to all Visual Studio configurations (i.e. Release, Debug, etc)
• Configuration level properties – These properties may be configured differently for each Visual Studio configuration (i.e. Release, Debug, etc)
• Isolated development environment properties – These properties are developer specific and are stored in the DBPROJ.USER file. These property settings override the Visual Studio configuration settings when the developer has chosen Isolated development environment from the Project Settings \ Deploy \ Configure deployment settings for configuration option. This allows the developer, for example, to specify a different SQL Server instance for their personal development environment than the one that will be used when the Debug configuration is built and deployed.

 

Project level properties

The following table describes the properties that apply to all defined project configurations (i.e. Release and Debug).

Property	Description
AllowDatabaseObjects	This property determines whether or not SQL database level objects are allowed in the project. For example, tables and views. This property is True for database and server projects. Do not change this value.
AllowServerObjects	This property determines whether or not SQL instance level objects are allowed in the project. LOGINs are an example. This property is True for Server projects. Do not change this value.
DAC	This property indicates that the project is a DAC project. Change the setting by selecting Data-tier Application from the Project Properties \ Project Settings \ Database version setting.
DacApplicationName	This property is DAC specific. It defines the name of the Data-tier Application Component. You may change the setting using the Project Properties \ Project Settings \ Data-tier Application properties \ Name setting.
DacVersionString	This property is DAC specific. It defines the version number of the Data-tier Application Component. You may change the setting using the Project Properties \ Project Settings \ Data-tier Application properties \ Version setting.
DacDescription	This property is DAC specific. It defines the description of the Data-tier Application Component. You may change the setting using the Project Properties \ Project Settings \ Data-tier Application properties \ Description setting.
DefaultFileStructure	This property is set by the new project wizard. It indicates if the folders created for the project will be organized by SQL Type (Table, View, etc.) or by SQL Schema (dbo, HumanResources, etc.). This property can only be set by running the new database project wizard. For example, File \ New Project \ Database \ SQL Server \ SQL Server 2008 Wizard.
DeployToDatabaseAddToServerExplorer	This is a Boolean flag that determines whether or not a connection is persisted when the database project is deployed from Visual Studio. Specifically, the connection defined in the Project Properties \ Deploy \ Target connection setting. Persisting the connection will make it appear in Server Explorer, Data Generation, Schema Compare, etc.. Change this setting by manually editing the DBPROJ file.
DSP	This is the property in the project file identifying the Database Service Provider (DSP) (aka the version of SQL server) that the project is targeted for. You may change this value using the Project Properties \ Project Settings \ Database version setting.
IncludeSchemaNameInFileName	This properties controls whether or not the name of the SQL schema to which the object is bound will be included in the filename for the .sql script file. You may change this value using the Project Settings Include Schema name in file name setting.
LoadSqlClrTypes	This property is specific to SQL 2008 database and DAC projects. When set to True it causes the SQL Server 2008 geodetic types to be automatically included in the model for the project. Change this setting by manually editing the DBPROJ file.
ModelCollation	This is the collation used when creating the project model. You may change this value using the Project Properties \ Project Settings \ Collation for database model setting.
Name	This is the name of the project. You may change it by right-clicking on the project node in Solution Explorer and selected Rename.
PostUpgradeAddToSCC	This is a list of files that need to be added to SCC after upgrading a project created in an earlier version of Visual Studio. It is only used during the project upgrade process. Don’t change this value.
PreviousProjectVersion	This is the project version we upgraded from, assuming the project was created in an earlier version of Visual Studio. It is used so that features like Data Generation know the data generation plans should be upgraded when they are opened in Visual Studio 2010. Don’t change this value.
ProjectGuid	This is a value used to uniquely identify each project. Don’t change this value.
ProjectVersion	The version of the template used to create the .dbproj file. This property is used to determine whether or not the project file needs to be upgraded. Don’t change this setting.
SchemaVersion	This property represents the version of the schema for the project file. Don’t change this setting.
ShowWizard	This property determines whether or not the new project wizard will be launched. If you want to execute the new project wizard you should create a new project using the wizard template.

 

Configuration level properties

The following table describes the properties that may be assigned unique values for each defined project configurations (i.e. Release or Debug).

Property	Description
BuildScriptName	This property defines the name of the .sql file that will be created when the user deploys the project. You may change this value using the Project Properties \ Deploy \ Deployment script name setting.
CatalogPropertiesFile	This property defines the name of the file containing the SQL database (aka Catalog) settings. You may change this value using the Project Properties \ Project Settings\ Catalog properties file setting.
CopyToOutputDirectory	This property defines whether or not the file will be copied to the output folder when the project is built. Change this setting by selecting the specific file in Solution Explorer, opening the Property Viewer (F4) and modifying the property setting Copy to output directory.
DefaultSchema	This property controls which SQL schema new objects are bound to by default. You may change this value using the Project Properties \ Project Settings\ Default schema setting.
DeploymentConfigFile	This property defines the name of the file containing configuration settings for the deployment engine. You may change this value using the Project Properties \ Deploy \ Deployment configuration file setting.
DeployToDatabase	This property controls whether or not the deployment .sql script that is created when the user Deploys the project from within Visual Studio is actually executed against the target database. You may change this value using the Project Properties \ Deploy \ Deploy action setting.
DeployToScript	This property controls whether or not the deployment .sql script is saved to disk when the user Deploys the project from within Visual Studio. The project system always sets this property to True when you choose a value using the Project Properties \ Deploy \ Deploy action setting. If you want to set the property to False you must manually edit the project file.
OutputPath	This is the location where build generated artifacts will be saved. You may change this value using the Project Properties \ Build \ Build output path setting.
ServerPropertiesFile	This property defines the name of the file containing the SQL instance settings. This property is only valid for server projects. You may change this value using the Project Properties \ Project Settings\ Server properties file setting.
SqlCommandVariablesFile	This property defines the name of the file containing definitions for any SQLCMD variables the user has defined. You may change this value using the Project Properties \ Deploy \ Sql command variables file setting.
SuppressWarnings	This is the list of T-SQL Static Code Analysis rules that have been suppressed for the entire project. You may change this value using the Project Properties \ Build \ Suppress warnings setting.
TargetConnectionString	This property stores the SQL connection string that will be used when deploying the project. You may change this value using the Project Properties \ Deploy \ Target connection setting.
TargetDatabase	This property stores the database name that will be used when deploying the project. You may change this value using the Project Properties \ Deploy \ Target database name setting.
TreatWarningsAsErrors	You may change this value using the Project Properties \ Build \ Treat warnings as errors setting.
DacDatabaseCollation	This property is specific to DAC projects. The setting defines the SQL collation of the target database. You may change the value using the Project Properties \ Build \ Database collation setting.
ValidatePolicy	This property is specific to DAC projects. The setting determines whether or not the DAC deployment engine will validate that the target SQL instance meets the policy settings defined in the .sqlpolicy file. You may change the value using the Project Properties \ Deploy \ Validate server selection policy on destination instance setting.

 

Isolated development environment

The purpose of the isolated development environment settings is to individual developers to control database deployment behavior without affecting other team members. These settings are not persisted in the DBPROJ file so they will not be checked into source code control. The following table describes the properties that may be set for the developer’s Isolated development environment. These properties are persisted in the project’s .user file as opposed to the project file itself. When the developer sets Project Settings \ Deploy \ Configure deployment settings for to My isolated development environment the following properties are used for the associated setting instead of the Visual Studio configuration’s properties.

Property	Description
UseSandboxSettings	This property determines whether or not the settings defined for the user’s Isolated development environment will be used. Set this value by changing the Project Settings \ Deploy \ Configure deployment settings for setting to My isolated development environment.
SandboxDeployToDatabase	This property controls whether or not the deployment .sql script that is created when the user Deploys the project from within Visual Studio is actually executed against the target database. You may change this value using the Project Properties \ Deploy \ Deploy action setting.
SandboxDeployScriptFileName	This property defines the name of the .sql file that will be created when the user deploys the project. You may change this value using the Project Properties \ Deploy \ Deployment script name setting.
SandboxTargetConnectionString	This property stores the SQL connection string that will be used when deploying the project. You may change this value using the Project Properties \ Deploy \ Target connection setting.
SandboxTargetDatabase	This property stores the database name that will be used when deploying the project. You may change this value using the Project Properties \ Deploy \ Target database name setting.
SandboxDeploymentConfigFile	This property defines the name of the file containing configuration settings for the deployment engine. You may change this value using the Project Properties \ Deploy \ Deployment configuration file setting.
SandboxSqlCommandVariablesFile	This property defines the name of the file containing definitions for any SQLCMD variables the user has defined. You may change this value using the Project Properties \ Deploy \ Sql command variables file setting.

Comments

• Anonymous
  June 30, 2010
  So there are two sets of connection settings I can specify.  One for the project, and one for the user.  When is one or the other setting used?  How do tell VS which of the two settings to deploy to?