Today I attended a Microsoft "Big Event" in Denver that covered Visual Studio Team System.

A lot the content was already familiar to me, but I did appreciate Rob Bagby's demo of Visual Studio Database Edition.  I've known about it, but haven't used it, and wasn't sure if it was something that would help me or not.

There is some good stuff in there, such as database unit tests and nice tooling for generating sample data to test against.  Overall, however, I don't think Database Edition is really geared towards the way I work.  Let me explain.

For nearly all of my 20 year career, I've worked in small startup companies developing database oriented applications.  I've developed two defect tracking systems (PVCS Tracker and TeamTrack), a build / continuous integration system (BuildBeat), and currently, an automated branching & merging system (MergeMagician).  Although the development language changed over the years, all of them used back-end relational databases and some sort of ORM mapping layer.  In all cases, the database was created by the user of the software (none of them were SaaS solutions), using an administrative component and/or wizard that was embedded in the system itself.  Having created the database, the system would then populate it with data as the user used the system (submitted defects, ran builds, etc.)

In this mode of operation, information about the database schema, starts in the application, usually in the form of an XML metadata file that is associated with the ORM mapping layer.  Using this metadata, the application dynamically generates SQL statements and issues them to the database.  This seems a very natural way to work, and because it is so ingrained in how I've worked, it's hard for me to envision doing it any other way.  It turns out, however, that in IT-oriented environments, that database in many cases pre-dates the application under development and already exists.  Developer's in these cases need to transform or import the database schema into a form that programmers can work with.  As the code is modified and deployed, you run into synchronization problems with keeping your code, your development database, and your production database aligned with each other.  This is one of the main areas the Database Edition targets.

Using the database that already exists (once again, an exasperating concept to me), you import the schema with Database Edition and you get a bunch of .SQL file injected into your project.  That means that "the truth about your schema" can now exist independent of the actual database instance.  Since they are regular text files, you can check them into source code, version them, and do all the same sorts of things that you would do with ordinary source code.  You can also now create new database instances, using the single schema truth (your .SQL files in your project).  That makes perfect sense, and once again, the fact you would do anything other than 1) Create file(s) that describe the schema you want, and then 2) Create a database instance using that description, makes it difficult for me to see this concept as revolutionary.  But apparently it is to a lot of people.

The problem for me, however, is that I never work directly with .SQL files.  I always work with a more descriptive file format (usually XML) that not only holds the information that is needed by the database to initialize the schema, but I also store extra metadata in the file that assists in other areas, such as generating documentation and generating code.  The descriptive XML file works in conjunction with the ORM layer and/or code generator so that you get what you want.  Microsoft recognizes this way of working with two technologies, LINQ to SQL, which uses a .DBML file format, and Entity Framework, which uses .EDMX, both of which are XML based.  I have my own ORM mapping layer, which also uses an XML-based metadata file.  The SQL statements are either dynamically created at runtime, or they are code generated.  I think most ORM tools on the market work this way.  It insulates you from working directly with .SQL files.  This is why I have a hard time seeing how Database Edition would be all that useful to me.

I tried to explain these thoughts to Rob Bagby, who, not surprisingly, didn't agree with me.  He asserted that storing the "schema truth" in .SQL files was the right way to do things because "SQL is the industry standard."  For me and my way of working, I still don't buy it.  SQL is a bad file format for me for the following reasons:

* It's hard to parse, and thus hard to manipulate with external tools.  With an XML file format, I can create an XSD and then code generate a data binding layer to read in the file and process it however I want.  With SQL, I have to break into heavy duty lex/yacc and start dealing with language grammars.  I'm sure there are a lot of SQL parsers out there I could use, but it's going to be more work for me.
* It doesn't store enough information.  I want something that not only stores information needed to create the schema, but extra metadata, such as prefixes, name transformations, namespaces, etc., that will feed into my code generator.
* It's not extensible.
* If I target multiple databases (such a MySQL and Oracle), using SQL is dangerous because different databases use slightly different syntaxes.  With an XML file format, I can make different transformations to handle other database types.

For me, SQL is an intermediate file format, not an input file format.  People who work the way I do, are more in need of a tool that will synchronize their XML metadata file (.DBML, .EDMX, or other) with the database schema.  I know a lot of people that are struggling with this very issue.

I've started on an open source project to do this sort of thing.  It's called DbmlManager, and it's available at http://www.codeplex.com/dbmlmanager.  It's not very far along however, but if anyone wants to work on it with me, let me know.