Azure SQL Collection

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 efficiently collect metrics and monitor performance, it's best to host a virtual machine running WISdom services in the same Azure region as the managed instance(s) or SQL database(s). This proximity reduces latency issues that could impede data collection. Alternatively, you can host the data collector locally, but this may introduce latency challenges, potentially affecting the reliability and timeliness of the collected data. Ensure you 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 Permissions

• VIEW SERVER STATE

• EXEC permissions are required to execute the following procedures:

  • xp_readerrorlog
  • xp_instance_regenumvalues
  • xp_enumerrorlogs
  • xp_regread

MSDB Database Permissions

• 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 Permissions (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 and Server Roles

• VIEW DATABASE STATE

Server Roles

• ##MS_ServerStateReader##
• ##MS_DefinitionReader##
• ##MS_DatabaseConnector##

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