Microsoft.SqlVirtualMachine sqlVirtualMachines 2023-01-01-preview

Article
10/18/2024

Bicep resource definition

The sqlVirtualMachines resource type can be deployed with operations that target:

Resource groups - See resource group deployment commands

For a list of changed properties in each API version, see change log.

Resource format

To create a Microsoft.SqlVirtualMachine/sqlVirtualMachines resource, add the following Bicep to your template.

resource symbolicname 'Microsoft.SqlVirtualMachine/sqlVirtualMachines@2023-01-01-preview' = {
  identity: {
    type: 'string'
  }
  location: 'string'
  name: 'string'
  properties: {
    assessmentSettings: {
      enable: bool
      runImmediately: bool
      schedule: {
        dayOfWeek: 'string'
        enable: bool
        monthlyOccurrence: int
        startTime: 'string'
        weeklyInterval: int
      }
    }
    autoBackupSettings: {
      backupScheduleType: 'string'
      backupSystemDbs: bool
      daysOfWeek: [
        'string'
      ]
      enable: bool
      enableEncryption: bool
      fullBackupFrequency: 'string'
      fullBackupStartTime: int
      fullBackupWindowHours: int
      logBackupFrequency: int
      password: 'string'
      retentionPeriod: int
      storageAccessKey: 'string'
      storageAccountUrl: 'string'
      storageContainerName: 'string'
    }
    autoPatchingSettings: {
      additionalVmPatch: 'string'
      dayOfWeek: 'string'
      enable: bool
      maintenanceWindowDuration: int
      maintenanceWindowStartingHour: int
    }
    enableAutomaticUpgrade: bool
    keyVaultCredentialSettings: {
      azureKeyVaultUrl: 'string'
      credentialName: 'string'
      enable: bool
      servicePrincipalName: 'string'
      servicePrincipalSecret: 'string'
    }
    leastPrivilegeMode: 'string'
    serverConfigurationsManagementSettings: {
      additionalFeaturesServerConfigurations: {
        isRServicesEnabled: bool
      }
      azureAdAuthenticationSettings: {
        clientId: 'string'
      }
      sqlConnectivityUpdateSettings: {
        connectivityType: 'string'
        port: int
        sqlAuthUpdatePassword: 'string'
        sqlAuthUpdateUserName: 'string'
      }
      sqlInstanceSettings: {
        collation: 'string'
        isIfiEnabled: bool
        isLpimEnabled: bool
        isOptimizeForAdHocWorkloadsEnabled: bool
        maxDop: int
        maxServerMemoryMB: int
        minServerMemoryMB: int
      }
      sqlStorageUpdateSettings: {
        diskConfigurationType: 'string'
        diskCount: int
        startingDeviceId: int
      }
      sqlWorkloadTypeUpdateSettings: {
        sqlWorkloadType: 'string'
      }
    }
    sqlImageOffer: 'string'
    sqlImageSku: 'string'
    sqlManagement: 'string'
    sqlServerLicenseType: 'string'
    sqlVirtualMachineGroupResourceId: 'string'
    storageConfigurationSettings: {
      diskConfigurationType: 'string'
      enableStorageConfigBlade: bool
      sqlDataSettings: {
        defaultFilePath: 'string'
        luns: [
          int
        ]
        useStoragePool: bool
      }
      sqlLogSettings: {
        defaultFilePath: 'string'
        luns: [
          int
        ]
        useStoragePool: bool
      }
      sqlSystemDbOnDataDisk: bool
      sqlTempDbSettings: {
        dataFileCount: int
        dataFileSize: int
        dataGrowth: int
        defaultFilePath: 'string'
        logFileSize: int
        logGrowth: int
        luns: [
          int
        ]
        persistFolder: bool
        persistFolderPath: 'string'
        useStoragePool: bool
      }
      storageWorkloadType: 'string'
    }
    virtualMachineResourceId: 'string'
    wsfcDomainCredentials: {
      clusterBootstrapAccountPassword: 'string'
      clusterOperatorAccountPassword: 'string'
      sqlServiceAccountPassword: 'string'
    }
    wsfcStaticIp: 'string'
  }
  tags: {
    {customized property}: 'string'
  }
}

Property values

AADAuthenticationSettings

Name	Description	Value
clientId	The client Id of the Managed Identity to query Microsoft Graph API. An empty string must be used for the system assigned Managed Identity	string

AdditionalFeaturesServerConfigurations

Name	Description	Value
isRServicesEnabled	Enable or disable R services (SQL 2016 onwards).	bool

AssessmentSettings

Name	Description	Value
enable	Enable or disable SQL best practices Assessment feature on SQL virtual machine.	bool
runImmediately	Run SQL best practices Assessment immediately on SQL virtual machine.	bool
schedule	Schedule for SQL best practices Assessment.	Schedule

AutoBackupSettings

Name	Description	Value
backupScheduleType	Backup schedule type.	'Automated' 'Manual'
backupSystemDbs	Include or exclude system databases from auto backup.	bool
daysOfWeek	Days of the week for the backups when FullBackupFrequency is set to Weekly.	String array containing any of: 'Friday' 'Monday' 'Saturday' 'Sunday' 'Thursday' 'Tuesday' 'Wednesday'
enable	Enable or disable autobackup on SQL virtual machine.	bool
enableEncryption	Enable or disable encryption for backup on SQL virtual machine.	bool
fullBackupFrequency	Frequency of full backups. In both cases, full backups begin during the next scheduled time window.	'Daily' 'Weekly'
fullBackupStartTime	Start time of a given day during which full backups can take place. 0-23 hours.	int
fullBackupWindowHours	Duration of the time window of a given day during which full backups can take place. 1-23 hours.	int
logBackupFrequency	Frequency of log backups. 5-60 minutes.	int
password	Password for encryption on backup.	string
retentionPeriod	Retention period of backup: 1-90 days.	int
storageAccessKey	Storage account key where backup will be taken to.	string
storageAccountUrl	Storage account url where backup will be taken to.	string
storageContainerName	Storage container name where backup will be taken to.	string

