asp:review

Total SQL Analyzer

Tremendous Bang for the Buck

By Thomas Wagner

One of the aspects in which SQL Server 7.0 and later versions differ from many other databases on the market is in SQL Server's support for the Distributed Management Object (DMO) interface. This interface has mapped the server- and database-management functionality typically provided by the SQL Server Management Console to a set of COM objects. In short, DMO allows a programmer to manipulate the server in much the same way as a database administrator might via the Enterprise Manager. DMO is the cornerstone of FMS' Total SQL Analyzer. Through the extensive use of this interface, Total SQL Analyzer can dissect the contents of SQL Server and report in marvelous detail about the internals of any database.

In the product's jargon, a documentation session is called a job. Job-definition information is stored in a JET data file. The results of an analysis job are stored in a second JET data file. The only limitation of which I am aware is that Total SQL Analyzer is unable to document multi-server systems. However, I suspect that organizations that deploy multi-server systems would not be typical FMS customers and probably account for a small percentage of SQL Server users overall.

The product's IDE allows you to define, run, view, and print job results quickly. FIGURE 1 illustrates an example job in which I had the program dissect all databases located on a development machine.

FIGURE 1: An example job in Total SQL Analyzer.

I found several choices FMS made when creating Total SQL Analyzer to be thoughtful and based on great attention to detail. For example, the product lists results in a menu tree that uses the same icons as SQL Server. That's just one usability advantage. You won't waste time figuring out that a particular icon really stands for the stored procedure section.

Dozens of Reports

Doing an analysis job is one way to work with Total SQL Analyzer. More importantly, though, in my mind, the user will be able to use more than 70 reports that ship with the program. Here is a small sample list of reports:

• Database Object Inventory, Sorted by Database and Object
• Database Scripts, Sorted by Database
• Replication Distributor Properties, Sorted by Property Name
• Replication Properties, Sorted by Property Name
• Replication Publisher Properties, Sorted by Property Name
• Stored Procedure List with T-SQL, Sorted by Procedure
• Table Dictionary, Sorted by Table Name
• Table Dictionary with Column Properties, Sorted by Table Name
• User Defined Function Properties, Sorted by Function
• View List with T-SQL, Sorted by View

One of my personal favorites is the Issues report. Here's a partial list of some of the flags it could raise:

• Lock Timeout set to default (Server)
• Max Asynchronous IO set to default (Server)
• "sa" user with no password (Server)
• Column consistency issues (Server)
• Database and transaction log share physical drive (Database)
• Database has multiple owners (Database)

The column-consistency check is especially useful. It pointed out that a database I have inherited contained several columns with data-type problems (varchars of different sizes that should have been the same).

In order to select and print a report, you pick the type of objects you want to output, and then a specific report applicable to the object. FIGURE 2 illustrates the report-selection dialog box.

FIGURE 2: Selecting one of dozens of reports available in Total SQL Analyzer.

In order to provide maximum flexibility, it is also possible to filter certain reports by concentrating the output on one database or, alternately, one object type across numerous databases.

And, finally, there is the printed output. I am certain I've missed other useful pieces of information Total SQL Analyzer reports, so I suggest you visit the FMS Web site for more detailed information. There isn't enough space in this magazine to show the output in detail.

The Bottom Line

Total SQL Analyzer is a unique tool with a great price. I'm not certain this product has any competition. One of the products in Embarcadero's suite, which would cost much more, might come close. Building on 15 years of experience, FMS continues to deliver outstanding bang-for-the-buck development tools. I highly recommend this product.

Thomas Wagner is chief executive officer of etechpartner, Inc., located in Los Angeles, CA. Readers may reach him at mailto:thomas.wagner@etechpartner.com.

asp:factfile

Total SQL Analyzer provides detailed analysis of any database. The product comes with 70 report options, such as Database Object Inventory, Sorted by Database and Object. An enhanced version of the product, Total SQL Analyzer Pro, was released recently and offers detailed analysis, improvement suggestions, and dependency information.

FMS, Inc.

8027 Leesburg Pike, Suite 410

Vienna, VA 22182-2710

Phone: (703) 356-4700 or toll-free at (866) 367-7801

E-Mail: mailto:info@fmsinc.com

Web Site: http://www.fmsinc.com

Price: Total SQL Analyzer single-user license, US$499; Total SQL Analyzer Pro single-user license, US$999.

Rating: