• Article

March 2009

Volume 24 Number 03

Database Development - Introducing New Features In The VSTS Database Edition GDR

By Jamie Laflen | March 2009

This article discusses:

  • Offline Schema Development
  • Product Architectural Changes
  • Introducing Server Projects
  • SQL CLR Project Support
 This article uses the following technologies:
VSTS 2008 Database Edition GDR

Contents

Offline Schema Development
Database Administration
Product Architectural Changes
Introducing Server Projects
Server Project References
Referencing for Shared Server-Level Objects
Referencing for System Objects
Project Upgrade
New Database Project Build and Deploy
Command-Line Facility—VSDBCmd
SQL CLR Project Support
Static Code Analysis Improvements
Tips and Tricks

In November 2008, the General Distribution Release (GDR) for Microsoft Visual Studio Team System 2008 (VSTS) Database Edition was released. The GDR installs on top of the initial release of VSTS 2008 Database Edition, but it is more than a minor version upgrade. The GDR adds support for SQL Server 2008, incorporates improvements to existing features, includes many new features and extensibility points, and incorporates features that were previously released as power tools.

In this article, we focus specifically on highlighting new features in the GDR, including its support for offline schema development, tools that support new processes that you can use when you develop a database schema, and features that support database administration.

In addition to these process improvements, VSTS Database Edition GDR also provides the following capabilities:

  • Interpretation and evaluation of your project's schema and interdependencies. Offline processing enables developers to catch syntax and reference errors prior to deployment.
  • Refactoring—By using VSTS Database Edition, you can change the name of an object (such as a table or column), and that change will update all references to the new name.
  • Automated differencing engine—When you deploy a project, it generates a Transact-SQL (T-SQL) script that contains only the necessary changes to make the target database match the source.
  • Database unit testing—You can use a designer that enables development of T-SQL-oriented tests to exercise and verify your schema prior to checking in your code.
  • Test data generation—You can use this tool to generate pseudo-random realistic test data that can be used when you run unit tests.

Along with descriptions of the capabilities these features provide, you'll find some hands-on examples that show them in action.

Offline Schema Development

Historically, database schema development has required writing code against a shared development server and then writing update scripts to migrate changes from one environment to another. This approach does not provide any way to track and merge changes to database objects, which makes moving changes from one environment to another a very manual process.

In VSTS Database Edition, objects (such as tables, stored procedures, and views) that make up your schema are managed in a Visual Studio database project as CREATE scripts. Storing these objects as scripts means you can manage the scripts through a source code control system and allows more than one database developer to edit the scripts simultaneously and then merge them as appropriate when checking in changes. The process of editing script files without being connected to a database is known as offline schema development. Once the definition of your database has been moved from a live database to an offline/modeled environment, database developers can apply agile or iterative development practices that have made application development leaner and less expensive.

A database developer can begin developing offline by creating a database project in Visual Studio and then importing the current development database into the project. The imported project might raise some warnings, but after you clean up the warnings and the project builds successfully, you can check it into your source code control system.

After the project and source code have been checked in, developers on your team can retrieve the project and source code and work with them on their local computers. Changes can now be deployed to a local sandbox database and manually tested. This approach allows a developer to work in isolation without introducing instability into a shared database environment. You can refine this approach by writing one or more database unit tests to verify a change. Once a change has been verified, run all the unit tests against the sandbox database, which verifies not only the modified code but also that the change did not break any other code. After passing the tests, check the changes into the source code control system.

When changes have been checked in and the team is ready, a build can be performed by getting the source from the source code control system. Once built, the database can then be deployed along with application code to an environment in which users can start working with the newly developed product.

Database Administration

VSTS Database Edition provides several tools that facilitate common tasks you perform when you administer a database. One such tool is Schema Compare, which automates the task of comparing and updating schemas. You can use Schema Compare to compare two databases and then generate an update script to create, alter, or drop objects in the target database. If changes need to be made directly to a live database outside the normal deployment process, you can use Schema Compare to compare a database with an offline database project and then import any updates back to the database project to be checked into source code control.

The Schema Compare tool has a number of new features in the GDR. Most notable is its ability to persist comparison settings and options between usage sessions. (Comparison results are not saved, just the options and settings.)

The capability to persist settings and options introduces a new file type to the project system, the .scmp file, which is stored in the Schema Comparison folder of the project system by default. Multiple schema-compare files can be created and saved in the project. You can also save your Schema Compare settings without a database project to retain comparison settings you use frequently for troubleshooting or other operational reasons.

