Lab Reports: Db Maint 2.01

Automate database management

Server 7.0 makes the tasks that database administrators face easier than previous SQL Server releases. However, managing database servers effectively can be a significant hurdle for many organizations, particularly small to medium-sized companies where a DBA might wear many hats.

An inexpensive but effective tool to consider is Db Maint from LAN Assistans & Konsulting AB. Db Maint is an easy-to-use, centralized application that automates SQL Server management, monitors management task results and performs data verification, optimization, backups, and other database administration tasks.

Db Maint is a good, reasonably inexpensive tool for many tasks. However, for adept DBAs who want flexibility, Db Maint doesn't offer much more capability than what you get with SQL Server 7.0. SQL Server's database maintenance tools and interfaces are generally more complex than those in Db Maint, but with that added complexity comes flexibility that's not available in Db Maint. Much of Db Maint's functionality is available in SQL Server 7.0, but that common functionality is far easier to use in Db Maint than in SQL Server's Enterprise Manager and SQL Server. Db Maint provides a layer on top of the functionality that Enterprise Manager provides.

Getting Started

I installed Db Maint on Windows NT 4.0, Service Pack 5 (SP5), running SQL Server 7.0. The installation was trivial and didn't require a reboot.

To create the jobs, I first assigned the databases on my server into groups for convenience. This way, when I ran a set of tasks on all or a group of SQL Servers, I didn't have to set up and administer the tasks separately for each server. Next I created the backup devices to back up SQL Server data to either disk or tape. I then created the jobs.

A job in Db Maint is a sequence of one or more steps for preprocessing, data verification, backup, optimization, and postprocessing. A job in Db Maint consists of five steps that you can omit or modify (to some extent) as needed. The steps are preprocessing (a stored procedure, which must either be global or exist in each database on which the job is to run), data verification, backup, optimization, and post-processing (again, a stored procedure). The verification, backup, and optimization steps have various options you can choose, such as what level of verification to perform, whether to do data repair when verifying, and which device to back up to. The user can specify that the stored procedure run during the job at several possible steps. The stored procedures connect and run based on the step's success or failure. One stored procedure runs if the step succeeds, and another runs if it fails. A job repeats (in sequence) for each database you assign to it.

One aspect I dislike about Db Maint is that Db Maint jobs correspond to steps in SQL Server Agent, and Db Maint tasks—a sequence of jobs—are called jobs in SQL Server Agent. If you work in both user interfaces, the difference could be confusing. Experienced DBAs will quickly note that the steps in a job correspond almost exactly to the actions that the SQL Server sqlmaint utility can perform.

Configuring in Db Maint

Db Maint comes with a quick-setup option for commonly used database maintenance task configurations, but I set up my own management jobs. The quick-setup configurations come from a Windows configuration text file. You can use this feature to quickly set up new servers with common configurations.

The Db Maint client uses a split-screen interface with a tree control on the left that shows jobs and tasks; a details window on the right showing a job history summary; and a Log Details window listing completed job results, export options and database structure, as Screen 1 shows. One of Db Maint's strongest features is that it centralizes and provides easy access to the results of completed jobs, which are visible in the Log Details window.

Jobs, Tasks, and Logs

After I set up the jobs, I created tasks. Each task is a logical entity, which consists of a schedule, a sequence of jobs, and additional backup and execution parameters. For example, I created a task to run every night at 2:00 a.m. to verify data and run backup steps. The task contained parameters to specify which device to direct the backup to. The Db Maint interface hides some of the flexibility that SQL Server Agent jobs have—for example, in Db Maint each task might have only one schedule, whereas in SQL Server Agent, a task can have many schedules. I found it disturbing that changes I made in Db Maint showed up in Enterprise Manager, but changes I made in Enterprise Manager didn't show up in Db Maint, even after I reconnected Db Maint to the server.

As I established the jobs and tasks, I appreciated Db Maint's centralized status and results log of the jobs. Although SQL Server Agent appends job output to a job's history, I found that Db Maint's Log Details window was more convenient because it collected all the result information in one place and it was easy to navigate. Db Maint also has a nice export feature. You can easily export many file types, such as Db Maint dialog and configuration screens, to a text file. Also, because Db Maint stores everything in Microsoft database (msdb), I went directly to the tables to examine the results.

Performance Monitoring

A feature that you can't easily achieve without Db Maint is the ability to capture SQL Server performance counters and store them in additional tables in a database. I found it much simpler to use Db Maint's SQL-based performance data for analysis, tuning, and capacity planning in Windows NT Performance Monitor log files than setting up the Performance Monitor on my own to capture the data and exporting it out of the Performance Monitor for analysis.

Simplifying DBA Tasks

The Db Maint architecture is robust; it uses native SQL Server scheduling tools, but it executes work through Interactive SQL (ISQL) to capture all possible output and isolate potential failures. Because Db Maint uses SQL Server facilities, all the notification paths—the Db Maint log, NT event log, HTML, email, operator notification, and SNMP—are available.

An experienced DBA could do most of what Db Maint does without buying the product, but Db Maint lets you manage database servers in an effective, centralized way. Why spend time using other applications when a good, reasonably inexpensive tool can do these DBA tasks for you, freeing you up to take on more heroic and rewarding tasks?