At work, I’ve been investigating what will be involved to keep track of the database changes made by our programming team. So far, we’ve been able to work with a common shared database and mostly avoid stepping on each other’s toes. However, there are many shortcomings with not having the database managed with a source versioning system. The shortcomings are starting to cause pain to our managers, so we’ve been asked to do something about it.
From day one, we’ve kept our Visual Basic code in Visual SourceSafe. For a long time, I’ve wanted to work out a plan for doing this, but haven’t had the authorization to spend the necessary time until now. That hasn’t kept me from gathering bits and pieces of information about this issue. However, our needs are more than just storing the database objects in a SourceSafe database.
Some aspects of the process are very simple. We can easily version the views, stored procedures, user functions, and other basic objects. When you want to deploy these objects, you replace the existing copy with the newer version.
But what about table changes and system data? This is not a big deal when generating new databases, but we we need to deploy data conversions to other developers, QA databases, and to customer databases. Data conversions are not a simple “replace” functionality. They have dependencies. If a table has been changed twice since the version you have, the data conversions will likely need to be run in a particular order. In addition, you don’t want to accidentally run a data conversion a second time, as it may corrupt the data if you haven’t built safeguards into the process.
In my mind, deployment needs to occur from what’s in SourceSafe. It would be nice to have a process that helps support the deployment process as well as the simple process of keeping database objects in SourceSafe.
Finally, we’re doing our editing in Query Analyzer. I’d like to have a process that involves an editor that’s geared toward SQL, but has some of the features that are considered standard in a programmer’s editor (auto-indent, split windows, bookmarks, etc.).
Unfortunately, I’ve found that lots of small shops have this issue, but there aren’t many commercial solutions that fill our need. There are large enterprise solutions that might help, but a small development shop can’t afford a tool that would cost 5000 per developer. We could roll our own, but our team of seven developers is busy enough trying to churn out the new features and squash the bugs (some of which are caused by our informal process). I think we’re going to have to settle for a mix of products that help with some parts of the process and home-grown utilities to help with the other parts of the process.
Some other people on the web are also grappling with some of these issues. Here are some of the links I have found:
- Korby Parnell has devoted a category to this topic.
- Here’s a blog entry that talks about VSS and VS.NET, but it seems to only deal with stored procedures.
- Mike Roselius offers a tip on generating scripts from the database, but this just covers one of the easier pieces of the puzzle.