AutoPatchingSettings

Name	Description	Value
additionalVmPatch	Additional Patch to be enable or enabled on the SQL Virtual Machine.	'MicrosoftUpdate' 'NotSet'
dayOfWeek	Day of week to apply the patch on.	'Everyday' 'Friday' 'Monday' 'Saturday' 'Sunday' 'Thursday' 'Tuesday' 'Wednesday'
enable	Enable or disable autopatching on SQL virtual machine.	bool
maintenanceWindowDuration	Duration of patching.	int
maintenanceWindowStartingHour	Hour of the day when patching is initiated. Local VM time.	int

KeyVaultCredentialSettings

Name	Description	Value
azureKeyVaultUrl	Azure Key Vault url.	string
credentialName	Credential name.	string
enable	Enable or disable key vault credential setting.	bool
servicePrincipalName	Service principal name to access key vault.	string
servicePrincipalSecret	Service principal name secret to access key vault.	string

Microsoft.SqlVirtualMachine/sqlVirtualMachines

Name	Description	Value
identity	DO NOT USE. This value will be deprecated. Azure Active Directory identity of the server.	ResourceIdentity
location	Resource location.	string (required)
name	The resource name	string Constraints: Min length = 1 Max length = 1 Pattern = `^((?!_)[^\\/"'\[\]:\|<>+=;,?*@&]{1,64}(?<![.-]))$` (required)
properties	Resource properties.	SqlVirtualMachineProperties
tags	Resource tags.	TrackedResourceTags

ResourceIdentity

Name	Description	Value
type	The identity type. Set this to 'SystemAssigned' in order to automatically create and assign an Azure Active Directory principal for the resource.	'None' 'SystemAssigned'

Schedule

Name	Description	Value
dayOfWeek	Day of the week to run assessment.	'Friday' 'Monday' 'Saturday' 'Sunday' 'Thursday' 'Tuesday' 'Wednesday'
enable	Enable or disable assessment schedule on SQL virtual machine.	bool
monthlyOccurrence	Occurrence of the DayOfWeek day within a month to schedule assessment. Takes values: 1,2,3,4 and -1. Use -1 for last DayOfWeek day of the month	int
startTime	Time of the day in HH:mm format. Eg. 17:30	string
weeklyInterval	Number of weeks to schedule between 2 assessment runs. Takes value from 1-6	int

ServerConfigurationsManagementSettings

Name	Description	Value
additionalFeaturesServerConfigurations	Additional SQL feature settings.	AdditionalFeaturesServerConfigurations
azureAdAuthenticationSettings	Azure AD authentication Settings.	AADAuthenticationSettings
sqlConnectivityUpdateSettings	SQL connectivity type settings.	SqlConnectivityUpdateSettings
sqlInstanceSettings	SQL Instance settings.	SQLInstanceSettings
sqlStorageUpdateSettings	SQL storage update settings.	SqlStorageUpdateSettings
sqlWorkloadTypeUpdateSettings	SQL workload type settings.	SqlWorkloadTypeUpdateSettings

SqlConnectivityUpdateSettings

Name	Description	Value
connectivityType	SQL Server connectivity option.	'LOCAL' 'PRIVATE' 'PUBLIC'
port	SQL Server port.	int
sqlAuthUpdatePassword	SQL Server sysadmin login password.	string
sqlAuthUpdateUserName	SQL Server sysadmin login to create.	string

SQLInstanceSettings

Name	Description	Value
collation	SQL Server Collation.	string
isIfiEnabled	SQL Server IFI.	bool
isLpimEnabled	SQL Server LPIM.	bool
isOptimizeForAdHocWorkloadsEnabled	SQL Server Optimize for Adhoc workloads.	bool
maxDop	SQL Server MAXDOP.	int
maxServerMemoryMB	SQL Server maximum memory.	int
minServerMemoryMB	SQL Server minimum memory.	int

SQLStorageSettings

Name	Description	Value
defaultFilePath	SQL Server default file path	string
luns	Logical Unit Numbers for the disks.	int[]
useStoragePool	Use storage pool to build a drive if true or not provided	bool

SqlStorageUpdateSettings

Name	Description	Value
diskConfigurationType	Disk configuration to apply to SQL Server.	'ADD' 'EXTEND' 'NEW'
diskCount	Virtual machine disk count.	int
startingDeviceId	Device id of the first disk to be updated.	int

SQLTempDbSettings

Name	Description	Value
dataFileCount	SQL Server tempdb data file count	int
dataFileSize	SQL Server tempdb data file size	int
dataGrowth	SQL Server tempdb data file autoGrowth size	int
defaultFilePath	SQL Server default file path	string
logFileSize	SQL Server tempdb log file size	int
logGrowth	SQL Server tempdb log file autoGrowth size	int
luns	Logical Unit Numbers for the disks.	int[]
persistFolder	SQL Server tempdb persist folder choice	bool
persistFolderPath	SQL Server tempdb persist folder location	string
useStoragePool	Use storage pool to build a drive if true or not provided	bool

SqlVirtualMachineProperties

