In which William Brewer, our software reviewer, goes through the spectrum of emotions whilst using SQLTAC until deciding that, quirks and all, it is very useful.

He ends up feeling that the world of Software Tools will be enriched by this maverick offering, one that tackles a series of database chores, and does them rather well

So what does it do then?

I first came across SQLTAC last year, whilst looking for a good tool for documenting databases. It was called 'Full Metal Jacket' then. Fortunately, the title soon changed, as the term refers to the type of ammunition used by infantry riflemen, and also to the steel coffin that unlucky infantrymen were sent home in.

SQLTAC is an ideal tool for any programmer who is part of a team that is developing a database-driven application in Visual Studio 2008, and being involved in both the database and the front-end. It was once described to me, by Phil Factor I think, as being a 'Swiss-Army knife': He added ruefully that, like the original knife, it still had the tool for taking stones out of horses' hooves. He's right, of course, but on the other hand I'm a softie for tools like this that do a series of simple straightforward routine jobs, and do it well without fuss.

...somewhat baffling,
until you click
the 'Show me' button

Every time I download SQLTAC, Steven has written another feature for it. Steven's life as a consultant is writ large in the row of tabs at the top of the application. For some of these functions, their purpose and usefulness is immediately obvious, whereas others are initially somewhat baffling, until you click the 'Show me' button, at which point it generally all becomes clear. All but two features just work, but these two assume that you have the released version of Visual Studio 2008 as well as Framework 3.5. Visual Studio 2008 is required for creating the data access layer. Sandcastle is also required at one point for for creating help files. 'But most of the heavy lifting is done by SQLTAC', adds Steven.

Ignore the quirks!

SQLTAC is one of those tools one can get very fond of, but in the way one is fond of an eccentric maiden aunt. I had a great aunt who was fine for most of the time but was rather partial to occasionally running around the orchard of her home at night, her hair dishevelled, wailing like a banshee, and wearing nothing but a liberty bodice. Other than this small quirk, she was a pillar of home and community. One got used to just ignoring the quirks, though a stay at her home was always memorable.

We are in Steven McCabe's
designer-free zone

For a start, you mustn't by put off by the website, which is terrible, but in a rather endearing way. We are in Steven McCabe's designer-free zone. In fact, it is rather content-free as well, and parts of the site still refer to the old pre-release version of Visual Studio 2008. It is strangely difficult to find out from the site precisely what the application does, unless you watch the videos, which Steven does well. It is obvious from the FAQ page that nobody asks them. There is one question, and the answer is cut off in mid sentence, as if the answerer had received a sudden Full Metal Jacket round.

When you first run the application, you soon realise that this is not a run-of-the-mill application. You are faced with a box that says

'Licensing and registration so simple even a Cave Man can do it!

Buttons tend to crop up
in unexpected places,
like summer mushrooms

The application occasionally pops up quite unusual messages, but nothing that one could find objectionable. The application itself has a retro feel which is not unpleasant, but does tend to suggest that a designer has not been allowed to stamp his authority on the application. Buttons tend to crop up in unexpected places, like summer mushrooms with inscriptions like 'Bkup …'. Some boxes have thick, bright red, borders like Ikea picture-frames.

I never found a help file. Instead, there is a 'Show Me' button that displays a video of how to use the current feature. If you are intending to use SQLTAC, it is absolutely essential to look at these videos first, as they are by far the best way of understanding what the application is intended to do. At one point, Steven gives startling off-the-cuff remarks such as "…older programmers who may have a touch of Alzheimer's", (thereby losing some rapport from Phil Factor) but the information itself is given in a straightforward manner.

Down to Business

SQLTAC is great for entering documentation into the extended properties of a SQL Server database, and generating this documentation in various formats. For doing this alone, it is worth having, because it draws your attention to the parts of the database schema that are documented and those that aren't. (Steven calls this 'Domain Knowledge'). Even here, it is prone to the odd eccentricity. If you have yet to enter much of your documentation in a database it wails

'Critical! Disaster is at your front door! This is a critical business and operational failure!'

There are nine main functions in the application. These are not all directly related to each other, though there is certainly a common theme. The functions are labelled, slightly bafflingly, . 'Database', 'Comments', 'Analyser', 'SQLValidator', 'Consistency', 'Investigate', 'Create/Load', 'Source Code', and 'Compare'

'Database'

The 'Database' tab analyses your database, and tells you where documentation is missing. It also allows you to manage 'to-do' lists of items that need attention in your database. Whilst it is very handy for a DBA with a responsibility for a large number of developments to be able to quickly assess how well documented a database is, it doesn't assess the documentation in comments within the build scripts and done as part of the routines (procedures, functions, triggers, rules, constraints etc). You can have a very well-documented database which will trigger false alarms if you use just SQLTAC. Many developers are reluctant to use extended properties simply because SSMS and QA are poor at associating them with the script, and the build scripts can start to get very messy. Steven will argue, and I'm sure he's right, that it must be extended properties or nothing because otherwise it will not be available to the programmer in Visual Studio.

