How to monitor -MS-SQL Server

image_pdfimage_print

Create an MS-SQL Server user

Create a user that will be used for the monitoring of the MS-SQL Server database.
This user must have the serveradmin role, you can call it anything you wish.
A domain user is preferable to using the “sa” account.
This user will then be added into ServiceNav as an MS-SQL type of monitoring account.
Run the following script on the MS-SQL Server instance you want to monitor.
Before launching it, you must edit the following lines to include the user’s login information:

SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
SET @check_mssql_health_PASS = 'Password'
Do not omit the “.
DECLARE @dbname VARCHAR(255) DECLARE @check_mssql_health_USER VARCHAR(255) DECLARE @check_mssql_health_PASS VARCHAR(255) DECLARE @check_mssql_health_ROLE VARCHAR(255) DECLARE @SOURCE VARCHAR(255) DECLARE @options VARCHAR(255) DECLARE @backslash INT/*******************************************************************/ SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"' SET @check_mssql_health_PASS = 'Password' SET @check_mssql_health_ROLE = 'serveradmin' /*******************************************************************PLEASE CHANGE THE ABOVE VALUES ACCORDING TO YOUR REQUIREMENTS- Example for Windows authentication:
 SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
 SET @check_mssql_health_ROLE = 'Rolename'- Example for SQL Server authentication:
 SET @check_mssql_health_USER = 'Username'
 SET @check_mssql_health_PASS = 'Password'
 SET @check_mssql_health_ROLE = 'Rolename'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 It is strongly recommended to use Windows authentication. Otherwise
 you will get no reliable results for database usage.
 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*********** NO NEED TO CHANGE ANYTHING BELOW THIS LINE *************/SET @options = 'DEFAULT_DATABASE=MASTER, DEFAULT_LANGUAGE=English'
 SET @backslash = (SELECT CHARINDEX('\', @check_mssql_health_USER))
 IF @backslash > 0
 BEGIN
 SET @source = ' FROM WINDOWS'
 SET @options = ' WITH ' + @options
 END
 ELSE
 BEGIN
 SET @source = ''
 SET @options = ' WITH PASSWORD=''' + @check_mssql_health_PASS + ''',' + @options
 ENDPRINT 'CREATE Nagios plugin USER ' + @check_mssql_health_USER
 EXEC ('CREATE LOGIN ' + @check_mssql_health_USER + @source + @options)
 EXEC ('USE MASTER GRANT VIEW SERVER STATE TO ' + @check_mssql_health_USER)
 PRINT 'USER ' + @check_mssql_health_USER + ' created.'
 PRINT ''declare dblist cursor for
 select name from sysdatabases WHERE name NOT IN ('master', 'tempdb', 'msdb') open dblist
 fetch next from dblist into @dbname
 while @@fetch_status = 0 begin
 EXEC ('USE ' + @dbname + ' print ''GRANT permissions IN the db '' + ''"'' + DB_NAME() + ''"''')
 EXEC ('USE ' + @dbname + ' CREATE ROLE ' + @check_mssql_health_ROLE)
 EXEC ('USE ' + @dbname + ' GRANT EXECUTE TO ' + @check_mssql_health_ROLE)
 EXEC ('USE ' + @dbname + ' GRANT VIEW DATABASE STATE TO ' + @check_mssql_health_ROLE)
 EXEC ('USE ' + @dbname + ' GRANT VIEW DEFINITION TO ' + @check_mssql_health_ROLE)
 EXEC ('USE ' + @dbname + ' CREATE USER ' + @check_mssql_health_USER + ' FOR LOGIN ' + @check_mssql_health_USER)
 EXEC ('USE ' + @dbname + ' EXEC sp_addrolemember ' + @check_mssql_health_ROLE + ' , ' + @check_mssql_health_USER)
 EXEC ('USE ' + @dbname + ' print ''Permissions IN the db '' + ''"'' + DB_NAME() + ''" GRANTED.''')
 fetch next from dblist into @dbname
 end
 close dblist
 deallocate dblist

Configuring access to the database from the ServiceNav Box

This is done by Coservit (or the reseller) for each monitoring box in production.
Edit /etc/freetds.conf file:

[global]
# TDS protocol version
# tds version = 4.2
tds version = 8.0

[CONNECTION_NAME]
host = fqdn or @IP
port = port TCP/IP of the instance
instance = instance name
tds version = 8.0

Connection_NAME is important: this is the name that will be used in the service configuration.

Configuration ServiceNav

Example: Configuring the User Service “MS-SQL database-backup-age” with the objective to establish the number of hours since the last backup of the  MS SQL Server database instance.

Configuration: the database instance, the account and  password of the user with access to the database, the number of hours over which the state goes to ALERT, the number of hours above which the state goes to CRITICAL. The instance must be defined on the monitoring server. The user must have the serveradmin role on the instance.

adding a service

Removing User

The following script will remove the user created earlier.

DECLARE @dbname VARCHAR(255)
 DECLARE @check_mssql_health_USER VARCHAR(255)
 DECLARE @check_mssql_health_ROLE VARCHAR(255)
 SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
 SET @check_mssql_health_ROLE = 'serveradmin'
 DECLARE dblist cursor FOR
 SELECT name FROM sysdatabases WHERE name NOT IN ('master', 'tempdb', 'msdb') OPEN dblist
 fetch NEXT FROM dblist INTO @dbname
 while @@fetch_status = 0 BEGIN
 EXEC ('USE ' + @dbname + ' print ''Revoke permissions in the db '' + ''"'' + DB_NAME() + ''"''')
 EXEC ('USE ' + @dbname + ' EXEC sp_droprolemember ' + @check_mssql_health_ROLE + ' , ' + @check_mssql_health_USER)
 EXEC ('USE ' + @dbname + ' DROP USER ' + @check_mssql_health_USER)
 EXEC ('USE ' + @dbname + ' REVOKE VIEW DEFINITION TO ' + @check_mssql_health_ROLE)
 EXEC ('USE ' + @dbname + ' REVOKE VIEW DATABASE STATE TO ' + @check_mssql_health_ROLE)
 EXEC ('USE ' + @dbname + ' REVOKE EXECUTE TO ' + @check_mssql_health_ROLE)
 EXEC ('USE ' + @dbname + ' DROP ROLE ' + @check_mssql_health_ROLE)
 EXEC ('USE ' + @dbname + ' print ''Permissions in the db '' + ''"'' + DB_NAME() + ''" revoked.''')
 fetch NEXT FROM dblist INTO @dbname
 END
 close dblist
 deallocate dblist
 PRINT ''
 PRINT 'drop Nagios plugin user ' + @check_mssql_health_USER
 EXEC ('USE MASTER REVOKE VIEW SERVER STATE TO ' + @check_mssql_health_USER)
 EXEC ('DROP LOGIN ' + @check_mssql_health_USER)
 PRINT 'User ' + @check_mssql_health_USER + ' dropped.'
Facebook
Google+
Google+
http://servicenav.coservit.com/documentation/how-to-monitor-ms-sql-server/
Youtube
Youtube
LinkedIn