POC on Nagios(monitoring tool) for SQL Server

Purpose:

One of my client is planning to create a centralized monitoring system for all their databases. Due to budget constraint they thought of going for Nagios. From MS SQL Server end, to have the basic understanding on what is Nagios and the feasibility, I have done a small POC. Below are the areas, I have worked, read and learnt on Nagios.

About Nagios:

üNagios is a monitoring and alerting engine.

üIt does

ØSystem Monitoring

ØDatabase Monitoring

ØApplication Monitoring

üIt serves as the basic event scheduler, event processor, and alert manager for elements that are monitored.

üIt is designed to run natively on Linux/Unix systems.

üProducts:

ØNagios XI

ØNagios Core

ØNagios Fusion

ØNagios Incident Manager

ØNagios Network Analyzer

Pre-requisite:

ØNagios Enterprises highly recommends and will only support installing Nagios XI on a newly installed, “clean” system

ØAttempting to install Nagios XI on a pre-existing system with other applications already installed can cause the Nagios XI installation process to fail

ØInternet access is required for installation and upgrades!

ØLinux distributions:

qRHEL 5 & 6 32-bit and 64-bit (requires RHN registration)

qCentOS 5 & 6 32-bit and 64-bit

Database Monitoring:

ØNagios XI is the product to be installed for database monitoring.

ØNagios supported databases:

üMy SQL

üPostgres

üDB2

üOracle

üMS SQL Server

Installation:

http://ptihosting.com/blog/it-blog/monitor-mssql-with-nagios/ (Monitor from Linux)

Plugins for SQL Server:

http://exchange.nagios.org/directory/Plugins/Databases/SQLServer/

Pricing:

For more details: http://www.nagios.com/products/nagiosxi/pricing

Conclusion:

Considering the pricing as compare to other monitoring tools, Nagios is cheap for licensing and support. But to do so, the person configuring requires linux/unix knowledge with Nagios core also it is not easy and time consuming, since it requires linux server with Nagios installation. Overall it definitely a choice for who wants to implement monitoring tool having cost constraint.

References:

E-Book:

http://oss.org.cn/man/newsoft/nagios/index.html

Few links:

http://nagios.frank4dd.com/howto/db-monitoring.htm

http://exchange.nagios.org/#/

http://assets.nagios.com/downloads/nagiosxi/docs/XI_High_Availability_Options.pdf

Online Demo:

http://nagiosxi.demos.nagios.com/

SQL Server 2012 - Always On - a glance

AlwaysOn = HA + DR 

Features:

• Multi-Database Failovers .
• Multiple Secondaries.
• Active Secondaries .
• Integrated HA Management.
• Does not Require any Shared Storage . 

Benefits:

• Supports alternative availability modes (Sync / Async)
• Supports several forms of availability group fail-over (auto/manual/forced)
• Readable secondary replicas (Yes/No/Read Intent)
• Supports automatic page repair for protection against page corruption. Supports encryption and compression, which provide a secure, high performing transport
• Backups from secondary replicas

Performance Improvements:

• Data Latency (secondary replica)
• Read-Only Workload Impact (secondary replica)
• Statistics for Read-Only Access Databases (secondary replica)

Limitations and Restrictions:

Availability group Restrictions :

• Availability replicas must be hosted by different nodes of one WSFC cluster.
• Unique availability group name
• Maximum number of availability groups and availability databases per computer(as per white papers 10 Availability Group with 10 DB’s each )

Cross DB connection:

• Cross DB access can be given as a work around cannot be given directly between availability groups.
• For SQL authentication the cross DB can be performed through rev- logins only.
• Cross availability group DB connection will not work if any of the referenced AG fails.

Availability Database Restrictions :

• Impact on add-file operations.
• RESTORE WITH MOVE

Network restrictions :