Schema Compare also now provides session-level option settings that are accessible from the Schema Compare toolbar, shown in Figure 1. You can filter objects to exclude specific object types from comparisons, SQLCMD variables defined in your projects can be substituted for comparisons, and scripting options can be defined that control how the update script is created. All these options can be saved in a .scmp file.

Figure 1 Schema Compare Options

The Schema Compare toolbar provides easy navigation between each schema difference in a comparison. You can filter for types of differences to view new, missing, different, equal, or a combination of these types. Schema Compare also supports comparing .dbschema files. You can now compare any combination of a project, database, and .dbschema file. Writing updates if the target is a .dbschema file is not possible with this release, but all other combinations support both comparison and writing updates to a target.

VSTS Database Edition also provides a tool that makes it easy to compare the data in two database systems. By using Data Compare, an administrator can compare tables that have primary/unique keys and detect where missing, extra, or different data exists in the two systems. Like Schema Compare, Data Compare can generate a script that will write updates directly to the target system to make its data match the source system's data.

Product Architectural Changes

The GDR introduces a number of key architectural changes. One of the most significant is a true model representation of SQL Server databases.

In prior versions of VSTS Database Edition, a design instance of SQL Server 2005 was required. The project system used the design instance as a final validation step for the Data Definition Language (DDL) scripts in the database project. This required each developer to have an instance of SQL Server running locally on his or her desktop or build machines in order to build an offline representation of the database.

With the GDR, the design instance is no longer required, and the project system is supported by a fully modeled representation of the database schema. The GDR builds the model representing the SQL Server database from DDL scripts in your database project and provides the model to all the database tools that are available in Visual Studio. Building the database project produces a .dbschema file that is a serialized XML representation of the model. Deployment of a database project generates a deployment script that is based on a comparison of the project's build output with the model created from the target database.

Between usage sessions of the project, the database project's model is persisted in a .dbml file under the root directory of the project. The .dbml file provides a cache of the model to speed up opening and building of existing database projects that have larger schemas.

Models are implemented by database schema providers (DSPs). The GDR includes three DSPs, one for each supported version of SQL Server—SQL Server 2008, as well as SQL Server 2000 and SQL Server 2005, as in prior versions. With a provider-based model, releases of VSTS Database Edition are now decoupled from releases of SQL Server. When a new version of SQL Server is released or a service pack updates an existing version with new functionality or syntax, a new or updated DSP can be released to support these changes in SQL Server functionality. In future releases of Visual Studio Team System, this provider-based model will enable third-parties to extend VSTS Database Edition's project environment support for other database platforms.

Introducing Server Projects

Server projects, a new feature provided by the GDR, enable a team to define a server-level configuration for their SQL Server databases in a stand-alone project. The main purpose of a server project is to share an expected configuration and objects among database projects. A server project can be referenced by multiple database projects without duplicating server object definitions. For example, you might have three database projects, each representing a user database on your target server. Each of these database projects should have a reference to the same server project to ensure that the configuration of the target database server is consistent.

The server configuration settings defined in a server project are used for verification purposes during deployment, although no actual changes are made to the server configuration at the time of deployment. During deployment, the configuration of the server is verified to match the expected configuration described in the server project. If the configuration verification fails as specified in the server project, deployment will fail and no changes are made to shared server-level objects or user databases.

To configure server settings in a server project, you make changes to the Server.sqlsettings file found in the server project's properties folder. (You can consult SQL Server Books Online for the version of SQL Server you are using for a description of what each server setting does.) An example of server settings is shown in Figure 2.

Figure 2 Examples of Server Settings for a Server Project

By default, no server settings are verified during deployment. To have a setting verified during deployment, check the Verify check box and configure the expected value in the Value column.

Server Project References

Objects in database projects that reference server-level objects require the server-level objects to be defined. Modeling your server-level objects in a server project allows you to reference those objects from your database projects. For instance, if your database project defines a user with a login, you need to create a server project to model the server-level login that is required by the user in your database project. If you are only using system objects and don't have shared server-level objects, you can simply add a reference to the master.dbschema file that is installed with the GDR.

Referencing for Shared Server-Level Objects

Server projects provide support for modeling shared server-level objects, which have different behavior and scope than user-database-level objects. Invalid server-level object references by objects in database projects are flagged and tracked back to the server project, with each unresolved reference indicated by errors or warnings.