Name	Description	Value
assessmentSettings	SQL best practices Assessment Settings.	AssessmentSettings
autoBackupSettings	Auto backup settings for SQL Server.	AutoBackupSettings
autoPatchingSettings	Auto patching settings for applying critical security updates to SQL virtual machine.	AutoPatchingSettings
enableAutomaticUpgrade	Enable automatic upgrade of Sql IaaS extension Agent.	bool
keyVaultCredentialSettings	Key vault credential settings.	KeyVaultCredentialSettings
leastPrivilegeMode	SQL IaaS Agent least privilege mode.	'Enabled' 'NotSet'
serverConfigurationsManagementSettings	SQL Server configuration management settings.	ServerConfigurationsManagementSettings
sqlImageOffer	SQL image offer. Examples include SQL2016-WS2016, SQL2017-WS2016.	string
sqlImageSku	SQL Server edition type.	'Developer' 'Enterprise' 'Express' 'Standard' 'Web'
sqlManagement	SQL Server Management type. NOTE: This parameter is not used anymore. API will automatically detect the Sql Management, refrain from using it.	'Full' 'LightWeight' 'NoAgent'
sqlServerLicenseType	SQL Server license type.	'AHUB' 'DR' 'PAYG'
sqlVirtualMachineGroupResourceId	ARM resource id of the SQL virtual machine group this SQL virtual machine is or will be part of.	string
storageConfigurationSettings	Storage Configuration Settings.	StorageConfigurationSettings
virtualMachineResourceId	ARM Resource id of underlying virtual machine created from SQL marketplace image.	string
wsfcDomainCredentials	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.	WsfcDomainCredentials
wsfcStaticIp	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.	string

SqlWorkloadTypeUpdateSettings

Name	Description	Value
sqlWorkloadType	SQL Server workload type.	'DW' 'GENERAL' 'OLTP'

StorageConfigurationSettings

Name	Description	Value
diskConfigurationType	Disk configuration to apply to SQL Server.	'ADD' 'EXTEND' 'NEW'
enableStorageConfigBlade	Enable SQL IaaS Agent storage configuration blade in Azure Portal.	bool
sqlDataSettings	SQL Server Data Storage Settings.	SQLStorageSettings
sqlLogSettings	SQL Server Log Storage Settings.	SQLStorageSettings
sqlSystemDbOnDataDisk	SQL Server SystemDb Storage on DataPool if true.	bool
sqlTempDbSettings	SQL Server TempDb Storage Settings.	SQLTempDbSettings
storageWorkloadType	Storage workload type.	'DW' 'GENERAL' 'OLTP'

TrackedResourceTags

Name	Description	Value

WsfcDomainCredentials

Name	Description	Value
clusterBootstrapAccountPassword	Cluster bootstrap account password.	string
clusterOperatorAccountPassword	Cluster operator account password.	string
sqlServiceAccountPassword	SQL service account password.	string

Quickstart templates

The following quickstart templates deploy this resource type.

Template	Description
Deploy SQL Always ON setup with existing SQL Virtual Machines	Deploy SQL Always ON setup with existing SQL Virtual Machines. The virtual machines should already be joined to an existing domain and must be running enterprise version of SQL Server.
SQL Server VM with performance optimized storage settings	Create a SQL Server Virtual Machine with performance optimized storage settings on PremiumSSD
SQL VM Performance Optimized Storage Settings on UltraSSD	Create a SQL Server Virtual Machine with performance optimized storage settings, using UltraSSD for SQL Log files

ARM template resource definition

The sqlVirtualMachines resource type can be deployed with operations that target:

Resource groups - See resource group deployment commands

For a list of changed properties in each API version, see change log.

Resource format

To create a Microsoft.SqlVirtualMachine/sqlVirtualMachines resource, add the following JSON to your template.

{
  "type": "Microsoft.SqlVirtualMachine/sqlVirtualMachines",
  "apiVersion": "2023-01-01-preview",
  "name": "string",
  "identity": {
    "type": "string"
  },
  "location": "string",
  "properties": {
    "assessmentSettings": {
      "enable": "bool",
      "runImmediately": "bool",
      "schedule": {
        "dayOfWeek": "string",
        "enable": "bool",
        "monthlyOccurrence": "int",
        "startTime": "string",
        "weeklyInterval": "int"
      }
    },
    "autoBackupSettings": {
      "backupScheduleType": "string",
      "backupSystemDbs": "bool",
      "daysOfWeek": [ "string" ],
      "enable": "bool",
      "enableEncryption": "bool",
      "fullBackupFrequency": "string",
      "fullBackupStartTime": "int",
      "fullBackupWindowHours": "int",
      "logBackupFrequency": "int",
      "password": "string",
      "retentionPeriod": "int",
      "storageAccessKey": "string",
      "storageAccountUrl": "string",
      "storageContainerName": "string"
    },
    "autoPatchingSettings": {
      "additionalVmPatch": "string",
      "dayOfWeek": "string",
      "enable": "bool",
      "maintenanceWindowDuration": "int",
      "maintenanceWindowStartingHour": "int"
    },
    "enableAutomaticUpgrade": "bool",
    "keyVaultCredentialSettings": {
      "azureKeyVaultUrl": "string",
      "credentialName": "string",
      "enable": "bool",
      "servicePrincipalName": "string",
      "servicePrincipalSecret": "string"
    },
    "leastPrivilegeMode": "string",
    "serverConfigurationsManagementSettings": {
      "additionalFeaturesServerConfigurations": {
        "isRServicesEnabled": "bool"
      },
      "azureAdAuthenticationSettings": {
        "clientId": "string"
      },
      "sqlConnectivityUpdateSettings": {
        "connectivityType": "string",
        "port": "int",
        "sqlAuthUpdatePassword": "string",
        "sqlAuthUpdateUserName": "string"
      },
      "sqlInstanceSettings": {
        "collation": "string",
        "isIfiEnabled": "bool",
        "isLpimEnabled": "bool",
        "isOptimizeForAdHocWorkloadsEnabled": "bool",
        "maxDop": "int",
        "maxServerMemoryMB": "int",
        "minServerMemoryMB": "int"
      },
      "sqlStorageUpdateSettings": {
        "diskConfigurationType": "string",
        "diskCount": "int",
        "startingDeviceId": "int"
      },
      "sqlWorkloadTypeUpdateSettings": {
        "sqlWorkloadType": "string"
      }
    },
    "sqlImageOffer": "string",
    "sqlImageSku": "string",
    "sqlManagement": "string",
    "sqlServerLicenseType": "string",
    "sqlVirtualMachineGroupResourceId": "string",
    "storageConfigurationSettings": {
      "diskConfigurationType": "string",
      "enableStorageConfigBlade": "bool",
      "sqlDataSettings": {
        "defaultFilePath": "string",
        "luns": [ "int" ],
        "useStoragePool": "bool"
      },
      "sqlLogSettings": {
        "defaultFilePath": "string",
        "luns": [ "int" ],
        "useStoragePool": "bool"
      },
      "sqlSystemDbOnDataDisk": "bool",
      "sqlTempDbSettings": {
        "dataFileCount": "int",
        "dataFileSize": "int",
        "dataGrowth": "int",
        "defaultFilePath": "string",
        "logFileSize": "int",
        "logGrowth": "int",
        "luns": [ "int" ],
        "persistFolder": "bool",
        "persistFolderPath": "string",
        "useStoragePool": "bool"
      },
      "storageWorkloadType": "string"
    },
    "virtualMachineResourceId": "string",
    "wsfcDomainCredentials": {
      "clusterBootstrapAccountPassword": "string",
      "clusterOperatorAccountPassword": "string",
      "sqlServiceAccountPassword": "string"
    },
    "wsfcStaticIp": "string"
  },
  "tags": {
    "{customized property}": "string"
  }
}

