az sql vm

Manage SQL virtual machines.

Commands

az sql vm add-to-group

Adds SQL virtual machine to a SQL virtual machine group.

az sql vm create

Creates a SQL virtual machine.

az sql vm delete

Deletes a SQL virtual machine.

az sql vm group

Manage SQL virtual machine groups.

az sql vm group ag-listener

Manage SQL availability group listeners.

az sql vm group ag-listener create

Creates an availability group listener.

az sql vm group ag-listener delete

Deletes an availability group listener.

az sql vm group ag-listener list

Lists all availability group listeners in a SQL virtual machine group.

az sql vm group ag-listener show

Gets an availability group listener.

az sql vm group ag-listener update

Updates an availability group listener.

az sql vm group create

Creates a SQL virtual machine group.

az sql vm group delete

Deletes a SQL virtual machine group.

az sql vm group list

Lists all SQL virtual machine groups in a resource group or subscription.

az sql vm group show

Gets a SQL virtual machine group.

az sql vm group update

Updates a SQL virtual machine group if there are not SQL virtual machines attached to the group.

az sql vm list

Lists all SQL virtual machines in a resource group or subscription.

az sql vm remove-from-group

Remove SQL virtual machine from its current SQL virtual machine group.

az sql vm show

Gets a SQL virtual machine.

az sql vm start-assessment

Starts SQL best practice assessment on SQL virtual machine.

az sql vm update

Updates the properties of a SQL virtual machine.

az sql vm add-to-group

Adds SQL virtual machine to a SQL virtual machine group.

az sql vm add-to-group --sqlvm-group
                       [--bootstrap-acc-pwd]
                       [--ids]
                       [--name]
                       [--operator-acc-pwd]
                       [--resource-group]
                       [--service-acc-pwd]

Examples

Add SQL virtual machine to a group.

az sql vm add-to-group -n sqlvm -g myresourcegroup --sqlvm-group sqlvmgroup --bootstrap-acc-pwd {bootstrappassword} --operator-acc-pwd {operatorpassword} --service-acc-pwd {servicepassword}

Required Parameters

--sqlvm-group -r

Name or resource ID of the SQL virtual machine group. If only name provided, SQL virtual machine group should be in the same resource group of the SQL virtual machine.

Optional Parameters

--bootstrap-acc-pwd -b

Password for the cluster bootstrap account if provided in the SQL virtual machine group.

--ids

One or more resource IDs (space-delimited). It should be a complete resource ID containing all information of 'Resource Id' arguments. You should provide either --ids or other 'Resource Id' arguments.

--name -n

Name of the SQL virtual machine.

--operator-acc-pwd -p

Password for the cluster operator account provided in the SQL virtual machine group.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--service-acc-pwd -s

Password for the SQL service account provided in the SQL virtual machine group.

az sql vm create

Creates a SQL virtual machine.

az sql vm create --name
                 --resource-group
                 [--backup-pwd]
                 [--backup-schedule-type {Automated, Manual}]
                 [--backup-system-dbs {false, true}]
                 [--connectivity-type {LOCAL, PRIVATE, PUBLIC}]
                 [--credential-name]
                 [--day-of-week {Everyday, Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday}]
                 [--enable-auto-backup {false, true}]
                 [--enable-auto-patching {false, true}]
                 [--enable-encryption {false, true}]
                 [--enable-key-vault-credential {false, true}]
                 [--enable-r-services {false, true}]
                 [--full-backup-duration]
                 [--full-backup-frequency {Daily, Weekly}]
                 [--full-backup-start-hour]
                 [--image-offer]
                 [--image-sku {Developer, Enterprise, Express, Standard, Web}]
                 [--key-vault]
                 [--license-type {AHUB, DR, PAYG}]
                 [--location]
                 [--log-backup-frequency]
                 [--maintenance-window-duration]
                 [--maintenance-window-start-hour]
                 [--port]
                 [--retention-period]
                 [--sa-key]
                 [--sp-name]
                 [--sp-secret]
                 [--sql-auth-update-pwd]
                 [--sql-auth-update-username]
                 [--sql-mgmt-type {Full, LightWeight, NoAgent}]
                 [--sql-workload-type {DW, GENERAL, OLTP}]
                 [--storage-account]
                 [--tags]

