A two-pronged look at heterogeneous management tools
It's no secret that many organizations are looking to cut IT costs. To do so, they're either requiring increased output from their highly skilled DBAs or simply replacing them with less skilled DBAs.What's helping to fill the gaps between overwork and inexperience? Software. In this comparative review, I examine two comprehensive database-management products created specifically for such scenarios: Embarcadero Technologies' DBArtisan and Quest Software's Quest Central for SQL Server.
You'll find that the scope of these two products is quite vast, considering that both DBArtisan and Quest Central are well known in the industry for their heterogeneous platform-management capabilities. Both products provide an assortment of tools to help DBAs automate routine tasks, and recently both vendors have outfitted their products with a bevy of complex analysis and optimization tools that let DBAs easily secure, configure, and optimize their servers and applications.As I walk you through my evaluation of the two products, I'll do so from the standpoint of how well they provide value to two types of DBAs: For less skilled DBAs, who need help with analysis, optimization, and capacity planning, I'll consider how well the products help DBAs improve their overall skill set as well as cope with day-to-day pressures and requirements. For more experienced DBAs, who merely need an extra set of eyes and arms, I'll consider how well these tools can help tackle ever-increasing workloads.
I've used Embarcadero products in the past—for example, ER/Studio and Rapid SQL—but I had never used DBArtisan before my testing for this review. Fortunately, obtaining a 15-day trial version of the software from Embarcadero's Web site was simple. Installation went smoothly, and within 24 hours, Embarcadero's support staff contacted me to ensure that the download and installation had gone well and hadn't left me frustrated in any way—nice touch.
The basics. After completing the installation,I began experimenting with the standard administration console to view the various options available for managing my server, databases, and database objects. As Figure 1 shows, the interface's options and functionality are roughly comparable to those of SQL Server's native Enterprise Manager (or the newer SQL Server Management Studio). However, I was immediately impressed by the UI's speed and responsiveness.We all know what a tugboat Enterprise Manager can be; in comparison, DBArtisan is downright snappy.
As I began to edit objects, I discovered a terrific surprise.The product's administrative object editors provide a GUI, as well as the ability to rapidly toggle between the graphical representation and the object's Data Definition Language (DDL). In DBArtisan, everything associated with an object is accessible through tabs in the editor, and toggling back and forth between the tabs (including moving from the GUI to the DDL code) is instantaneous.With this functionality, skilled DBAs can make quick changes with the GUI but can still easily perform more complex operations manually when necessary. Likewise, this capability is a big win for inquisitive, less-skilled DBAs because it provides them with a great tool for contextualizing options, making changes, and seeing how those changes are translated into the corresponding DDL.
In the product's Utilities menu, I found the Schema Migration, Schema Extraction, Data Unload, and Data Load tools. Such tools are important to me because I've spent a lot of time as a DBA herding data and schema between servers and databases. Sadly, the Data Unload and Data Load tools didn't work as I anticipated. I tested them a few times, only to find that zero rows had been exported. I checked online, as well as the included documentation, to make sure that I wasn't confused about the nature of the Unload and Load functionality, then decided to retry the wizard, this time selecting the Use BCP option. Doing so fixed the problem, and I was able to export and import. I had hoped for a proprietary feature—similar to MySQL's dump functionality—but having a GUI to wrap Bulk Copy Program (BCP) functionality is still a decent solution.The Schema Extraction tool was easy to use, exporting selected schema quickly, but I ran into trouble when I tried to recreate an entire database by running the script in a new, blank database. Apparently, the generation process doesn't script objects in the correct order.That made the export functionality effectively useless as a tool for creating new databases through scripting—which might be a concern for some DBAs.
Diving deeper. Next, I turned my attention to DBArtisan's Analyst features.The first tool I tried was Space Analyst, which displayed a console for quickly evaluating a number of useful drive-utilization metrics. Metrics were exposed in easily approachable graphs, showing space consumed by database as well as available remaining disk space. Space Analyst also provided valuable metrics about object fragmentation and density. For junior DBAs, Space Analyst provides dependable functionality to avoid running out of space; more skilled DBAs can use the graphs and metrics to spot trends and evaluate performance bottlenecks and problems.
Backup Analyst is another DBArtisan component geared toward simplifying database administration.The tool provides functionality roughly on par with that of Enterprise Manager, letting you create maintenance plans by walking through a wizard. Backup Analyst also provides detailed reports about backup frequency and status. The big benefit, however, is the integrated functionality that allows for encrypted and compressed backups—probably this product's sweet spot, considering that dedicated solutions that provide encryption and compression can be quite costly.
DBArtisan also includes a Performance Analyst tool, which provides real-time metrics about a server's current activity and operations.This tool's well-designed UI lets you quickly navigate data about memory usage, I/O activity, space utilization, user activity, and core database metrics (e.g., errors, replication, backups, current configuration). In many ways, the information is similar to what you get from Performance Monitor and SQL Server Profiler, but DBArtisan arranges the data in compartments to ensure that the metrics are meaningful and easily digestible. For skilled DBAs, this functionality provides quick and easy insight into current operations, and it provides enough raw data to permit real trend analysis. For junior-level DBAs, Performance Analyst is a helpful tool for identifying bottlenecks and potential performance problems before they become nightmares.
I rounded out my DBArtisan evaluation by taking a quick look at Capacity Analyst. This tool requires you to set up traces, or profiles, for the collection of metrics it can later use for analysis and trending. I started up the Capacity Collection Wizard, which guided me smoothly through the process of specifying which data points I wanted to collect for later analysis. Capacity Analyst is a great tool for experienced DBAs: It doesn't provide much information that you couldn't get by carefully crafting performance logs in Performance Monitor, but it makes information collection easy, and it also lets you "slice and dice" various metrics so that you can watch for specific items. I doubt this tool will be quite as enticing for less-experienced DBAs, but because of the way it lets you collect information, I can imagine using it to watch for specific problems. For example, you can easily set up a complex trace that watches for NETWORKIO waits on a specific file group, database, or even table or index. And after you specify what to watch for, you can easily identify the metrics you want to include to help you analyze any collected data.
PROS: Cross-platform manageability; quick and highly responsive administration interface; excellent performance-tracking tools; great real-time metrics
Quest Central for SQL Server
As with DBArtisan, my first exposure to Quest Software's Quest Central for SQL Server came during the testing for this review. Obtaining an evaluation copy of the software was simple, and the Quest Central installer was well organized, making setup a snap.
The basics. After I completed the installation, I set about exploring the standard administration features. I found the options to be roughly similar to those of Enterprise Manager. Servers, databases, and constituent objects were all arranged in hierarchical fashion, with context-sensitive menu options providing all the necessary administrative functionality, as Figure 2, shows. As for the editors available for managing objects, I didn't find them to be as strong or compelling as DBArtisan's editors. In some cases (such as for editing users), the editor simply directs you to what appear to be windows pulled directly out of Enterprise Manager. Additionally, the editors don't provide immediate access to the SQL or DDL code for each object. Instead, as with Enterprise Manager, you need to load the scripted definitions in another tool. In the case of Quest Central, this tool is called Script Runner.As an overall code editor, Script Runner isn't terribly impressive: It offers an interface similar to that of Query Analyzer (providing a query tab, a results tab, and a statistics tab), but the editor could stand to be more robust. Still, if you use Quest Central as a single point of interaction for heterogeneous platforms, these concerns probably won't be as problematic.To be thorough, I also tested Quest Central's graphical Query Builder tool and found it to be quite intuitive and well designed—and it generates ANSI-92-compliant SQL code, by default. Unlike DBArtisan, Quest Central doesn't provide any data import/export functionality, nor does it offer any readily visible way to export or migrate schema. It does, however, provide a powerful utility for quickly and easily comparing schema between databases, but it's missing the ability to synchronize the differences through the creation of a script.
One intriguing Quest Central feature is a plug-in called Knowledge Expert. Similar to SQL Server Books Online (BOL), this collection of expert information is organized hierarchically and lets you browse, search, and add topics to a list of favorites. It also lets you add your own notes against each topic (at the bottom of a reader pane).The information itself is solid, very approachable, and beneficial. Topics include coding standards, syntax references, backup and recovery best practices, and so on. Knowledge Expert is an excellent resource for junior-level DBAs who are looking for help. I'm not sure this feature will be beneficial to experienced DBAs, who already have solid notions and opinions about backup needs and coding techniques. Also, skilled DBAs are probably more likely to gather pertinent information from online resources.
Diving deeper. Next,I turned my attention to Quest Central's various analysis agents. I started with the Database Analysis functionality, and the results impressed me. Quest Software markets this functionality as a way to "yield targeted, insightful advice that rivals that of expensive consultants," and the tool does a solid job of delivering on that promise. A simple wizard starts the Analysis Agent (running outside of SQL Server), which then returns with an extensive report on findings. For my lightly loaded SQL Server installation (effectively just a stock SQL Server installation on a virtual PC), the analysis was pretty quick, but it checked more than 40 rules and returned the results in an easy-to-navigate report. Rules covered everything from server settings (including whether the Address Windowing Extensions—AWE—setting was correctly configured to take advantage of available memory) to specific settings and configuration details for individual databases and objects. This is a very thorough analysis tool that any DBA will find beneficial: It even points out foreign keys (FKs) with cascaded constraints that don't have properly accompanying indexes. For less skilled DBAs, this tool is an excellent resource to get servers quickly compliant with best practices that will help ensure security, stability, and scalability. More important,to help provide context and understanding,targeted documentation accompanies each recommendation—a big advantage. The tool even creates an action-plan view that summarizes all the findings and lists them in order of priority, accompanied by a comparative complexity/effort rating. More experienced DBAs will use this tool to ensure that tuned servers and databases stay that way: A quick scan would provide easy insight into a server's current standing.
Another powerful tool bundled with Quest Central is the SQL Tuning component, designed to evaluate SQL statements for efficiency.The SQL Tuning component executes show plans on queries that you want to evaluate, then uses its own algorithms to generate magically equivalent ( mathematically verified optional ways to perform the query) versions of the SQL statement, which it then evaluates to see if it can find a less expensive version of the existing query. The tool automatically prunes queries with more expensive execution plans, then lets you run all remaining alternatives as a batch to gather actual execution costs.This ingenious feature provides excellent value to less experienced DBAs, who will ideally learn how to write more efficient code. However, users need to be cautious: Just because their current syntax has no faster-performing variants doesn't mean that it's "optimized" and can't be improved. Quest Central seems to be aware of that concern, and the documentation warns users that the tool isn't a substitution for index tuning. It's just another tool to help DBAs improve code performance.
The final piece of Quest Central functionality that I evaluated was Spotlight for SQL Server, a performance-diagnostics tool. Similar to DBArtisan's Performance Analyst, this tool provides real-time information about current system activity and load. A toolbar at the top of the UI lets you quickly and easily navigate to metrics families such as Memory, CPU, Activity, Support Services (e.g., SQL Agent, SQL Mail, Replication), and the home page, which displays overall status.The tool is intuitive and easy to navigate, and it provides data in a clear and manageable fashion. For experienced DBAs, Spotlight for SQL Server provides the kind of real-time information that's helpful for troubleshooting problems. For example, suppose you're seeing lots of WRITEs to your server; Spotlight for SQL Server lets you easily view WRITE information per file for all files in a database and for all databases on the server. For less skilled DBAs tasked with managing complex systems, Spotlight for SQL Server also offers context-sensitive information about presented data types through an easy-to-access context menu.
PROS: Cross-platform manageability; speedy UI, excellent real-time monitoring functionality; solid analysis components to help with query, performance, and server tuning
Winner by a Hair
Both products offer a number of solid features, and both come with few weaknesses, mostly surrounding standard administration functionality, as well as schema-change management and data import/export functionality. Those weaknesses are a fairly big deal in my world, but they aren't showstoppers. Personally,because of those weaknesses,I don't imagine either tool becoming a one-stop replacement for the tools I already use. However, both DBArtisan and Quest Central offer powerful functionality that I wouldn't hesitate to acquire for supplemental needs in the right environments.
I've been both a reluctant DBA and a seasoned veteran, and I can say that I'd be comfortable using either of these tools or recommending them to colleagues. Both provide value in terms of making management easier, and both offer valuable services that help ensure better uptime, scalability, and reliability—but DBArtisan takes a slight edge thanks to its strong object editors.