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:
- Check out the stored procedure to a file
- Edit the checked out file (Query Analyzer is a popular tool for this)
- Apply the changes to the database
- Save the changes to the file
- 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 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.
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.