Examples

Create a SQL virtual machine with AHUB billing tag.

az sql vm create -n sqlvm -g myresourcegroup -l eastus --license-type AHUB

Create a SQL virtual machine with DR billing tag.

az sql vm create -n sqlvm -g myresourcegroup -l eastus --license-type DR

Create a SQL virtual machine with specific sku type and license type.

az sql vm create -n sqlvm -g myresourcegroup -l eastus --image-sku Enterprise --license-type AHUB

Create a SQL virtual machine with NoAgent type, only valid for EOS SQL 2008 and SQL 2008 R2.

az sql vm create -n sqlvm -g myresourcegroup -l eastus --license-type AHUB --sql-mgmt-type NoAgent --image-sku Enterprise --image-offer SQL2008-WS2008R2

Enable R services in SQL2016 onwards.

az sql vm create -n sqlvm -g myresourcegroup -l eastus --license-type PAYG --sql-mgmt-type Full --enable-r-services true

Create SQL virtual machine and configure auto backup settings.

az sql vm create -n sqlvm -g myresourcegroup -l eastus --license-type PAYG --sql-mgmt-type Full --backup-schedule-type manual --full-backup-frequency Weekly --full-backup-start-hour 2 --full-backup-duration 2 --sa-key {storageKey} --storage-account 'https://storageacc.blob.core.windows.net/' --retention-period 30 --log-backup-frequency 60

Create SQL virtual machine and configure auto patching settings.

az sql vm create -n sqlvm -g myresourcegroup -l eastus --license-type PAYG --sql-mgmt-type Full --day-of-week sunday --maintenance-window-duration 60 --maintenance-window-start-hour 2

Create SQL virtual machine and configure SQL connectivity settings.

az sql vm create -n sqlvm -g myresourcegroup -l eastus --license-type PAYG --sql-mgmt-type Full --connectivity-type private --port 1433 --sql-auth-update-username {newlogin} --sql-auth-update-pwd {sqlpassword}

Required Parameters

--name -n

Name of the SQL virtual machine. The name of the new SQL virtual machine must be equal to the underlying virtual machine created from SQL marketplace image.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

Optional Parameters

--backup-pwd

Password for encryption on backup.

--backup-schedule-type

Backup schedule type.

accepted values: Automated, Manual
--backup-system-dbs

Include system databases on backup.

accepted values: false, true
--connectivity-type

SQL Server connectivity option.

accepted values: LOCAL, PRIVATE, PUBLIC
--credential-name

Credential name.

--day-of-week

Day of week to apply the patch on.

accepted values: Everyday, Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday
--enable-auto-backup

Enable or disable autobackup on SQL virtual machine. If any backup settings provided, parameter automatically sets to true.

accepted values: false, true
--enable-auto-patching

Enable or disable autopatching on SQL virtual machine. If any autopatching settings provided, parameter automatically sets to true.

accepted values: false, true
--enable-encryption

Enable encryption for backup on SQL virtual machine.

accepted values: false, true
--enable-key-vault-credential

Enable or disable key vault credential setting. If any key vault settings provided, parameter automatically sets to true.

accepted values: false, true
--enable-r-services

Enable or disable R services (SQL 2016 onwards).

accepted values: false, true
--full-backup-duration

Duration of the time window of a given day during which full backups can take place. 1-23 hours.

--full-backup-frequency

Frequency of full backups. In both cases, full backups begin during the next scheduled time window.

accepted values: Daily, Weekly
--full-backup-start-hour

Start time of a given day during which full backups can take place. 0-23 hours.

--image-offer

SQL image offer. Examples include SQL2008R2-WS2008, SQL2008-WS2008.

--image-sku

SQL image sku.

accepted values: Developer, Enterprise, Express, Standard, Web
--key-vault

Azure Key Vault url.

--license-type -i

SQL Server license type.

accepted values: AHUB, DR, PAYG
--location -l

Location. If not provided, virtual machine should be in the same region of resource group.You can configure the default location using az configure --defaults location=<location>.

--log-backup-frequency

Frequency of log backups. 5-60 minutes.

--maintenance-window-duration

Duration of patching. 30-180 minutes.

--maintenance-window-start-hour

Hour of the day when patching is initiated. Local VM time 0-23 hours.

--port

SQL Server port.

--retention-period

Retention period of backup. 1-30 days.

--sa-key

Storage account key where backup will be taken to.

--sp-name

Service principal name to access key vault.

--sp-secret

Service principal name secret to access key vault.

--sql-auth-update-pwd

SQL Server sysadmin login password.

--sql-auth-update-username

SQL Server sysadmin login to create.

--sql-mgmt-type

SQL Server management type. If NoAgent selected, please provide --image-sku and --offer-type.

accepted values: Full, LightWeight, NoAgent
default value: LightWeight
--sql-workload-type

SQL Server workload type.

accepted values: DW, GENERAL, OLTP
--storage-account

Storage account url where backup will be taken to.

--tags

Space-separated tags: key[=value] [key[=value] ...]. Use "" to clear existing tags.

az sql vm delete

Deletes a SQL virtual machine.

az sql vm delete [--ids]
                 [--name]
                 [--resource-group]
                 [--yes]

Optional Parameters

--ids

One or more resource IDs (space-delimited). It should be a complete resource ID containing all information of 'Resource Id' arguments. You should provide either --ids or other 'Resource Id' arguments.

--name -n

Name of the SQL virtual machine.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--yes -y

Do not prompt for confirmation.

az sql vm list

Lists all SQL virtual machines in a resource group or subscription.

az sql vm list [--resource-group]

Optional Parameters

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

az sql vm remove-from-group

Remove SQL virtual machine from its current SQL virtual machine group.

az sql vm remove-from-group [--ids]
                            [--name]
                            [--resource-group]

Examples

Remove SQL virtual machine from a group.

az sql vm remove-from-group -n sqlvm -g myresourcegroup

Optional Parameters

--ids

One or more resource IDs (space-delimited). It should be a complete resource ID containing all information of 'Resource Id' arguments. You should provide either --ids or other 'Resource Id' arguments.

--name -n

Name of the SQL virtual machine.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

az sql vm show

Gets a SQL virtual machine.

az sql vm show [--expand {*, AssessmentSettings, AutoBackupSettings, AutoPatchingSettings, KeyVaultCredentialSettings, ServerConfigurationsManagementSettings}]
               [--ids]
               [--name]
               [--resource-group]

Optional Parameters

--expand

Get the SQLIaaSExtension configuration settings. To view all settings, use *. To select only a few, the settings must be space-separated.

accepted values: *, AssessmentSettings, AutoBackupSettings, AutoPatchingSettings, KeyVaultCredentialSettings, ServerConfigurationsManagementSettings
--ids

One or more resource IDs (space-delimited). It should be a complete resource ID containing all information of 'Resource Id' arguments. You should provide either --ids or other 'Resource Id' arguments.

--name -n

Name of the SQL virtual machine.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

az sql vm start-assessment

Starts SQL best practice assessment on SQL virtual machine.

az sql vm start-assessment [--ids]
                           [--name]
                           [--resource-group]

Examples

Starts SQL best practice assessment.

az sql vm start-assessment -n sqlvm -g myresourcegroup

Optional Parameters

--ids

One or more resource IDs (space-delimited). It should be a complete resource ID containing all information of 'Resource Id' arguments. You should provide either --ids or other 'Resource Id' arguments.

--name -n

Name of the SQL virtual machine.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

az sql vm update

Updates the properties of a SQL virtual machine.

