EV Observe - Configure MS SQL Server Monitoring Prerequisites

Last modified on 2023/03/16 17:39
Contents

SQL Server is used to manage databases using SQL.

To use services to monitor MS SQL Server instances and the associated databases, specific prerequisites must first be met.

  • You configure these prerequisites once only.
  • This is done in two steps:
    • Create an account dedicated to the monitoring of the MS SQL Server instance and associated databases
    • Configure the Box to monitor a new connection to the MS SQL Server server

Notes

  • You can use Microsoft SQL Server Management Studio to create the dedicated monitoring account.
  • The configuration of the account does not apply to specific databases on the MS SQL Server server, i.e. master, tempdb and msdb.
  • The user must have minimum access rights to system information on the MS SQL Server instance and all associated databases.

Best Practice

  • We recommend that you create a monitoring account for a domain user (Windows user) instead of an sa user.
  • We recommend that you use Windows authentication.

Procedure: How to configure MS SQL Server monitoring prerequisites


Step 1: Create an account dedicated to the monitoring of the MS SQL Server instance and associated databases

1. Specify the user credentials in the creation script.

Replace:

  • [Servername OR Domainname]\Username with the user name
  • Password with the user password
  • ServiceNavMonitoring with the role dedicated to the monitoring of MS SQL Server instances and associated databases

SET @check_mssql_health_USER = '"[Servername OR Domainname]\Username"'
SET @check_mssql_health_PASS = 'Password'
SET @check_mssql_health_ROLE = 'ServiceNavMonitoring'

example  

  • Windows authentication

    SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
    SET @check_mssql_health_ROLE = 'Rolename'

  • SQL Server authentication

    SET @check_mssql_health_USER = 'Username'
    SET @check_mssql_health_PASS = 'Password'
    SET @check_mssql_health_ROLE = 'Rolename'

2. Run the script.

  • The user account dedicated to the monitoring of MS SQL Server servers and MS SQL Server databases will be created.
  • The role dedicated to the monitoring of MS SQL Server servers and MS SQL Server databases will be created.
  • The role is assigned to the user for each of the databases on the MS SQL Server instance except for specific databases, i.e. master, tempdb and msdb.


Step 2: Configure the Box to monitor a new connection to the MS SQL Server server

1. Run the commands below to open the freetds.conf configuration file.

Note: Log in to the Box as Putty and switch to the root account.

sudo su –
nano /etc/freetds.conf

2. Add the rows below to the end of the file to specify the server connection information for the new connection to be monitored.

Replace:

  • {ConnectionName} with the name of the new connection
  • {VeeamServer_IP_address} with the IP address of the Veeam server
  • {SQLInstance_port} with the TCP/IP port of the MS SQL Server instance (usually 1433)
  • {SQLInstance_name} with the name of the MS SQL Server instance Caution: The name of the MS SQL Server instance is different from the database name.
  • {TDS_version} with the TDS protocol version: 8.0 or 7.0 depending on your MS SQL Server version

[{ConnectionName}]
    host = {VeeamServer_IP_address}
    port  = {SQLInstance_port}
    instance = {SQLInstance_name}
    tds version = {TDS_version}

example  Configuration of a new Veeam connection

[VEEAM]
    host = 192.168.10.22
    port  = 1433
    instance = MSSQLServer
    tds version = 8.0

3. Save your modifications.

The name of the new connection will be used when a service is created. It corresponds to the server name.
 

Step 3: Check that the connection to the server works correctly in the Box 

1. Run the command below to check the connection.

Replace:

  • {VeeamServer_IP_address} with the IP address of the Veeam server
  • {SQLInstance_port} with the port of the MS SQL Server instance as specified in the configuration file

telnet {VeeamServer_IP_address} {SQLInstance_port}

example  telnet 192.168.10.22 1433

2. Correct any errors that occur and run the test again.

The connection is blocked

  • Solution: Check your configuration, your network firewall and the Windows firewall.
Tags:
Powered by XWiki © EasyVista 2022