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.