Going hand in hand with my exploratory work on Team Foundation Server 2010 I have been thinking recently about how my team can improve the quality of our database work too. We tend to keep things simple with databases by using just tables and stored procedures. The stored procedures are always extremely simple - either a read, or an insert, or an update. We don't store any advanced application logic; I have always found T-SQL difficult to program in and we use VB.NET for all the complicated stuff.
We're facing a few problems. All of us are working on one database at the same time which I have read in a couple of places (including K. Scott Allen's excellent blog on the subject) that this is a bad thing. We also have different versions of the database without really knowing what the differences are between them and it's difficult to keep track.
After reading around on the subject I'm planning on introducing the following changes:
1. I would like to give each developer a sandbox area in which they can work in isolation. I am going to look at how this can be achieved through the use of a virtual desktop.
2. I want to introduce a scheme repository that developers write change scripts to and can access to get the latest database build. Again I have taken inspiration from K. Scott Allen. The repository will be a single database table with some information about the version, update date, author etc. and also the location of the text file that contains the script. So the first entry will be 1.0 and will be the build script for the database as it stands at the moment. Every time a developer makes a change they will use a custom app (I need to write this) that will create a new entry in the database and also write the script to a text file. I then need to have a process that will let someone get the latest build by creating one giant script encompassing the baseline and all changes made. The developer could also pick a specific version to build up to, and it would only include all changes up to that build, leaving out any newer changes.
I think this will be a big help for us in terms of organising and moving forward. Hopefully I can post some more on this subject when I get it all up and running.
One last point; I know that Team Foundation Server 2010 supports database projects that can source control all the objects in a database but we don't have the correct version - it's not included in the Professional license (I think Premium and Ultimate only).