sql_virtual_machines

Creates, updates, deletes, gets or lists a sql_virtual_machines resource.

Overview

Name	sql_virtual_machines
Type	Resource
Id	azure.sql_virtual_machine.sql_virtual_machines

Fields

The following fields are returned by SELECT queries:

get

Name	Datatype	Description
id	string	Resource ID.
name	string	Resource name.
assessmentSettings	object	SQL best practices Assessment Settings.
autoBackupSettings	object	Auto backup settings for SQL Server.
autoPatchingSettings	object	Auto patching settings for applying critical security updates to SQL virtual machine.
enableAutomaticUpgrade	boolean	Enable automatic upgrade of Sql IaaS extension Agent.
identity	object	Azure Active Directory identity of the server.
keyVaultCredentialSettings	object	Key vault credential settings.
leastPrivilegeMode	string	SQL IaaS Agent least privilege mode. Known values are: "Enabled" and "NotSet".
location	string	Resource location. Required.
provisioningState	string	Provisioning state to track the async operation status.
serverConfigurationsManagementSettings	object	SQL Server configuration management settings.
sqlImageOffer	string	SQL image offer. Examples include SQL2016-WS2016, SQL2017-WS2016.
sqlImageSku	string	SQL Server edition type. Known values are: "Developer", "Express", "Standard", "Enterprise", and "Web".
sqlManagement	string	SQL Server Management type. Known values are: "Full", "LightWeight", and "NoAgent".
sqlServerLicenseType	string	SQL Server license type. Known values are: "PAYG", "AHUB", and "DR".
sqlVirtualMachineGroupResourceId	string	ARM resource id of the SQL virtual machine group this SQL virtual machine is or will be part of.
storageConfigurationSettings	object	Storage Configuration Settings.
systemData	object	Metadata pertaining to creation and last modification of the resource.
tags	object	Resource tags.
troubleshootingStatus	object	Troubleshooting status.
type	string	Resource type.
virtualMachineResourceId	string	ARM Resource id of underlying virtual machine created from SQL marketplace image.
wsfcDomainCredentials	object	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.
wsfcStaticIp	string	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.

list_by_sql_vm_group

Name	Datatype	Description
id	string	Resource ID.
name	string	Resource name.
assessmentSettings	object	SQL best practices Assessment Settings.
autoBackupSettings	object	Auto backup settings for SQL Server.
autoPatchingSettings	object	Auto patching settings for applying critical security updates to SQL virtual machine.
enableAutomaticUpgrade	boolean	Enable automatic upgrade of Sql IaaS extension Agent.
identity	object	Azure Active Directory identity of the server.
keyVaultCredentialSettings	object	Key vault credential settings.
leastPrivilegeMode	string	SQL IaaS Agent least privilege mode. Known values are: "Enabled" and "NotSet".
location	string	Resource location. Required.
provisioningState	string	Provisioning state to track the async operation status.
serverConfigurationsManagementSettings	object	SQL Server configuration management settings.
sqlImageOffer	string	SQL image offer. Examples include SQL2016-WS2016, SQL2017-WS2016.
sqlImageSku	string	SQL Server edition type. Known values are: "Developer", "Express", "Standard", "Enterprise", and "Web".
sqlManagement	string	SQL Server Management type. Known values are: "Full", "LightWeight", and "NoAgent".
sqlServerLicenseType	string	SQL Server license type. Known values are: "PAYG", "AHUB", and "DR".
sqlVirtualMachineGroupResourceId	string	ARM resource id of the SQL virtual machine group this SQL virtual machine is or will be part of.
storageConfigurationSettings	object	Storage Configuration Settings.
systemData	object	Metadata pertaining to creation and last modification of the resource.
tags	object	Resource tags.
troubleshootingStatus	object	Troubleshooting status.
type	string	Resource type.
virtualMachineResourceId	string	ARM Resource id of underlying virtual machine created from SQL marketplace image.
wsfcDomainCredentials	object	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.
wsfcStaticIp	string	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.

list_by_resource_group

