Remote Microsoft SQL Server monitoring

This guide walks you through every step required to set up remote Microsoft SQL Server monitoring in Site24x7. It covers the prerequisites, sizing the agent machine, configuring permissions, and completing the agent setup workflow.

Prerequisites

Confirm the following before you proceed.

Requirement Detail
SQL Server version SQL Server 2008 or later.
Edition All editions are supported, for example, Developer, Standard, Express, and Enterprise.
Default TCP port 1433
.NET Framework Version 3.0 or later, installed on the agent machine.
Site24x7 license Each SQL Server Database Monitor consumes a DB instance license. Confirm that you have sufficient allocation before you add instances.
Network connectivity The agent machine must be able to reach the SQL Server host over the network. Verify that firewall rules allow traffic on the SQL Server port.
Windows agent installed The Site24x7 Full-Stack (Windows) agent, version 22.3.00 and above, must already be installed and running on the agent machine before you configure remote SQL Server monitoring.
Service account credentials A domain Windows account that has access to the remote SQL Server. SQL Authentication credentials (username and password) are also supported as an alternative.

To install the agent, run the Site24x7FullStackAgent.msi installer and enter your Site24x7 device key when prompted.

Note

Add the Site24x7 monitoring agent services and folders to the allowlist in your malware detection and endpoint management systems to ensure uninterrupted monitoring.

System requirements for the agent machine

The agent machine is the Windows server on which the Site24x7 agent is installed. It does not need to be the SQL Server host. In a remote monitoring setup, the agent runs on a separate machine and connects to the SQL Server over the network.

Minimum system requirements

Deployment size MSSQL instances CPU Memory (RAM) Disk Machine count
Small Up to 5 4 vCPU 4-5 GB 80 GB 1
Small-Medium Up to 10 4 vCPU (3 GHz) 5-6 GB 100 GB 1
Medium Up to 20 8 vCPU (3 GHz) 8-9 GB 120 GB 1
Medium-Large Up to 50 8 vCPU (3 GHz) 9-10 GB 120 GB 1
Large Up to 100 16 vCPU (3 GHz) 16-18 GB 150 GB 1
  • Test environment: These results were measured in an environment running only the server monitor and remote MSSQL instance monitors.
  • MSSQL Insight: The figures apply when both MSSQL and MSSQL Insight are enabled, with 10 databases per instance. Enabling Insight increases CPU peak and memory consumption significantly compared to basic monitoring alone.
  • Variance: Actual resource usage may vary based on the number of databases per instance, the query output size in MSSQL Insight top queries, and the time taken to execute custom queries. Always provision headroom above the minimums listed.

Service account permissions and privileges

The Site24x7 agent's Windows service must run under a domain account that has both the right to log on as a service on the agent machine and the necessary read privileges on the remote SQL Server.

Grant the "Log on as a service" right

This step is required on the agent machine, not the SQL Server host. Without it, the Windows service cannot start under the domain account.

  1. Open Run (Win + R), type secpol.msc, and press Enter.
  2. Navigate to Local Policies > User Rights Assignment.
  3. Double-click Log on as a service.
  4. Click Add User or Group.
  5. Enter the domain service account, and then click OK.

SQL Server permissions

When you use SQL Authentication, create a dedicated SQL login and grant it the minimum permissions required for data collection.

The table below lists every T-SQL statement to run on the target SQL Server instance, along with why each permission is needed and which monitor type depends on it. Run the statements using an account with sysadmin or equivalent privileges.

T-SQL statement Purpose and monitor requirement
CREATE LOGIN site24x7 WITH PASSWORD = '<PASSWORD>'; Creates a server-level login named site24x7 using SQL Server authentication. This is the entry point for the agent to authenticate against the SQL Server instance. Replace <PASSWORD> with a strong password.
USE master;
CREATE USER site24x7 FOR LOGIN site24x7;
Creates a database-level user in the master database, mapped to the server-level login. Required by all monitors to establish the initial connection and run server-level queries.
GRANT CONNECT ANY DATABASE TO site24x7; Allows the login to connect to any database on the instance without requiring a user to be created in each individual database. Required by the SQL Database monitor to connect to individual databases and collect database-level metrics.
GRANT VIEW SERVER STATE TO site24x7; Grants permission to read server-level metadata and Dynamic Management Views (DMVs). Required by the SQL Insight monitor to collect instance-level performance metrics, by the SQL Always On Availability Group monitor to collect availability group metadata, and by the SQL Database monitor to collect database-level diagnostics from server-scoped DMVs.
GRANT VIEW ANY DEFINITION TO site24x7; Grants permission to view the definition and metadata of any object across all databases on the instance. Required by the SQL Insight monitor and the SQL Database monitor to collect object-level metadata and configuration details.