az sql vm update [--add]
                 [--am-day {Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday}]
                 [--am-month-occ {-1, 1, 2, 3, 4}]
                 [--am-schedule {false, true}]
                 [--am-time]
                 [--am-week-int {1, 2, 3, 4, 5, 6}]
                 [--backup-pwd]
                 [--backup-schedule-type {Automated, Manual}]
                 [--backup-system-dbs {false, true}]
                 [--connectivity-type {LOCAL, PRIVATE, PUBLIC}]
                 [--credential-name]
                 [--day-of-week {Everyday, Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday}]
                 [--enable-assessment {false, true}]
                 [--enable-auto-backup {false, true}]
                 [--enable-auto-patching {false, true}]
                 [--enable-encryption {false, true}]
                 [--enable-key-vault-credential {false, true}]
                 [--enable-r-services {false, true}]
                 [--force-string]
                 [--full-backup-duration]
                 [--full-backup-frequency {Daily, Weekly}]
                 [--full-backup-start-hour]
                 [--ids]
                 [--image-sku {Developer, Enterprise, Express, Standard, Web}]
                 [--key-vault]
                 [--license-type {AHUB, DR, PAYG}]
                 [--log-backup-frequency]
                 [--maintenance-window-duration]
                 [--maintenance-window-start-hour]
                 [--name]
                 [--port]
                 [--remove]
                 [--resource-group]
                 [--retention-period]
                 [--sa-key]
                 [--set]
                 [--sp-name]
                 [--sp-secret]
                 [--sql-mgmt-type {Full}]
                 [--sql-workload-type {DW, GENERAL, OLTP}]
                 [--storage-account]
                 [--tags]
                 [--workspace-name]
                 [--workspace-rg]
                 [--yes]

Examples

Add or update a tag.

az sql vm update -n sqlvm -g myresourcegroup --set tags.tagName=tagValue

Remove a tag.

az sql vm update -n sqlvm -g myresourcegroup --remove tags.tagName

Update a SQL virtual machine with specific sku type.

az sql vm update -n sqlvm -g myresourcegroup --image-sku Enterprise

Update a SQL virtual machine manageability from LightWeight to Full.

az sql vm update -n sqlvm -g myresourcegroup --sql-mgmt-type Full --yes

Update SQL virtual machine auto backup settings.

az sql vm update -n sqlvm -g myresourcegroup --backup-schedule-type manual --full-backup-frequency Weekly --full-backup-start-hour 2 --full-backup-duration 2 --sa-key {storageKey} --storage-account 'https://storageacc.blob.core.windows.net/' --retention-period 30 --log-backup-frequency 60

Disable SQL virtual machine auto backup settings.

az sql vm update -n sqlvm -g myresourcegroup --enable-auto-backup false

Update SQL virtual machine auto patching settings.

az sql vm update -n sqlvm -g myresourcegroup --day-of-week sunday --maintenance-window-duration 60 --maintenance-window-start-hour 2

Disable SQL virtual machine auto patching settings.

az sql vm update -n sqlvm -g myresourcegroup --enable-auto-patching false

Update a SQL virtual machine billing tag to AHUB.

az sql vm update -n sqlvm -g myresourcegroup --license-type AHUB

Update a SQL virtual machine billing tag to DR.

az sql vm update -n sqlvm -g myresourcegroup --license-type DR

Update a SQL virtual machine to disable SQL best practice assessment.

az sql vm update -n sqlvm -g myresourcegroup --enable-assessment false

Update a SQL virtual machine to disable schedule for SQL best practice assessment.

az sql vm update -n sqlvm -g myresourcegroup --enable-assessment-schedule false

Update a SQL virtual machine to enable schedule with weekly interval for SQL best practice assessment when VM is already associated with a Log Analytics workspace.

az sql vm update -n sqlvm -g myresourcegroup --assessment-weekly-interval 1 --assessment-day-of-week monday --assessment-start-time-local '19:30'

Update a SQL virtual machine to enable schedule with monthly occurrence for SQL best practice assessment while associating with a Log Analytics workspace.

az sql vm update -n sqlvm -g myresourcegroup --workspace-name myLogAnalyticsWorkspace --workspace-rg myRg --assessment-monthly-occurrence 1 --assessment-day-of-week monday --assessment-start-time-local '19:30'

Update a SQL virtual machine to enable SQL best practices assessment without setting a schedule for running assessment on-demand

az sql vm update -n sqlvm -g myresourcegroup --enable-assessment true

Optional Parameters

--add

Add an object to a list of objects by specifying a path and key value pairs. Example: --add property.listProperty <key=value, string or JSON string>.

--am-day --assessment-day-of-week

