asp:review
Total SQLAnalyzer
TremendousBang for the Buck
By ThomasWagner
One ofthe aspects in which SQL Server 7.0 and later versions differ from many otherdatabases on the market is in SQL Server's support for the DistributedManagement Object (DMO) interface. This interface has mapped the server- anddatabase-management functionality typically provided by the SQL ServerManagement Console to a set of COM objects. In short, DMO allows a programmerto manipulate the server in much the same way as a database administrator mightvia the Enterprise Manager. DMO is the cornerstone of FMS' Total SQL Analyzer.Through the extensive use of this interface, Total SQL Analyzer can dissect thecontents of SQL Server and report in marvelous detail about the internals ofany database.
In theproduct's jargon, a documentation session is called a job. Job-definitioninformation is stored in a JET data file. The results of an analysis job arestored in a second JET data file. The only limitation of which I am aware isthat Total SQL Analyzer is unable to document multi-server systems. However, Isuspect that organizations that deploy multi-server systems would not betypical FMS customers and probably account for a small percentage of SQL Serverusers overall.
The product'sIDE allows you to define, run, view, and print job results quickly. FIGURE 1illustrates an example job in which I had the program dissect all databaseslocated on a development machine.
FIGURE 1: An example job in Total SQLAnalyzer.
I foundseveral choices FMS made when creating Total SQL Analyzer to be thoughtful andbased on great attention to detail. For example, the product lists results in amenu tree that uses the same icons as SQL Server. That's just one usabilityadvantage. You won't waste time figuring out that a particular icon reallystands for the stored procedure section.
Dozens of Reports
Doing ananalysis 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 shipwith the program. Here is a small sample list of reports:
- DatabaseObject Inventory, Sorted by Database and Object
- DatabaseScripts, Sorted by Database
- ReplicationDistributor Properties, Sorted by Property Name
- ReplicationProperties, Sorted by Property Name
- ReplicationPublisher Properties, Sorted by Property Name
- StoredProcedure List with T-SQL, Sorted by Procedure
- TableDictionary, Sorted by Table Name
- TableDictionary with Column Properties, Sorted by Table Name
- UserDefined Function Properties, Sorted by Function
- ViewList with T-SQL, Sorted by View
One ofmy personal favorites is the Issues report. Here's a partial list of some ofthe flags it could raise:
- LockTimeout set to default (Server)
- MaxAsynchronous IO set to default (Server)
- "sa"user with no password (Server)
- Columnconsistency issues (Server)
- Databaseand transaction log share physical drive (Database)
- Databasehas multiple owners (Database)
Thecolumn-consistency check is especially useful. It pointed out that a database Ihave inherited contained several columns with data-type problems (varchars ofdifferent sizes that should have been the same).
In orderto 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 thereport-selection dialog box.
FIGURE 2: Selecting one of dozens of reportsavailable in Total SQL Analyzer.
In orderto provide maximum flexibility, it is also possible to filter certain reportsby concentrating the output on one database or, alternately, one object typeacross numerous databases.
And,finally, there is the printed output. I am certain I've missed other usefulpieces of information Total SQL Analyzer reports, so I suggest you visit the FMSWeb site for more detailed information. There isn't enough space in thismagazine to show the output in detail.
The Bottom Line
TotalSQL Analyzer is a unique tool with a great price. I'm not certain this producthas any competition. One of the products in Embarcadero's suite, which wouldcost much more, might come close. Building on 15 years of experience, FMScontinues to deliver outstanding bang-for-the-buck development tools. I highlyrecommend this product.
ThomasWagner is chief executive officer of etechpartner, Inc., located in LosAngeles, CA. Readers may reach him at mailto:thomas.wagner@etechpartner.com.
asp:factfile
TotalSQL Analyzer provides detailed analysis of any database. The product comes with70 report options, such as Database Object Inventory, Sorted by Database andObject. An enhanced version of the product, Total SQL Analyzer Pro, wasreleased recently and offers detailed analysis, improvement suggestions, anddependency 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-userlicense, US$499; Total SQL Analyzer Pro single-user license, US$999.
Rating:?????