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 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
[--boostrap-acc-pwd]
[--bootstrap-acc-pwd]
[--ids]
[--name]
[--operator-acc-pwd]
[--resource-group]
[--service-acc-pwd]
[--subscription]

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

--boostrap-acc-pwd

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

--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). If provided, no other 'Resource Id' arguments should be specified.

--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.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

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 {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]
[--key-vault]
[--license-type {AHUB, 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-workload-type {DW, GENERAL, OLTP}]
[--storage-account]
[--subscription]
[--tags]

Examples

Create a SQL virtual machine with AHUB billing tag.

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

Enable R services in SQL2016 onwards.

az sql vm create -n sqlvm -g myresourcegroup -l eastus --enable-r-services true

Create SQL virtual machine and configure auto backup settings.

az sql vm create -n sqlvm -g myresourcegroup -l eastus --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 --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 --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: 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.

--key-vault

Azure Key Vault url.

--license-type

SQL Server license type.

accepted values: AHUB, 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-workload-type

SQL Server workload type.

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

Storage account url where backup will be taken to.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--tags

Space-separated tags in 'key[=value]' format. Use "" to clear existing tags.

az sql vm delete

Deletes a SQL virtual machine.

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

Optional Parameters

--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

--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>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--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]
[--subscription]

Optional Parameters

--resource-group -g

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

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

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]
[--subscription]

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). If provided, no other 'Resource Id' arguments should be specified.

--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>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

az sql vm show

Gets a SQL virtual machine.

az sql vm show [--expand]
[--ids]
[--name]
[--resource-group]
[--subscription]

Optional Parameters

--expand

Get the SQLIaaSExtension configuration settings.

--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

--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>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

az sql vm update

Updates the properties of a SQL virtual machine.

az sql vm update [--add]
[--backup-pwd]
[--backup-schedule-type {Automated, Manual}]
[--backup-system-dbs {false, true}]
[--connectivity-type {LOCAL, PRIVATE, PUBLIC}]
[--credential-name]
[--day-of-week {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}]
[--force-string]
[--full-backup-duration]
[--full-backup-frequency {Daily, Weekly}]
[--full-backup-start-hour]
[--ids]
[--key-vault]
[--license-type {AHUB, 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-workload-type {DW, GENERAL, OLTP}]
[--storage-account]
[--subscription]
[--tags]

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 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

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>.

--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: 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
--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). If provided, no other 'Resource Id' arguments should be specified.

--key-vault

Azure Key Vault url.

--license-type

SQL Server license type.

accepted values: AHUB, 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-workload-type

SQL Server workload type.

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

Storage account url where backup will be taken to.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--tags

Space-separated tags in 'key[=value]' format. Use "" to clear existing tags.