CAST Workbench

CAST Workbench is a SQL Server development tool from CAST Software. CAST Workbench is oriented to database and SQL code development, not database administration. It's the only comprehensive SQL Server development product that I'm aware of and contains several advanced features that might cause Visual Studio users to salivate. Even if you're comfortable with your current SQL Server development tools, you need to see this product to understand what you're missing.

CAST Workbench includes both server and client components. It builds a repository (a data dictionary) database on SQL Server, adds several stored procedures to the master database, and then uses graphical tools to administer the repository and analyze other databases. In addition to tools for SQL Server analysis, CAST Workbench includes tools to analyze C++, PowerBuilder, and VB code for database references. The product is available for SQL Server, Sybase Adaptive Server, and Oracle.

This review will focus on only the server-side components of the SQL Server version. I reviewed version 3.71, which works with only SQL Server 6.5. (See "CAST Workbench and SQL Server 7.0" for more information about the new 3.72 version.)

Overview

When you install CAST Workbench, it creates a database called CAST Workbench Meta-Model (CWMM) on your selected server. This database holds the CAST Workbench in a custom, relatively open format. Most of the other CAST Workbench tools use the repository to store data about the objects you choose from other databases on that server.

The CAST Workbench package contains five main tools. You can use the Repository Manager to inspect the repository database and to resynchronize it with target databases. You can use DB-Builder, the CAST Workbench database application designer tool, to reverse engineer a physical diagram of the database. You can then use DB-Analyzer to analyze the model, do an impact analysis of a schema change, or automatically generate documentation. SQL-Builder is a Transact-SQL (T-SQL) coding tool, consisting of a color-syntax editor, query window, debugger, and query maker. Finally, Release Builder lets you script the database in dependency order, compare databases or database objects, and manage bulk copy program (bcp) graphically. (A sixth tool, Object Designer, is an object-oriented design tool that CAST does not support in the United States.)

Impact Analysis

CAST Workbench shines in database diagramming and impact analysis. Many reverse engineering database tools are on the market, but CAST Workbench is the only one I know that scans trigger, view, and stored procedure code for dependencies, showing them all (including indexes) on an entity relationship (ER) database diagram. Screen 1, page 48, shows some of the objects associated with a table (I removed some objects to simplify the screen shot). Note that the main table, p_Prop, appears as an ER table/entity, with a referencing (foreign key) table called p_BusOp at its left. Going clockwise around the diagram, you can see selected triggers (with a triangle in the background), another referenced table, and then a view (rounded corner rectangle) associated with that table and the main table. Finally, you can see indexes (with a bow-tie in the background), and stored procedures (small circles in the background). For each T-SQL object, the arrows identify the direction and nature of the SQL calls: S for SELECT, U for UPDATE, etc.

After you've made a model, you can use DB-Analyzer to do an impact analysis of a potential change to the database. DB-Analyzer will collect all the objects that the change affects and report them back in the same diagramming format. I changed a column name from status to stat, and the DB-Analyzer returned a diagram showing all the objects (views, stored procedures, triggers) that I needed to change. Double-clicking a trigger invokes the SQL-Builder editor, which highlights the trigger's rows that need to be changed. If you can't make all the changes at once, you can rerun the impact analysis at any time.

The ability to automatically assess the impact of even minor changes on a complex database is unique to CAST Workbench, as far as I know. Also, CAST Workbench can assist coverage and test planning for quality assurance and testing.

Killer Features

CAST Workbench includes several interesting features. Most of the product's utilities contain a child window that scrolls current error and other messages. This capability is in welcome contrast to the dumbed-down Windows interface style of other products in which you see only an occasional dialog box with an OK button.

The T-SQL editor, SQL-Builder, has features that outshine SQL Server 7.0's or Visual Studio's parallel utilities. For example, you can edit T-SQL in box mode, which lets you edit multiple lines at a time. In other words, you can use a CREATE TABLE statement and (if you've lined up the columns) add the keyword DEFAULT to several lines at once. Also, the editor can cycle through a list of possible T-SQL commands that you can use to automatically complete a keyword. SQL-Builder not only checks syntax, but also analyzes the query's semantics and recommends improvements to SQL commands.

Another SQL-Builder feature: If you're debugging a stored procedure, you can save the procedure's parameter values to a file and restore them later without having to retype them. SQL-Builder even has a graphical query builder that guides you through building a T-SQL cursor and the usual SQL queries. Also, CAST Workbench embeds an Interactive SQL (ISQL) session in the same connection as the debugger, so you can change the values of stored procedure variables on the fly while debugging.

The Release Builder scripting tool performs the same function as SQL Server's Enterprise Manager object scripting, but unlike Enterprise Manager, the Release Builder produces CREATE TABLE statements without the annoying default-value constraint names. It also provides a configurable database comparison tool, which you can use to generate delta scripts between a model and a database.

CAST Workbench installation software inspects your server and generates an initial key. Then you call CAST with the key information, which a CAST technician uses to generate your CAST Workbench installation key. Each installation key is tied to a particular server. After I installed CAST Workbench 3.71, I was intrigued to see that it uses DB-Library exclusively. The longest part of installation was installing CAST stored procedures. You'll need 3MB free space in your SQL Server 6.5 master database for stored procedures. The database I analyzed had 39 tables, and about the same number of stored procedures and indexes. Performance on a 350MHz Pentium II was snappy.

Limitations
The main technical limitation I see with the product is that you must use the CAST Workbench tools to modify database objects in order to keep the repository up to date. You can use the Repository Manager to correct this, or you can schedule periodic updating. But it will always be a potential problem, especially with the wide availability of the data tools in Visual Studio and SQL 7.0's Enterprise Manager. In other words, if you make changes to the database structure using the Microsoft tools, those changes will not appear in your CAST model until you resync it.

The main practical limitation is the $15,000 starting price for one developer license. In addition, CAST charges a 20 percent annual maintenance fee. At the time of this review, CAST offers a $7650 starter pack, which helps soften the blow.