Table: azure_mssql_virtual_machine - Query Azure SQL Server Virtual Machines using SQL

Azure SQL Server Virtual Machines are a fully managed service that provides the broadest SQL Server engine compatibility and native virtual network (VNET) support. This service offers a set of capabilities for enterprise-grade data workloads, enabling users to run their SQL Server workloads on a virtual machine in Azure. It is an ideal choice for applications requiring OS-level access.

Table Usage Guide

The azure_mssql_virtual_machine table provides insights into SQL Server instances running on Azure Virtual Machines. As a database administrator or a DevOps engineer, explore instance-specific details through this table, including configurations, status, and operational aspects. Utilize it to manage and monitor your SQL Server workloads running on Azure Virtual Machines effectively.

Examples

Basic info

Analyze the settings of your Azure SQL virtual machines to gain insights into their current status and configurations. This can help you understand the provisioning state, image offer, license type, and geographical location of each machine, aiding in resource management and optimization.

select
  id,
  name,
  type,
  provisioning_state,
  sql_image_offer,
  sql_server_license_type,
  region
from
  azure_mssql_virtual_machine;

select
  id,
  name,
  type,
  provisioning_state,
  sql_image_offer,
  sql_server_license_type,
  region
from
  azure_mssql_virtual_machine;

List failed virtual machines

Determine the areas in which virtual machines have failed to provision properly within your Azure SQL environment, allowing you to address and rectify these issues promptly.

select
  id,
  name,
  type,
  provisioning_state
from
  azure_mssql_virtual_machine
where
  provisioning_state = 'Failed';

select
  id,
  name,
  type,
  provisioning_state
from
  azure_mssql_virtual_machine
where
  provisioning_state = 'Failed';

Schema for azure_mssql_virtual_machine

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
auto_backup_settings	jsonb		Auto backup settings for SQL Server.
auto_patching_settings	jsonb		Auto patching settings for applying critical security updates to SQL virtual machine.
cloud_environment	text		The Azure Cloud Environment.
id	text		The resource ID.
identity	jsonb		Azure Active Directory identity for the SQL virtual machine.
key_vault_credential_settings	jsonb		Key vault credential settings for the SQL virtual machine.
name	text	=	The resource name.
provisioning_state	text		Provisioning state to track the async operation status.
region	text		The Azure region/location in which the resource is located.
resource_group	text	=	The resource group which holds this resource.
server_configurations_management_settings	jsonb		SQL server configuration management settings for the SQL virtual machine.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
sql_image_offer	text		SQL image offer for the SQL virtual machine.
sql_image_sku	text		SQL Server edition type. Possible values include: 'Developer', 'Express', 'Standard', 'Enterprise', 'Web'.
sql_management	text		SQL Server Management type. Possible values include: 'Full', 'LightWeight', 'NoAgent'.
sql_server_license_type	text		SQL server license type for the SQL virtual machine. Possible values include: 'PAYG', 'AHUB', 'DR'.
sql_virtual_machine_group_resource_id	text		ARM resource id of the SQL virtual machine group this SQL virtual machine is or will be part of.
storage_configuration_settings	jsonb		Storage configuration settings for the SQL virtual machine.
subscription_id	text	=, !=, ~~, ~~, !~~, !~~	The Azure Subscription ID in which the resource is located.
tags	jsonb		A map of tags for the resource.
title	text		Title of the resource.
type	text		The resource type.
virtual_machine_resource_id	text		ARM resource id of underlying virtual machine created from SQL marketplace image.
wsfc_domain_credentials	jsonb		Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- azure

You can pass the configuration to the command with the --config argument:

steampipe_export_azure --config '<your_config>' azure_mssql_virtual_machine