Total Pageviews

Thursday, March 23, 2017

Using PerfCollector with InfluxDB and Grafana

Using PerfCollector with InfluxDB and Grafana:
To collect Windows and SQL Server performance metric data, the following steps needs to be completed:

1.       Install InfluxDB from www.influxdata.com and run it as a service.
2.       Create a database named metricsdb.
3.       Install Grafana from www.grafana.com and run it as a service.
(a)    Create data source as metricsdb for PerfCollector.
(b)    Import the pre-designed dashboard.
4.       Create a Windows Scheduler Task to run PerfCollector continuously.


Installation of InfluxDB:
Currently PerfCollector utilizes InfluxDB for data storage. This guide will help you install InfluxDB on a Windows environment. Please note that the InfluxDB can be install on different OS. Please see  www.influxdata.com for more details.

In this guide, we will install influxDB as a Windows Server Service:

1.       Download the latest pre-build Windows version of influxDB from www.influxdata.com.
2.       Create a folder, for example, d:\influxdb
3.       Extract the binaries into this folder.
4.       Navigate to the d:\influxdb folder.
5.       Create three more folders as follows:

d:\influxdb\data
d:\influxdb\wal
d:\influxdb\meta

6.       Open the “influxdb.conf” configuration file with Windows WordPad. Change the following sections as follows:

[meta]
  dir = "d:\\influxdb\\meta"

[data]
  dir = "d:\\influxdb\\data"
  wal-dir = "d:\\influxdb\\wal"

7.       Open an elevated Windows command prompt and then navigate to the folder, d:\influxdb
8.       Run the InfluxDB daemon as follows:
Influxd –config influxdb.conf

Creating a database for PerfCollector:
1.       Open another elevated Windows command prompt.
2.       Navigate to d:\influxdb
3.       Run influx.exe on the command prompt.
4.       While you are in influx shell, create a database with any name of your choice. Please note that the name is case sensitive.
5.       To create a database, execute, CREATE DATABASE metricsdb

Creating a Windows Service for InfluxDB:
To create a Windows Service for InfluxDB, download the open source nssm tool from https://nssm.cc/.
1.       Extract the contents in a folder, for example, d:\app.
2.       Open an elevated Windows command prompt and navigate to d:\app
3.       Execute the following command to create a Windows Service:
d:\app\nssm.exe install influxdb
4.       Fill in the input fields in the dialog box as follows:




5.       Press “Install Service” when done. The nssm will take you back to the command prompt.
6.       Now on the command prompt, execute net start influxdb to start the InfluxDB service.


Installation of Grafana:
To display PerfCollector metrics in-real time, the open source web based metrics visualization tool “Grafana” is highly recommended. We will be using nssm to create a Windows Service for Grafana.

Download the latest Windows build of Grafana from https://grafana.com.
1.       Create a folder, for example d:\grafana
2.       Extract the zipped file to this folder.
3.       Open an elevated Windows Command Prompt and navigate to d:\app folder.
4.       Execute the following command:
d:\app\nssm.exe install grafana
5.       Fill in the input fields as shown in following dialog boxes:




Importing dashboard into Grafana:
Open a web browser and then browse to http://localhost:3000. Use the default username (admin) and password (admin) to login to the Grafana Interface. You will see the following Grafana web interface:



1.       Data Source for PerfCollector: A data source is needed for the dashboard to display real-time metrics. Click the “Add data source” and create a data source for InfluxDB database which is metricsdb.



2.       Import the dashboard: navigate to the Dashboard menu and hit the “Import button” to import a pre-designed dashboard for PerfCollector.



3.       Dashboard: From the import dialog box, browse the dashboard JSON file and fill in the input fields as follows:


 Sample dashboards:












The fastest and agentless performance data collector with Dashboard for Windows and SQL Server

PerfCollector:
PerfCollector provides thorough insight into Windows and SQL Server performance data in real-time combined with extreme reliability and accuracy.  Data collection process is light-weight, blazing fast and efficient. It is designed to collect more than 200 crucial performance metrics from multiple servers remotely. You will have the remarkable ability to monitor any server in real-time and to analyze the health status of any Windows and SQL Server instance at any given time.

The performance metrics collector process (PerfCollector.exe) is a command line executable and it is a native machine code; it is smaller, faster and has no dependencies. And of course, it has nearly no impact on the monitored server while collecting metrics data.