A common example of a shared server-level object is a login. Multiple user databases can use the same login when defining user objects in database projects by referencing the login object in a server project. If you have user objects defined in your database projects that reference a login, you must establish a database reference to a server project where the login exists to have your user object fully resolve in your database project. If not, you will receive a validation error similar to the one shown in Figure 3.

fig03.gif

Figure 3 An Example of a Validation Error

To reference objects in a server project, first create a server project that represents your master database. The server project is created from a server project template that can be found under the Database Projects node of the New Project dialog box. There are three server project templates, one for each supported version of SQL Server. If the target server already exists, you can import the master database of the target sever into a new server project. Any user-defined objects in the server will be imported. Next, build the server project to verify that no issues exist and create a .dbschema file that models the server.

In your database project, you reference the server project by establishing a new database reference. In the following example, I have a server project (MyServer) and a database project (MyDatabase) in the same solution. Create a database reference by right-clicking the Database References folder of the MyDatabase project in Solution Explorer and then clicking Add Database Reference. You see the dialog box show in Figure 4.

fig04.gif

Figure 4 The Add Database Reference Dialog Box

For this type of reference, you should accept the default settings presented in the Add Database Reference dialog box. Using the literal master allows your database objects (such as a stored procedure) to reference objects that exist in the master database (such as a table) by including the master literal as part of the three-part name. If you had unresolved reference errors to server-level objects, they should be fixed by the new reference. Figure 5shows the database references for the MyDatabase project in Solution Explorer after the reference has been created.

fig05.gif

Figure 5 The MyDatabase References

Referencing for System Objects

A reference to a master database is also required if your database project uses system objects. An example of a system object is a system stored procedure, system table, system view, or system catalog. A system object used often in user databases is the sysobjects view. For example, look at the stored procedure shown in Figure 6, which uses the sys.sysobjects and sys.schemas system views. Without a reference to a master database containing the definitions of these objects, numerous warnings are produced, indicating that the system objects used in the stored procedure cannot be resolved. Because stored procedures allow deferred name resolution, these are unresolved reference warnings. This same SELECT statement in a VIEW definition would produce errors.

Figure 6 Warnings and Errors Occur without a Reference to a Master Database

To remove these warnings, you must create a reference to a master database that contains the definitions of these objects. However, referencing system objects does not require a server project. Remember, server projects contain the objects and settings you have defined. To reference a master database containing system objects, you reference a master.dbschema file to resolve references to system objects. To create a reference to the master.dbschema file, define a database reference in the same manner as previously presented, but instead of referencing a project, reference a .dbschema file. The master.dbschema files can be found in the following directories:

"%programfiles%\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\ <SQL Version>\DBSchemas"

There are seven .dbschema files installed with the GDR, two for each supported version of SQL Server and an additional dbschema file for the new SQL Types available in SQL Server 2008. Note that each supported version of SQL Server has corresponding master.dbschema and msdb.dbschema files. It is also worth noting that when you create references to master.dbschema files, the model of your project can take a little while to update because these .dbschema files contain all the objects defined in the master database of the SQL Server version that you are targeting. Finally, it is also possible to have both a reference to a server project and a reference to a static master.dbschema file because they serve different purposes. Both references would use the master literal.

Project Upgrade

If you are working with a project created in a version of VSTS Database Edition prior to the GDR, you need to move any server-level objects you created in the pre- and post-deployment scripts into a new server project. During the project upgrade process, any server object defined in the pre- and post-deployment scripts will be moved into a script file named Upgraded.AllServerObjects.sql, which is placed in a subfolder of the root project folder named Upgrade. You can import this script file into a newly created server project by using the Import Script command. To resolve any reference errors or warnings, create a reference from your database project to your new server project.

New Database Project Build and Deploy

When you think about database deployment, a couple of scenarios come immediately to mind:

  • Most IT groups have multiple environments that a build of a departmental application must move through before reaching the production environment and end users.
  • Software vendors have a target database schema in mind when they release an update, but they want to be able to gracefully update a customer's schema that might not match the previous version's schema.

These scenarios were difficult to manage in prior versions of VSTS Database Edition because a T-SQL deployment script was output when a database project was built. The build output was generated by differencing the database project and the database on the target instance (or the design database instance) and then writing a script that would update the target instance to "look like" the project's source. Because the script was generated at build time, there was no way to take the output of a "build" and execute it multiple times against different environments (with potentially different schemas).