On SQL Server 2022 (16.x) and later, also run the following statements:

T-SQL statement Purpose and monitor requirement
GRANT VIEW ANY ERROR LOG TO site24x7; Allows the login to read the SQL Server error log. Required for error log collection.
USE master;
GRANT EXECUTE ON xp_readerrorlog TO site24x7;
Allows the login to run the xp_readerrorlog extended stored procedure. Required for the Log Query feature.
GRANT VIEW SERVER SECURITY STATE TO site24x7; Grants permission to view server-level security-related state through Dynamic Management Views (DMVs). This is one of the granular permissions introduced in SQL Server 2022.
GRANT VIEW DATABASE PERFORMANCE STATE TO site24x7; Grants permission to view database-level performance state through DMVs. This is one of the granular permissions introduced in SQL Server 2022.
GRANT VIEW SERVER PERFORMANCE STATE TO site24x7; Grants permission to view server-level performance state through DMVs. This is one of the granular permissions introduced in SQL Server 2022.

Permission for failed job data collection

Collecting failed job details requires read access to the msdb system database, where SQL Server Agent stores job history. The required grant depends on the authentication mode in use.

Windows Authentication mode

Run the following T-SQL to grant the necessary permission to the Windows system account:

USE msdb;
GRANT SELECT TO [NT AUTHORITY\SYSTEM];

SQL Server Authentication mode

Run the following T-SQL with admin privileges to add the db_datareader role to the site24x7 user in msdb:

USE msdb;
EXEC sp_addrolemember N'db_datareader', N'site24x7';

Both grants are required by the SQL Insight monitor to read job history from the msdb system tables (sysjobs, sysjobhistory) and report failed job details in Site24x7.

Remote SQL Server setup: Agent configuration

With the agent installed and permissions in place, use the Agent Manager command-line interface to register the remote SQL Server instance.

  1. Right-click the Site24x7 Agent tray icon in the Windows system tray and select Open Agent Manager. This launches the command-line management interface at C:\Program Files (x86)\Site24x7\WinAgent\monitoring\bin\AgentManager.exe.
  2. At the Agent Manager prompt, type the following command and press Enter. This starts the remote SQL Server instance configuration wizard.
    sqlserver add_instance
  3. When prompted to select the SQL Server type, choose one of the following options:

    Option 1: Cloud SQL

    Enter the Hostname, Port, SQL login Username, and Password to add the instance. The agent validates connectivity and adds the SQL Server Database Monitor to your Site24x7 account.

    Option 2: Remote SQL

    Choose this option if SQL Server is installed on another machine. Then choose the authentication method that matches your environment:

    Option 1: Windows Authentication

    The agent uses the service logon account's domain credentials to authenticate against the remote SQL Server. The current logon user of the Site24x7 App Monitoring Service is displayed. When prompted Do you need to change the service Log On user to a domain account with access to SQL Server instance? (Y/N), enter one of the following:

    • Y: Enter the service account username (for example, mssql@site24x7.com) and its password to switch to a different domain account.
    • N: Continue using the current service logon account as is.

    Option 2: SQL Authentication

    Enter the Hostname, Instance Name, SQL login Username, and Password to add the instance using SQL Server credentials directly. Enter SQLSERVER as the instance name for the default unnamed instance, or the named instance identifier if applicable. The agent validates connectivity and adds the SQL Server Database Monitor to your Site24x7 account.

To change the Windows service logon account at any point after the initial setup, run the following command in Agent Manager. The command is case-sensitive.

Update APPMonitoring User
Shortlink has been copied!