• An Availability group to support automatic fail-over, the secondary replica that is the automatic-failover partner must be in the SYNCHRONIZED state.
• If the network link to this secondary replica fails (even intermittently), the replica enters the UNSYNCHRONIZED state and cannot begin to re-synchronize until the link is restored.
• If the WSFC cluster requests an automatic fail-over while the secondary replica is unsynchronized, automatic fail-over will not occur.

Database level Audit for DDL changes

One of the application team came with a requirement that they don't have a control of who is modifying the schema level changes as the development team have full access in development environment. After discussion, we thought of enabling an audit which traps the necessary information in a table. That can be provided as a report to the application team when needed.I just tried this and it worked fine.

Steps followed:

1. Create a DDL trigger on the datbase which needs to track the schema changes.
2. Create a table on the database which is used by DBA for auditing.
3. Insert that values into the audit table.
4. The table will have data viz Event time, event type, object name, object type, login name, command text and Host Name.
5. So whenever there is a schema change all the above information will be captured and inserted into a different DB which is owned by the DBA team.

Setup:

Creation of trigger in the AppDB:

USE [Appdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [trg_DBEvents]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

BEGIN

      SET NOCOUNT ON

      DECLARE @xmlEventData XML

      DECLARE @hostname NVARCHAR(100)

      SET @xmlEventData = eventdata()

      SET @hostname = HOST_NAME()

      INSERT INTO AuditDb.dbo.DDL_Event_log([EventTime], [EventType],

[ObjectName], [ObjectType],[LoginName],

       [CommandText],[HostIP])

      SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),

            CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),

CONVERT(VARCHAR(100),

@xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),

            CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),

            CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')),

            CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')),

            @hostname

           

END

Creation of table in the AuditDB : 

USE [AuditDb]

GO

CREATE TABLE [dbo].[DDL_Event_log](

      [EventID] [int] IDENTITY(1,1) NOT NULL,

      [EventTime] [varchar](100) NULL,

      [EventType] [varchar](100) NULL,

      [ObjectName] [varchar](100) NULL,

      [ObjectType] [varchar](100) NULL,

      [LoginName] [varchar](100) NULL,

      [CommandText] [varchar](max) NULL,

      [HostIP] [varchar](50) NULL,

 CONSTRAINT [PK_DDL_Event_log] PRIMARY KEY CLUSTERED

(

      [EventID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Secondary]

) ON [Secondary]

GO

Check how it works :


I am going to create a new stored procedure in AppDB database logging in as sql user.

USE [Appdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[new_dummy_proc]

     

AS

BEGIN

SELECT * from dbo.inventorydetails where inventoryid is not null;

     

END

When we see the log table created under the AuditDB database the changes has been captured.

Now I will alter the same procedure with a windows user and see the log table.









Security:

When a new user is created, DBA needs to be provide write access to the AuditDB to capture the ddl changes.

Note:Though creating trigger impact performance, it is accepted as the setup is easy and it is implemented on non-production environment.

SQL Server Vs Oracle - terminology difference

This Thursday, while I was having a discussion with team on change the current SQL architecture, one management guy from Oracle background was suggesting to group all the schema with a common name based on the business logic.

It took a while for me to understand that why he wants to keep all the schema from different database to have it in a single name, instead we can keep in the same database and put it in a different instance. Later we understood that as both are talking on different technology the technical terms varies.

Based on that, I thought of writing the terminology difference between both SQL Server and Oracle.

Consider - while creating a new SQL Server database

1. Do not keep data or log files on the drive where OS is installed.

2. Keep the data file (.mdf and .ndf) and log file in seperate drive.

3. Keep the growth size of both data and log file in megabytes not in percent.

4. Set the maximum file size growth don't set is as unrestricted file growth.

5. Create a secondary filegroup and make it as default.

6. Create a secondary file (.ndf) to store the data

7. It is recommended to create (.ndf) one new file for each module.

8. Set the recovery model as full (prod env).

9. Set the Auto update statistics = True.

10. Set the page verify = CHECKSUM