Property values

AADAuthenticationSettings

Name	Description	Value
clientId	The client Id of the Managed Identity to query Microsoft Graph API. An empty string must be used for the system assigned Managed Identity	string

AdditionalFeaturesServerConfigurations

Name	Description	Value
isRServicesEnabled	Enable or disable R services (SQL 2016 onwards).	bool

AssessmentSettings

Name	Description	Value
enable	Enable or disable SQL best practices Assessment feature on SQL virtual machine.	bool
runImmediately	Run SQL best practices Assessment immediately on SQL virtual machine.	bool
schedule	Schedule for SQL best practices Assessment.	Schedule

AutoBackupSettings

Name	Description	Value
backupScheduleType	Backup schedule type.	'Automated' 'Manual'
backupSystemDbs	Include or exclude system databases from auto backup.	bool
daysOfWeek	Days of the week for the backups when FullBackupFrequency is set to Weekly.	String array containing any of: 'Friday' 'Monday' 'Saturday' 'Sunday' 'Thursday' 'Tuesday' 'Wednesday'
enable	Enable or disable autobackup on SQL virtual machine.	bool
enableEncryption	Enable or disable encryption for backup on SQL virtual machine.	bool
fullBackupFrequency	Frequency of full backups. In both cases, full backups begin during the next scheduled time window.	'Daily' 'Weekly'
fullBackupStartTime	Start time of a given day during which full backups can take place. 0-23 hours.	int
fullBackupWindowHours	Duration of the time window of a given day during which full backups can take place. 1-23 hours.	int
logBackupFrequency	Frequency of log backups. 5-60 minutes.	int
password	Password for encryption on backup.	string
retentionPeriod	Retention period of backup: 1-90 days.	int
storageAccessKey	Storage account key where backup will be taken to.	string
storageAccountUrl	Storage account url where backup will be taken to.	string
storageContainerName	Storage container name where backup will be taken to.	string

AutoPatchingSettings

Name	Description	Value
additionalVmPatch	Additional Patch to be enable or enabled on the SQL Virtual Machine.	'MicrosoftUpdate' 'NotSet'
dayOfWeek	Day of week to apply the patch on.	'Everyday' 'Friday' 'Monday' 'Saturday' 'Sunday' 'Thursday' 'Tuesday' 'Wednesday'
enable	Enable or disable autopatching on SQL virtual machine.	bool
maintenanceWindowDuration	Duration of patching.	int
maintenanceWindowStartingHour	Hour of the day when patching is initiated. Local VM time.	int

KeyVaultCredentialSettings

Name	Description	Value
azureKeyVaultUrl	Azure Key Vault url.	string
credentialName	Credential name.	string
enable	Enable or disable key vault credential setting.	bool
servicePrincipalName	Service principal name to access key vault.	string
servicePrincipalSecret	Service principal name secret to access key vault.	string

Microsoft.SqlVirtualMachine/sqlVirtualMachines

Name	Description	Value
identity	DO NOT USE. This value will be deprecated. Azure Active Directory identity of the server.	ResourceIdentity
location	Resource location.	string (required)
name	The resource name	string Constraints: Min length = 1 Max length = 1 Pattern = `^((?!_)[^\\/"'\[\]:\|<>+=;,?*@&]{1,64}(?<![.-]))$` (required)
properties	Resource properties.	SqlVirtualMachineProperties
tags	Resource tags.	TrackedResourceTags

ResourceIdentity

Name	Description	Value
type	The identity type. Set this to 'SystemAssigned' in order to automatically create and assign an Azure Active Directory principal for the resource.	'None' 'SystemAssigned'

Schedule

