Amazon SQL Collections
When collecting data from SQL instances hosted in Amazon Web Services (AWS), proper configuration of both the instance and the collection account is essential. Unlike Azure, WMI collection can be configured for both EC2 and RDS instances, including SQL Server-specific WMI counters. However, since these are managed instances, access to certain data may be limited.
Both EC2 and RDS instances support Active Directory (AD) integration for SQL instances and WMI collections. Using an AD account for WISdom data collection is the recommended approach, and must be used to collect the available WMI metrics. The WMI collection setup in AWS is comparable to that of on-premises or self-hosted instances, with the suggested permission being membership in the Local Administrators group. For more details, please refer to the Windows Collection Requirements page.
To optimize metric collection and performance monitoring, it is advisable to host a virtual machine running WISdom services in the same region as the AWS SQL instances. This proximity helps minimize latency issues that could hinder data collection. Alternatively, hosting the data collector locally may introduce latency challenges, potentially affecting the reliability and timeliness of the collected data. Be sure to check the latency between the data collector and the remote target instances.
EC2 Instances
Configuring the collection for an AWS EC2 instance is the same as a stand-alone or self-hosting SQL instance. While System Administrator (SA) rights will grant the account all the permission required for collection, the recommended setup would be the principle of least privileges. For specifics into the configuration of least privileges, see the SQL Instance Collection section of the User Guide.
RDS Instances
Monitoring an RDS instance requires specific permissions to be granted, similar to how permissions are assigned to a non-sysadmin account. Additionally, the account will need access to certain specific RDS objects. They are as follows:
Master Database Permissions
Role:
- ProcessAdmin
Permissions:
- View Server State
- View Any Definition
- Create and View Any Database
- Alter Trace
MSDB Database Permission
Role:
- SQLAgentUserRole
RDS Object Permissions:
- EXECUTE Permissions on:
- rds_backup_database
- rds_restore_database
- rds_task_status
- rds_cancel_task
- SELECT Permissions on:
- sysJobs
- sysJobHistory
- sysJobActivity
User Database Requirements:
- Permission:
- Grant ShowPlan
Query Store Permissions (optional when in use)
- Optional for user databases where Query Store is enabled.
- Use for a more efficient collection of Query Statistics information.
- SELECT permission are required on:
- QueryStoreTable
- QueryStoreRuntimeStats
- QueryStoreWaitStats
- QueryStorePlan
- QueryStoreQuery