Name	Datatype	Description
id	string	Resource ID.
name	string	Resource name.
assessmentSettings	object	SQL best practices Assessment Settings.
autoBackupSettings	object	Auto backup settings for SQL Server.
autoPatchingSettings	object	Auto patching settings for applying critical security updates to SQL virtual machine.
enableAutomaticUpgrade	boolean	Enable automatic upgrade of Sql IaaS extension Agent.
identity	object	Azure Active Directory identity of the server.
keyVaultCredentialSettings	object	Key vault credential settings.
leastPrivilegeMode	string	SQL IaaS Agent least privilege mode. Known values are: "Enabled" and "NotSet".
location	string	Resource location. Required.
provisioningState	string	Provisioning state to track the async operation status.
serverConfigurationsManagementSettings	object	SQL Server configuration management settings.
sqlImageOffer	string	SQL image offer. Examples include SQL2016-WS2016, SQL2017-WS2016.
sqlImageSku	string	SQL Server edition type. Known values are: "Developer", "Express", "Standard", "Enterprise", and "Web".
sqlManagement	string	SQL Server Management type. Known values are: "Full", "LightWeight", and "NoAgent".
sqlServerLicenseType	string	SQL Server license type. Known values are: "PAYG", "AHUB", and "DR".
sqlVirtualMachineGroupResourceId	string	ARM resource id of the SQL virtual machine group this SQL virtual machine is or will be part of.
storageConfigurationSettings	object	Storage Configuration Settings.
systemData	object	Metadata pertaining to creation and last modification of the resource.
tags	object	Resource tags.
troubleshootingStatus	object	Troubleshooting status.
type	string	Resource type.
virtualMachineResourceId	string	ARM Resource id of underlying virtual machine created from SQL marketplace image.
wsfcDomainCredentials	object	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.
wsfcStaticIp	string	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.

list

Name	Datatype	Description
id	string	Resource ID.
name	string	Resource name.
assessmentSettings	object	SQL best practices Assessment Settings.
autoBackupSettings	object	Auto backup settings for SQL Server.
autoPatchingSettings	object	Auto patching settings for applying critical security updates to SQL virtual machine.
enableAutomaticUpgrade	boolean	Enable automatic upgrade of Sql IaaS extension Agent.
identity	object	Azure Active Directory identity of the server.
keyVaultCredentialSettings	object	Key vault credential settings.
leastPrivilegeMode	string	SQL IaaS Agent least privilege mode. Known values are: "Enabled" and "NotSet".
location	string	Resource location. Required.
provisioningState	string	Provisioning state to track the async operation status.
serverConfigurationsManagementSettings	object	SQL Server configuration management settings.
sqlImageOffer	string	SQL image offer. Examples include SQL2016-WS2016, SQL2017-WS2016.
sqlImageSku	string	SQL Server edition type. Known values are: "Developer", "Express", "Standard", "Enterprise", and "Web".
sqlManagement	string	SQL Server Management type. Known values are: "Full", "LightWeight", and "NoAgent".
sqlServerLicenseType	string	SQL Server license type. Known values are: "PAYG", "AHUB", and "DR".
sqlVirtualMachineGroupResourceId	string	ARM resource id of the SQL virtual machine group this SQL virtual machine is or will be part of.
storageConfigurationSettings	object	Storage Configuration Settings.
systemData	object	Metadata pertaining to creation and last modification of the resource.
tags	object	Resource tags.
troubleshootingStatus	object	Troubleshooting status.
type	string	Resource type.
virtualMachineResourceId	string	ARM Resource id of underlying virtual machine created from SQL marketplace image.
wsfcDomainCredentials	object	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.
wsfcStaticIp	string	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.

Methods

The following methods are available for this resource:

Name	Accessible by	Required Params	Optional Params	Description
get	select	resource_group_name, sql_virtual_machine_name, subscription_id	$expand	Gets a SQL virtual machine.
list_by_sql_vm_group	select	resource_group_name, sql_virtual_machine_group_name, subscription_id		Gets the list of sql virtual machines in a SQL virtual machine group.
list_by_resource_group	select	resource_group_name, subscription_id		Gets all SQL virtual machines in a resource group.
list	select	subscription_id		Gets all SQL virtual machines in a subscription.
create_or_update	insert	resource_group_name, sql_virtual_machine_name, subscription_id, location		Creates or updates a SQL virtual machine.
update	update	resource_group_name, sql_virtual_machine_name, subscription_id		Updates a SQL virtual machine.
create_or_update	replace	resource_group_name, sql_virtual_machine_name, subscription_id, location		Creates or updates a SQL virtual machine.
delete	delete	resource_group_name, sql_virtual_machine_name, subscription_id		Deletes a SQL virtual machine.
start_assessment	exec	resource_group_name, sql_virtual_machine_name, subscription_id		Starts SQL best practices Assessment on SQL virtual machine.
redeploy	exec	resource_group_name, sql_virtual_machine_name, subscription_id		Uninstalls and reinstalls the SQL IaaS Extension.