Name	Description	Value
dayOfWeek	Day of the week to run assessment.	'Friday' 'Monday' 'Saturday' 'Sunday' 'Thursday' 'Tuesday' 'Wednesday'
enable	Enable or disable assessment schedule on SQL virtual machine.	bool
monthlyOccurrence	Occurrence of the DayOfWeek day within a month to schedule assessment. Takes values: 1,2,3,4 and -1. Use -1 for last DayOfWeek day of the month	int
startTime	Time of the day in HH:mm format. Eg. 17:30	string
weeklyInterval	Number of weeks to schedule between 2 assessment runs. Takes value from 1-6	int

ServerConfigurationsManagementSettings

Name	Description	Value
additionalFeaturesServerConfigurations	Additional SQL feature settings.	AdditionalFeaturesServerConfigurations
azureAdAuthenticationSettings	Azure AD authentication Settings.	AADAuthenticationSettings
sqlConnectivityUpdateSettings	SQL connectivity type settings.	SqlConnectivityUpdateSettings
sqlInstanceSettings	SQL Instance settings.	SQLInstanceSettings
sqlStorageUpdateSettings	SQL storage update settings.	SqlStorageUpdateSettings
sqlWorkloadTypeUpdateSettings	SQL workload type settings.	SqlWorkloadTypeUpdateSettings

SqlConnectivityUpdateSettings

Name	Description	Value
connectivityType	SQL Server connectivity option.	'LOCAL' 'PRIVATE' 'PUBLIC'
port	SQL Server port.	int
sqlAuthUpdatePassword	SQL Server sysadmin login password.	string
sqlAuthUpdateUserName	SQL Server sysadmin login to create.	string

SQLInstanceSettings

Name	Description	Value
collation	SQL Server Collation.	string
isIfiEnabled	SQL Server IFI.	bool
isLpimEnabled	SQL Server LPIM.	bool
isOptimizeForAdHocWorkloadsEnabled	SQL Server Optimize for Adhoc workloads.	bool
maxDop	SQL Server MAXDOP.	int
maxServerMemoryMB	SQL Server maximum memory.	int
minServerMemoryMB	SQL Server minimum memory.	int

SQLStorageSettings

Name	Description	Value
defaultFilePath	SQL Server default file path	string
luns	Logical Unit Numbers for the disks.	int[]
useStoragePool	Use storage pool to build a drive if true or not provided	bool

SqlStorageUpdateSettings

Name	Description	Value
diskConfigurationType	Disk configuration to apply to SQL Server.	'ADD' 'EXTEND' 'NEW'
diskCount	Virtual machine disk count.	int
startingDeviceId	Device id of the first disk to be updated.	int

SQLTempDbSettings

Name	Description	Value
dataFileCount	SQL Server tempdb data file count	int
dataFileSize	SQL Server tempdb data file size	int
dataGrowth	SQL Server tempdb data file autoGrowth size	int
defaultFilePath	SQL Server default file path	string
logFileSize	SQL Server tempdb log file size	int
logGrowth	SQL Server tempdb log file autoGrowth size	int
luns	Logical Unit Numbers for the disks.	int[]
persistFolder	SQL Server tempdb persist folder choice	bool
persistFolderPath	SQL Server tempdb persist folder location	string
useStoragePool	Use storage pool to build a drive if true or not provided	bool

SqlVirtualMachineProperties

Name	Description	Value
assessmentSettings	SQL best practices Assessment Settings.	AssessmentSettings
autoBackupSettings	Auto backup settings for SQL Server.	AutoBackupSettings
autoPatchingSettings	Auto patching settings for applying critical security updates to SQL virtual machine.	AutoPatchingSettings
enableAutomaticUpgrade	Enable automatic upgrade of Sql IaaS extension Agent.	bool
keyVaultCredentialSettings	Key vault credential settings.	KeyVaultCredentialSettings
leastPrivilegeMode	SQL IaaS Agent least privilege mode.	'Enabled' 'NotSet'
serverConfigurationsManagementSettings	SQL Server configuration management settings.	ServerConfigurationsManagementSettings
sqlImageOffer	SQL image offer. Examples include SQL2016-WS2016, SQL2017-WS2016.	string
sqlImageSku	SQL Server edition type.	'Developer' 'Enterprise' 'Express' 'Standard' 'Web'
sqlManagement	SQL Server Management type. NOTE: This parameter is not used anymore. API will automatically detect the Sql Management, refrain from using it.	'Full' 'LightWeight' 'NoAgent'
sqlServerLicenseType	SQL Server license type.	'AHUB' 'DR' 'PAYG'
sqlVirtualMachineGroupResourceId	ARM resource id of the SQL virtual machine group this SQL virtual machine is or will be part of.	string
storageConfigurationSettings	Storage Configuration Settings.	StorageConfigurationSettings
virtualMachineResourceId	ARM Resource id of underlying virtual machine created from SQL marketplace image.	string
wsfcDomainCredentials	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.	WsfcDomainCredentials
wsfcStaticIp	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.	string

SqlWorkloadTypeUpdateSettings

Name	Description	Value
sqlWorkloadType	SQL Server workload type.	'DW' 'GENERAL' 'OLTP'

StorageConfigurationSettings

Name	Description	Value
diskConfigurationType	Disk configuration to apply to SQL Server.	'ADD' 'EXTEND' 'NEW'
enableStorageConfigBlade	Enable SQL IaaS Agent storage configuration blade in Azure Portal.	bool
sqlDataSettings	SQL Server Data Storage Settings.	SQLStorageSettings
sqlLogSettings	SQL Server Log Storage Settings.	SQLStorageSettings
sqlSystemDbOnDataDisk	SQL Server SystemDb Storage on DataPool if true.	bool
sqlTempDbSettings	SQL Server TempDb Storage Settings.	SQLTempDbSettings
storageWorkloadType	Storage workload type.	'DW' 'GENERAL' 'OLTP'

