Monitoring
Monitoring throught Performance Counters
Considering that we have already installed and configured both Pandora and the system to monitor, we are going to explain how to get information about the SQL server status in general, from the activity of the different services to specific Powershell counters that through different cmdlets will be in charge of checking critical elements of our systems
In this case, we will install both a Pandora Agent and the different plugins of the Powershell agent in that machine.
To do that the Pandora software agent that we have installed in our server to monitor execute that script, we should edit the agent configuration file and do the call to the plugin through the module_plugin configuration token
We are going to edit the Pandora agent configuration file from the Pandora FMS administration console or writing directly pandora_agent.conf. To do this, we have first to activate the remote_config option in the same file to 1.
We should introduce the following at the end of the configuration file, for example:
# Agent Plugins for SQL Monitoring
module_plugin "<ruta-powershell>\powershell.exe" -command C:\'<rutaplugin>\Pandora_Plugin_PerfCounter_vx.y.ps1' -list C:\'<ruta-listado>\counters.txt'
2> counter_plugin.error
** Pandora_Plugin_PerfCounter not included in .zip. Plugin PerfCounter are available in the server with its appropriate .zip
Save the file and restart the Pandora agent.
Please, consider the error re-addressing of the plugin to one error log, specially due to the cmdlets execution timeout when you have to process lot of information in little time.
If for example, we want to generate one module for each one of the machine counters, the cmdlet will have to process an average of 20000 counters by once and so until it hasn't process all the counter list it won't show the information, all the time from which it process the info until it presents it, the Powershell cmdlet will get an error message after another warning that the list
counter hasn't been found.
This is because the Powershell cmdlet understands that if x time has passed since the counter information request and this information has been shown in the output, then, the information that you were looking for couldn't been find, but what really happen is that this information was found, but it hasn't been shown.
To avoid incrementing the log without control and even whit this receive all the errors made when executing the plugin in the last interval (just in case there is a real error), do the re-addressing using the symbol “2>” as it comes specified in the line that you should introduce in the configuration file.
Once we have configured it, we should distribute the necessary files through file collections or copying in the appropriate agent folder.
One of the most powerful characteristics of the plugin in Powershell, is the possibility of specifying instead of create one by one modules for each performance counter, select all the counters that are specified in one list, as the plugin will do a single check and generate automatically one module for all these counters, optimizing at maximum the time to extract the information. Those list should be located in the same folder where the plugin is, with the name counters.txt. Lets see an example of its content:
\SQLServer:Access Methods\Full Scans/sec
\SQLServer:Access Methods\Range Scans/sec
\SQLServer:Access Methods\Probe Scans/sec
\SQLServer:Access Methods\Scan Point Revalidations/secAs these counters are in the counters.txt file, the plugin will do one module for each one of them. If one counter has several instances, as in the case of (*), the plugin will do one module for each one of the instances of the counter.
SQL Instances Monitoring
In an active/passive cluster architecture with remote instances, accessible form all the SQL Server nodes, it is possible using the SQL Server Powershell administration console, to do checks of the availability of these instances from these nodes, and also to extract information about the performance and the status of the databases stored in those started instances, from the cluster
active nodes.
In the case of the passive nodes, the monitoring is limited to the instance availability checks, in order to show if this node remains passive or changes to active status and meanwhile it won't try to connect with the instances or returns any information about its status.
Additionally, from the Pandora server it is possible to define ICMP checks against the remote instances service Ips in order to guarantee their connectivity.
With this aim, we have developed a Powershell plugin that, showing in a list of instances to monitor, will search in the node list of services, for each instance, if the SQL Server service of that instance is up (in case of the active node) and in that case it will try to connect with the Ip\instancename when collecting information about the DDBB status that are located in that instance.
If it is specified, it will get also the status of all the service of tha node linked with SQL.
The parameters of this plugin are the following:
-select all All local instances and services are checked (not to
be used in clusters)
-select list Instances from the list are checked
-select services All local services are checked
-list Provides a path for a list with instances to check
Usage example: .\Pandora_Plugin_SQL_v1.0.ps1 -select all -list instances.txt 2>
plugin_error.logIn order the plugin works right we should inform that:
– The “select all” mode will only be used in SQL servers whose instances are all locals.
– In the case of instances located in remote servers, it isn't possible to resolve the hostname of the machine where the SQL server remote instance is located, so, to connect with the instance you will have to do it with the combo IP\instancename.
Example to instances.txt:
10.93.6.81\MSSQLSERVER
10.93.6.85\ESTGCDBP008
10.93.6.86\ESTGCDBP008
10.93.6.89\ESTGCDBP008
It´s necessary install and working correctly the command invoke-sqlcmd in Powershell. For the installation and the proper operation follow these steps:
1.- Install SQL Server 2008 R2 Management Objects Download the tool Microsoft Web Platform → Installer http://www.microsoft.com/web/downloads/platform.aspx. Execute and over Product section search SQL Server 2008 R2 Management Objects, select this option and install it
2.- Install Windows PowerShell Extensions for SQL Server --> https://docs.microsoft.com/es-es/sql/powershell/download-sql-server-ps-module?view=sql-server-ver15
To check the execution of the command invoke-sqlcmd, execute these commands previously in a powershell comand line.
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
In a Windows 2012 Server only execute this command.
Import-Module SqlPsMonitoring SQL Querys ( Time or Query result)
With this document is added a script to monitor sql querys and the execution time of these querys.
Script for SQL query result:
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
invoke-sqlcmd -ServerInstance <HOSTNAME>\<INSTANCE_NAME>
-Username Username -Password Password -Query “query” -Database
<DATABASE_NAME>
Once generated the script, called sql_query.ps1, this is the module configuration:
module_begin
module_name Query result
module_type generic_data_string
module_exec powershell.exe <PATH>/sql_query.ps1
module_end
Script SQL query time execution, called sql_time.ps1
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
$var1=echo $( (( ((get-date).Minute * 60) + ((get-date).second)
)*1000) + (get-date).Millisecond )
$command=invoke-sqlcmd -ServerInstance
<HOSTNAME>\<INSTANCE_NAME> -Username User -Password Password
-Query “query” -Database
$var2=echo $( (( ((get-date).Minute * 60) + ((get-date).second)
)*1000) + (get-date).Millisecond )
$var3 = ($var2 - $var1) / 1000
echo $var3Module Configuration:
module_begin module_name Query result module_type generic_data module_exec powershell.exe /sql_time.ps1 module_end