Essential SQL Server Tools

Microsoft SQL Server environments are becoming larger and more complex. In many companies, SQL Server is a mission-critical component. To manage a complex SQL Server environment, you need to understand the essential processes, procedures, and tools that are specific to SQL Server systems.

As SQL Server has progressed from version 6.5 to 7.0 to 2000, the tools that ship with the product have become more feature-rich and more scalable. However, although these built-in tools can help you manage your environment, they're lacking key features that a DBA needs to efficiently manage multiple systems and environments. To supplement the functionality of these Microsoft tools and become a more powerful SQL Server administrator, you can look to third-party vendors such as Lumigent Technologies, Embarcadero Technologies, and Red Gate Software.

Access the Transaction Log
As a DBA, you're frequently called upon to restore databases to recover altered or deleted data. In many cases, such data loss is accidental, but sometimes it's because of deliberate data manipulation resulting from security penetrations. To restore data in the latter case, you probably need to resort to a full database restoration, which can cause significant downtime in your production environment. Rather than restore the entire database, you'd much rather be able to access the SQL Server transaction log and apply your knowledge of your database's structure to locate and reverse the transactions that damaged your production data. SQL Server doesn't offer such transaction-log functionality, but Lumigent's Log Explorer does.

Log Explorer puts the SQL Server transaction log and associated transaction log backups into your hands. You can use the software to locate offending transactions and generate scripts to reverse changes. You can therefore restore damaged data without incurring an outage.

The ability to read the transaction log is also useful for troubleshooting applications. On many occasions, I've run across an environment in which data seems to randomly disappear during certain operations. The ability to view the actual transactions issued against the database—as opposed to the transactions a developer told me were being executed—is extremely helpful when I need to isolate poorly written transactions.

I use a significant number of third-party tools to efficiently manage my production environments, but Log Explorer is the only product that I consider absolutely necessary to my SQL Server environment. The ability to read the transaction log and restore data, even after the transaction has been committed, is invaluable.

Send Notifications About Crucial Actions
Many products are available that monitor your SQL Server environment and notify you of certain events, such as excess CPU usage, deadlocking, and unusual disk-space usage. Such tools monitor what's happening to a SQL Server system, but they don't provide a capability to monitor what's happening inside a SQL Server system. For example, these tools typically don't monitor database data or schema changes.

You can use a variety of methods—such as Performance Monitor, SQL Server Profiler, and scheduling queries—to obtain CPU usage, disk usage, and query performance. However, only Profiler and custom-written queries let you track changes that occur within a SQL Server system, and unless you write specific code to do so, these tools don't send notifications when crucial events occur.

To fill this void, Lumigent—extending Log Explorer's capabilities—offers Entegra. Entegra gives you control over your environment by letting you proactively manage your SQL Server systems. As Figure 1 shows, Entegra lets you configure notifications for a variety of server-level actions that can potentially affect your environment's security.

Entegra mines the transaction log at configured intervals to locate events that match your predefined notification criteria. When Entegra locates an alert condition, the software generates, logs, and sends notification to the person and device of your choosing. An administrator can then view the change that occurred and take corrective action, if necessary. Because Entegra monitors the transaction log and is always moving forward, you won't receive duplicate notifications. Also, Entegra won't be fooled by SQL Server­savvy intruders who aim to directly rewrite the SQL Server transaction log.

In addition to letting you configure notifications about crucial server-level actions, Entegra lets you track and receive notifications about changes to sensitive data. You can configure a notification based on changes to a table or even a column within a table. For example, you would want to know when someone is modifying a payroll table's salary column, to ensure that only authorized users are making changes.

Manage Source Code
Log Explorer and Entegra benefit administrators, but what about developers? A plethora of tools are available for administrators, but you'll find a cor-responding lack of tools for developers. However, this imbalance isn't much of a problem. SQL Query Analyzer provides nearly all the capabilities that a database developer needs to do his or her job. One glaring exception is in the area of source-code control.

The capability to manage multiple versions of source code, revert to previous versions, or compare multiple databases for differences is crucial to the management of applications that have a database back end. Unfortunately, tools that offer such functionality require formalized processes and strict adherence to those processes by developers.

Developers excel at using source-code control when they build applications because source-code control is embedded into every modern development environment. All professional developers use source-code control for their projects. However, in the case of database code, developers write directly against a SQL Server system and almost never manage the source code.

Because developers connect directly to a database server to develop code or modify the database structure, the ability to identify, extract, and archive changes is crucial to understanding the database portion of an application. Embarcadero's DBArtisan Change Manager and Red Gate's SQL Compare each offer a different level of source-code control functionality.

DBArtisan Change Manager lets you configure and schedule a job to extract and archive your database schema. You can then use these archives to view different versions of the database and restore code to a previous state. The solution isn't perfect: You generate archives either manually or through a scheduled job, so the archives won't pick up multiple changes to one object between jobs. In addition to viewing previous versions, you can compare one archive with the next to determine differences. You can also compare an archive with a live database or two live databases to each other. DBArtisan Change Manager can then generate a script that synchronizes objects.

SQL Compare, a competitor of DBArtisan Change Manager, also lets you compare two live databases. When you finish the comparison, the software can generate a script to synchronize the objects. Both products are useful for determining which code has changed on a development server so that you can move the code into production with the next production build.

Synchronize Data
Because SQL Server environments are becoming larger and more distributed, administrators find themselves managing multiple servers that contain the same data or subsets of data, along with ever-increasing numbers of servers. The key to efficiently managing large numbers of servers is ensuring that they're standardized, and the key to managing distributed data is ensuring that that data remains synchronized with the master copy.

Red Gate offers two products that fill these gaps. DTS Compare lets you synchronize settings, jobs, logins, and Data Transformation Server (DTS) packages between servers. SQL Data Compare provides a compact interface in which you can synchronize table data between two databases. Although some scalability concerns are apparent when you're dealing with tables that have millions of rows, SQL Data Compare is essential if you're trying to troubleshoot and resynchronize replicated databases. Instead of wasting time and effort writing openrowset queries, you can use the point-and-click SQL Data Compare tool to select specific tables and columns from two databases to compare a table's data. Like SQL Compare, SQL Data Compare also generates a script to resynchronize the data.

Beyond the Box
Out of the box, SQL Server provides a staggering amount of functionality in an easy-to-use fashion. Although the tools that ship with SQL Server take care of 80 percent of the functionality you need, the tool set has some gaps. Lumigent, Embarcadero, and Red Gate offer products that provide functionality to fill those gaps. Each has seen successful testing in large and small production environments.

Some Microsoft partners also provide add-on products to SQL Server for improving database performance, querying and reporting, data mining, data analysis, and other tasks. See "Contact the Vendors," page 34, for a list of these vendors and their products.