In the GDR, the actions taken during build and deploy have changed to better support the scenarios I described. At a high level, a build aggregates the project's contents and generates a set of files that logically represent a deployment package. Deployment consumes the "deployment package" files, generates a deployment plan, and then executes that plan by generating a T-SQL script. You can also configure deployment to apply that script against the target database. As in previous versions, both build and deploy are available as MSBuild tasks and can be used on a team's build server.

To support deploying one build to multiple environments, deployment-oriented configuration information is factored into different files. Although using multiple files adds some complexity, it lets you place the configurations used when deploying to different environments under source code control. To make this concrete, let's look at an example, shown in Figure 7, in which we want to deploy a build of our database project to four different environments.

Figure 7 Example Environments
Environment Deployment Configuration
Development Deployments to this environment always recreate the database. This is the Debug project configuration. Customized SqlCmd variables.
Integration Deployments are differencing, where ALTER statements and DROPs are generated for objects that do not exist in the project. This is the Release project configuration. Customized SqlCmd variables.
User test Preparation for product deployment. Deployments are differencing. Customized SqlCmd variables.
Production Deployments are differencing. Customized SqlCmd variables.

Based on these four environments, we can define four customized deployment configuration files and SqlCmd files, each storing the configuration for the specific environment they are meant to target. After setting up these files, the project's properties look like Figure 8.

Figure 8 Project Properties Set Up for Custom Deployment

In addition to the files, you can see that I have set the Development.sqldeployment file to be the default configuration for the Debug project configuration. Similarly, the Development.sqlcmdvars file is the default definition for SqlCmd variables and values.

After a project is successfully built, the build output can be used to deploy to multiple database instances. The same build output can be used to deploy a new database or to update an existing database. As you saw earlier, a deployment by default will use the project's configuration when the project was built; however, you can override any of the .deploymanifest properties on the command line. For example, to deploy to all the environments listed in Figure 8, you would use the following command-line arguments:

Development:

msbuild EnterpriseDB.dbproj /t:Deploy /p:DeploymentConfiguration­File=sql\debug\Development.sqldeployment /p:SqlCommandVariablesFile=sql\debug\Development.sqlcmdvars /p:TargetConnectionString= "DataSource=DEV\sql2008;Integrated Security=true;Pooling=false"

Integration:

msbuild EnterpriseDB.dbproj /t:Deploy /p:DeploymentConfigurationFile=sql\debug\Integration.sqldeployment /p:SqlCommandVariablesFile=sql\debug\Integration.sqlcmdvars /p:TargetConnectionString= "DataSource=INT\sql2008;Integrated Security=true;Pooling=false"

User Test:

msbuild EnterpriseDB.dbproj /t:Deploy /p:DeploymentConfigurationFile=sql\debug\UserTest.sqldeployment /p:SqlCommandVariablesFile=sql\debug\UserTest.sqlcmdvars /p:TargetConnectionString= "DataSource=USERTEST\sql2008;Integrated Security=true;Pooling=false"

Production:

msbuild EnterpriseDB.dbproj /t:Deploy /p:DeploymentConfigurationFile=sql\debug\Production.sqldeployment /p:SqlCommandVariablesFile=sql\debug\Production.sqlcmdvars /p:TargetConnectionString= "DataSource=PRODUCTION\sql2008;Integrated Security=true;Pooling=false"

These examples use the Deploy MSBuild task to deploy one build of the EnterpriseDB.dbproj to multiple environments using different configurations. You can perform the same type of deployment using a new command-line tool that shipped as part of the GDR, called VSDBCmd.

Command-Line Facility—VSDBCmd

The GDR comes with a command-line tool called VSDBCmd (vsdbcmd.exe). This tool can create a .dbschema file from an existing database and deploy a build output or only a .dbschema file to a target instance. VSDBCmd can also be used on a computer on which Visual Studio is not installed. To move the command-line tool from one computer to another, copy the executable and its components from the Deploy directory under the VSTSDB directory. In a standard Visual Studio installation, this will be "%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\. The Deploy directory can be copied onto a thumb-drive and then placed on another computer.