Day of the week to run assessment.

accepted values: Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday
--am-month-occ --assessment-monthly-occurrence

Occurrence of the DayOfWeek day within a month to schedule assessment. Supports values 1,2,3,4 and -1. Use -1 for last DayOfWeek day of the month (for example - last Tuesday of the month).

accepted values: -1, 1, 2, 3, 4
--am-schedule --enable-assessment-schedule

Enable or disable assessment Schedule. If any assessment schedule settings provided, parameter automatically sets to true.

accepted values: false, true
--am-time --assessment-start-time-local

Time of the day in HH:mm format. Examples include 17:30, 05:13.

--am-week-int --assessment-weekly-interval

Number of weeks to schedule between 2 assessment runs. Supports value from 1-6.

accepted values: 1, 2, 3, 4, 5, 6
--backup-pwd

Password for encryption on backup.

--backup-schedule-type

Backup schedule type.

accepted values: Automated, Manual
--backup-system-dbs

Include system databases on backup.

accepted values: false, true
--connectivity-type

SQL Server connectivity option.

accepted values: LOCAL, PRIVATE, PUBLIC
--credential-name

Credential name.

--day-of-week

Day of week to apply the patch on.

accepted values: Everyday, Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday
--enable-assessment

Enable or disable assessment feature. If any assessment settings provided, parameter automatically sets to true.

accepted values: false, true
--enable-auto-backup

Enable or disable autobackup on SQL virtual machine. If any backup settings provided, parameter automatically sets to true.

accepted values: false, true
--enable-auto-patching

Enable or disable autopatching on SQL virtual machine. If any autopatching settings provided, parameter automatically sets to true.

accepted values: false, true
--enable-encryption

Enable encryption for backup on SQL virtual machine.

accepted values: false, true
--enable-key-vault-credential

Enable or disable key vault credential setting. If any key vault settings provided, parameter automatically sets to true.

accepted values: false, true
--enable-r-services

Enable or disable R services (SQL 2016 onwards).

accepted values: false, true
--force-string

When using 'set' or 'add', preserve string literals instead of attempting to convert to JSON.

--full-backup-duration

Duration of the time window of a given day during which full backups can take place. 1-23 hours.

--full-backup-frequency

Frequency of full backups. In both cases, full backups begin during the next scheduled time window.

accepted values: Daily, Weekly
--full-backup-start-hour

Start time of a given day during which full backups can take place. 0-23 hours.

--ids

One or more resource IDs (space-delimited). It should be a complete resource ID containing all information of 'Resource Id' arguments. You should provide either --ids or other 'Resource Id' arguments.

--image-sku

SQL image sku.

accepted values: Developer, Enterprise, Express, Standard, Web
--key-vault

Azure Key Vault url.

--license-type -i

SQL Server license type.

accepted values: AHUB, DR, PAYG
--log-backup-frequency

Frequency of log backups. 5-60 minutes.

--maintenance-window-duration

Duration of patching. 30-180 minutes.

--maintenance-window-start-hour

Hour of the day when patching is initiated. Local VM time 0-23 hours.

--name -n

Name of the SQL virtual machine.

--port

SQL Server port.

--remove

Remove a property or an element from a list. Example: --remove property.list OR --remove propertyToRemove.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--retention-period

Retention period of backup. 1-30 days.

--sa-key

Storage account key where backup will be taken to.

--set

Update an object by specifying a property path and value to set. Example: --set property1.property2=.

--sp-name

Service principal name to access key vault.

--sp-secret

Service principal name secret to access key vault.

--sql-mgmt-type

SQL Server management type. Updates from LightWeight to Full.

accepted values: Full
--sql-workload-type

SQL Server workload type.

accepted values: DW, GENERAL, OLTP
--storage-account

Storage account url where backup will be taken to.

--tags

Space-separated tags: key[=value] [key[=value] ...]. Use "" to clear existing tags.

--workspace-name

Name of the Log Analytics workspace to associate with VM.

--workspace-rg

Resource group containing the Log Analytics workspace.

--yes -y

Do not prompt for confirmation. Requires --sql-mgmt-type.

default value: 1