In the comments to my post on running separate dev, test, and live databases, Karl raises a question about the build process and using source control. Well, first, if you've read my Script It! post, you know I advocate scripting all database changes and keep them in source control. I've used Visual SourceSafe before, but it's a pain if you've got someone working remotely. If so, you could use SourceGear's SourceOffSite (I haven't used it, just read about it) or as Damian would recommend, switch to CVS. But be aware that the model used in CVS is "compare and merge" vs. VSS's "exclusive check-out".
Either way, when I'm working in a team of developers, we appoint one person to be the Build Master and it is that person's responsibility to watch out for and resolve conflicting changes, and to make sure everything that makes up one upgrade is all packaged together. In VSS you can PIN a version, which means that version in the history is the one you will receive when doing a Get Latest Version, and so the Build Master can pin the versions that make up the next upgrade package, retrieve them all into an upgrade directory and apply them. Other people like to use branching and merging in their source control, and I'll leave it to you to find out how that works in whichever application you're using. Depending on the scale of your project, in addition to the Build Master you might have a Development DBA who handles all the reviews of changes for SQL stuff separate from the other application updates. Again, it is this DBA's responsibility to determine which SQL updates get made when.
As for your maintaining the integrity of your dev system, I believe that the dev database should be refreshed periodically to clean out the junk that normally builds up. If all updates are scripted, then it's easy enough to refresh the database from backup or from a snapshot of another DB and then re-apply any changes made or in-progress. With everything scripted, your developers should not lose any work. Of course your dev image should be updated periodically to include recent changes. If developers are running their own local copies of development databases, then there needs to be some procedure for updating/refreshing those. At the least, before a developer releases his code to QA, he should have to apply all upgrades that have been processed into the live system so he can re-test all of his changes against current code.
posted @ Friday, February 27, 2004 9:43 AM