To introduce VSDBCmd, let's run the tool from the command line. Figure 9shows the results. Although these options might appear complicated at first glance, there are several points to keep in mind that will help you understand how these options are grouped:

  • VSDBCmd is designed to be used with multiple database platforms. Today, VSTS Database Edition supports only SQL Server, but this is likely to change in future releases.
  • To import a database schema or deploy to a database, you need to supply a connection string. This connection string is a standard ADO.NET connection string for the database provider.
  • Because the tool is independent of any specific database provider, you need to provide a hint with regard to which provider should be used with the connection string, and a connection string must be defined to determine the database version you are targeting. For instance, some options for SQL Server deployment are only available for SQL2005 and later.

Figure 9 Output from VSDBCmd

>"%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd" /? Help for dynamic property usage /help[+|-] (short form /?) /Action:{Import|Deploy} (short form /a) /ConnectionString:<string> (short form /cs) /DatabaseSchemaProvider:<string> (short form /dsp) @<file> Read response file for more options Help for command actions /Action:{Import|Deploy} (short form /a) /Quiet[+|-] (short form /q) /ConnectionString:<string> (short form /cs) /DeployToDatabase[+|-] (short form /dd) /ModelFile:<string> (short form /model) /ManifestFile:<string> (short form /manifest) /DeploymentScriptFile:<string> (short form /script) /DatabaseSchemaProvider:<string> (short form /dsp) /Properties:<string> (short form /p) @<file> Read response file for more options

To create a model from the Northwind database, I can execute the following:

"%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd" /a:Import /cs:"Data Source=(local)\sql2008;Integrated Security=true;Initial Catalog=Northwind08" /dsp:sql /model:northwind.dbschema

Here I indicate that I want to create a .dbschema file named northwind.dbschema from the database specified in the connection string. Because VSDBCmd supports multiple providers, I must also specify the provider to use when creating the .dbschema file, in this case the "sql" provider. As you can see, the options to import a database into a model are fairly straightforward. There are some additional import options that are specific to SQL Server. To see these options, you can use the following dynamic property help command:

"%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd" /a:Import /cs:"Data Source=(local)\sql2008;Integrated Security=true" /dsp:sql /?

We can now turn around and deploy the model that we imported to a new database. To deploy the model, use the following command:

"%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd" /a:Deploy /cs:"Data Source=(local)\sql2008;Integrated Security=true" /model:northwind.dbschema /dd:+ /p:TargetDatabase=NewNorthwind

This command deploys the model contained in northwind.dbschema to the instance (local)\sql2008 and into a new database named NewNorthwind. When VSDBCmd is executed without the deploy to database option (/dd:+), only a deployment script is generated, which is very similar to the script generated by deploying a project or by using Schema Compare. In the previous example, if (/dd:+) was not specified, a diff script would be output (because we did not specify a name, the file name would be northwind.txt). This can be a good way to generate a script for verification and review prior to executing the script against the target instance.

The option /p:TargetDatabase=NewNorthwind is different from the other options. It is an example of the many options that are exposed through the tool extensibility framework. To see all the options that are available to SQL Server, use the following command against a SQL Server 2008 database:

"%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd" /a:Deploy /cs:"Data Source=(local)\sql2008;Integrated Security=true" /dsp:sql /?

These are the same options that are available on various deployment and Schema Compare configuration pages.

In addition to deploying a .dbschema file, VSDBCmd can also be used to consume and deploy the output of a database project build. You could use VSDBCmd to perform the same multienvironment deployment performed earlier with MSBuild. However, with VSDBCmd, you do not need to use the .dbproj file to execute the Deploy task or have VSTS Database Edition installed. In the following examples, VSDBCmd is executed within the build output directory:

Development:

"%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd" /a:Deploy /manifest:EnterpriseDB.deploymanifest /p:DeploymentConfigurationFile=Development.sqldeployment /p:SqlCommandVariablesFile=Development.sqlcmdvars /cs:"Data Source=DEV\sql2008;Integrated Security=true"

Integration:

"%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd" /a:Deploy /manifest:EnterpriseDB.deploymanifest /p:DeploymentConfigurationFile=Integration.sqldeployment /p:SqlCommandVariablesFile=Integration.sqlcmdvars /cs:"Data Source=INT\sql2008;Integrated Security=true"

User Test:

"%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd" /a:Deploy /manifest:EnterpriseDB.deploymanifest /p:DeploymentConfigurationFile=UserTest.sqldeployment /p:SqlCommandVariablesFile=UserTest.sqlcmdvars /cs:"Data Source=USERTEST\sql2008;Integrated Security=true"

Production:

