VSS and SQL Server: Reviews

I’ve been busy the past two weeks really digging into demos of products that will help us integrate our SQL development with SourceSafe. I’ve also been looking at bug tracking products, since our current bug tracking is done through Exchange Server using a set of public folders, attached Word documents within the public folders, and categories to control our priorities. I will write more on the bug tracking in a future post.

When I started the review process, I was concerned mainly with these things:

  • Recording changes in SourceSafe
  • Automated lookup of column names
  • Updating other databases with the changes

I spent a lot of time reviewing our current process for database udpates. I thought about how the database update process could be automated. Table changes make the update process really difficult. In the end, I put the idea of automating the database updates on the back burner. However, the issue still affected my decisions in the review process.

Recording changes in SourceSafe is pretty straightforward. Lots of companies have a manual process for editing stored procedures. The procedure is generally like this:

  1. Check out the stored procedure to a file
  2. Edit the checked out file (Query Analyzer is a popular tool for this)
  3. Apply the changes to the database
  4. Save the changes to the file
  5. Check in the file

I like the idea of an integrated tool. Visual Basic has a simple integration to SourceSafe that makes things very easy. Why can’t we have the same thing with SQL development? I looked at two products in depth, SQL Source Control 2003 and mssqlXpress. These two products were the only ones I could find in a price range that is reasonable for small development teams. Both products are relatively immature, as they have been available for less than a year.

SQL Source Control Review

SQL Source Control is developed in Poland. It has two features that I would really like – custom documentation of objects and automated deployment (updates).

I really like the idea of documenting tables and columns. We have that kind of documentation, but it is not integrated into the development environment. SQL Server has metadata, but it is limited to 255 characters. A lot of our documentation, which is stored in Word documents, is usage documentation that describes the relationship of a column with other columns in the same table and in other tables. This often takes more than 255 characters.

SQL Source Control allows documentation of everything and it is easily accessed within the development environment. This feature had me leaning to SQL Source Control before I dove into the depths of the product. However, other problems in the product soon overrode the benefit of this feature.

The biggest problem for me is that Intellisense only works when you qualify every column in your query with the full table name. Aliases do not bring up Intellisense. I never use full table names in my queries. In fact, we’ve developed a standard set of aliases that we use with our tables. If we used this product, we wouldn’t have Intellisense, which is a major feature.

The perceived problem is performance. The demo only allows you to work on the first five objects. Even with only having 5 views, 5 stored procedures, 5 tables, etc., the application was slow to go through a “synchronization” process. I shudder to imagine how long the synchronization process will take with our 1300 stored procedures, 400 tables, and 1700 views. This may not really be a problem, but I wouldn’t be able to find out without spending money.

The automated deployment idea sounded great, but I didn’t trying it in depth because of the Intellisense issue.

The documentation for SQL Source Control is pretty good.

mssqlXpress Review

mssqlXpress is developed in Australia. It doesn’t have the documentation feature. It doesn’t have the the automated deployment feature, although their product comparison page promises it “Soon!”

mssqlXpress’s Intellisense, which they call “Code Complete”, works on table aliases. The layout of the user interface is a little funky at first, but I found that I quickly got used to it.

The F5 key serves two purposes, which I think is a no-no in UI design. If the focus is in the database object tree, it refreshes the status of the tree. If the focus is in the code window, it executes the SQL code (just like query analyzer). I got bit by this because I wanted to refresh the object tree, but I hit F5 with the focus in the code window. A change was made that I wasn’t ready to commit to the database. I was easily able to roll it back, but I wouldn’t have had this problem if the F5 key only served one purpose.

mssqlXpress keeps track of table changes by keeping both the create script of the table and a change script in SourceSafe. The change script can be checked out and edited. Each change is stored as a separate file name. This design makes a lot of sense to me. I can envision automating table updates and data conversions by figuring out which version of the table you currently have, then sequentially executing each change script until you get to the desired version.

mssqlXpress also maintains a public forum for their products. I found this forum invaluable in learning the product. Unfortunately, I was driven to the forum because the HTML documentation for the product is pretty poor. A number of features and options in the product weren’t documented at all.

I can sympathize with the documentation problems. At work, we have a great documentation team, but our product is changing so rapidly that the documentation team can’t keep up with the changes. I’m giving the company the benefit of the doubt and presuming they are having the same problems.

In my opinion, this documentation problem is a symptom of a positive aspect about the product. The programmers are actively working on the product and rapidly creating new functionality (as well as new bugs!). In the two weeks that I was working on the product, I thought that an “auto indent” feature would be very helpful. I also ran across a problem that was due to our development SQL server using a binary sort order. I reported this problem to the developers.

This morning, I received a beta announcement. This beta fixes the problem with a binary sort order. It also adds an auto indent feature. I haven’t tried this new version yet, but the rapid response is a sign of a product and company with which I’d want to develop a relationship.

Conclusion

Given all the factors I’ve described here, I recommended to our management team that we invest in mssqlXpress. It will achieve our primary objectives, and seems to be on a fast track to being a great product. All comments are welcome.

Advertisements
  1. #1 by Michael on October 22, 2003 - 2:49 pm

    Here’s an approach that I’ve been using w/o any third party tools (though based on your review I plan to take a look at mssqlXpress):

    I set up a database project in VS.NET. Under change scripts, I have folders named “Change to [version]”, where [version] is the last shipped version. This naming scheme allows me to avoid problems where marketing decides to change the current work-in-progress version on me. I am always working with the latest “Changes to…” folder, and I *only* add files to it. These files are VS-generated change scripts (in case of tables) or gen scripts (in case of sprocs). I name these files “[index]_[description].sql”, where [index] is an ascending three-digit 1-based number, and the [description] is just a hint to me at what this script contains.

    Given this structure, I can always go through the incremental changes needed to get schema from one version to the next. I’ve also built some infrastructure around this where my application knows about its schema version and is able to automatically update the DB when the schema has fallen behind (using the aforementioned script files).

    This approach has its limitations, but it does work quite well for me (I’ve used it over the years at two companies with two dev teams).

    -Michael

  2. #2 by hurcane on October 22, 2003 - 11:56 pm

    Michael:

    There are many “homegrown” solutions out there. It’s what we do when there’s not another viable solution. If you search Google for “abaperls”, you’ll find a comprehensive “homegrown” solution that has been placed into the public domain. I looked closely at this process, but found that it wouldn’t work for our environment.

    Our database development is for an enterprise-level application that we sell. Many solutions work great when you have complete control over the administrative process. We have that internally, but we still need something that will streamline applying updates at customer sites as well.

    I’m relatively new at this, so I certainly appreciate the feedback.

  3. #3 by Bryan Oliver on November 30, 2003 - 9:14 pm

    Hi Hurricane,

    I’m glad you enjoyed using the product, once we have released 1.3 we will be sending the documentation back to the writers to be updated and revamped (they have a hard time keeping up at the best of times).

    The F5 issue is a hard one even Microsoft QA has the same issue it has F5 for refreshing its tree and F5 for executing the code.

    We welcome all feedback and suggestion that you have for making our product better.

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: