Microsoft SQL Server Monitoring

MS SQL Server Monitoring

Skip Prerequisites, Web and LPAR2RRD tabs in case of configuring Virtual Appliance, Docker or a Container

Prerequisites
Web
LPAR2RRD
MS SQL Server

HW sizing

Consider usage of our brand new full-stack infrastructure monitoring tool XorMon Next Generation as LPAR2RRD replacement.
It brings a new level of infrastructure monitoring by relying on a modern technology stack.
In particular, reporting, exporting, alerting and presentation capabilities are unique on the market.

Follow installation procedure for your operating system platform

LPAR2RRD obtains data with the help of perl-DBI and perl-DBD-ODBC modules.

Prerequisites

Microsoft ODBC driver for SQL Server (Linux):
docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server

unixODBC + FreeTDS
unixodbc.org
freetds.org

yum install unixODBC unixODBC-devel freetds

Note: Make sure the driver path is present in "/etc/odbcinst.ini" (Most installations will do this automatically)

$ cat /etc/odbcinst.ini

# Driver from the freetds-libs package
# Setup from the unixODBC package
[FreeTDS]
Description     = Free Sybase & MS SQL Driver
Driver          = /usr/lib/libtdsodbc.so
Setup           = /usr/lib/libtdsS.so

Make sure you have installed perl modules: perl-DBI and perl-DBD-ODBC on LPAR2RRD server
```
# rpm -q perl-DBI perl-DBD-ODBC
  perl-DBI-1.627-4.el7.x86_64
  perl-DBD-ODBC-1.50-3.el7.x86_64
```
Install them if they are missing under root user:
- Linux RedHat, CentOS
```
# yum install perl-DBD-ODBC
```
- AIX
  not supported
- Other OS platforms
  Install perl ODBC drivers, let us know if you need any help

Connectivity check

$ perl /home/lpar2rrd/lpar2rrd/bin/conntest.pl 192.168.1.1 1433
  Connection to "192.168.1.1" on port "1433" is ok

Create DB user for LPAR2RRD

CREATE LOGIN lpar2rrd WITH PASSWORD = 'your_passw0rd';
CREATE USER lpar2rrd FOR LOGIN lpar2rrd; 
GRANT CONNECT ANY DATABASE TO lpar2rrd;
USE master;
GRANT VIEW SERVER STATE TO lpar2rrd;

Attach Microsoft SQL Server

As SQL Server gives us info about every DB on host machine we suggest that you use the machine hostname as "Host alias" and choose one DB for initial connect.
LPAR2RRD UI ➡ Settings icon ➡ Microsoft SQL Server ➡ New :

Use <servername>\<instancename> in the Host field if you have Named Instances like in this example:
Make sure cron job for Microsoft SQL Server is in place (upgrade script might do it for you automatically)
Skip this step if you install the Virtual Appliance - it is already taken care of.
```
$ crontab -l | grep "load_sqlserver.sh"
$
```
Add following lines to crontab if necessary
```
$ crontab -e
# Microsoft SQL Server support
0,5,10,15,20,25,30,35,40,45,50,55 * * * *  /home/lpar2rrd/lpar2rrd/load_sqlserver.sh > /home/lpar2rrd/lpar2rrd/load_sqlserver.out 2>&1
```
Wait 30 minutes and then go to the web UI: http://<your web server>/lpar2rrd/
Use Ctrl-F5 to refresh the web browser cache.

Install LPAR2RRD server (all under lpar2rrd user)

Download the latest LPAR2RRD server
Upgrade your already running LPAR2RRD instance.

Install it:

# su - lpar2rrd
$ tar xvf lpar2rrd-7.XX.tar
$ cd lpar2rrd-7.XX
$ ./install.sh
$ cd /home/lpar2rrd/lpar2rrd

Make sure all Perl modules are in place
```
cd /home/lpar2rrd/lpar2rrd
. etc/lpar2rrd.cfg; $PERL bin/perl_modules_check.pl
```
If there is missing "LWP::Protocol::https" then check this docu to fix it

Enable Apache authorisation

su - lpar2rrd
umask 022
cd /home/lpar2rrd/lpar2rrd
cp html/.htaccess www
cp html/.htaccess lpar2rrd-cgi

Schedule to run it from lpar2rrd crontab (it might already exist there)
```
$ crontab -l | grep load.sh
$
```
Add if it does not exist as above
```
$ crontab -e

# LPAR2RRD UI
0,30 * * * * /home/lpar2rrd/lpar2rrd/load.sh > /home/lpar2rrd/lpar2rrd/load.out 2>&1 
```
Assure there is just one such entry in crontab.
You might need to add lpar2rrd user into /etc/cron.allow (Linux) or /var/adm/cron/cron.allow (AIX) if 'crontab -e' command fails
Allow it for lpar2rrd user as root user.
```
# echo "lpar2rrd" >> /etc/cron.allow
```

Assure you have a cron jobs for Oracle DB in place (upgrade script might do it automatically) Skip it on the Virtual Appliance, it is already there.

$ crontab -l | grep "load_postgres.sh"
$

Add it if it does not exist like above

$ crontab -e

# Microsoft SQL Server support
0,5,10,15,20,25,30,35,40,45,50,55 * * * *  /home/lpar2rrd/lpar2rrd/load_sqlserver.sh > /home/lpar2rrd/lpar2rrd/load_sqlserver.out 2>&1

Initial start from cmd line:

$ cd /home/lpar2rrd/lpar2rrd
$ ./load.sh

Go to the web UI: http://<your web server>/lpar2rrd/
Use Ctrl-F5 to refresh the web browser cache.

Troubleshooting

If you have any problems with the UI then check:
(note that the path to Apache logs might be different, search apache logs in /var)

tail /var/log/httpd/error_log             # Apache error log
tail /var/log/httpd/access_log            # Apache access log
tail /var/tmp/lpar2rrd-realt-error.log    # STOR2RRD CGI-BIN log
tail /var/tmp/systemd-private*/tmp/lpar2rrd-realt-error.log # STOR2RRD CGI-BIN log when Linux has enabled private temp

Test of CGI-BIN setup
```
umask 022
cd /home/lpar2rrd/lpar2rrd/
cp bin/test-healthcheck-cgi.sh lpar2rrd-cgi/
```
go to the web browser: http://<your web server>/lpar2rrd/test.html
You should see your Apache, LPAR2RRD, and Operating System variables, if not, then check Apache logs for connected errors