Parameters

Parameters can be passed in the WHERE clause of a query. Check the Methods section to see which parameters are required or optional for each operation.

Name	Datatype	Description
resource_group_name	string	Name of the resource group that contains the resource. You can obtain this value from the Azure Resource Manager API or the portal. Required.
sql_virtual_machine_group_name	string	Name of the SQL virtual machine group. Required.
sql_virtual_machine_name	string	Name of the SQL virtual machine. Required.
subscription_id	string
$expand	string	The child resources to include in the response. Default value is None.

SELECT examples

get

Gets a SQL virtual machine.

SELECT
id,
name,
assessmentSettings,
autoBackupSettings,
autoPatchingSettings,
enableAutomaticUpgrade,
identity,
keyVaultCredentialSettings,
leastPrivilegeMode,
location,
provisioningState,
serverConfigurationsManagementSettings,
sqlImageOffer,
sqlImageSku,
sqlManagement,
sqlServerLicenseType,
sqlVirtualMachineGroupResourceId,
storageConfigurationSettings,
systemData,
tags,
troubleshootingStatus,
type,
virtualMachineResourceId,
wsfcDomainCredentials,
wsfcStaticIp
FROM azure.sql_virtual_machine.sql_virtual_machines
WHERE resource_group_name = '{{ resource_group_name }}' -- required
AND sql_virtual_machine_name = '{{ sql_virtual_machine_name }}' -- required
AND subscription_id = '{{ subscription_id }}' -- required
AND $expand = '{{ $expand }}'
;

list_by_sql_vm_group

Gets the list of sql virtual machines in a SQL virtual machine group.

SELECT
id,
name,
assessmentSettings,
autoBackupSettings,
autoPatchingSettings,
enableAutomaticUpgrade,
identity,
keyVaultCredentialSettings,
leastPrivilegeMode,
location,
provisioningState,
serverConfigurationsManagementSettings,
sqlImageOffer,
sqlImageSku,
sqlManagement,
sqlServerLicenseType,
sqlVirtualMachineGroupResourceId,
storageConfigurationSettings,
systemData,
tags,
troubleshootingStatus,
type,
virtualMachineResourceId,
wsfcDomainCredentials,
wsfcStaticIp
FROM azure.sql_virtual_machine.sql_virtual_machines
WHERE resource_group_name = '{{ resource_group_name }}' -- required
AND sql_virtual_machine_group_name = '{{ sql_virtual_machine_group_name }}' -- required
AND subscription_id = '{{ subscription_id }}' -- required
;

list_by_resource_group

Gets all SQL virtual machines in a resource group.

SELECT
id,
name,
assessmentSettings,
autoBackupSettings,
autoPatchingSettings,
enableAutomaticUpgrade,
identity,
keyVaultCredentialSettings,
leastPrivilegeMode,
location,
provisioningState,
serverConfigurationsManagementSettings,
sqlImageOffer,
sqlImageSku,
sqlManagement,
sqlServerLicenseType,
sqlVirtualMachineGroupResourceId,
storageConfigurationSettings,
systemData,
tags,
troubleshootingStatus,
type,
virtualMachineResourceId,
wsfcDomainCredentials,
wsfcStaticIp
FROM azure.sql_virtual_machine.sql_virtual_machines
WHERE resource_group_name = '{{ resource_group_name }}' -- required
AND subscription_id = '{{ subscription_id }}' -- required
;

list

Gets all SQL virtual machines in a subscription.

SELECT
id,
name,
assessmentSettings,
autoBackupSettings,
autoPatchingSettings,
enableAutomaticUpgrade,
identity,
keyVaultCredentialSettings,
leastPrivilegeMode,
location,
provisioningState,
serverConfigurationsManagementSettings,
sqlImageOffer,
sqlImageSku,
sqlManagement,
sqlServerLicenseType,
sqlVirtualMachineGroupResourceId,
storageConfigurationSettings,
systemData,
tags,
troubleshootingStatus,
type,
virtualMachineResourceId,
wsfcDomainCredentials,
wsfcStaticIp
FROM azure.sql_virtual_machine.sql_virtual_machines
WHERE subscription_id = '{{ subscription_id }}' -- required
;

INSERT examples

