VSS And SQL Server: Part 2

Nik Shenoy makes the following comment regarding my previous post about Visual SourceSafe And SQL Server:

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.

I agree 100% with this statement. File versions of stored procedures, views, and functions are pretty much interchangeable. However, data has historical dependencies. I can’t update data in a child table if the parent table hasn’t been created yet. A table transformation depends on all previous transformations of the same table to have been completed.

Nik’s comment describes how he is thinking about implementing the table updates. Here is an idea that I have borrowed from a UNIX-based product I used to support. I haven’t thought about this in depth, so I don’t have details worked out.

Create a table in the database that keeps track of data conversion history. The table contains a primary key column (a unique name for the conversion).

Have a master data conversion script that will contain all historical data conversions. Each conversion will have a unique name. The script will check if the conversion exists in the conversion history table. If it exists, the script does not execute that conversion. If it does not exist, the conversion is executed and a record is added to the conversion history table.

An alternative idea I have been toying with is to use the build version that we store in the database and forget the data conversion history table. A master data conversion script is still used. The master script retrieves the old build version. Each data conversion knows which build version it was created against, so it can make sure the old build version of the databsae was at or below the build version for the conversion.

Because we use exclusive checkouts, a master script may not be workable if we have two developers wanting to write conversions at the same time. Whatever mechanism we use to perform updates, we’ll need to be able to track which conversions need to be done and make sure they are done in the proper order.

We also have a problem with maintaining and updating “system” data. For example, we have a codes table that is used to populate drop-down combo boxes in our UI. We’ll need a mechanism to update these system data tables. Some tables contain a mix of developer-maintained and user-maintained data. For example, we have a table that stores system numbers (next sales order, next purchase order, next invoice, etc.). The NextNumber column needs to be preserved during an update, while the rest of the data needs to be refreshed during an update.

These are not unique problems. Every database development team faces these issues. There don’t seem to be well-documented solutions to these problems, however.

Advertisements
  1. #1 by abombss on December 13, 2003 - 9:14 pm

    Why not get Visual Studio .NET 2003. It works awesome with VSS and SQL. The VSS is built directly into the tool. The developer simply gets the project directly in Visual studio from VSS and starts working. If you need to modify, simply right click ‘check out’ make your changes and check in.

    It also provides EM MMC snap-ins along with templating and query builders. Sorry no code completion yet.

    I prefer scripts to gui creation anyway, but the gui builders allow sql code blocks to be automatically inserted inside your source scripts.

    There is also the ability to autogen deployment scripts. Just choose the scripts to include and bat file with error checking is auto created. Pretty nice feature.

    peace…

  2. #2 by hurcane on December 14, 2003 - 12:39 pm

    Thanks for the thoughts about VS 2003. Our environment is still VB6, and we won’t probably be moving to VB.NET for a couple years.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: