To monitor Microsoft SQL Server with Database Visibility, you must be running the 2005 version or newer.
To configure an Azure SQL Managed Instance, follow the configuration procedures of a Microsoft SQL Server Collector described in this topic.
|Database Type||The database type that you want to monitor.|
|Database Agent||The Database Agent that manages the collector.|
|Name||The name you want to identify the collector by.|
|Hostname or IP Address||The hostname or IP address of the machine that your database is running on.|
|Failover Partner||The hostname or IP address of the failover partner.|
|Listener Port||The TCP/IP address of the port on which your database communicates with the Database Agent|
|Custom JDBC Connection String||The JDBC connection string generated by the database agent, for example, |
|Window Authentication||Click to enable Windows authentication when connecting to the database.|
|Username||The name of the user who is connecting to and monitoring the database through the Database Agent. The user should have the permissions described in User Permissions for Microsoft SQL Server.|
|Password||The password of the user who is connecting to and monitoring the database through the Database Agent.|
Click to monitor multiple database instances in a consolidated view, and aggregate metrics of multiple databases. To monitor a custom cluster, you can add additional hostname or IP address, and port details for each sub-collector. You can add up to a total of 29 sub-collectors. Thereby, 30 databases can be monitored in a custom cluster. In addition to the licenses consumed by the main collector, each sub-collector consumes one or more licenses, depending on the database type.
|Connection Properties||Click to add a new JDBC connection property or edit an existing property for relational databases.|
|CyberArk||Click to enable CyberArk for database username and password. When CyberArk is enabled, information about Application, Safe, Folder, and Object is required to fetch the username and password for your database. To use CyberArk with Database Visibility, you must download the JavaPasswordSDK.jar file from the CyberArk web site and rename the file to cyberark-sdk-9.5.jar. Then, you must copy the JAR file to the lib directory of the database agent zip file.|
|Exclude Databases||The databases that you want to exclude, separated by commas.|
|Logging Enabled||Click to enable verbose mode logging, which logs all communications between the Controller and the Collector. Enable only during troubleshooting because logging consumes a lot of disk space. If you have enabled logging, you can click the logging icon in the Log column of the Collector Administration window to view the log file. The log files are located in the <db_agent_home>\agent directory and have the format <CollectorName>_out.log and <CollectorName>_err.log.|
To handle High Availability (Multi-AZ) for Amazon RDS, set the databaseName JDBC connection string property to a user database.
User Permissions for Microsoft SQL Server
The user account used for monitoring can be a Windows authenticated account (if the Database Agent is running on Windows) or SQL Server authenticated (if the Database Agent is running on Windows or Linux).
Required Permissions to See Execution Plans
The SQL Server user, specified in the Create Collector > Connection Details section must be a SQL Server Authenticated user that is a member of the sysadmin server role or a Windows Authenticated Account with SHOWPLAN access on each database.
SQL Server Authentication
If you are running AppDynamics Database Visibility on Linux, then you must use SQL Server authentication.
If your SQL Server database uses mixed-mode authentication, then you can use SQL Server authentication or Windows authentication. If you would like to lock the role/permissions for the account down, then the account running AppDynamics Database Visibility requires:
- View any database
- View any definition
- View server state
One additional requirement for I/O monitoring is to give permissions on a System view called sys.sysaltfiles. To do this you need to select the master database > Views > System Views > Properties for sys.sysaltfiles and then give select permissions on the object to the Public role.
The sys.sysaltfiles view is only available for SQL Server versions 2005 and earlier. For SQL Server versions 2008 and later, use sys.master_files.
If you would like to use a Windows authenticated account to connect to the SQL Server database, the following is required:
- When creating the collector from the Create New Collector dialog, do not specify Username and Password in the database Connection Details.
- Also, the agent must be started with the path to its authentication library. For more information, see Windows Authentication for Microsoft SQL Server.
- If you are using a Windows Service to run the dbagent, you must change the logon credentials for the service to your SQL Server Windows authenticated account and then restart the service.
Server Level Permissions Required for SQL Server Logon
The easiest way to configure permissions for monitoring SQL Server is to grant server-level permissions for the monitoring user. Otherwise, you will need to grant permissions on individual objects, as described in the Object Permissions for Monitoring SQL Server section. Follow the steps below to create a SQL Server user with the minimum permissions required to monitor SQL Server.
- Using SQL Server Management Studio, create a new login for the AppDynamics SQL Server Database Collector, such as DBMon_Agent_User.
From the User Mapping tab, map the new user to the master and msdb databases.
Viewing Object InformationTo view object information on the Database > Objects Browser, map the monitoring user to the databases of interest.
- Once you have created the login, grant the following privileges to the user, substituting DBMon_Agent_User with the name you specified on the Login - New window:
where DBMon_Agent_User is the name of the SQL Server user account specified in Create New Collector, Connection Details, Username field.
Note: You can execute the statements above as a batch from a query window in Management Studio.
Object Permissions for Monitoring SQL Server
You can grant permissions individually for the following objects in order to monitor SQL Server: