SQL server database configuration

DB User Permissions

Create a new user with SQL server authentication and provide the below two permissions.
1. db_datareader to all the databases. Including master database.
2. grant alter trace,  view server state, view database state and alter any event session permission.

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 newly created username.

Example: Let's consider the newly created user name is appms

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;

Step 1: Login into Applicare console, go to Download Agents screen and download SingleAgentWindows. Copy ApplicareSingleAgentWindows.zip file to the machine where SQL server is running.

Step 2: Unzip the ApplicareSingleAgentWindows.zip file to ApplicareSingleAgentWindows directory.

Step 3: Start the agent by running the startAgent.bat (Administrator mode for windows)  inside the ApplicareSingleAgentWindows.zip extracted directory.

Step 4: Once the agent is started, It will be automatically added in the Applicare controller. Login into the Applicare console and go to the Overview menu.

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

Step 6: Enable the "Add Database Configuration" checkbox and select "SQL Server" option from Database dropdown. Select your sql server version in Version dropdown. If the version is not available in the dropdown then select the latest version which is available in the dropdown.

Step 7: Enter the JDBC URL, Username and Password and click on update button.

Note:
Checking Test connection is not mandatory. Test connection will test the connection from controller machine to agent machine.

Note:

By default it will capture the SQLs executed in all the databases.

In order to restrict and capture SQLs executed in specific database, add 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; 

Incase, if you don't see the database metrics in applicare dashboard, after followed the above steps then validate the trace configuration in sql server.

Refer here to view Other Database monitoring configuration.

For Other Docs - Click here