TrackedResourceTags

Name	Description	Value

WsfcDomainCredentials

Name	Description	Value
clusterBootstrapAccountPassword	Cluster bootstrap account password.	string
clusterOperatorAccountPassword	Cluster operator account password.	string
sqlServiceAccountPassword	SQL service account password.	string

Quickstart templates

The following quickstart templates deploy this resource type.

Template	Description
Deploy SQL Always ON setup with existing SQL Virtual Machines	Deploy SQL Always ON setup with existing SQL Virtual Machines. The virtual machines should already be joined to an existing domain and must be running enterprise version of SQL Server.
SQL Server VM with performance optimized storage settings	Create a SQL Server Virtual Machine with performance optimized storage settings on PremiumSSD
SQL VM Performance Optimized Storage Settings on UltraSSD	Create a SQL Server Virtual Machine with performance optimized storage settings, using UltraSSD for SQL Log files

Terraform (AzAPI provider) resource definition

The sqlVirtualMachines resource type can be deployed with operations that target:

Resource groups

For a list of changed properties in each API version, see change log.

Resource format

To create a Microsoft.SqlVirtualMachine/sqlVirtualMachines resource, add the following Terraform to your template.

resource "azapi_resource" "symbolicname" = {
  type = "Microsoft.SqlVirtualMachine/sqlVirtualMachines@2023-01-01-preview"
  name = "string"
  identity = {
    type = "string"
  }
  location = "string"
  body = jsonencode({
    properties = {
      assessmentSettings = {
        enable = bool
        runImmediately = bool
        schedule = {
          dayOfWeek = "string"
          enable = bool
          monthlyOccurrence = int
          startTime = "string"
          weeklyInterval = int
        }
      }
      autoBackupSettings = {
        backupScheduleType = "string"
        backupSystemDbs = bool
        daysOfWeek = [
          "string"
        ]
        enable = bool
        enableEncryption = bool
        fullBackupFrequency = "string"
        fullBackupStartTime = int
        fullBackupWindowHours = int
        logBackupFrequency = int
        password = "string"
        retentionPeriod = int
        storageAccessKey = "string"
        storageAccountUrl = "string"
        storageContainerName = "string"
      }
      autoPatchingSettings = {
        additionalVmPatch = "string"
        dayOfWeek = "string"
        enable = bool
        maintenanceWindowDuration = int
        maintenanceWindowStartingHour = int
      }
      enableAutomaticUpgrade = bool
      keyVaultCredentialSettings = {
        azureKeyVaultUrl = "string"
        credentialName = "string"
        enable = bool
        servicePrincipalName = "string"
        servicePrincipalSecret = "string"
      }
      leastPrivilegeMode = "string"
      serverConfigurationsManagementSettings = {
        additionalFeaturesServerConfigurations = {
          isRServicesEnabled = bool
        }
        azureAdAuthenticationSettings = {
          clientId = "string"
        }
        sqlConnectivityUpdateSettings = {
          connectivityType = "string"
          port = int
          sqlAuthUpdatePassword = "string"
          sqlAuthUpdateUserName = "string"
        }
        sqlInstanceSettings = {
          collation = "string"
          isIfiEnabled = bool
          isLpimEnabled = bool
          isOptimizeForAdHocWorkloadsEnabled = bool
          maxDop = int
          maxServerMemoryMB = int
          minServerMemoryMB = int
        }
        sqlStorageUpdateSettings = {
          diskConfigurationType = "string"
          diskCount = int
          startingDeviceId = int
        }
        sqlWorkloadTypeUpdateSettings = {
          sqlWorkloadType = "string"
        }
      }
      sqlImageOffer = "string"
      sqlImageSku = "string"
      sqlManagement = "string"
      sqlServerLicenseType = "string"
      sqlVirtualMachineGroupResourceId = "string"
      storageConfigurationSettings = {
        diskConfigurationType = "string"
        enableStorageConfigBlade = bool
        sqlDataSettings = {
          defaultFilePath = "string"
          luns = [
            int
          ]
          useStoragePool = bool
        }
        sqlLogSettings = {
          defaultFilePath = "string"
          luns = [
            int
          ]
          useStoragePool = bool
        }
        sqlSystemDbOnDataDisk = bool
        sqlTempDbSettings = {
          dataFileCount = int
          dataFileSize = int
          dataGrowth = int
          defaultFilePath = "string"
          logFileSize = int
          logGrowth = int
          luns = [
            int
          ]
          persistFolder = bool
          persistFolderPath = "string"
          useStoragePool = bool
        }
        storageWorkloadType = "string"
      }
      virtualMachineResourceId = "string"
      wsfcDomainCredentials = {
        clusterBootstrapAccountPassword = "string"
        clusterOperatorAccountPassword = "string"
        sqlServiceAccountPassword = "string"
      }
      wsfcStaticIp = "string"
    }
  })
  tags = {
    {customized property} = "string"
  }
}

Property values

AADAuthenticationSettings

Name	Description	Value
clientId	The client Id of the Managed Identity to query Microsoft Graph API. An empty string must be used for the system assigned Managed Identity	string

AdditionalFeaturesServerConfigurations

Name	Description	Value
isRServicesEnabled	Enable or disable R services (SQL 2016 onwards).	bool

AssessmentSettings

Name	Description	Value
enable	Enable or disable SQL best practices Assessment feature on SQL virtual machine.	bool
runImmediately	Run SQL best practices Assessment immediately on SQL virtual machine.	bool
schedule	Schedule for SQL best practices Assessment.	Schedule

AutoBackupSettings

