Are you using Microsoft SQL Server in your application? You can use CopperEgg to monitor your Microsoft SQL Server performance. As an illustration, you can configure and receive alerts for the following scenarios:

  • Performance: alert if the Microsoft SQL Server performance is poor.
  • If the cpu_usage value is significantly above its normal range, that would imply that the Microsoft SQL Server is under heavy load.

Prerequisites

CopperEgg offers a SAAS based out-of-the-box solution for monitoring Microsoft SQL Server for performance and availability. If you are not familiar with the concept of custom metrics as used in CopperEgg, please go through Custom Metrics information once before continuing with this document.

  1. CopperEgg account.

If you don’t already have an account, you can sign up for a 14 day FREE TRIAL of CopperEgg. (no Credit Card required).

  1. Microsoft SQL Server up and running in your environment.
  2. Root access to server to install the Microsoft SQL Server agent.

Monitor Microsoft SQL Server using out-of-the-box custom metrics installer from CopperEgg

There are three steps that you need to do to start monitoring a Microsoft SQL Server:

  1. Microsoft SQL Server Agent Installation
  2. Verify that your Microsoft SQL Server Dashboard is up and running
  3. Configure alerts to be notified about Microsoft SQL Server performance issues

1.Microsoft SQL Server Agent Installation

image1.png

Figure 1: In your CopperEgg Account, navigate to Databases>Getting Started

Login to your CopperEgg account and navigate to Custom tab -> Getting Started. Click on Microsoft SQL Server icon.  image3.png.

image2.png

Figure 2: The Microsoft SQL Server agent installation script

Click on image5.png icon to copy the installer script to your clipboard. Paste the command into the terminal window on the server where you want to install the agent.  Script can be run on the server running Microsoft SQL Server, or it can be a different server, but this server will need to be continually running in order to monitor Microsoft SQL Server.

As soon as the script is run on a terminal with root access, it starts downloading the required Microsoft SQL Server monitoring agent on your server. You will need to configure the following items in the agent installation process. Item specified in square brackets ( [ ] ) are the default.

image4.png

Figure 3: Executing the Microsoft SQL Server agent installation script

  1. Press y to monitor Microsoft SQL Server.

image7.png

Figure 4: Executing the Microsoft SQL Server agent installation script with monitoring frequency as 60 seconds

  1. Monitoring frequency: 15, [60], 300, 900, 3600 seconds

image6.png

Figure 5: Executing the Microsoft SQL Server agent installation script with group name as “MSSQL”, group label as “SQL Server Metrics” and Dashboard  as “SQL Server”

  1. Group label, group name and dashboard name for custom metrics [MSSQL][SQL Server Metrics][SQL Server]

image9.png

Figure 6: Executing the Microsoft SQL Server agent installation script with unique name, authentication type, server hostname , username, password. Only one Microsoft SQL Server is added for monitoring.

  1. Unique name for this server (good to differentiate if you are monitoring different Microsoft SQL Servers). [instance1 is default]
  2. Server Name
  3. Authentication type, hostname username,password(for sql authentication). We are choosing Windows Authentication mode in this illustration.
  4. After configuring one database, you’ll get an option to add more Microsoft SQL Servers.

image8.png

Figure 7: Executing the Microsoft SQL Server agent installation script with an upstart init file and the default value for log file name. The installation completes successfully!

  1. Configure Microsoft Azure SQL Server[Default : N]

2.Verify that your Microsoft SQL Server Dashboard is up and running

Navigate to Dashboard and you will see a new Microsoft SQL Server Dashboard has been added automatically by the installer.

image12.png

Figure 8: A new Dashboard for Microsoft SQL Server has been added

As the installed script executes, you will notice data samples appearing on the widgets for this Dashboard.

image10.png

Figure 9: Widget inside Microsoft SQL Server Dashboard displaying cache hits ratio.

image11.png

Figure 10: Widget inside Microsoft SQL Server Dashboard displaying batch requests per second.

image13.png

Figure 11: Widget inside Microsoft SQL Server Dashboard displaying checkpoint pages per second.

Monitored metrics

CopperEgg currently monitors 29 metrics which cover most of the important metrics for Microsoft SQL Server from a performance and stability perspective. You can see these 29 metrics by navigating to the Custom Tab > Custom Objects and selecting Details on the Custom Metrics Group Label [SQL Server Metrics] that was given as installation input. For some of these metrics, alerts have been automatically created (see section 3 below). To create new alerts see section: Configuring your own Alerts for the Microsoft SQL Server Database.

