SelectStar

Microsoft SQL Server

Prerequisites

Before attempting to monitor your Microsoft SQL Server database with SelectStar, ensure you are running one of the following versions of Microsoft SQL Server:

  • 2008 R2
  • 2012
  • 2014
  • 2016

Connection Information

The following information is required by SelectStar in order to monitor to your Microsoft SQL Server instances:

Field
API Field
Description

Host

host

Hostname or IP address of the system on which your database is running

Port

port

Port on which your database is listening [default: 1433]

Instance

instance

Service Name of the database [default: MSSQLSERVER]

Credentials

Field
API Field
Description

Username

username

SQL authenticated user or domain and active directory user

Password

password

User's password

SelectStar collects health and performance data from Microsoft SQL Server by connecting to the Microsoft SQL Server Instance through JDBC. Metrics are collected primarily from Dynamic Management Views.

Access Rights

To set up the minimum user permissions needed for monitoring with SelectStar, the following tasks must be completed. You should use your own credentials, user, and role names.

  1. Grant your Microsoft SQL Server login credentials the VIEW ANY DEFINITION and VIEW SERVER STATE permissions.
  2. On each user database, create a user that maps to your Microsoft SQL Server monitoring credentials and add it as a member of the db_datareader role.

Note

You may also add the user and role membership on msdb and model if permissions are available, and then future user databases will have the necessary user and role membership upon creation.

The RDS master user does not have the ability to add members to the db_datareader role in system databases, so the msdb and model steps cannot be completed on RDS. If you are monitoring an RDS SQL Server instance, you will have to add the user and role membership to each new user database as they are created.

  1. To return Job information ensure the monitoring user has SELECT permission on the sysjobs tables:

Note

RDS users are not currently able to access Job information, due to limited permissions on the Default Master User.

USE msdb;
GRANT SELECT ON sysjobs TO monitoring_user;
GRANT SELECT ON sysjobhistory TO monitoring_user;
GRANT SELECT ON sysjobschedules TO monitoring_user;

Windows Authentication

To set up windows authentication specificy the domain then user in the credential's username field.

domain\username

Note

Using Windows authentication will log more than normal to the Windows event log which can impact system if not maintained.