We have been using Visual Studio Database Projects for a few years now and it’s easy to take the product for granted. It’s one of the greatest boons to our SQL Server application development process and I can’t sing its praises enough.
Very briefly, here’s how we use it: A developer updates or adds database objects in the Visual Studio Solution. He tests the change locally in his own sandbox (which includes a database instance). When happy, he checks in the set. Continuous Integration takes over and creates a SQL script to upgrade a central build instance of the database to include the new changes checked in. The script is run against that instance and any errors applying the script reported back as ‘build’ errors. So the team gets immediate feedback on the validity of SQL artifacts just like any .NET code. The final step in the process is preparation of database upgrade scripts for shipping to your customers. The key here is to take these off the central build instance of the database which ensures that only changes that were checked in and part of successful builds get included. So never again are you in the situation where database artefacts are shipped but not in source control! (We’ve all been there, don’t deny it.)
Ok, just a couple points of detail you may benefit from if you go ahead with this approach:
1) If using Visual Studio 2008 Database Edition, be sure to install GDR R2 which has the latest updates and fixes you’ll need
2) When running MSBuild.exe, you will need to make two passes: one with “/t:Rebuild” and a second with “/t:Deploy”
Well that’s the highlights of the process. For more detail on any one of the individual steps feel free to comment on this post or ping us a note.
-Krip
