SQL Server database setup and configuration

Database User Access Permissions

Create a new user with SQL server authentication and assign the following permissions.
1. db_datareader for all databases. Including the master database.
2. Permission to alter trace,  view server state, view database state and alter any event session.

Syntax:

use master;
grant alter trace to <new_user>;
grant view server state to <new_user>;
grant view database state to <new_user>;
grant alter any event session to <new_user>;

Replace <new_user> with the username you have just created. 

Example: If the new user name is appms. use that in place of <new_user>

use master;
grant alter trace to appms;
grant view server state to appms;
grant view database state to appms;
grant alter any event session to appms;

The agent should be installed on the same machine where SQL Server is running.

Enable TCP/IP in SQL Server Configuration Manager.

1. Open SQL Server Configuration Manager.
2. Expand SQL Server Network Configuration → Click Protocols for <your instance>.
3. Ensure TCP/IP is enabled. If it's disabled, right-click and select Enable.
4. After enabling TCP/IP, restart the SQL Server service.

Step 1: Install Applicare SingleAgent on the machine where the MS SQL database is running. Follow the instructions for installing Applicare SingleAgent

Step 2: After starting the agent, It will be automatically registered with the Applicare controller. Log in to the Applicare console and navigate to the Overview menu.

Step 3: Select the newly added agent and click the Edit button.

Step 4: Check the "Add Database Configuration" box and choose "SQL Server" from the Database dropdown menu. Select your SQL server version from the Version dropdown. If your specific version is not listed, select the most recent version.

Step 5: Enter the JDBC URL, username and Password then click the update button.

Testing the connection is optional. The 'Test connection' feature verifies the link between the controller machine to agent machine.

By default, it will capture the SQL statements executed across all databases.

To limit and capture SQL statements executed in specific databases, enter the database names in the "Custom Databases" text box.

Sample JDBC URL

Syntax: jdbc:sqlserver://[serverName]:[portNumber]\[instanceName];databaseName=[databaseName];

samples:

jdbc:sqlserver://168.18.25.84:1433;databaseName=fuzzy
jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=nopcom; 

If you don't see the database metrics on the Applicare dashboard after completing the above steps then validate the trace configuration in sql server.

Refer here to view Other Database monitoring configuration.

For Other Docs - Click here