I was recently introduced to SQL Sentry Power Suite 6.2, a suite of monitoring programs that provide real-time and historical performance data for SQL Server. As a business intelligence (BI) developer, I didn't have much interest in this software at first because SQL Server monitoring didn't seem to apply. However, after seeing a demonstration of the software's features, I was surprised to see just how useful it could be for BI developers.
After the demonstration, I installed the software on my development machine, which is a 64-bit Windows 7 Intel Core i5 machine with 8GB of RAM. Power Suite requires SQL Server 2005 or later for the database and Windows XP or later for the SQL Sentry service. It also requires the Microsoft Report Viewer Redistributable Package and Microsoft .NET Framework 4.0, which the suite installs if they aren't already on your system.
After the installation, a setup wizard guided me through the initial product configurations, such as what to monitor, the SQL Server instance name, what type of authorization (SQL Server or Windows) to use, and the username and password for the SQL Sentry service account. The service account should be a domain account that is a member of the Administrator group on each monitored server and has SQL Server sysadmin privileges.
Power Suite consists of SQL Sentry Event Manager and SQL Sentry Performance Advisor, which can also be purchased individually. Event Manager makes it easy to monitor multiple types of events across your enterprise. Performance Advisor provides a dashboard and several sub-dashboards loaded with performance statistics related to one or more SQL Server instances and the server that hosts them. Event Manager works with the SQL Server Database Engine and SQL Server Analysis Services (SSAS), but Performance Advisor works only with the SQL Server Database Engine. A version that works with SSAS (Performance Advisor for Analysis Services) is sold separately.
Event Manager provides time-based information about jobs, alerts, SQL Server Integration Services (SSIS) packages, SQL Server Reporting Services (SSRS) reports, deadlocks, and more. Events can be drilled into for additional details. For example, you can see information about each step in a job. Event Manager displays events using a Microsoft Outlook-style Calendar View, which can be filtered to display only certain types of events or events that exceed a preset threshold. You can save a filtered view as a Custom Event View if you often apply those filters.
In the Calendar View, indicators show whether events succeeded or failed and their duration. This makes it easy to see whether events overlap temporally and, if so, whether they're competing with each other for resources. You can reschedule future events by simply dragging and dropping them, making it easy to level the load on any device. Notes can be added to events so that everyone with access to the Event Manager UI can be kept informed about the actions being taken.
Another noteworthy feature is event chaining. This feature lets you run jobs or tasks from any monitored server based on the outcomes of other events, thereby creating a chain of events. With this feature, simple problems that come up regularly can be set to resolve themselves, based on predefined conditions and workflows.
Event Manager integrates well with Performance Advisor. By right-clicking an event in Event Manager, you can jump to the Performance Advisor dashboard, which will display performance statistics as they existed during the duration of the selected event.
Figure 1 shows the Performance Advisor UI. It displays time-based performance statistics related to the server and the SQL Server instances it hosts in eight views offered as tabs across the top of the UI. The Dashboard tab displays data about the physical servers' memory, disk usage, network, and CPU usage on the left with related SQL Server statistics on the right. On the other tabs, you can drill into the details.
Figure 1: The Performance Advisor UI in SQL Sentry Power Suite 6.2
In Performance Advisor, you can also perform such actions as setting up event notifications, logging to a disk, and executing a job or task. These actions are accessed in the General Actions, Failsafe Actions, and Audit Actions tabs on the right side of the UI.
Power Suite is feature rich and provides easy navigation between its integrated applications using a predictable console. I was impressed by how comprehensive it was and surprised at how useful it could be as a tool for ensuring good performance from the BI solutions that I design. I'm able to see an SSIS package's effect on both the server and SQL Server instance. I also can see which queries are resource-intensive in the database and in SSAS (although the latter requires Performance Advisor for Analysis Services). The suite is available as a free trial, so if you're in the market for a monitoring tool, I think this one deserves a look.
|
SQL Sentry Power Suite 6.2 |