create_or_update

Creates or updates a SQL virtual machine.

INSERT INTO azure.sql_virtual_machine.sql_virtual_machines (
location,
tags,
identity,
properties,
resource_group_name,
sql_virtual_machine_name,
subscription_id
)
SELECT 
'{{ location }}' /* required */,
'{{ tags }}',
'{{ identity }}',
'{{ properties }}',
'{{ resource_group_name }}',
'{{ sql_virtual_machine_name }}',
'{{ subscription_id }}'
RETURNING
id,
name,
identity,
location,
properties,
systemData,
tags,
type
;

Manifest

# Description fields are for documentation purposes
- name: sql_virtual_machines
props:
  - name: resource_group_name
    value: "{{ resource_group_name }}"
    description: Required parameter for the sql_virtual_machines resource.
  - name: sql_virtual_machine_name
    value: "{{ sql_virtual_machine_name }}"
    description: Required parameter for the sql_virtual_machines resource.
  - name: subscription_id
    value: "{{ subscription_id }}"
    description: Required parameter for the sql_virtual_machines resource.
  - name: location
    value: "{{ location }}"
    description: |
      Resource location. Required.
  - name: tags
    value: "{{ tags }}"
    description: |
      Resource tags.
  - name: identity
    description: |
      Azure Active Directory identity of the server.
    value:
      principalId: "{{ principalId }}"
      type: "{{ type }}"
      tenantId: "{{ tenantId }}"
  - name: properties
    value:
      virtualMachineResourceId: "{{ virtualMachineResourceId }}"
      sqlImageOffer: "{{ sqlImageOffer }}"
      sqlServerLicenseType: "{{ sqlServerLicenseType }}"
      sqlManagement: "{{ sqlManagement }}"
      leastPrivilegeMode: "{{ leastPrivilegeMode }}"
      sqlImageSku: "{{ sqlImageSku }}"
      sqlVirtualMachineGroupResourceId: "{{ sqlVirtualMachineGroupResourceId }}"
      wsfcDomainCredentials:
        clusterBootstrapAccountPassword: "{{ clusterBootstrapAccountPassword }}"
        clusterOperatorAccountPassword: "{{ clusterOperatorAccountPassword }}"
        sqlServiceAccountPassword: "{{ sqlServiceAccountPassword }}"
      wsfcStaticIp: "{{ wsfcStaticIp }}"
      autoPatchingSettings:
        enable: {{ enable }}
        dayOfWeek: "{{ dayOfWeek }}"
        maintenanceWindowStartingHour: {{ maintenanceWindowStartingHour }}
        maintenanceWindowDuration: {{ maintenanceWindowDuration }}
      autoBackupSettings:
        enable: {{ enable }}
        enableEncryption: {{ enableEncryption }}
        retentionPeriod: {{ retentionPeriod }}
        storageAccountUrl: "{{ storageAccountUrl }}"
        storageContainerName: "{{ storageContainerName }}"
        storageAccessKey: "{{ storageAccessKey }}"
        password: "{{ password }}"
        backupSystemDbs: {{ backupSystemDbs }}
        backupScheduleType: "{{ backupScheduleType }}"
        fullBackupFrequency: "{{ fullBackupFrequency }}"
        daysOfWeek:
          - "{{ daysOfWeek }}"
        fullBackupStartTime: {{ fullBackupStartTime }}
        fullBackupWindowHours: {{ fullBackupWindowHours }}
        logBackupFrequency: {{ logBackupFrequency }}
      keyVaultCredentialSettings:
        enable: {{ enable }}
        credentialName: "{{ credentialName }}"
        azureKeyVaultUrl: "{{ azureKeyVaultUrl }}"
        servicePrincipalName: "{{ servicePrincipalName }}"
        servicePrincipalSecret: "{{ servicePrincipalSecret }}"
      serverConfigurationsManagementSettings:
        sqlConnectivityUpdateSettings:
          connectivityType: "{{ connectivityType }}"
          port: {{ port }}
          sqlAuthUpdateUserName: "{{ sqlAuthUpdateUserName }}"
          sqlAuthUpdatePassword: "{{ sqlAuthUpdatePassword }}"
        sqlWorkloadTypeUpdateSettings:
          sqlWorkloadType: "{{ sqlWorkloadType }}"
        sqlStorageUpdateSettings:
          diskCount: {{ diskCount }}
          startingDeviceId: {{ startingDeviceId }}
          diskConfigurationType: "{{ diskConfigurationType }}"
        additionalFeaturesServerConfigurations:
          isRServicesEnabled: {{ isRServicesEnabled }}
        sqlInstanceSettings:
          collation: "{{ collation }}"
          maxDop: {{ maxDop }}
          isOptimizeForAdHocWorkloadsEnabled: {{ isOptimizeForAdHocWorkloadsEnabled }}
          minServerMemoryMB: {{ minServerMemoryMB }}
          maxServerMemoryMB: {{ maxServerMemoryMB }}
          isLpimEnabled: {{ isLpimEnabled }}
          isIfiEnabled: {{ isIfiEnabled }}
        azureAdAuthenticationSettings:
          clientId: "{{ clientId }}"
      storageConfigurationSettings:
        sqlDataSettings:
          luns:
            - {{ luns }}
          defaultFilePath: "{{ defaultFilePath }}"
        sqlLogSettings:
          luns:
            - {{ luns }}
          defaultFilePath: "{{ defaultFilePath }}"
        sqlTempDbSettings:
          dataFileSize: {{ dataFileSize }}
          dataGrowth: {{ dataGrowth }}
          logFileSize: {{ logFileSize }}
          logGrowth: {{ logGrowth }}
          dataFileCount: {{ dataFileCount }}
          persistFolder: {{ persistFolder }}
          persistFolderPath: "{{ persistFolderPath }}"
          luns:
            - {{ luns }}
          defaultFilePath: "{{ defaultFilePath }}"
        sqlSystemDbOnDataDisk: {{ sqlSystemDbOnDataDisk }}
        diskConfigurationType: "{{ diskConfigurationType }}"
        storageWorkloadType: "{{ storageWorkloadType }}"
      assessmentSettings:
        enable: {{ enable }}
        runImmediately: {{ runImmediately }}
        schedule:
          enable: {{ enable }}
          weeklyInterval: {{ weeklyInterval }}
          monthlyOccurrence: {{ monthlyOccurrence }}
          dayOfWeek: "{{ dayOfWeek }}"
          startTime: "{{ startTime }}"
      enableAutomaticUpgrade: {{ enableAutomaticUpgrade }}

UPDATE examples

update

Updates a SQL virtual machine.

UPDATE azure.sql_virtual_machine.sql_virtual_machines
SET 
tags = '{{ tags }}'
WHERE 
resource_group_name = '{{ resource_group_name }}' --required
AND sql_virtual_machine_name = '{{ sql_virtual_machine_name }}' --required
AND subscription_id = '{{ subscription_id }}' --required
RETURNING
id,
name,
identity,
location,
properties,
systemData,
tags,
type;

REPLACE examples

create_or_update

Creates or updates a SQL virtual machine.

REPLACE azure.sql_virtual_machine.sql_virtual_machines
SET 
location = '{{ location }}',
tags = '{{ tags }}',
identity = '{{ identity }}',
properties = '{{ properties }}'
WHERE 
resource_group_name = '{{ resource_group_name }}' --required
AND sql_virtual_machine_name = '{{ sql_virtual_machine_name }}' --required
AND subscription_id = '{{ subscription_id }}' --required
AND location = '{{ location }}' --required
RETURNING
id,
name,
identity,
location,
properties,
systemData,
tags,
type;

DELETE examples

delete

Deletes a SQL virtual machine.

DELETE FROM azure.sql_virtual_machine.sql_virtual_machines
WHERE resource_group_name = '{{ resource_group_name }}' --required
AND sql_virtual_machine_name = '{{ sql_virtual_machine_name }}' --required
AND subscription_id = '{{ subscription_id }}' --required
;

Lifecycle Methods

start_assessment

Starts SQL best practices Assessment on SQL virtual machine.

EXEC azure.sql_virtual_machine.sql_virtual_machines.start_assessment 
@resource_group_name='{{ resource_group_name }}' --required, 
@sql_virtual_machine_name='{{ sql_virtual_machine_name }}' --required, 
@subscription_id='{{ subscription_id }}' --required
;

redeploy

Uninstalls and reinstalls the SQL IaaS Extension.

EXEC azure.sql_virtual_machine.sql_virtual_machines.redeploy 
@resource_group_name='{{ resource_group_name }}' --required, 
@sql_virtual_machine_name='{{ sql_virtual_machine_name }}' --required, 
@subscription_id='{{ subscription_id }}' --required
;