Visual SourceSafe and SQL Server

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:

  1. #1 by Nik Shenoy on September 14, 2003 - 2:45 pm

    I have exactly the same problem. I was thinking about it after you posted this, because migration has become something of a nightmare. I think this has to do with the fact that, in the case of SQL scripts, the upgrade scripts are as important as the create scripts.

    My thought here is that there is a significant difference between a “release” and a specific file version. A release has a little more management involved and is a little more concerned with file and functional dependencies.

    So, my thought was, you could formalize the release process a little by keeping a release table in your database (nothing fancy, release number, date of release, released by, etc.). You could then define a set of update scripts that would move you from one version to the next in another table. You could then build a small tool that would uprade form the current (known) release to any future release by looking at the update table and executing all scripts to get to that version. It could even do a dependency analysis and do multiple steps in order, and it could handle dependencies between the update scripts. After update scripts for a specific release were run, the release table would be updated.

    The tool would have to be able to handle failures in some graceful way. A failed upgrade script could get pretty messy.

    This is essentially what I have to do now, manually, but it would be nice to formalize the process. It would also be great in the case where you have QA servers and production servers, and the release schedules are not in lock-step.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: