Azure SQL Managed Instance
Introduction
Azure SQL MI O11ySource is a comprehensive solution designed to provide visibility, analysis, and actionable insights into the performance, behavior, and health of your Azure SQL Managed Instance infrastructure. It follows a systematic approach to gather, monitor, and interpret metrics, and key indicators from Azure SQL Managed Instances, enabling deep visibility into database operations.
Getting Started
Compatibility
Supported compatibility levels: 100–160. If your instance uses a higher level, validate support before onboarding.
Data Collection Method
vuSmartMaps collects Azure SQL Managed Instance metrics through a data collector deployed within the vuSmartMaps platform, which uses JDBC to retrieve performance and health metrics.
Prerequisites
Dependent Configuration
To configure this O11ySource, create a 'credential' of type 'user' under the 'Definition' tab.
Inputs for Configuring Data Source
- Instance Name: Please enter the name of the Azure SQL Managed Instance instance. This should be a unique identifier for the specific Azure SQL Managed Instance deployment you want to monitor.
- DB Details: Provide the database connection details.
- Azure SQL Managed Instance: Azure SQL Managed Instance address needs to be a valid FQDN
- Port: Enter a valid port number.
- Credential: This field is required.
- Metrics Collection Queries: These queries are utilized for Azure SQL Managed Instance server's metrics.
- Query: Write the query to be executed on the customer database.
- Polling Interval [seconds]: The interval must be between 180 and 86,400 seconds.
- Custom SQL Queries: Optional.
- Query Name: The name of the query.
- Query Topic: Assign a query topic to route query data to a specific destination topic. Ensure the target topic is created prior to configuring the source.
nan
Firewall Requirement
To collect data from this O11ySource, ensure the following ports are opened:
| Source IP | Destination IP | Destination Port | Protocol | Direction |
|---|---|---|---|---|
| vuSmartMaps Collection/Ingress node IP | IP address or FQDN of Azure SQL Managed Instance | 1433(private endpoint) or 3342(public endpoint) | TCP | Outbound |
*Before providing the firewall requirements, please update the port based on the customer environment.
Configuring the Target
Create a login on every Azure SQL Managed Instance you want to monitor and create a read-only vunet user with the required permissions.
Open SQL Server Management Studio (SSMS), connect to the Azure SQL Managed Instance, and run:
USE master;
CREATE LOGIN vunet WITH PASSWORD = '<PASSWORD>';
CREATE USER vunet FOR LOGIN vunet;
GRANT SELECT ON sys.master_files TO vunet;
GRANT SELECT ON sys.dm_os_performance_counters TO vunet;
GRANT VIEW SERVER STATE TO vunet;
GRANT VIEW ANY DEFINITION TO vunet;
GRANT CONNECT ANY DATABASE TO vunet;
USE msdb;
CREATE USER vunet FOR LOGIN vunet;
GRANT SELECT ON msdb.dbo.sysjobhistory TO vunet;
GRANT SELECT ON msdb.dbo.sysjobschedules TO vunet;
GRANT SELECT ON msdb.dbo.sysjobs TO vunet;
Configuration Steps
Metrics Collected
| Name | Description | Data Type |
|---|---|---|
| average_cpu_utilization_in_percent | Average CPU utilization percentage. | Float32 |
| average_data_io_utilization_in_percent | Average Data IO utilization percentage. | Float32 |
| average_log_write_io_utilization_in_percent | Average Log Write IO utilization percentage. | Float32 |
| average_memory_usage_utilization_in_percent | Average memory usage utilization percentage. | Float32 |
| avg_cpu_time_msec | Average CPU time in milliseconds for a query. | Float64 |
| avg_dtu_percent | Average DTU usage percentage. | Float32 |
| avg_elapsed_time_msec | Average elapsed time for a query in milliseconds. | Float64 |
| blocking_session_id | Session ID of the blocker in blocking scenario. | Nullable(UInt32) |
| bs_statement_text | Statement text of the blocking session. | String |
| clerk_type | Type of memory clerk. | String |
| client_host_name | Name of the client host initiating the session. | String |
| command | Command being executed (e.g., SELECT, INSERT). | String |
| connection_start_time | Start time of the database connection. | String |
| counter | Metric or performance counter name. | String |
| counter_type | Type of the counter. | String |
| cpu_time_ms | CPU time consumed in milliseconds. | UInt64 |
| cpu_time_pct | CPU time as a percentage. | Float32 |
| database_name | Name of the Azure SQL database. | String |
| db_buffer_bytes | Buffer cache usage in bytes. | Float64 |
| db_buffer_MB | Buffer cache usage in megabytes. | Float64 |
| db_buffer_pages | Number of database pages in the buffer. | UInt64 |
| db_buffer_percent | Percentage of buffer usage. | Float32 |
| dbname | Name of the database context. | String |
| equality_columns | Columns used in equality conditions. | String |
| exectime | Execution time of the statement. | String |
| execution_count | Number of times the query was executed. | UInt64 |
| execution_time | Total query execution duration. | String |
| file_id | ID of the file. | String |
| file_name | Logical file name. | String |
| file_physical_name | Physical path of the file on disk. | String |
| file_state | State of the file (e.g., online, offline). | String |
| file_type | Type of file (e.g., data, log). | String |
| granted_query_memory_pages | Memory pages granted to the query. | UInt32 |
| host_name | Name of the host machine. | String |
| included_columns | Non-key columns included in an index. | String |
| inequality_columns | Columns used in inequality predicates. | String |
| instance | SQL Server instance name. | String |
| instance_type | Type of SQL instance (e.g., primary, replica). | String |
| io_stall | Total I/O stall in milliseconds. | UInt64 |
| io_stall_queued_read_ms | Time read I/O spent waiting in queue. | UInt64 |
| io_stall_queued_write_ms | Time write I/O spent waiting in queue. | UInt64 |
| io_stall_read_ms | Total stall time on read operations. | UInt64 |
| io_stall_write_ms | Total stall time on write operations. | UInt64 |
| is_hadr_enabled | Indicates if HADR is enabled. | String |
| is_read_only | Indicates if the DB is read-only (1 = Yes). | Int8 |
| last_elapsed_time_msec | Last execution elapsed time in ms. | Float64 |
| last_request_end_time | Time when the last request ended. | String |
| last_request_start_time | Time when the last request started. | String |
| last_worker_time_msec | Last worker thread time in milliseconds. | Float64 |
| logical_filename | Logical name of the file in SQL Server. | String |
| logical_reads | Number of logical reads. | UInt64 |
| login_name | Login name used for the session. | String |
| login_time | Time when the user logged in. | String |
| maximum_cpu_utilization_in_percent | Maximum CPU usage. | Float32 |
| maximum_data_io_utilization_in_percent | Maximum Data IO usage. | Float32 |
| maximum_log_write_io_utilization_in_percent | Maximum Log Write IO usage. | Float32 |
| maximum_memory_usage_utilization_in_percent | Maximum memory usage percentage. | Float32 |
| max_size_mb | Maximum allowed size of the database in MB. | Nullable(Float64) |
| max_wait_time_ms | Maximum wait time in milliseconds. | UInt64 |
| measurement | Metric name or measurement type. | String |
| memory_usage_kb | Memory usage in KB. | UInt64 |
| metrics_group | Grouping or categorization of metrics. | String |
| name | Generic name or identifier. | String |
| nt_user_name | Windows NT user name. | String |
| num_of_bytes_read | Number of bytes read. | Float64 |
| num_of_bytes_written | Number of bytes written. | Float64 |
| num_of_reads | Total number of read operations. | UInt64 |
| num_of_writes | Total number of write operations. | UInt64 |
| objectid | ID of the object referenced. | UInt64 |
| open_transaction | Number of open transactions. | UInt32 |
| other_process_cpu | CPU used by other processes. | Float64 |
| percent_complete | Completion percentage of a task. | Float32 |
| physical_filename | Physical file name. | String |
| program_name | Application name that initiated the request. | String |
| query_hash | Hash of the query text. | UInt64 |
| query_plan_hash | Hash of the query execution plan. | UInt64 |
| query_type | Type of query (e.g., adhoc, proc). | String |
| reads | Total number of reads. | UInt64 |
| read_bytes | Bytes read from disk. | Float64 |
| read_latency_ms | Latency of read operations in ms. | Float64 |
| recovery_model_desc | Recovery model (e.g., FULL, SIMPLE). | String |
| request_id | Request ID within the session. | UInt32 |
| resource_wait_ms | Time spent waiting for a resource. | UInt64 |
| sample_ms | Duration of the sample in milliseconds. | Float64 |
| server_name | SQL Server name. | String |
| session_db_name | Name of the database for the session. | String |
| session_id | Session identifier. | UInt32 |
| session_status | Status of the session (e.g., running). | String |
| signal_wait_time_ms | Time waiting for a signal (thread to run). | UInt64 |
| size_kb | File or object size in KB. | Float64 |
| size_on_disk_bytes | Actual size on disk in bytes. | Float64 |
| source_id | ID for the source of the metric. | String |
| sqlserver_process_cpu | CPU consumed by SQL Server process. | Float64 |
| sqlserver_start_time | Time when SQL Server started. | String |
| sql_instance | SQL instance name. | String |
| statement_text | Query or statement being executed. | String |
| state_desc | Description of the object state. | String |
| status | Status of the object/session. | String |
| stmt_object_name | Name of the object referenced by statement. | String |
| system_idle_cpu | CPU percentage in idle state. | Float64 |
| table_name | Name of the table involved. | String |
| target | Target of the performance counter. | String |
| task_state | State of the task. | String |
| timestamp | Timestamp of the metric record. | DateTime |
| total_buffer | Total memory buffer in use. | UInt32 |
| total_cost | Estimated cost of query execution. | UInt64 |
| total_elapsed_time_ms | Total elapsed time of the query. | Float64 |
| total_elapsed_time_msec | Same as above (duplicate representation). | Float64 |
| total_logical_reads | Total number of logical reads. | UInt64 |
| total_logical_writes | Total number of logical writes. | UInt64 |
| total_physical_reads | Total number of physical reads. | UInt64 |
| total_space_mb | Total space used in MB. | Float64 |
| total_worker_time_msec | Total worker time for execution. | Float64 |
| transaction_isolation_level | Isolation level of the transaction. | String |
| uptime_sec | SQL Server uptime in seconds. | UInt64 |
| user_access_desc | Access level of the user. | String |
| value | Value of the performance counter. | Float64 |
| waiting_tasks_count | Number of tasks waiting on this wait type. | UInt64 |
| wait_category | Category of the wait (e.g., CPU, IO). | String |
| wait_resource | Resource on which the session is waiting. | String |
| wait_time_ms | Time spent waiting in milliseconds. | UInt64 |
| wait_type | Type of wait (e.g., LCK_M_SCH_M). | String |
| writes | Number of write operations. | UInt64 |
| write_bytes | Bytes written to disk. | Float64 |
| write_latency_ms | Write latency in milliseconds. | Float64 |
| sql_version | SQL Server version. | String |
| last_execution_time | Timestamp of last execution. | String |
| last_elapsed_time | Elapsed time of the last execution. | String |
| last_worker_time | Worker thread time of last execution. | String |
| full_backup_size_bytes | Size of the last full backup of the database in bytes. | Float64 |
| last_full_backup_time | Timestamp of the last full backup, in milliseconds since the epoch. | UInt64 |
| last_transaction_log_backup_time | Timestamp of the last transaction log backup, in milliseconds since the epoch. | UInt64 |
| transaction_log_backup_size_bytes | Size of the last transaction log backup in bytes. | UInt64 |
| recovery_model | Recovery model of the database (e.g., Full, Simple, Bulk-logged). | LowCardinality(String) |