"%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd" /a:Deploy /manifest:EnterpriseDB.deploymanifest /p:DeploymentConfigurationFile=Production.sqldeployment /p:SqlCommandVariablesFile=Production.sqlcmdvars /cs:"Data Source=PRODUCTION\sql2008;Integrated Security=true"

From these examples, you can see that VSDBCmd can be used to deploy a build of your database project to multiple environments using different configurations.

SQL CLR Project Support

Since SQL Server 2005, developers have had the capability to implement types, procedures, functions, and triggers as CLR types. Before the GDR, using these types as part of a database project's schema was difficult because the assembly output by the C# or Visual Basic project had to be added as a script with base64-encoded text. With the GDR, support for SQL CLR projects has greatly increased. They can now actively be used as part of your database schema at design time and managed on an instance during project deployment. Let's walk through an example to examine how this works.

First, create a solution and add a SQL Server 2008 database project. Next, add a SQL CLR C# project named CustomTypes, as shown in Figure 10. When this C# project is added, it prompts you for a deployment target. Because the assembly's deployment is going to be managed by the database project, cancel this dialog box.

Figure 10 Adding a SQL CLR Project

Now add a reference from the database project to the C# project. Like C# project references, this reference indicates that the contents of the referenced project are used by the referencing project. Add the reference by right-clicking on the References node in the Database project and then clicking Add Reference. In the Add Reference dialog box, select the CustomTypes project.

Although the reference has been added, you still need to customize how it is configured so that it is deployed correctly. To modify the assembly's configuration, select the reference and then press F4. Figure 11shows what you will see in the property browser.

fig11.gif

Figure 11 Setting Properties for CustomTypes

For this example, I modified AssemblyName to match the name of the project and AssemblyOwner to explicitly be dbo. By default, the permission level of the assembly is Safe. However, you can change this setting if you need to deploy unsafe code to your SQL Server instance.

At this point, you can deploy the assembly created by the CustomTypes CLR project by selecting Deploy from Database1's project properties. Because we haven't configured a connection string for the project, it will generate a CREATE deployment script. In the output window, you'll see the output shown in Figure 12.

Figure 12 Deploying the CustomTypes Assembly

------ Build started: Project: CustomTypes, Configuration: Debug Any CPU ------ C:\WINDOWS\Microsoft.NET\Framework\v3.5\Csc.exe /noconfig /nowarn:1701,1702 /warn:4 /define:DEBUG;TRACE /reference:C:\WINDOWS\ Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\WINDOWS\ Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\WINDOWS\ Microsoft.NET\Framework\v2.0.50727\System.XML.dll /debug+ /debug:full /optimize- /out:obj\Debug\SqlClassLibrary.dll /target:library Properties\AssemblyInfo.cs Compile complete -- 0 errors, 0 warnings CustomTypes -> D:\Demos\Database1\CustomTypes\bin\Debug\SqlClassLibrary.dll ------ Build started: Project: Database1, Configuration: Debug Any CPU ------ Loading project references... Loading project files... Building the project model and resolving object interdependencies... Validating the project model... Writing model to Database1.dbschema... Database1 -> D:\Demos\Database1\Database1\sql\debug\Database1.dbschema ------ Deploy started: Project: CustomTypes, Configuration: Debug Any CPU ------ Error: Cannot deploy. There is no database connection specified. To correct this error, add a database connection using the project properties. Error: The operation could not be completed ------ Deploy started: Project: Database1, Configuration: Debug Any CPU ------ Deployment script generated to: D:\Demos\Database1\Database1\sql\debug\Database1.sql The deployment script was generated, but was not deployed. You can change the deploy action on the Deploy tab of the project properties. ========== Build: 2 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 1 succeeded, 1 failed, 0 skipped ==========

The CustomTypes project is referenced by Database1, so Visual Studio attempted to deploy the project before deploying Database1. Because we did not supply a connection string for the CustomTypes project, it could not be deployed, which caused an error. We can ignore this error because the assembly is going to be deployed as part of the Database1 deployment script. You can prevent this error by turning off deployment of CustomTypes through the Configuration Manager.

After building and deploying the project, you can open the Database1 deployment script and see that the assembly is created toward the end of the script:

GO PRINT N'Creating CustomTypes...'; GO CREATE ASSEMBLY [CustomTypes] AUTHORIZATION [dbo] FROM 0x4D5A90000300000004[snip…] WITH PERMISSION_SET = SAFE; GO