Some potential uses of this tool:

1.      Real-time performance monitoring and research.
2.      Historical performance data review and trend analysis.
3.      Load testing of Windows and SQL Server.

Download Link:
Version 1.3 - August 2017

The zipped files contain the following:
1.      How-To guide,
2.      Three dashboards for Grafana,
3.       PerfCollector.exe along with the license file.

Sample Dashboard:




What is PerfCollector?
PerfCollector is a WMI and T-SQL based metrics data collection process written in Free Pascal. It has no external dependencies and can be executed on any Windows OS without configuring and deploying to a remote server.

What does PerfCollector do?
It collects a predefined set of metrics data in a regular user defined interval (for example every 5 seconds) from one or more servers running on either Windows 2003 and above or SQL Server 2005 and above. Data collection can be performed remotely and the collection method is completely agentless and configuration free.

The collected data will be written sequentially to an influxDB (www.influxdata.com) database, which is one of the fastest Open Source Time Series Database.

How to use PerfCollector?
On the command prompt, simply execute the “PerfCollector.exe”, “PerfCollector.exe ?”, “PerfCollector.exe /?” or “PerfCollector.exe Help”. It will show the syntax and examples on how to use the performance collector.

Command line Syntax:
PerfCollector has a number of parameters, some are mandatory and some are optional. The following is the syntax and parameters of PerfCollector.:

Syntax:
PerfCollector.exe [WinServer\SQLServer] [Milliseconds] [Influxdb Server:Port] [InfluxDB]\[Print] [WIN][SQL] ["Environment Tag\Application Tag"].
Where each argument represents the following:

PerfCollector.exe is the name of the executable.

[WinServer\SQLServer] is the target Windows Server along with the SQL Server named instance. For a default instance of SQL Server providing the parameter value of “SQLServer”, the instance name is not required.

[Milliseconds] is the data collection interval. The recommended interval is 5000 milliseconds however it can be as low as 100 milliseconds.

[Influxdb Server:Port] represents the InfluxDB Server name along with the port number.

[InfluxDB]\[Print] is the database name of influxDB. [Print] is optional for displaying collected data on the console window. Please note that the database name is case sensitive.

[WIN][SQL] is the type of metrics data PerfCollector will collect from a target Server. If “WIN” is specified, then the PerfCollector collects only Windows Performance metrics. If “SQL” is specified, then the PerfCollector collects Windows and SQL Server Performance metrics. If nothing is specified, the PerfCollector will collect Windows metrics by default.

["Environment Tag\Application Tag"] is the tag to identify a target server. The ‘Environment Tag’ will be used for the environment that the target server belongs to and the ‘Application Tag’ is the name of the application that the target server is serving. One or both can be specified.

Example of using PerfCollector command line tool.
The InfluxDB has been installed on “SrvInFlux201” and the port of the influxDB is 8086. We would like to collect data from two Windows Servers (WinFin001, HLT092) and three SQL Server instances where one SQL Server is a named instance (FinSrv1092, HR093, SrvGE981\SQL2014). Streaming data will be then inserted into an influx database: “metricsdb”. The following are various ways we can collect data from each server:

Data Collection - Windows Only:
(a)    Collect Windows metrics every 2 seconds and insert collected data into metricsdb.

PerfCollectorexe WinFin001 2000 SrvinFlux201:8086 metricsdb

(b)   Collect Windows metrics every 5 seconds and insert collected data into metricsdb. Add Server identification with tag.
PerfCollectorexe WinFin001 5000 SrvinFlux201:8086 metricsdb Win “Production\Finance”

(c)    Collect Windows metrics every second and insert collected data into metricsdb.
PerfCollector.exe HLTSrv62 1000 SrvinFlux201:8086 metricsdb Win “Production\Health watch System”

Data Collection – Windows and SQL Server:

(a)    Collect Windows and SQL Server metrics from FinSrv1092 where SQL Server has been installed as a default instance. Insert data into influx database without tag.
PerfCollector.exe FinSrv1092 5000 SrvinFlux201:8086 metricsdb SQL

(b)   Collect Windows and SQL Server metrics from FinSrv1092 where SQL Server has been installed as a default instance. Insert data into influx database with tag.
PerfCollector.exe FinSrv1092 5000 SrvinFlux201:8086 metricsdb SQL “HR\Employee CRM”.

