Product Review - Document SQL Server Databases

Visual Studio Magazine, Dianne Siebold 
April 5, 2002

One of SQL Server's most notable shortcomings is its complete lack of tools for documenting servers and databases. FMS fills this niche with Total SQL Analyzer (TSA), a tool that allows you to generate information about your servers and databases and run reports from that information.

TSA is useful for DBAs who need to document their servers and refine performance. It's also a useful tool for developers who work with SQL Server databases. TSA comes with a brief printed user's guide, which is handy to flip through to find out the product basics. The installation is simple and straightforward, and the interface is easy to navigate. It works with SQL Server 7.0 and 2000.

The first thing you need to do in TSA is create a job that defines the server, connection info, and what objects on your server you want to document. These objects can be any database objects, such as tables and stored procedures, as well as server objects such as backup devices, linked servers, logins, and Data Transformation Services (DTS). TSA comes with a job wizard that steps you through the process of creating a job. Once you define a job, you can run it immediately or schedule it to run at another time. Unfortunately, you can't schedule a job to run on a repeat basis—for example, every night at 1 a.m. Another drawback is that jobs are on a per-server basis, so you can't run a report that compares two databases on separate servers.

TSA keeps track of all the results for a job each time you run it, and it provides options for managing job results, such as the ability to delete them, rebuild the storage file, or compact the storage file. TSA stores job results in an SDB file, which is actually an Access database. You can't save the job results in a SQL Server database, but you could probably export the results from the Access database.

The heart of this product is its reports, which you can use to provide data from the job results. TSA comes with predefined reports that are created in Crystal Reports, so you can edit them or create your own. You can export reports only to HTML or Word right now, so support for other formats in the future would be a good feature.

Another useful feature is that TSA lists possible performance issues based on which objects it's documenting. However, it flags things such as columns that allow NULLs as possible performance problems, so it certainly isn't a substitute for the knowledge of a good DBA.

I can envision how great this tool would be to provide clients with detailed documentation of their servers and databases. Overall, TSA fills a glaring hole in the existing SQL Sever toolset and is easy to use. FMS could make a few minor improvements, but at $499, this tool is a bargain for the value it provides.

About the Author

Dianne Siebold is a programmer specializing in VB and SQL Server. She is a regular contributor to Visual Studio Magazine and the author of the Visual Basic Developer's Guide to SQL Server (Sybex). Reach her at dsiebold@earthlink.net.

Back to Main Reviews Page