The contents of the assembly are also used to verify type references at build time. To see this, add a user-defined type (UDT) to the CustomTypes project using the default template. Call this file Type1.cs. To use this type in the database, it must be declared in the database project. To add the user-defined type, right-click Database1, click Add Item, and then use the User-defined Type (CLR) template. By default, the file created by the template will be the following:

CREATE TYPE [dbo].[Type1] EXTERNAL NAME [assembly_name].[type_name]

Now modify this T-SQL to reference Type1 in CustomTypes, like so:

CREATE TYPE [dbo].[Type1] EXTERNAL NAME [CustomTypes].[Type1]

Save this file, and then look in the error list. You will see an error like the following:

TSD04117: The referenced type with the required Clr attribute was not found in the loaded assembly.

This error is displayed because the CustomTypes project has not been built since we added Type1.cs. Rebuilding the CustomTypes project will make this error disappear. You can use this custom type as a column type by adding the following table to the project:

CREATE TABLE [dbo].[Table1] ( column_1 int NOT NULL, column_2 dbo.Type1 NULL )

When the table is added to the project, the reference to dbo.Type1 is evaluated by the project system and will cause errors if dbo.Type1 does not exist.

In addition to writing a deployment script, the project can also be deployed to a SQL 2008 instance, as shown in Figure 13. When the project is deployed to the target instance, the deployment engine detects that the database needs to be created and the contents of the project are created in correct dependency order.

Figure 13 Deploying the CustomTypes Project

------ Build started: Project: CustomTypes, Configuration: Debug Any CPU ------ CustomTypes -> D:\Demos\Database1\CustomTypes\bin\Debug\SqlClassLibrary.dll ------ Build started: Project: Database1, Configuration: Debug Any CPU ------ Loading project references... Loading project files... Building the project model and resolving object interdependencies... Validating the project model... Writing model to Database1.dbschema... Database1 -> D:\Demos\Database1\Database1\sql\debug\Database1.dbschema ------ Skipped Deploy: Project: CustomTypes, Configuration: Debug Any CPU ------ Project not selected to build for this solution configuration ------ Deploy started: Project: Database1, Configuration: Debug Any CPU ------ Deployment script generated to: D:\Demos\Database1\Database1\sql\debug\Database1.sql Creating Database1... Creating CustomTypes... Creating dbo.Type1... Creating dbo.Table1... ========== Build: 2 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 1 succeeded, 0 failed, 1 skipped ==========

Static Code Analysis Improvements

Prior to the release of the GDR, static code analysis was available through a power tool. The GDR has integrated static code analysis and provides a couple of changes to the Static Code Analysis (SCA) engine that make its execution more flexible.

SCA can now be executed as part of every build or from the command line using MSBuild. To force SCA to run with every build, toggle the Enable Code Analysis option in the Code Analysis section of the project's properties. Each rule or rule group can be treated as a warning (default) or an error. SCA rules that find errors in your project can cause the build to fail and halt deployment. SCA warnings and errors can also now be suppressed at the rule and file level.

To suppress a rule, right-click an SCA warning or error in the error list and choose Suppress Static Code Analysis Message(s). Rules that have been suppressed are added to the file called StaticCodeAnalysis.SupressMessages.xml in the root of the project system. To include rules that have been suppressed, delete the file from the project or remove specific rule and file combinations from the file. Figure 14shows the code analysis section of the project properties window with the new options.

Figure 14 Static Code Analysis Options

SCA can be enabled as part of a database project's build. If SCA is enabled, it will run as part of each project build and could cause the build to fail if errors are detected by SCA. This is also true for builds started from the command line using MSBuild. The GDR also provides an SCA MSBuild target (StaticCodeAnalysis), which allows SCA to be executed independently of the Enable SCA For Build option.

SCA can be configured per project, which means that a different set of rules and error options can be defined for each configuration or environment. Running SCA from the command line produces a results file called StaticCodeAnalysis.Results.xml. This file is written to the directory from which MSBuild is invoked. The results file name and location can be changed by overriding the ResultsFile property.

To execute SCA from the command line with MSBuild, use the following command:

msbuild MyDatabase.dbproj /t:StaticCodeAnalysis

Figure 15 shows an example of the SCA results file produced.

Figure 15 A Sample SCA Results File