'Comments'

The 'Comments' Tab is where you can add and alter the documentation for all the objects in the database. This is where the tool gets very interesting and this tab alone makes the entire product worth-while. It is difficult to over-emphasise the importance of providing documentation and comments for your database objects using extended properties. If you do so, then SQLTAC can use a number of utilities to provide help files, Intellisense, and websites that explain your database to the team members, technical authors, configuration managers, DBAs and so on. This tab provides the quickest route to getting the job done that I've ever come across, simply because it is simply designed and, for this job, the Steven McCabe approach pays off well.

'Analyzes'

The 'Analyzes' feature is potentially very useful. This is because it highlights and helps you fix a range of anomalies and design weaknesses in your database. It will list the dependent object for any objects that it identifies as having problems. It builds a script for any anomalies you want fixed. It needs a bit of a rethink where the problems are only really a matter of opinion. Just as an example, Steven thinks it is a mistake to enter the primary key for MyTabler as MyTable_ID. He advises ID. (oh yes, lots of object-qualification ensues). I consider the reverse to be the case. When you have lots of columns in your database all called ID, then it can start to get very confusing. There must be a means to switch off the stuff that you don't agree with. He alerts you when you don't have a 'logical key' as well as a primary key, which I wouldn't normally consider a 'problem'. Tables are considered 'lonely' if they neither reference another table, nor are they referenced by another table. If you design databases rather differently from what Steven considers 'Best Practice' then the real errors can be swamped by the contentious errors.

...and we zoom in to the detail....

'SQLValidates'

The 'SQLValidates' feature lists all your routines (functions, procedures, default values, computed columns and so on) and allows you to inspect them or execute them. It is certainly a lot quicker to use than SSMS, but then most things other than the growth of trees is. It will also show you any routines that don't compile, and will allow you to rectify them. This is a useful routine, and well-worth having, especially in a large team-built database where it is starting to get a bit messy.

'Consistency'

There is a 'Consistency' tab that shows you any 'orphans' (foreign key records that don't match a primary key value) in the database I can imagine that this tool can be a life-saver for a DBA doing a mopping-up exercise after the procedural brigade have been too near the works.

'Investigate'

The 'Investigate' tab allows you to see where, in any routine, any table column is being referenced. It also shows you the range of values being used by that column. You can find the column you are investigating by picking it from a list or drilling down from the table. For this sort of utility, the Caber Computing approach pays off well. Give Steven a grid and the results are happy for all participants.

'Create/Load'

The Create/Load tab is used for creating a test database. It allows you to create the database, and also to stock the tables in the right order, from your production database, filtering via 'WHERE' clauses if necessary. It doesn't aim to provide you with a completely synthesized database with spoof data but merely allows you to import data from the production database (this is forbidden in most corporate databases as an obvious security risk).

'Source Code'

The 'Source Code' tab is used to build the data access layer for Net Framework 3.5 (LINQ), and results in the 'Intellisense' help for Visual Studio. It will also produce a CHM help file of your beautifully-documented database. This will save a great deal of tiresome work.

'Compare'

The 'Compare' tab shows you, at a glance, a summary of the differences between two build scripts. If you click on a database obkject in the list, you can inspect these differences in detail. SQLTAC will use a Diff viewer for looking at differences in source between two versions of a SQL Server build script. Unlike the usual 'Compare' tools, it does not attempt to synchronise databases but it is very handy if you have no other way of inspecting the differences between builds. It is a fairly thin layer of functionality on top of WinDiff, but adds value by summarising differences in your database objects at a glance.

Getting beta all the time

The application is obviously written using Steven's ExceptionX error-handling system that is described in Simple-talk. The version I used was very definitely a Beta version which required a permanent connection to the Internet. Occasionally, the application gets unlucky, like the poor infantrymen, and spills its guts in front of you with a long list describing the error, invites you to email Steven with the whole mess and then the application disappears in a 'Full metal Jacket'. One quickly loses enthusiasm with bothering Steven with it, though he always emails with an explanation. End-users really don't want to know the entire program structure at the point of the exception. Of course, with his ExceptionX strategy, it would be the work of a moment to make the exception handling send a discreet message to Steven and not confront the hapless user with the entrails.

Wrap-up

So here we have it, a utility that it would be silly to be without if you are using the Data Access Layer of Visual Studio 2008, and a boon to anyone who is serious about documenting a SQL Server Database properly. One feels guilty about drawing attention to the occasional quirk in the application because, in many cases, it actually results in a more useful tool. There are, however, things that need to be put right. There must be a full conventional help file to supplement the videos, the Website needs attention, the beta error-handling code needs to be changed to a release form, references to the LinQ CTP need to be taken out, and a designer, or usability expert needs to help Steven to tidy up the user-interface a bit. Then, we'll have a really good application.

We showed Steven the review before publication and he has already put right many of the things William criticised. Steven is going to launch SQLTAC (www.sqltac.com) at the end of the month, so help him by downloading the Beta and telling him what you think!