The metrics are:

  • active_parallel_threads : Active Parallel Threads (alert automatically created)
  • active_requests : Active Requests (alert automatically created)
  • active_transactions : Active Transactions (alert automatically created)
  • backup_restore_throughput_sec : Backup Restore Throughput/Sec
  • batch_requests_sec : Batch Requests/Sec
  • blocked_tasks : Blocked Tasks
  • cache_hit_ratio : Cache Hit Ratio
  • cache_object_counts : Cache Object Counts
  • checkpoint_pages_sec : Checkpoint Pages/Sec
  • cpu_usage : CPU Usage (%) (alert automatically created)
  • dropped_messages_total : Dropped Messages Total
  • errors_sec : Errors/Sec
  • free_memory : Free Memory (alert automatically created)
  • lock_wait : Lock Wait
  • number_of_deadlocks_sec : No. of Deadlocks/Sec (alert automatically created)
  • open_connection_count : Open Connections Count (alert automatically created)
  • page_life_expectancy : Page Life Expectancy
  • page_lookups_sec : Page Lookups/Sec
  • page_reads_sec : Page Reads/Sec
  • page_splits_sec : Access Page Splits
  • page_writes_sec : Page Writes/Sec
  • processes_blocked : Processes Blocked (alert automatically created)
  • queued_requests : Queued Requests
  • sql_compilations_sec : SQL Compilations/Sec
  • sql_re_compilations_sec : SQL Recompilations/Sec
  • transaction_delay : Transaction Delay
  • transaction_ownership_waits : Transaction Ownership Waits
  • transactions : Transactions
  • write_transactions_sec : Write Transactions/Sec

3.Configure Alerts to be notified about Microsoft SQL Server performance issues.

Default Pre-Configured Alerts for the Microsoft SQL Server Database

As soon as your Microsoft SQL Server Database Monitoring Dashboard comes up, you will notice that some alert configurations have been automatically created by our Installation Script (under Alerts > Configure Alerts). These pre-configured alerts for Microsoft SQL Server Database include:

Sr. No. Metric Name Definition Alert Definitions Duration
1 Active Parallel threads Current number of active  Parallel threads
  1. Warning alert when active threads greater than or equal to 150.
  2. Alert when parallel threads greater than or equal to 200.
3 Min
2 Active requests Current number of active requests
  1. Warning alert when active requests greater than or equal to 100.
  2. Alert when active requests greater than or equal to 150.
3 Min
3 Active transactions Current number of active transactions
  1. Warning alert when active transactions greater than or equal to 100.
  2. Alert when active transactions greater than or equal to 150.
3 Min
4 CPU usage Average CPU utilization
  1. Warning alert when cpu usage greater than or equal to 65%.
  2. Alert when cpu usage greater than or equal to 80%.
3 Min
5 Free memory Free Memory
  1. Warning alert when free memory less than or equal to 50%.
  2. Alert when free memory less than or equal to 30%.
3 Min
6 Number of deadlocks Count of deadlocks Alert when Number of Deadlocks greater than or equal to 1. 3 Min
7 Open connection count Count of open connections
  1. Warning alert when open connections greater than or equal to 400.
  2. Alert when open connections greater than or equal to 500
3 Min
8 Processes blocked Count of blocked processes Alert when processes blocked greater than or equal to 1. 3 Min

If the alerting condition is met for these pre-configured alerts, some alert notifications may also be triggered.

Configuring your own Alerts for the Microsoft SQL Server Database

image14.png

Figure 12: A new alert being added that will be triggered when cpu usage metric exceeds a threshold of <entered value> within a time period of 1 min

Alerts can also be configured against any of these 29 monitored metrics for Microsoft SQL Server.

You can configure new alerts that will be triggered when performance issues with Microsoft SQL Server arise. You can also configure the notification mechanisms for a triggered alert.

  1. Go to Alerts Tab > Configure alerts and click on “New Alerts” button
  2. Provide values for these fields in the New Alert page:
  • Description: A description of the alert that will be easily recognized by you and your team if the alert is triggered
  • Alert me when: Select the metric of interest and the condition upon which the alert is triggered. In the Alert me when dropdown, you can prefill “Custom: <your Microsoft SQL Server Metric Group>” to get only the list of monitored metrics for your specific Microsoft SQL Server. From this list, you can then select the specific metric for which you want to configure an alert. As an illustration, in Figure 12, the chosen metric of interest in ‘Alert me when’ is ‘Custom: SQL Server Metrics: CPU Usage(%)’  and the condition for triggering the alert is if the average value is more than <entered value in dropdown>.
  • For at least: The duration for which the alert condition must be valid for the alert to be triggered
  • Matching tags: By default, (match everything) is chosen. If needed, you can attach tags to a particular metric object and select those tags here, which would cause only alerts on that metric object to be triggered.
  • Excluding tags: By default, (exclude nothing) is chosen. If needed, you can attach tags to a particular metric object and select those tags here, which would cause alerts on that metric object to be skipped.
  • Annotate: When enabled, an annotation is automatically created when the alert is triggered. Annotations will be visible in the custom metrics dashboard where the data stream is displayed.
  • Automatic Clear: When enabled, the alert issue is automatically cleared if the triggering condition is no longer true
  • Notify on clear: When enabled, notifications are also sent when the alert issue is cleared. Please do note that notifications are always sent when the alert issue is triggered.
  • Send Notifications To: Here you can configure the notification mechanisms by which the alert is communicated to you and others in your team.

CopperEgg supports notifying different sets of users with differing notification mechanisms for each alert type.

Notification mechanisms include:

  • Email
  • SMS
  • PagerDuty
  • Twitter
  • HipChat
  • Campfire
  • Slack
  • OpsGenie

Webhooks are also exposed for clients to configure custom notification mechanisms.

More about setting up website probe alerts can be found here.

CopperEgg Free Trial! Sign up today!

Sign up for a 14 day FREE TRIAL of CopperEgg (no Credit Card required).

You can find more information about CopperEgg pricing options here.

We charge only $0.08 per month per metric!

Sign up today!