SQL Server Solutions
GETTING STARTED WITH
Purpose of this document
Due to its depth and potential for customization, there are often features of SQL Diagnostic Manager that are overlooked during the initial trial period. This document is designed to highlight areas that may be missed or that can be modified to give you fuller control over management and reporting in your SQL Server environment.
Introduction
SQL Diagnostic Manager is a powerful performance monitoring and diagnostics solution that proactively alerts administrators to health, performance and availability problems within the SQL Server environment.
Why use SQL Diagnostic Manager?
• Immediate feedback
Once you add an instance, best practice performance data will be fed back in seconds • Reach out to other monitoring tools
Connect directly to SCOM, or use email, SMTP, or SNMP for additional services • Mobile management
Use a web browser to stay connected, manage & collaborate with other staff members • Use Diagnostic Manager as your eyes and ears
Diagnostic Manager watches and alerts on your environment so you can be more efficient • Granular retrospective analysis
Drill in to a point-in-time snapshot and see what was causing system issues • Analyze patterns of poor performance
Utilize reports and past activity to iron out repetitive issues • Wait Stat analysis
Forensic-level investigation of query and server waits that impact overall performance • Session detail analytics
Find out who’s blocking, locking, and causing waits as well as where they’re coming from • Identify the impact of slow running code
Identify, group, filter, and dissect slow-running queries • Immediate feedback and automated responses
Alert when there are issues, as well as automate responses to common problems • Historical trending and management information
Use pre-built reports in console or by web via SSRS, and even add your own custom reports • Flexibility – Adapt Diagnostic Manager for your specific needs
Customize dashboards, reports, and even choose additional areas to monitor • Management segmentation
Easily group and manage your large environment • Manage large estates
System Requirements
Management Console• Windows XP SP2+, Windows Server 2003 SP2, Windows Server 2008 SP1+, Windows Vista SP1+, Windows 7, Windows 2008 R2, Windows 8 & 8.1, Windows 2012
• .Net 4.0+ and MDAC 2.8
• Monitor resolution of 1280x800 with small text resolution. Web Console
• Windows XP SP2+, Windows Server 2003 SP2, Windows Server 2008 SP1+, Windows Vista SP1+, Windows 7, Windows 2008 R2, Windows 8 & 8.1, Windows 2012
• .Net 4.0
• Browsers: Internet Explorer IE 9.x+, Chrome, Firefox. Services and Data Repository
• Windows 2000 SP4, Windows XP SP2+, Windows Server 2003 SP2, Windows Server 2008 SP1+, Windows Vista SP1+, Windows 7, Windows 2008 R2, Windows 8 & 8.1, Windows 2012
• .Net 2.0 SP2+ and MDAC 2.8
• Repository: SQL Server 2005 SP1+, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 Supported Monitored SQL servers
• SQL Server 2000 SP4, SQL Server 2005 SP1+, SQL Server 2008 SP1, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Express (2005+)
• SQL diagnostic manager does not install any components, DLLs, scripts, stored procedures or tables on the SQL Server instances being monitored.
Mobile Requirements
• Services and Web Server OS: Windows Server 2003 SP2, Windows XP SP2+, Windows Server 2008 RTM+, Windows Vista SP1+, Windows 7, Windows Server 2008 R2, Windows 8 & 8.1.
• .Net Framework: .Net 4.0
• Repository: SQL Server 2005 SP1+, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 • Web Server: IIS 7.2+
Mobile and Desktop Devices
• Mobile device: Android v2.1+, iOS v3.2+, RIM v6.0+, Windows Phone 7+ • Desktop Browsers: IE 9.x+, Chrome, Firefox
Permissions
The SQL DM Mobile & Newsfeed Service account requires the following permissions. By default, setup program assigns the Local System account to the SQL DM Mobile & Newsfeed Service. During install, you can enter credentials for a Windows user account or SQL Server login.
PERMISSION WHY IT’S REQUIRED
Log on as Service Allows the SQL DM Mobile & Newsfeed Service account to run as a service. Read and write privileges on the Allows the SQL DM Mobile & Newsfeed Service to receive and maintain SQL DM Mobile Repository database performance and configuration data in the SQL DM Mobile Repository. SQL Server Privileges Either a Windows user account or SQL Server login that includes System
Port Requirements
By default, the SQL DM Mobile components use the following ports.
PORT # WHERE WHAT USES IT WHY
25 Mail Server SQL DM Mobile & Newsfeed Service
Allows the SQL DM Mobile & Newsfeed Service to send notification emails.
80 Web Server hosting the SQL DM Mobile Web site
SQL DM Mobile Web application
Allows access to the SQL DM Mobile screens via a mobile device inside or outside your corporate firewall. By default, IIS uses port 80 for the local virtual directory, so check your Web Server settings to pick an appropriate port for SQL DM Mobile. You can specify a different port during install, or later when you need to change this configuration.
135 Monitored instance Collection Service Gather WMI data
5166 &
5167 Computer running the SQL DM services
SQL DM services Allow communications between SQL DM and the Idera Newsfeed Platform for signup and login authentication.
5168 Computer running the SQL DM Mobile & Newsfeed Service
SQL DM Mobile & Newsfeed Service
Allows communications between the Idera Newsfeed and SQL DM for server status updates.
Architecture
SQL DM consists of a light, unobtrusive architecture that easily runs in your SQL Server environment with minimal configuration. All SQL DM components run outside and separate from SQL Server processes and nothing is installed on the instances we monitor.
1. SQL DM Console
The SQL DM Console retrieves historical information directly from the SQL DM Repository. All real-time requests use the SQL DM services to poll the monitored SQL Server.
2. SQL DM Services
SQL DM has three centralized services: the Management Service, the Collection Service, and the Predictive Service. These three services reside on the same computer.
3. SQL DM Repository
SQL Diagnostic Manager’s Capabilities
When a monitored instance is added to the SQL Diagnostic Manager, key performance metrics will be polled in order to start identifying which areas of your estate need attention the soonest. Within seconds you can be made aware of areas of your environment causing you pain: areas you may not have even known where connected to issues you have experienced.
Register a SQL Server instance:
To add an instance, you can click File > Manage Servers and follow the instructions in the wizard as shown below. Information will begin to be fed back in almost real time and alert to Microsoft best practices and standards. The speed at which data is returned can be set in the Tools > Console options.
Connect to Other Monitoring Tools
Diagnostic Manager has the scope to interact with multiple other management tools and to provide our in-depth granular diagnostic information to generic management tools currently on the market.
Mobile Management
Values related to the metric we have selected. There are multiple ways we can stipulate the conditions, depending on the metrics we select.
List of metrics we can alert on. Checking or unchecking them will decide if they are active
Additional options we can apply to alerts, including filtering & alert suppression.
Use Diagnostic Manager as Your Eyes & Ears
SQL DM allows you to configure alerts to inform and warn you about impending issues within your SQL Server instances. You can view these alerts using the SQL DM Console, the Idera Newsfeed, or SQL DM Mobile.When an alert threshold is reached, SQL DM can: • Send an email notification
• Pop up an alert message from your Windows taskbar • Write an event to the Windows Event log
• Generate an event on the Timeline
• Send the alert message to the Idera Newsfeed Action Provider,
Granular Retrospective Analysis
Diagnostic Manager will periodically take a snapshot of the collected information and store it in your repository database. You can then later refer to this information for the benefit of retrospective diagnostics. A classic example of this hearing from a user that the system was slow for a while yesterday but resolved itself so they didn’t want to bother you. Using the facilities shown below will assist in resolving those phantom levels of rogue utilization.
We can get to the properties of an instance by right clicking on it.
Then, select the snapshot frequency (among others) in the General options
A slider gives a graphical representation of when we alert on a metric, as well as a baseline (hatched bar below slider) to indicate the average performance range of a metric.
Note: Custom metrics can
Analyze Alert Patterns of Poor Performance
Outside of historical snapshots, we can choose to locate alerts that were generated over a period of time with the added benefit of being able to group or filter the results. This is really useful in quickly identifying and triaging alerts and issues before they have the chance to escalate, as well as, of course, pinpointing groups of alerts that happen over a period of time.Selecting a date from the calendar will show all snapshots for that day listed below. A snapshot can in turn be selected to freeze the console as of that point in time.
Choose to show all current active alerts, grouped or filtered. Or alternatively choose a timeframe for issue analysis.
Show the history browser by clicking here
Results can be grouped or filtered.
Note: Once a snapshot is
Wait Stat Analysis
The Query Waits view displays the queries on your SQL Server instance with the longest wait times. By default, the chart at the bottom of the view displays the query waits over time and allows you to further investigate based on Statements, Applications, Databases, Clients, Sessions, and Users.
By analyzing these waits, you can better determine where your biggest bottlenecks are occurring and what changes could potentially have the biggest performance boost on your SQL Server instance.
To utilize query waits, you must first enable the feature in the configuration
Different tabs can be selected to identify types, origin of and also instigators of query based waits.
We’re looking at query waits over time in this example, which displays a timeline on the x axis and the duration in milliseconds on the y axis. As an example we have selected the user tab to show all users that created waits in this timeframe, giving an insight into which accounts, including application service accounts, that could be adding otherwise unnoticed weight to the system.
Note: Right-clicking on a
historical event allows you to jump directly to the correct snapshot and show the details in the console
Note: Using the history
Session Detail Analytics
The Sessions Details view provides an in-depth analysis of sessions running on your monitored SQL Server instance. You can view a wide range of information from performance details to open transactions and configured options. You can also track process activity at the statement level. Individual sessions appear in the top portion of the window. Right-click any session in the list to view locks, show your query history, trace the session, kill the session, print the associated statistics, or export statistics to Excel.
Waits can also be shown by duration, and where we see hyperlinks, such as the session id below, we can click to filter on those parameters.
Details can be grouped by column heading to help identify activity. Enable here and drag a column heading to the grouping panel below
Note: Use session detail to
Identify the Impact of Slow-Running Code
Query monitor and its associated thresholds can be enabled here Filters can be applied
to refine the results to a specific area such as a slow application
Detail can be drilled to show the content of any slow running code
Note: Poorly performing
code can be assessed by SQL Doctor (a separate Idera tool,) which can give best practice analysis and optimization advice to reduce the transaction times as well as offer index and search optimization settings.
We can use SQL Diagnostic Manager to assist in identifying slow-running code: one of the main reasons for performance bottlenecks in SQL Server. The Query Monitor is a standard SQL Server trace that collects all the events that occur on your SQL Server instance over a period of time. You can enable this option if you experience query timeouts or other performance issues. The Query Monitor window allows you to enable or disable the Query Monitor. If you enable the Query Monitor, you can select the settings that are used.
Immediate Feedback & Automated Responses
SQL Diagnostic Manager allows you to not only be informed very quickly to issues of health and performance,but also implement steps to automatically minimize and resolve issues and problems it finds.
Within the Tools > Alert actions and responses option, we will find a list of current rules used to assist in the management of our estate. New rules can be added and modified at any time and allow for unparalleled extension of your eyes and ears.
Multiple alerts can be generated for different levels of severity, metric, and time frame. Even custom counters (which we’ll discuss later) can be alerted and reported on, meaning the breadth of alerting and management capabilities is limitless.
Select the conditions, including filters to apply alerts to only certain metrics, instances, tag groups and time frames
Apply actions to take place, such as run a script, job, send an email and log to various locations via multiple protocols
URL-Based Reports (via SSRS)
In addition to the included reports, SQL DM also allows you to generate reports using Microsoft Reporting Services. Microsoft Reporting Services allows you to build powerful custom reports to deploy for a comprehensive auditing solution.
Historical Trending & Management Information
In-console reports
Show what you want in the user interface
The Dashboard view is customizable per monitored SQL Server instance by allowing you to select the panels that you deem to be the most important to display in each instance. You can customize your Dashboard view for the selected SQL Server instance or as the default for all SQL Server instances added to SQL DM.Adapt DM for your specific needs
Custom counters
Management Segmentation
After an instance has been tagged, it belongs to a group, as shown below. Selecting that group displays all members in a dashboard-style view that can be used to monitor and segment departments, locations, versions and for each thumbnail in the list, the display can be changed to show an overall view or details specific to any of the key performance indicators.
Build your own reports
The Custom Reports wizard allows you to create or edit custom reports. Custom Reports can include any metric collected by SQL Diagnostic Manager, including custom counters. This wizard allows you to choose the counters you want to include in your report, order the way the metrics appear, and specify the aggregation method used on each of your metrics.
Managing Large Estates
Idera SQL Diagnostic Manager can comfortably manage about three hundred instances, each of which can in turn have its own management requirements. The first step we’re going to look at is the ‘Tagging’ facility. Instances can have one or more text-based labels applied to it to ensure ease of management for reporting and user management, as well as for creating an additional dashboard for each department, geographical location, and hosted customer to name just a few.
To access the instance properties, right-click the name in the navigation pane on the left and select properties
