1. SQL Server server Monitoring implementation method

Original: 1. SQL Server server Monitoring implementation method

For server monitoring, and monitoring of the database, there are very few tools, if any, usually paid software, or self-developed tools.
So if you do not want to buy software, and do not want to spend energy to develop, can be combined with some free/open source tools, custom scripts, to complete the monitoring of the database server.

I. Third-party tools
1. Open Source Tools
For example: Cacti,nagios,zabbix and so on, in addition to the host, for other devices on the network, such as routers can also be monitored.
Take cacti as an example (cacti can be installed under Windows), a diagram of server monitoring:
(1) Device status

(2) SQL Server service status

(3) Server event Log

(4) disk space

(5) Memory usage

(6) CPU usage

(7) Network card use

(8) Switch traffic

From the picture can be found that the server status monitoring is not a problem, but for server performance monitoring, only in disk space/memory/cpu/network bandwidth usage above, there is no detailed performance counter information.

Most of these open source tools are based on the SNMP protocol (Simple network Management Protocol, simplified Web Management Protocol), which monitors the status of devices connected to the network and, for detailed performance parameters of the device itself, usually requires the installation of additional plugins to complete ( This is also the UNIX idea?), such as the syslog plug-in used above, to receive the Windows event log, such as the integration of the Snmptools plug-in to accept Windows performance counter information.

2. Payment Tools
Different tools, the focus is not the same:
(1) Some biased to host monitoring , such as: Mom (Microsoft Operations Manager), SCOM (System Center Operations manager,scom is an upgraded version of MOM, Just as Lync is the upgraded version of Communicator), WhatsUp Gold;
(2) Some favor SQL Server database monitoring , for example: Idera SQL Diagnostic manager,sql sentry,red-gate SQL Monitor;
(3) There is a tool set, different functions to choose one of them, such as: Quest SPOTLIGHT,BMC Patrol (renamed BMC performance Manager)

Most of the tools for these Windows platforms are well-supported for performance counters, typically based on WMI implementations.
Wmi:windows Management instrumentation,windows Management Specification, WMI allows access to a wide range of operating system components through a common interface, allowing users to invoke WMI to manage both local and remote computers using tool software and scripting programs.

As an example of SQL Monitor, a diagram of server monitoring:
(1) Limited Windows monitoring options

(2) very direct support for performance counters

Two. Can performance monitor be used for monitoring?
With regard to performance monitoring, many people may want to read the value of the performance counter in SQL statements, but from a rights/security standpoint, SQL Server (application) should not and should not be able to reverse the concern that Windows (operating system) does not have resources assigned to it, so the Sys.dm_os What _performance_counters can find is just SQL Server's own performance counter value.

But then again, some dmv/sql statements really can see the operating system resource usage, only the part, should be SQL Server itself has been implemented, after all, is the home of things.

What if you want to do your own performance counter-based monitoring without using the development tools and only write a few SQL statements?
The performance counter log record is saved, import into the database with SQL statement analysis, do alarm.

1. How do I take the value of the performance counter?
(1) Direct view of the performance counter interface , limited to view, unable to save the log records;
(2) define a file in advance to save performance counters , open Performance Monitor, which has this function, Windows 2003 is called Counter Logs,windows 2008 called Data Collector,

If you want to automate, you can use the command line tool Typeperf, the function and graphical interface is the same.

2. How do I analyze performance counter logs?
(1) To view the log directly , you can use tools such as Perfmon/excel/pal analysis, but these tools can only help to generate charts, can not do automation, can not do alarm;
(2) Import the database to do analysis , can use any log parser/ssis, such as any can automatically import performance counter log into the database tools, and then use SQL statements to do analysis, since it is monitoring, it is real time, the frequency of the import is higher.

Third-party tools usually have a page alarm, SMS, mail and so on alarm way, if you do development, alarm this block also need their own to achieve, usually the end of the database is to use mail to alarm.
Setting the threshold of the alarm requires a systematic performance baseline, if not very sure, set a higher point, at least the performance log has been recorded, traceability is not a problem.

Summary

Server monitoring recommends using open source tools, if you are not satisfied with the performance monitoring of open source tools, you can:
(1) Extension of the Open Source Tool plug-in (if there is a budget, you may choose to pay for tools);
(2) Self-developed, either based on performance counter logs (for people who only database developers), or based on the WMI interface (individuals feel that based on WMI is easier, but have a bit of programming basis, at least know how to call WMI in the program/script).

1. SQL Server server Monitoring implementation method