In-Depth

Tooling Around in Your Database

These products can help make your occasional forays into the world of databases much easier to take.

• By Peter Varhol
• August 1, 2008

Sometimes you don't need a full-fledged database-management environment. Maybe you just need to go in and add a field, change a table or write an ad hoc query.

Certainly you can do all those things in the SQL Server administration tools, writing your own SQL at the command line. Unless you're a SQL expert, though, that process can be lengthy and error-prone.

Having a graphical tool that's easy to learn and use -- and one that you don't have to use every day to remember how to perform simple actions -- is much more productive. Simply point it at the database, click the mouse to tell the tool what to do and your database maintenance is done.

For 90 percent of the time, you don't need a heavy-duty DBA product -- you just need to make a quick modification. With more and more system and network administration tools using SQL Server or other relational databases as their repository, having access to a simple database tool is almost a necessity. While database system tools are designed to run mostly without intervention, you still need access to the data for analysis or compliance purposes. A few of these admin tools offer a good way of doing that. Altova Database Spy, Quest Toad and Embarcadero DBArtisan are easy for non-DBAs to install and use, and they have surprising power when doing work on databases.

REDMOND RATING Database Spy Toad for SQL Server, Professional Edition DBArtisan Installation 20% 9.0 9.0 9.0 Features 20% 10.0 8.0 9.0 Ease of use 20% 8.0 8.0 7.0 Administration 20% 9.0 8.0 9.0 Documentation 20% 8.0 9.0 8.0 Overall Rating: 8.8 8.4 8.4 Key: 1: Virtually inoperable or nonexistent  5: Average, performs adequately   10: Exceptional

The test database was SQL Server 2003 Developer Edition on a Pentium 4 system running Windows Server 2003, connected across a network from a laptop PC running the tools on Windows XP Professional. The sample databases came from data generated from network-analysis tools and security tools already on the test network. The testing involved setting up database connections for three different instances of SQL Server for each of the database tools, and performing a series of actions to edit, extract and build queries.

Each tool has its strengths. Database Spy is the best overall in terms of range of features. Toad is excellent if you have DBA experience or need to do repetitive tasks for reports or compliance. DBArtisan is also well-suited for those with database administration experience, and is more geared toward getting the most out of existing databases across several different servers. You're likely to find what you need in any of these database tools.

Altova Database Spy
Database Spy lets you view, edit, structure and create databases and database components. You can download it from the Altova Web site and request an evaluation key to begin using the software right away. It unpacks and installs in just a few minutes, and starts with a wizard that walks you through setting up a database connection.

Once you have a connection to your target database, you can switch to the browser view and examine the tables and fields. If you choose a particular type of view, the tool automatically generates the SQL it uses to bring that data to the window. If you need to repeat the process, you can copy the SQL and paste it into a script or other vehicle so you can automatically execute the command. This lets you build in queries or analyses as parts of larger processes.

One unique feature of Database Spy comes from Altova's position as one of the first vendors to provide XML tools with its products. The company incorporated XML data access into Database Spy, so if you're using XQuery or other XML-based access, you can work in that environment. XML has the added advantage of letting you work with Web services and other Web-based apps.

You can also create simpler databases from scratch using Database Spy. Just choose a new database structure, define it and Database Spy will go ahead and create the tables. The new SQL Design Editor feature lets you visually create database tables in just a few minutes. You can import data to populate the tables and within a short time you have an entirely new database. This is useful if your users need to store ad hoc data in a format they can easily query.

Database Spy has all the features you'll need to set up, configure and edit your database. It's set up to provide quick connections to SQL Server, Access, Oracle, DB2, MySQL and Sybase. You can probably manually connect to just about any SQL database.

Database Spy has so many features that it may be overly complex to understand and use for a few simple and well-defined tasks. It took some trial and error to determine how some of the features worked, so the learning curve wasn't as smooth as it could have been. Also, if a selected database doesn't exist or you attempt to do something invalid, the error messages aren't particularly easy to understand.

[Click on image for larger view.]

Altova Database Spy lets users connect to a variety of different databases and perform a variety of view and edit operations.

At $139 for a permanent license, Database Spy is one of the last great deals available. While it's not perfect, it lets you do anything you may need to do with your databases. If this one doesn't meet all your needs, Altova has a number of other discrete tools for analyzing and modifying database structures.

Overall, Database Spy provides virtually all of the features anyone could need to supplement products that store, log or analyze data in relational databases.

