best practices for using the sql server 2005 database project in visual studio 2008

Question

what are the best practices for using the 'sqlserver 2005 database project' in visual studio 2008?

I have created a project file. How to create the scripts for stored procedures, views and Tables? How would I generate scripts to populate some of the look up data?

What would I do about modifications that may need to be run on the production server after the database has already been deployed to production. (I imagine that I would create folders named by the deploy date with all the change scripts )

Is there a way to have Team Build tear down and rebuild a database in order to test that all the scripts work?

I have not been able to find a walk threw on the web. If you could point me in the right direction, It would be greatly appreciated.

Thanks

Answer 1

I don't generally use the database project type in VS. My most favorite tool for dealing with databases is Red Gate's SQL Compare (SQL Data Compare is also good). Invariably scripts get out of sync with qa/production and this tool will save your life (well, at least save you time and sanity).

Answer 2

The best practices for managing the lifecycle of any database, regardless of technology, are:

1. You should be able to build your project in one step. This includes running your unit tests, integration tests, regression tests, and by extension testing your migrations. (This is, of course, point 2 in The Joel Test.)
2. By extension, you should be able to deploy your database changes in one step.

At the very least, you should get something out of your automated build that you can run, without tweaking, on your production server, to upgrade your database to the new version when you are upgrading your program. Ruby on Rails migrations are a good example of how it should be done.

Unfortunately, SQL Server 2005 database projects take a completely different approach, which, as far as I can see, is totally incompatible with these principles. They do offer "incremental deployment," but it seems that this is based entirely on schema comparison tools. Schema comparisons are useful as a starting point, but there are many database refactorings that they can not handle, and more often than not, you are going to have to tweak them (a process made all the harder by the long-winded, difficult to read, spaghetti code that they generate.)

I've written a blog post where I examine these issues in more detail.