Microsoft SQL Server is one of the most used databases today. Because it often has an important role in your business you need to make sure you get alerted if there is a problem with your databases. The purpose of this article is to describe how op5 Monitor can be used to monitor both important environment parts of the operating system and how to perform queries to the database. In this document we will assume that Microsoft SQL Server is the main application running on the server.
This will be done
The suggested configuration components for monitoring Microsoft SQL Server are:
- basic checks on the server like CPU, memory, disc usage, swap usage etc.
- advanced checks of the operating system by checking several performance counters in the operating system
- queries to databases
- checking backup jobs.
Add the required check-commands, if they don't already exist in your configuration, with the import functionality in op5 Monitor ('Configure' -> 'Commands' -> 'Check Command Import'):
List of commands
Description of the commands
In the table below you will get a description of the so called performance check_commands used to monitor a Microsoft SQL Server.
|check_mssql_num_user_connections||If the number of connections at the same time is too high you might run in to problems and needs to increase the number of threads on the running server. This check_command will count the number of connections.|
The table below describes the check_command you may use when monitoring Microsoft SQL Server by queries
|check_mssql_query_count||The check_command takes a given query and return the number of hits.|
|check_mssql_query_reponstime||The check_command will report the responstime of a given query.|
Adding the services
Add the required services, ('Configure' -> 'Host: <your-mssql-server>' -> 'Go' -> 'Services for host <your-mssql-server>' -> 'Add new service' -> 'Go'):
When you added the host you had probably already added services like CPU usage, mem usage, disk usage and so on. The table below describes services you probably want to add. (Arguments are just examples, you need to adjust them to suite your environment).
|MSSQL DB File Size op5||check_mssql_db_file_size||1024!1256|
|MSSQL Log File Size op5||check_mssql_log_file_size||1024!1256|
|NT Memory Page/SecP||check_nt_memory_page_per_sec||20!30|
|NT Physical Disk Time||check_nt_physical_disk_time||45!55|
|MSSQL Cache Hit Ratio||check_mssql_cache_hit_ratio||90!80|
|MSSQL Num Deadlocks||check_mssql_num_deadlocks||1|
|MSSQL Num User Connections||check_mssql_num_user_connections||200!250|
|MSSQL Backup job ? master||check_mssql_backup_jobn||user!passwd!job name|
|MSSQL Select String||check_mssql_query_string_regex||user!password!db!select field from table where?!.*The job succeeded..*|
|MSSQL query count||check_mssql_query_count||user!password!db!select field from table where?!10!20|
|MSSQL respons time||check_mssql_query_reponstime||user!password!db!select field from table where?!5!10|
Use the "Test this service" button for the services to see if they work. Once they are correct and working as they should you may add the services to all of your Microsoft SQL Servers with the clone function.
Monitoring a cluster
It is not unusual that a Microsoft SQL Server is running in a clustered environment. Of cource we can monitor your Microsoft SQL Server in that kind of environment to. Please read the how-to about Monitoring Microsoft clustered servers.