Azure Collections
When collecting data from an Azure Managed Instance or an Azure SQL Database, it's important to note that host-level metrics will not be accessible. This limitation is consistent with the expectations of a Platform as a Service (PaaS) offering, where the underlying infrastructure is managed by Microsoft. While a limited set of information can be retrieved from a managed instance using Dynamic Management Views (DMVs), this data does not provide the comprehensive insights typically available from a virtual machine (VM) instance.
To effectively collect metrics and monitor performance, it is recommended to host a virtual machine in the same Azure region as the managed instance(s) or SQL database(s). This proximity helps minimize latency issues that could hinder data collection. Alternatively, the data collector can be hosted locally, but doing so may introduce latency challenges, potentially affecting the reliability and timeliness of the collected data. Check the latency between the data collector and the remote target instances.
For accessing metrics from these Azure instances or databases, the accounts used for data collection are typically Azure Active Directory (Azure AD) accounts, also known as Entra ID credentials, or SQL Authentication accounts. Utilizing Azure AD accounts can enhance security and streamline user management, while SQL Authentication accounts provide a traditional method for database access.
Azure Managed Instances
When collecting data from an Azure Managed Instance, the System Administrator (SA) role is available and will provide the necessary permissions for the WISdom services to collect data.
However, it is recommended to follow best practices by applying the principle of least privilege. This means granting only the necessary permissions to users. For Azure Managed Instances, the minimum permissions required are similar to those assigned to non-SA accounts in SQL Server 2019 and earlier versions.
These permissions are as follows:
All Databases:
- VIEW DATABASE STATE
Master Database
-
VIEW SERVER STATE
-
EXEC permissions are required to execute the following procedures:
- xp_readerrorlog
- xp_instance_regenumvalues
- xp_enumerrorlogs
- xp_regread
MSDB Database
- SELECT permissions are required on the following tables:
- sysmail_event_log
- sysmail_allitems
- log_shipping_monitor_primary
- log_shipping_primary_secondaries
- sysalerts
- suspect_pages
- sysjobhistory
- sysjobschedules
- sysschedules
- sysjobs
- syscategories
- sysjobsteps
- backupset
- EXEC permissions on:
- agent_datetime
Query Store (optional when used)
- 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
Azure SQL Database Permissions
When monitoring an Azure SQL Database, the data collection account will need to be granted access to each database that will be monitored by WISdom. In addition to having access to the databases, permissions and roles are also required for collection. They are as follows:
Account Permissions
- VIEW DATABASE STATE
Server Roles
- ##MS_ServerStateReader##
- ##MS_DefinitionReader##
- ##MS_DatabaseConnector##
Query Store (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