通过

GDR doesn't validate what!?

  • 项目

Up to the release of Visual Studio Team System 2008 Database Edition GDR a design database was used to store and validate the database project's schema model.  The design database was dynamically created when you opened a database project (.dbroj).  The project system stored the schema model in this design database, 2005 SQL Express by default, behind the project system.  As you versioned your database schema, changes were validated against this live database instance and any errors would be reported immediately through the error list in Visual Studio. For example, if you tried to use a variable that was not defined you would get an error immediately in the error list. Here is an example of this error in Visual Studio 2008 Database Edition.

With the release of the GDR the product no longer uses a live design database to store and validate the database schema model from the project system.  Instead, the database schema model is stored in a SQL CE database and validation is performed by the product's validation engine against an in memory model of the database schema.  This is a substantial change in the product's architecture and enables many scenarios not possible with the previous architecture. Database schema models are implemented by Database Schema Providers (DSP) and are extensible. So if you are working in a SQL Server environment you may be using a SQL100 DSP or if you are working in a Oracle environment you may be using ORA11 DSP. This allows DSP models to be added and serviced without requiring a major release for the development environment. Moreover, the model can now be compiled into .dbschema file and used by other project features and shared with other development teams.

In this model based architecture you are working in a completely disconnected database development environment. Connection to a live database from the project system is limited and only available when you are intending to import, compare, or deploy changes to a target database instance. As you develop your database schema, validation is ran interactively based on changes occurring through the project system, on idle while you are not actively making schema changes, or depending on the nature of the validation to performed, at build time to make sure the model is fully validated. Schema validation in the GDR is not as granular as the previous architecture. Validating the schema is a resource intensive process and so in the GDR it is focused on validating all top level objects, any references between objects, and most dependencies between objects and their composing children objects.  There are additional validations that run to catch common design time mistakes, but not all mistakes. The most significant difference between the GDR and previous version in terms of validation is the lack of validation of programmability object bodies (functions and stored procedures). Within the body of programmability objects validation is kept to minimum to not impact the performance of the developer's interactive user experience. Most of validation that is performed within the bodies of statements is to insure all objects referenced and their children can be fully resolved. The code is parsed and interpreted, but not everything is validated 100%. In fact, our example above would not be caught at design time, but at deploy time. Here is a deployment failure of the same issue:

Given this change, you will want to do trial deployments to ensure errors that can only be found at deployment are caught before making production deployments. Typically you will do this many times during development as you implement unit tests for your database schema. Deploying to a sandbox database will help you build confidence in deployments to other environments. This is also done in many environments during integration. The integration database is created or updated at the end of each day or after check-ins trigger build and deploy when you have implemented continuous integration.  You can also implement a custom static code analysis rule to check for undefined variables at design time if you wish. They key is to deploy early and often to validate your deployments.

Thanks to Gokhan Caglar of the VSTSDB team for his contributions to this post.

Comments

  • Anonymous
    March 16, 2009
    The comment has been removed