(c)    Collect only Windows metrics from FinSrv1092 without SQL Server metrics, though, SQL Server has been installed as a default instance. Insert data into influx database with tag.
PerfCollector.exe FinSrv1092 5000 SrvinFlux201:8086 metricsdb Win “HR\CRM Management”.

(d)   Collect Windows and SQL Server metrics from SrvGE981, where SQL Server has been installed as a named instance (SQL2014). Insert data into influx database with tag.
PerfCollector.exe SrvGE981\SQL2014 5000 SrvinFlux201:8086 metricsdb       SQL“GEO\Geographic Survey App.

Permission to Run PerfCollector:
PerfCollector only supports AD Accounts to collect metrics data. The AD Account which is used to execute PerfCollector.exe needs appropriate permission on the target (or remote) Windows and SQL Servers.

(a)   To collect Windows Server Metrics: WMI Read permission on the target (remote) Windows Server.
(b)   To collect SQL Server Metrics: VIEW SERVER STATE and SELECT permission on master and msdb database on the target (remote) SQL Server.

To collect SQL Server metrics data, both WMI and SQL Server permission is required.

Connectivity testing for data collection process from a Target Server:
The data collection process can be tested without having influxDB installed. To test the connectivity and to display the collected metrics on the screen for a remote server, for example “WinSrv2016” or “DellSRV016”, execute any of the following commands:

Connectivity Test - Windows:
C:\Metrics\PerfCollector.exe WinSrv2016 1000 NoServer:NoPort NoDB\Print WIN
Connectivity Test - SQL Server:
C:\Metrics\PerfCollector.exe DellSRV016 1000 NoServer:NoPort NoDB\Print SQL

Collecting real-time metrics data Using Windows Task Scheduler:
To collect metrics data from multiple remote servers, Windows Scheduler Task can be used seamlessly. A Multiple Windows Scheduler task needs to be created to collect data from multiple servers. The AD Account which will be executing each task must have WMI and SQL Server permission on the target server:

1.      Security option: Make sure that the option “When running the task, use the following account” for the Windows Account which executes the PerfCollector has WMI and SQL Server access permission on the target/remote server.
2.      Create a Windows Scheduler Task with the following options:
(a)      “Run whether user is logged on or not”;
(b)     “Run with highest privileges”;
(c)      “Hidden” and
(d)     “Configure for:” choose the preferable server option.
3.      In the “Edit Action”, input the following information:
(a)      Insert the location along with the PerfCollector.exe. Example:

C:\PerfCollector\PerfCollector.exe

(b)     “Add arguments (optional)”: Insert all the parameters without the executable name. Example: SrvGE981\SQL2014 5000 SrvinFlux201:8086 metricsdb SQL “GEO\Geographic Survey App”.

(c)      “Start in (optional)”, insert the location of the executable.

4.      Schedule the task to run every XXX minutes, and make sure “Do not run a second instance” from the Settings of the task has been selected.
5.      You may consider choosing “disable all task history” for smaller task histories.

False positive Alert of Anti-Virus:
You may receive a False Positive Warning from select Anti-Virus software. In that case, you may consider excluding the executable file (PerfCollector.exe) from being scanned.

License and Execution Restriction:
There are three different license modes of the PerfCollector. Regardless of the licensing mode, PerfCollector will always collect real-time metrics data:

1.      Self-Generated License: When the PerfCollector runs for the first time and if no license file has been found in the folder, it will generate a self-license which can be used to collect metrics data from only one local or remote machine for 30 days. After 30 days, PerfCollector will stop collecting all metrics data. You may delete the existing license file (PerfCollector.lic), then re-run the PerfCollector.exe which will cause a new license to generate for another 30 days.

2.      Courtesy License: The license already provided with the PerfCollector will be able to collect metrics data from 5 remote servers for 30 days. After 30 days, PerfCollector will stop collecting all metrics data.

3.      HOST based License: This type of license is specific for a Host and can collect metrics data from any number of servers for an unlimited time. However, this license can’t be used on a different Host.

4.      Enterprise License: This type of license can be used on any host for any number of target remote servers without any time limitations. With this license, PerCollector.exe can be used on any server that resides in the domain.

InfluxDB and Grafana Installation and importing dashboard: