Monitoring SQL Instances
There are various types of SQL instances that can be monitored, each requiring specific permissions for successful data collection.
Permissions Overview
Historically, the account used to collect data from a SQL instance has been a member of the System Administrators (SA) group. However, with the advent of cloud-based SQL databases and new SQL versions, the required permissions have evolved.
- SA Permissions: If the collection account has SA permissions, WISdom can collect all SQL metrics.
- Alternative Permissions: If SA permissions are not available, refer to the appropriate setup guide for configuring permissions based on the specific SQL version or type being monitored.
New Permissions Available with SQL 2022
With the introduction of SQL 2022, Microsoft has introduced new roles that allow a monitoring account to access system tables and Dynamic Management Views (DMVs) without granting access to all user tables and data. This provides a more secure alternative to using SA permissions. Andreas Wolter documented these new roles while at Microsoft, read the details on the SQL Server Blog.
For detailed instructions on setting up permissions for your specific SQL instance, please refer to the relevant documentation section.
SQL Instance Collection
This information outlines the permissions needed to monitor a SQL instance, whether it's on-premises or hosted on a cloud VM. If the account has SA (System Administrator) permissions, it will be fully equipped for collections. If SA permissions are not granted, refer to the page for alternative collection account permissions.
Azure SQL Collection
When collecting metrics from an Azure Managed Instance or Azure SQL Database, SA permissions are not available. This guide details the required permissions to effectively collect metrics from both Azure Managed Instances and Azure SQL Databases.
AWS SQL Collection
Permissions vary based on whether you're working with an ECS instance or an RDS instance. For ECS instances, using SA permissions ensures the account is ready for SQL collections. This guide details the necessary permissions for RDS instances and alternative permission accounts.