Name	Description	Value
backupScheduleType	Backup schedule type.	'Automated' 'Manual'
backupSystemDbs	Include or exclude system databases from auto backup.	bool
daysOfWeek	Days of the week for the backups when FullBackupFrequency is set to Weekly.	String array containing any of: 'Friday' 'Monday' 'Saturday' 'Sunday' 'Thursday' 'Tuesday' 'Wednesday'
enable	Enable or disable autobackup on SQL virtual machine.	bool
enableEncryption	Enable or disable encryption for backup on SQL virtual machine.	bool
fullBackupFrequency	Frequency of full backups. In both cases, full backups begin during the next scheduled time window.	'Daily' 'Weekly'
fullBackupStartTime	Start time of a given day during which full backups can take place. 0-23 hours.	int
fullBackupWindowHours	Duration of the time window of a given day during which full backups can take place. 1-23 hours.	int
logBackupFrequency	Frequency of log backups. 5-60 minutes.	int
password	Password for encryption on backup.	string
retentionPeriod	Retention period of backup: 1-90 days.	int
storageAccessKey	Storage account key where backup will be taken to.	string
storageAccountUrl	Storage account url where backup will be taken to.	string
storageContainerName	Storage container name where backup will be taken to.	string

AutoPatchingSettings

Name	Description	Value
additionalVmPatch	Additional Patch to be enable or enabled on the SQL Virtual Machine.	'MicrosoftUpdate' 'NotSet'
dayOfWeek	Day of week to apply the patch on.	'Everyday' 'Friday' 'Monday' 'Saturday' 'Sunday' 'Thursday' 'Tuesday' 'Wednesday'
enable	Enable or disable autopatching on SQL virtual machine.	bool
maintenanceWindowDuration	Duration of patching.	int
maintenanceWindowStartingHour	Hour of the day when patching is initiated. Local VM time.	int

KeyVaultCredentialSettings

Name	Description	Value
azureKeyVaultUrl	Azure Key Vault url.	string
credentialName	Credential name.	string
enable	Enable or disable key vault credential setting.	bool
servicePrincipalName	Service principal name to access key vault.	string
servicePrincipalSecret	Service principal name secret to access key vault.	string

Microsoft.SqlVirtualMachine/sqlVirtualMachines

Name	Description	Value
identity	DO NOT USE. This value will be deprecated. Azure Active Directory identity of the server.	ResourceIdentity
location	Resource location.	string (required)
name	The resource name	string Constraints: Min length = 1 Max length = 1 Pattern = `^((?!_)[^\\/"'\[\]:\|<>+=;,?*@&]{1,64}(?<![.-]))$` (required)
properties	Resource properties.	SqlVirtualMachineProperties
tags	Resource tags.	TrackedResourceTags

ResourceIdentity

Name	Description	Value
type	The identity type. Set this to 'SystemAssigned' in order to automatically create and assign an Azure Active Directory principal for the resource.	'None' 'SystemAssigned'

Schedule

Name	Description	Value
dayOfWeek	Day of the week to run assessment.	'Friday' 'Monday' 'Saturday' 'Sunday' 'Thursday' 'Tuesday' 'Wednesday'
enable	Enable or disable assessment schedule on SQL virtual machine.	bool
monthlyOccurrence	Occurrence of the DayOfWeek day within a month to schedule assessment. Takes values: 1,2,3,4 and -1. Use -1 for last DayOfWeek day of the month	int
startTime	Time of the day in HH:mm format. Eg. 17:30	string
weeklyInterval	Number of weeks to schedule between 2 assessment runs. Takes value from 1-6	int

ServerConfigurationsManagementSettings

Name	Description	Value
additionalFeaturesServerConfigurations	Additional SQL feature settings.	AdditionalFeaturesServerConfigurations
azureAdAuthenticationSettings	Azure AD authentication Settings.	AADAuthenticationSettings
sqlConnectivityUpdateSettings	SQL connectivity type settings.	SqlConnectivityUpdateSettings
sqlInstanceSettings	SQL Instance settings.	SQLInstanceSettings
sqlStorageUpdateSettings	SQL storage update settings.	SqlStorageUpdateSettings
sqlWorkloadTypeUpdateSettings	SQL workload type settings.	SqlWorkloadTypeUpdateSettings

SqlConnectivityUpdateSettings

Name	Description	Value
connectivityType	SQL Server connectivity option.	'LOCAL' 'PRIVATE' 'PUBLIC'
port	SQL Server port.	int
sqlAuthUpdatePassword	SQL Server sysadmin login password.	string
sqlAuthUpdateUserName	SQL Server sysadmin login to create.	string

SQLInstanceSettings

Name	Description	Value
collation	SQL Server Collation.	string
isIfiEnabled	SQL Server IFI.	bool
isLpimEnabled	SQL Server LPIM.	bool
isOptimizeForAdHocWorkloadsEnabled	SQL Server Optimize for Adhoc workloads.	bool
maxDop	SQL Server MAXDOP.	int
maxServerMemoryMB	SQL Server maximum memory.	int
minServerMemoryMB	SQL Server minimum memory.	int

SQLStorageSettings

Name	Description	Value
defaultFilePath	SQL Server default file path	string
luns	Logical Unit Numbers for the disks.	int[]
useStoragePool	Use storage pool to build a drive if true or not provided	bool

SqlStorageUpdateSettings

Name	Description	Value
diskConfigurationType	Disk configuration to apply to SQL Server.	'ADD' 'EXTEND' 'NEW'
diskCount	Virtual machine disk count.	int
startingDeviceId	Device id of the first disk to be updated.	int

SQLTempDbSettings

