VS Studio 2010 Database Projects

Initial Thought on VS Database Projects

NOTE: I wrote this up as notes on my first experience with database projects in Visual Studio 2010. It contains my notes and thoughts. As 'Noob-notes', nothing here is guaranteed to be correct, insightful, or interesting :)

Meta-model

It looks to me like VS 2010 creates a meta model of the a database from the source files and compares on that. It maps tables, columns, constraints, procedures(body, parameters...), to the source files that define them.

We ran into a situation where somebody defined a pk in the table.sql file instead of the normal key.sql file.  well. because the dif is on a against a meta model, it didn't matter that i changed the location of the pk def to correspond to the standard way in vs db projects. it still recognized that there wasn't a meaningful change and didn't insist on any update to the db.  Now that i've thought how you would do that it doesn't seem amazing to me so much. but i was very impressed with that level of intelligence in the being to handle the situation well.

Best Dev practices

you can develop in SSMS still, but first take your development database and sync it with the db project. when you are 'done', sync the project with your dev db.

only review the dev project for checkin, never the objects in the dev db.

don't have db source files and change scripts.  but do archive the change scripts that are generated and put into prod...  don't hand create them.

Some interesting possibilities

with db projects you would be able to move between versions of the database much more freely.

normally db changes are managed as change scripts in tfs and hopefully equivalent changes to source files for the creation of dbs from scratch. you could think of this as two possibly inconsistent representations of your DB.  for example, say a well-meaning developer creates a change script and doesn't update the 'source' for the db.  and, impossible though this would be, it passes code review and gets into tfs and production.  so you could create a fresh db that is wrong.  maybe this would be detected or maybe not.  But with one definition of the database, you don't have that problem.  You need disipline to generate (and archive) change scripts, not write them yourself.  and the tool will let you easily create a new one and compare it to an existing...or just compare the project to the existing db.

References

See http://vsdatabaseguide.codeplex.com for some really good (i hope, will read before posting) reference whitepapers.