You are here
Database - Microsoft SQL Opspack
Microsoft SQL Server is a relational database management system. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications which may run either on the same computer or on another computer across a network. Microsoft markets numerous editions of Microsoft SQL Server aimed at varying audiences/workloads (ranging from small single-machine applications to large Internet-facing applications) and our Opspack ensures that you can monitor your Microsoft SQL server to ensure it is operating correctly every step of the way.
What You Can Monitor
Opsview Monitor contains all the important service checks to make sure your Microsoft SQL Server is up and running. Service Checks for Microsoft SQL Server include:
- Database size, memory, and agents checks
- Database Log Information
- SQL Stats including Server cache statistics and wait statistics
- SQL transactions per second, time to connect and more
|Microsoft SQL - Active transactions||Number of active transactions for the Database|
|Microsoft SQL - Check memory||Memory usage|
|Microsoft SQL - Database size||Database size in KB's|
|Microsoft SQL - DB Agent Service||Check Microsoft SQL Agent service is started|
|Microsoft SQL - DB Browser||Check Microsoft SQL Browser service is started|
|Microsoft SQL - Generic SQL server statistics||SQL server stats|
|Microsoft SQL - List all DBs||Database enumeration|
|Microsoft SQL - List all lock types||Lock type enumeration|
|Microsoft SQL - List SQL cache types||Cache type enumeration|
|Microsoft SQL - Lock statistics||Lock statistics|
|Microsoft SQL - Log cache hit||Log cache hit ratio percentage.|
|Microsoft SQL - Log flush time||Length of time to flush logs (ms)|
|Microsoft SQL - Log growths||Log growth|
|Microsoft SQL - Server buffer manager||Server buffer stats|
|Microsoft SQL - Server Latch/SuperLatch statistics||Microsoft SQL latch/superlatch stats|
|Microsoft SQL - Server Listener||Microsoft SQL Server response time on TCP port 1433|
|Microsoft SQL - Server Service||Microsoft SQL Server Service status|
|Microsoft SQL - Server wait statistics||Server wait stats|
|Microsoft SQL - SQL Server cache statistics||Microsoft SQL server cache stats|
|Microsoft SQL - Stats||SQL stats|
|Microsoft SQL - Time to connect||Time to connect to the Database (ms)|
|Microsoft SQL - Transactions per second||Number of transactions per second for the database|
Setup and Configuration
Pymssql is required to run this plugin and can be installed on your system by running:
/opt/opsview/python/bin/pip install pymssql
TCP port 1433 needs to be open from the Opsview Monitor server to connect to the monitored host. For more details, see the Microsoft document Configure the Windows Firewall to Allow SQL Server Access
Creating a monitoring user
Create a new “SQL Authentication user” on the server you are monitoring by logging into the “Microsoft SQL Server Management Studio”, and navigating to “localhost > Security > Logins” and right clicking on Logins and selecting “New Login…”.
Populate the sections as below:
GENERAL: create a new login name (username), and change to “SQL Server Authentication”. Enter a password (must be complex, ex. numbers and upper case characters) and then remove “Enforce password expiration and “user must change password at new login”.
SERVER ROLES: Select “public” and “sysadmin”.
- USER MAPPING: Leave as default.
- SECURABLES: Click “Search..”, select “All objects of the types..”, and then “Servers” and click OK. Then scroll down the lists to find the permission titled “View server state”, and check “Grant”.
- STATUS: Ensure “Grant” and “Enabled” are checked respectively.
Step 1: Add the Host Template
Add the Database - Microsoft SQL Host Template to your Opsview Monitor host.
For more information, refer to Opsview Knowledge Center - Adding Host Templates to Hosts.
Step 2: Add and configure variables required for this host
|MSSQLCREDENTIALS||Used in authenticating to Microsoft SQL. Override the Username and Password with your credentials. We recommend that you do not use the sa user and instead create a specific user for monitoring.|
|MSSQLDATABASE||One instance of this variable per database you wish to monitor, the value should be set to the database name.|
|WINCREDENTIALS||Used in authenticating to Microsoft Windows. Override the Username and Password with your credentials.|
For more information, refer to Opsview Knowledge Center - Adding Variables to Hosts.
Step 3: Apply changes and the system will now be monitored
If we see an error such as 'CRITICAL:Unable to access SQL Server', then you should check that the credentials you have specified are those for Microsoft SQL (e.g. sa), and not the Windows credentials (e.g. Administrator)