Name	Description	Value
dataFileCount	SQL Server tempdb data file count	int
dataFileSize	SQL Server tempdb data file size	int
dataGrowth	SQL Server tempdb data file autoGrowth size	int
defaultFilePath	SQL Server default file path	string
logFileSize	SQL Server tempdb log file size	int
logGrowth	SQL Server tempdb log file autoGrowth size	int
luns	Logical Unit Numbers for the disks.	int[]
persistFolder	SQL Server tempdb persist folder choice	bool
persistFolderPath	SQL Server tempdb persist folder location	string
useStoragePool	Use storage pool to build a drive if true or not provided	bool

SqlVirtualMachineProperties

Name	Description	Value
assessmentSettings	SQL best practices Assessment Settings.	AssessmentSettings
autoBackupSettings	Auto backup settings for SQL Server.	AutoBackupSettings
autoPatchingSettings	Auto patching settings for applying critical security updates to SQL virtual machine.	AutoPatchingSettings
enableAutomaticUpgrade	Enable automatic upgrade of Sql IaaS extension Agent.	bool
keyVaultCredentialSettings	Key vault credential settings.	KeyVaultCredentialSettings
leastPrivilegeMode	SQL IaaS Agent least privilege mode.	'Enabled' 'NotSet'
serverConfigurationsManagementSettings	SQL Server configuration management settings.	ServerConfigurationsManagementSettings
sqlImageOffer	SQL image offer. Examples include SQL2016-WS2016, SQL2017-WS2016.	string
sqlImageSku	SQL Server edition type.	'Developer' 'Enterprise' 'Express' 'Standard' 'Web'
sqlManagement	SQL Server Management type. NOTE: This parameter is not used anymore. API will automatically detect the Sql Management, refrain from using it.	'Full' 'LightWeight' 'NoAgent'
sqlServerLicenseType	SQL Server license type.	'AHUB' 'DR' 'PAYG'
sqlVirtualMachineGroupResourceId	ARM resource id of the SQL virtual machine group this SQL virtual machine is or will be part of.	string
storageConfigurationSettings	Storage Configuration Settings.	StorageConfigurationSettings
virtualMachineResourceId	ARM Resource id of underlying virtual machine created from SQL marketplace image.	string
wsfcDomainCredentials	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.	WsfcDomainCredentials
wsfcStaticIp	Domain credentials for setting up Windows Server Failover Cluster for SQL availability group.	string

SqlWorkloadTypeUpdateSettings

Name	Description	Value
sqlWorkloadType	SQL Server workload type.	'DW' 'GENERAL' 'OLTP'

StorageConfigurationSettings

Name	Description	Value
diskConfigurationType	Disk configuration to apply to SQL Server.	'ADD' 'EXTEND' 'NEW'
enableStorageConfigBlade	Enable SQL IaaS Agent storage configuration blade in Azure Portal.	bool
sqlDataSettings	SQL Server Data Storage Settings.	SQLStorageSettings
sqlLogSettings	SQL Server Log Storage Settings.	SQLStorageSettings
sqlSystemDbOnDataDisk	SQL Server SystemDb Storage on DataPool if true.	bool
sqlTempDbSettings	SQL Server TempDb Storage Settings.	SQLTempDbSettings
storageWorkloadType	Storage workload type.	'DW' 'GENERAL' 'OLTP'

TrackedResourceTags

Name	Description	Value

WsfcDomainCredentials

Name	Description	Value
clusterBootstrapAccountPassword	Cluster bootstrap account password.	string
clusterOperatorAccountPassword	Cluster operator account password.	string
sqlServiceAccountPassword	SQL service account password.	string

Share via

Microsoft.SqlVirtualMachine sqlVirtualMachines 2023-01-01-preview

Bicep resource definition

Resource format

Property values

AADAuthenticationSettings

AdditionalFeaturesServerConfigurations

AssessmentSettings

AutoBackupSettings

AutoPatchingSettings

KeyVaultCredentialSettings

Microsoft.SqlVirtualMachine/sqlVirtualMachines

ResourceIdentity

Schedule

ServerConfigurationsManagementSettings

SqlConnectivityUpdateSettings

SQLInstanceSettings

SQLStorageSettings

SqlStorageUpdateSettings

SQLTempDbSettings

SqlVirtualMachineProperties

SqlWorkloadTypeUpdateSettings

StorageConfigurationSettings

TrackedResourceTags

WsfcDomainCredentials

Quickstart templates

ARM template resource definition

Resource format

Property values

AADAuthenticationSettings

AdditionalFeaturesServerConfigurations

AssessmentSettings

AutoBackupSettings

AutoPatchingSettings

KeyVaultCredentialSettings

Microsoft.SqlVirtualMachine/sqlVirtualMachines

ResourceIdentity

Schedule

ServerConfigurationsManagementSettings

SqlConnectivityUpdateSettings

SQLInstanceSettings

SQLStorageSettings

SqlStorageUpdateSettings

SQLTempDbSettings

SqlVirtualMachineProperties

SqlWorkloadTypeUpdateSettings

StorageConfigurationSettings

TrackedResourceTags

WsfcDomainCredentials

Quickstart templates

Terraform (AzAPI provider) resource definition

Resource format

Property values

AADAuthenticationSettings

AdditionalFeaturesServerConfigurations

AssessmentSettings

AutoBackupSettings

AutoPatchingSettings

KeyVaultCredentialSettings

Microsoft.SqlVirtualMachine/sqlVirtualMachines

ResourceIdentity

Schedule

ServerConfigurationsManagementSettings

SqlConnectivityUpdateSettings

SQLInstanceSettings

SQLStorageSettings

SqlStorageUpdateSettings

SQLTempDbSettings

SqlVirtualMachineProperties

SqlWorkloadTypeUpdateSettings

StorageConfigurationSettings

TrackedResourceTags

WsfcDomainCredentials

Feedback

Additional resources