<?xml version="1.0" encoding="utf-8" ?> <Problems> <Problems> <Rule>Microsoft.Design#SR0001</Rule> <ProblemDescription>The shape of the result set produced by a SELECT * statement will change if the underlying table or view structure changes.</ProblemDescription> <SourceFile>C:\USERS\XXXX\DOCUMENTS\VISUAL STUDIO 2008\PROJECTS\ \ MYDATABASE\MYDATABASE\SCHEMA OBJECTS\SCHEMAS\DBO\PROGRAMMABILITY\STORED PROCEDURES\MYSTOREDPROC.PROC.SQL</SourceFile> <Line>6</Line> <Column>8</Column> <Severity>Warning</Severity> </Problems> </Problems>

Tips and Tricks

There are a number of other changes that come with the GDR that I want to note before wrapping up the article.

With the GDR, you now have the capability to move an object from one schema to another. In addition, the GDR has the capability to preserve refactoring changes and emit the appropriate T-SQL to make those changes against the target server during deployment. For instance, if you rename a table, an sp_rename is issued during the next deployment instead of dropping and re-creating the table.

The types of files that can be referenced by a database project have been expanded. In the GDR, a reference can be created to the following file types:

  • Projects
  • .dbschema files
  • .dll (SQL CLR support)
  • .xsd (XSD)

In the case of a database project or a .dbschema file reference, the project is logically referencing another database. If the reference has no three- or four-part name, it is considered a "composite" project reference. Composite projects are a new feature that allows a database to be logically split into multiple projects—the intent was to split the database schema along security or organizational boundaries—for development and then manually deployed in the correct order. Composite projects are only supported for database-to-database references, not database-to-server or server-to-server references. In the case of a database-to-server reference, the reference is seeded with the literal "master."

As mentioned earlier in the article, references to SQL CLR projects, .dlls, and .xsds are references that include the referenced object into the database so that it can be used by the rest of your source code. These referenced objects will be deployed as part of your database project's deployment.

In the GDR release, the deployment property page has been reworked to allow for easy designation of whether settings are stored in the project file (.dbproj) or the user setting file (.user). Figure 16shows an example.

Figure 16 Sandbox Configuration Settings

With this new property page, you can specify where settings are stored. By default, connection strings and the rest of the deployment configuration is stored in the project file and shared between team members. If the setting dropdown is changed to My Isolated Development Environment, the settings under Target Database Settings will be stored in the .user file.

Database unit testing was only slightly modified for the GDR release. One change was added to better support test-driven development, where a database project is deployed each time a test is run. With the new split between build and deploy, test-driven development suffered a performance hit because the database project was actually deployed each time tests were run. (This was not true in previous releases, where the deploy script would short-circuit if it had already been executed.)

Since most developers will use a sandbox database for their unit test runs and only update that sandbox via test runs, a setting was added that will execute the database project's deployment only if the .dbschema for the project is newer than the .sql script. To enable this setting, modify the test project's app.config as shown here:

<DatabaseDeployment DatabaseProjectFileName="..\..\..\Database1\Database1.dbproj" Configuration="Debug" ForceDeployment="false"/>

To detect whether deployment needs to be executed and to distinguish from deployment to other machines, the deployment script is given the name of the sandbox database. For instance, my sandbox database is named Database1Sandbox, so the deployment script is named UnitTestDeployment_Database1Sandbox.sql. As stated before, this setting should not be used if the sandbox database schema is modified in any way other than through unit test execution.

Extensibility has also been updated in the GDR to make way for extensions that will target different database providers. To define a class as an extension targeting a particular provider, you need to decorate it with the DatabaseSchemaProviderCompatibilityAttribute. For a data generator that targets all SQL versions, this would look like the following:

[DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]

Unit testing does not include the concept of a target database provider yet, and all the existing test conditions are provider independent. To define a new test condition add the following attributes:

[DatabaseSchemaProviderCompatibility(DspCompatibilityCategory.Any)] [DatabaseSchemaProviderCompatibility(DspCompatibilityCategory.None)]

These attributes indicate that this test condition is valid if no database schema provider is present and the test condition is also valid for all database schema providers.

Jamie Laflen is a Tech Lead on the DBPro team. He is responsible for the unit testing, data generation, and build/deploy features. Prior to joining the development team, Jamie was part of Microsoft Services and helped customers implemement .NET applications inside Visual Studio.

Barclay Hill is a program manager and a new member of the DBPro team. Barclay comes to the team after leading enterprise database application development efforts in numerous industries for many years.