Quest Toad
Quest Toad for SQL Server, one of the original and probably the most widely used database tools, gives application developers and casual database users DBA-type power. There are three versions of Toad: Professional, Xpert and Dev Suite. The low-end version, Toad Professional, is included here. The higher-end versions add features specific for application development and query optimization. You can download a 15-day trial from the Quest Web site, and it installs quickly and easily.

Toad for SQL Server has a large number of features geared toward working with databases on a regular basis. For example, it lets you recover data stored in transaction log files, roll back operations and reconstruct transactions. If there's any database corruption or a mistaken entry, this can help you undo the cause of the problem.

For DBAs, Toad simplifies administration tasks for creating, altering and managing database objects, SQL code, users and log-ins. If you have to add a single user, for example, you can do so more easily with Toad than with a traditional DBA tool. It also lets you build ad hoc database queries from a query builder. As a systems administrator, you may be able to add users with the admin tool. If you want access across multiple databases, though, it's better to do it from just a single location.

One of Toad's unique features is its ability to compare and synchronize data, schemas and servers to identify any changes. This helps you diagnose issues with your system- or network-administration tools. For example, if you find a misconfigured server or network device, you may be able to go back into a database and find out what was done, by whom and for what reason.

Toad also includes a report writer that lets you quickly build a report from the console. These can be ad hoc reports for a one-time use, or standard reports to run on a regular basis. You can also run schema reports, which help document database objects and the overall database structure. This helps you ensure that you're getting the most out of the data collected by your system-administration tools.

Toad has a significant learning curve, as it was originally designed specifically for DBAs rather than casual users. If you have some database experience, the learning curve will be shorter. It took a while to figure out how to use many of its features. The more you know about databases, the easier it will be for you to get started with Toad.

As its name implies, this version of Toad is designed to work only with SQL Server, though there are also versions for Oracle. At $595 per copy, it's significantly more than Database Spy, but its feature set may be more relevant if you work with databases on a regular basis, or work with databases with specific and well-defined tasks.

Embarcadero DBArtisan
Embarcadero's DBArtisan is a robust database tool targeted more at traditional DBAs. However, it's well-suited for managing multiple databases residing on different servers, optimizing performance and availability, and preparing and running queries across those databases.

It comes in three versions: DBArtisan Standard, Professional and Workbench. Standard incorporates features for managing existing databases and is the base product. Professional includes the Embarcadero SQL Debugger and SQL Profiler for optimizing and troubleshooting SQL code. Workbench includes the Embarcadero Analyst components for advanced capacity planning, storage and performance management. The Professional version is reviewed here.

[Click on image for larger view.]

Embarcadero Technologies' DBArtisan lets professionals working with multiple databases connect to and manage all of them from a single console.

DBArtisan installed easily on the Windows XP laptop administration system and connected to each of the sample databases in turn. The entire process from installation to production took about 40 minutes. The tool provides a variety of impressive database-management features, including SQL scripting, query building, object management, project management and version control in live databases.

DBArtisan resulted in a more robust and professional environment than the other products. I created several SQL queries and database scripts and put them under version control so I could keep track of the version used to extract specific types of data. I assembled a sophisticated method of matching data across tools. I then reported on that data to determine the overall health of my system as measured by the three different tools at my disposal. While such a configuration was trivial for a single server, for multiple servers it would be quite valuable.

The tool also lets you view and edit data within each of the database tables, as do the other tools. The graphical environment makes it easy to open a database table and examine its contents manually. It also lets you open tables from several databases at one time to visually compare results across different tools.

DBArtisan helps you concurrently manage multiple databases from a single graphical console. This gives the product an interesting advantage over other database tools, in that you can construct queries and create reports from data across different system tools. You'd be able to data mine across different data to get unique and fuller views of system and network performance.

DBArtisan supports a number of different database-management systems, including Oracle 10g, Sybase 15, SQL Server 2005 and IBM DB2. With pricing beginning at $995 per user, it's probably best used by a single administrator with database experience who can make quick ad hoc changes to databases across several different servers in response to the needs of the rest of the team.

An advantage to this approach is that you can use the single implementation of DBArtisan with tools that may require, or have originally been set up with, different database systems.

This product may be the most powerful one reviewed here, although it will likely be considered overkill for some organizations lacking multiple system-administration tools and accompanying databases. If you have a number of databases supporting many different administration tools, however, it could be right up your alley.