Use Azure PowerShell to create a SQL Server on Azure VM

Article
03/15/2023

This guide covers options for using PowerShell to provision a SQL Server on Azure Virtual Machine (VM). For a streamlined Azure PowerShell example that relies on default values, see the SQL Server on Azure VM PowerShell quickstart.

If you don't have an Azure subscription, create a free account before you begin.

Note

This article uses the Azure Az PowerShell module, which is the recommended PowerShell module for interacting with Azure. To get started with the Az PowerShell module, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

Configure your subscription

Open PowerShell and establish access to your Azure account by running the Connect-AzAccount command.
```
Connect-AzAccount
```
When prompted, enter your credentials. Use the same email and password that you use to sign in to the Azure portal.

Define image variables

To reuse values and simplify script creation, start by defining a number of variables. Change the parameter values as you want, but be aware of naming restrictions related to name lengths and special characters when modifying the values provided.

Location and resource group

Define the data region and the resource group where you want to create the other VM resources.

Modify as you want and then run these cmdlets to initialize these variables.

$Location = "SouthCentralUS"
$ResourceGroupName = "sqlvm2"

Storage properties

Define the storage account and the type of storage to be used by the virtual machine.

Modify as you want, and then run the following cmdlet to initialize these variables. We recommend using premium SSDs for production workloads.

$StorageName = $ResourceGroupName + "storage"
$StorageSku = "Premium_LRS"

Network properties

Define the properties to be used by the network in the virtual machine.

Network interface
TCP/IP allocation method
Virtual network name
Virtual subnet name
Range of IP addresses for the virtual network
Range of IP addresses for the subnet
Public domain name label

Modify as you want and then run this cmdlet to initialize these variables.

$InterfaceName = $ResourceGroupName + "ServerInterface"
$NsgName = $ResourceGroupName + "nsg"
$TCPIPAllocationMethod = "Dynamic"
$VNetName = $ResourceGroupName + "VNet"
$SubnetName = "Default"
$VNetAddressPrefix = "10.0.0.0/16"
$VNetSubnetAddressPrefix = "10.0.0.0/24"
$DomainName = $ResourceGroupName

Virtual machine properties

Define the following properties:

Virtual machine name
Computer name
Virtual machine size
Operating system disk name for the virtual machine

Modify as you want and then run this cmdlet to initialize these variables.

$VMName = $ResourceGroupName + "VM"
$ComputerName = $ResourceGroupName + "Server"
$VMSize = "Standard_DS13"
$OSDiskName = $VMName + "OSDisk"

Choose a SQL Server image

Use the following variables to define the SQL Server image to use for the virtual machine.

First, list all of the SQL Server image offerings with the Get-AzVMImageOffer command. This command lists the current images that are available in the Azure portal and also older images that can only be installed with PowerShell:
```
Get-AzVMImageOffer -Location $Location -Publisher 'MicrosoftSQLServer'
```
Note

SQL Server 2008 and SQL Server 2008 R2 are out of extended support and no longer available from the Azure Marketplace.
For this tutorial, use the following variables to specify SQL Server 2022 on Windows Server 2022.
```
$OfferName = "SQL2022-WS2022"
$PublisherName = "MicrosoftSQLServer"
$Version = "latest"
```

Next, list the available editions for your offer.

Get-AzVMImageSku -Location $Location -Publisher 'MicrosoftSQLServer' -Offer $OfferName | Select Skus

For this tutorial, use the SQL Server 2022 Developer edition (SQLDEV). The Developer edition is freely licensed for testing and development, and you only pay for the cost of running the VM.
```
$Sku = "SQLDEV"
```

Create a resource group

With the Resource Manager deployment model, the first object that you create is the resource group. Use the New-AzResourceGroup cmdlet to create an Azure resource group and its resources. Specify the variables that you previously initialized for the resource group name and location.

Run this cmdlet to create your new resource group.

New-AzResourceGroup -Name $ResourceGroupName -Location $Location

Create a storage account

The virtual machine requires storage resources for the operating system disk and for the SQL Server data and log files. For simplicity, you'll create a single disk for both. You can attach additional disks later using the Add-Azure Disk cmdlet to place your SQL Server data and log files on dedicated disks. Use the New-AzStorageAccount cmdlet to create a standard storage account in your new resource group. Specify the variables that you previously initialized for the storage account name, storage SKU name, and location.

Run this cmdlet to create your new storage account.

$StorageAccount = New-AzStorageAccount -ResourceGroupName $ResourceGroupName `
   -Name $StorageName -SkuName $StorageSku `
   -Kind "Storage" -Location $Location

Tip

Creating the storage account can take a few minutes.

Create network resources

The virtual machine requires a number of network resources for network connectivity.

Each virtual machine requires a virtual network.
A virtual network must have at least one subnet defined.
A network interface must be defined with either a public or a private IP address.

Create a virtual network subnet configuration

Start by creating a subnet configuration for your virtual network. For this tutorial, create a default subnet using the New-AzVirtualNetworkSubnetConfig cmdlet. Specify the variables that you previously initialized for the subnet name and address prefix.

Note

You can define additional properties of the virtual network subnet configuration using this cmdlet, but that is beyond the scope of this tutorial.

Run this cmdlet to create your virtual subnet configuration.

$SubnetConfig = New-AzVirtualNetworkSubnetConfig -Name $SubnetName -AddressPrefix $VNetSubnetAddressPrefix

Create a virtual network

Next, create your virtual network in your new resource group using the New-AzVirtualNetwork cmdlet. Specify the variables that you previously initialized for the name, location, and address prefix. Use the subnet configuration that you defined in the previous step.

Run this cmdlet to create your virtual network.

$VNet = New-AzVirtualNetwork -Name $VNetName `
   -ResourceGroupName $ResourceGroupName -Location $Location `
   -AddressPrefix $VNetAddressPrefix -Subnet $SubnetConfig

Create the public IP address

Now that your virtual network is defined, you must configure an IP address for connectivity to the virtual machine. For this tutorial, create a public IP address using dynamic IP addressing to support Internet connectivity. Use the New-AzPublicIpAddress cmdlet to create the public IP address in your new resource group. Specify the variables that you previously initialized for the name, location, allocation method, and DNS domain name label.

Note

You can define additional properties of the public IP address using this cmdlet, but that is beyond the scope of this initial tutorial. You could also create a private address or an address with a static address, but that is also beyond the scope of this tutorial.

Run this cmdlet to create your public IP address.

$PublicIp = New-AzPublicIpAddress -Name $InterfaceName `
   -ResourceGroupName $ResourceGroupName -Location $Location `
   -AllocationMethod $TCPIPAllocationMethod -DomainNameLabel $DomainName

Create the network security group

To secure the VM and SQL Server traffic, create a network security group.

First, create a network security group rule for remote desktop (RDP) to allow RDP connections.

$NsgRuleRDP = New-AzNetworkSecurityRuleConfig -Name "RDPRule" -Protocol Tcp `
   -Direction Inbound -Priority 1000 -SourceAddressPrefix * -SourcePortRange * `
   -DestinationAddressPrefix * -DestinationPortRange 3389 -Access Allow

Configure a network security group rule that allows traffic on TCP port 1433. Doing so enables connections to SQL Server over the internet.

$NsgRuleSQL = New-AzNetworkSecurityRuleConfig -Name "MSSQLRule"  -Protocol Tcp `
   -Direction Inbound -Priority 1001 -SourceAddressPrefix * -SourcePortRange * `
   -DestinationAddressPrefix * -DestinationPortRange 1433 -Access Allow

Create the network security group.

$Nsg = New-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroupName `
   -Location $Location -Name $NsgName `
   -SecurityRules $NsgRuleRDP,$NsgRuleSQL

Create the network interface

Now you're ready to create the network interface for your virtual machine. Use the New-AzNetworkInterface cmdlet to create the network interface in your new resource group. Specify the name, location, subnet, and public IP address previously defined.

Run this cmdlet to create your network interface.

$Interface = New-AzNetworkInterface -Name $InterfaceName `
   -ResourceGroupName $ResourceGroupName -Location $Location `
   -SubnetId $VNet.Subnets[0].Id -PublicIpAddressId $PublicIp.Id `
   -NetworkSecurityGroupId $Nsg.Id

Configure a VM object

Now that storage and network resources are defined, you're ready to define compute resources for the virtual machine.

Specify the virtual machine size and various operating system properties.
Specify the network interface that you previously created.
Define blob storage.
Specify the operating system disk.

Create the VM object

Start by specifying the virtual machine size. For this tutorial, specify a DS13. Use the New-AzVMConfig cmdlet to create a configurable virtual machine object. Specify the variables that you previously initialized for the name and size.

Run this cmdlet to create the virtual machine object.

$VirtualMachine = New-AzVMConfig -VMName $VMName -VMSize $VMSize

Create a credential object to hold the name and password for the local administrator credentials

Before you can set the operating system properties for the virtual machine, you must supply the credentials for the local administrator account as a secure string. To accomplish this, use the Get-Credential cmdlet.

Run the following cmdlet. You'll need to type the VM's local administrator name and password into the PowerShell credential request window.

$Credential = Get-Credential -Message "Type the name and password of the local administrator account."

Set the operating system properties for the virtual machine

Now you're ready to set the virtual machine's operating system properties with the Set-AzVMOperatingSystem cmdlet.

Set the type of operating system as Windows.
Require the virtual machine agent to be installed.
Specify that the cmdlet enables auto update.
Specify the variables that you previously initialized for the virtual machine name, the computer name, and the credential.

Run this cmdlet to set the operating system properties for your virtual machine.

$VirtualMachine = Set-AzVMOperatingSystem -VM $VirtualMachine `
   -Windows -ComputerName $ComputerName -Credential $Credential `
   -ProvisionVMAgent -EnableAutoUpdate

Add the network interface to the virtual machine

Next, use the Add-AzVMNetworkInterface cmdlet to add the network interface using the variable that you defined earlier.

Run this cmdlet to set the network interface for your virtual machine.

$VirtualMachine = Add-AzVMNetworkInterface -VM $VirtualMachine -Id $Interface.Id

Set the blob storage location for the disk to be used by the virtual machine

Next, set the blob storage location for the VM's disk with the variables that you defined earlier.

Run this cmdlet to set the blob storage location.

$OSDiskUri = $StorageAccount.PrimaryEndpoints.Blob.ToString() + "vhds/" + $OSDiskName + ".vhd"

Set the operating system disk properties for the virtual machine

Next, set the operating system disk properties for the virtual machine using the Set-AzVMOSDisk cmdlet.

Specify that the operating system for the virtual machine will come from an image.
Set caching to read only (because SQL Server is being installed on the same disk).
Specify the variables that you previously initialized for the VM name and the operating system disk.

Run this cmdlet to set the operating system disk properties for your virtual machine.

$VirtualMachine = Set-AzVMOSDisk -VM $VirtualMachine -Name `
   $OSDiskName -VhdUri $OSDiskUri -Caching ReadOnly -CreateOption FromImage

Specify the platform image for the virtual machine

The last configuration step is to specify the platform image for your virtual machine. For this tutorial, use the latest SQL Server 2016 CTP image. Use the Set-AzVMSourceImage cmdlet to use this image with the variables that you defined earlier.

Run this cmdlet to specify the platform image for your virtual machine.

$VirtualMachine = Set-AzVMSourceImage -VM $VirtualMachine `
   -PublisherName $PublisherName -Offer $OfferName `
   -Skus $Sku -Version $Version

Create the SQL VM

Now that you've finished the configuration steps, you're ready to create the virtual machine. Use the New-AzVM cmdlet to create the virtual machine using the variables that you defined.

Tip

Creating the VM can take a few minutes.

Run this cmdlet to create your virtual machine.

New-AzVM -ResourceGroupName $ResourceGroupName -Location $Location -VM $VirtualMachine

The virtual machine is created.

Note

If you get an error about boot diagnostics, you can ignore it. A standard storage account is created for boot diagnostics because the specified storage account for the virtual machine's disk is a premium storage account.

Install the SQL IaaS Agent extension

SQL Server virtual machines support automated management features with the SQL Server IaaS Agent Extension. To register your SQL Server with the extension run the New-AzSqlVM command after the virtual machine is created. Specify the license type for your SQL Server VM, choosing between either pay-as-you-go or bring-your-own-license via the Azure Hybrid Benefit. For more information about licensing, see licensing model.

New-AzSqlVM -ResourceGroupName $ResourceGroupName -Name $VMName -Location $Location -LicenseType <PAYG/AHUB>

There are three ways to register with the extension:

Stop or remove a VM

If you don't need the VM to run continuously, you can avoid unnecessary charges by stopping it when not in use. The following command stops the VM but leaves it available for future use.

Stop-AzVM -Name $VMName -ResourceGroupName $ResourceGroupName

You can also permanently delete all resources associated with the virtual machine with the Remove-AzResourceGroup command. Doing so permanently deletes the virtual machine as well, so use this command with care.

Example script

The following script contains the complete PowerShell script for this tutorial. It assumes that you have already set up the Azure subscription to use with the Connect-AzAccount and Select-AzSubscription commands.

# Variables

## Global
$Location = "SouthCentralUS"
$ResourceGroupName = "sqlvm2"

## Storage
$StorageName = $ResourceGroupName + "storage"
$StorageSku = "Premium_LRS"

## Network
$InterfaceName = $ResourceGroupName + "ServerInterface"
$NsgName = $ResourceGroupName + "nsg"
$VNetName = $ResourceGroupName + "VNet"
$SubnetName = "Default"
$VNetAddressPrefix = "10.0.0.0/16"
$VNetSubnetAddressPrefix = "10.0.0.0/24"
$TCPIPAllocationMethod = "Dynamic"
$DomainName = $ResourceGroupName

##Compute
$VMName = $ResourceGroupName + "VM"
$ComputerName = $ResourceGroupName + "Server"
$VMSize = "Standard_DS13"
$OSDiskName = $VMName + "OSDisk"

##Image
$PublisherName = "MicrosoftSQLServer"
$OfferName = "SQL2017-WS2016"
$Sku = "SQLDEV"
$Version = "latest"

# Resource Group
New-AzResourceGroup -Name $ResourceGroupName -Location $Location

# Storage
$StorageAccount = New-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageName -SkuName $StorageSku -Kind "Storage" -Location $Location

# Network
$SubnetConfig = New-AzVirtualNetworkSubnetConfig -Name $SubnetName -AddressPrefix $VNetSubnetAddressPrefix
$VNet = New-AzVirtualNetwork -Name $VNetName -ResourceGroupName $ResourceGroupName -Location $Location -AddressPrefix $VNetAddressPrefix -Subnet $SubnetConfig
$PublicIp = New-AzPublicIpAddress -Name $InterfaceName -ResourceGroupName $ResourceGroupName -Location $Location -AllocationMethod $TCPIPAllocationMethod -DomainNameLabel $DomainName
$NsgRuleRDP = New-AzNetworkSecurityRuleConfig -Name "RDPRule" -Protocol Tcp -Direction Inbound -Priority 1000 -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange 3389 -Access Allow
$NsgRuleSQL = New-AzNetworkSecurityRuleConfig -Name "MSSQLRule"  -Protocol Tcp -Direction Inbound -Priority 1001 -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange 1433 -Access Allow
$Nsg = New-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroupName -Location $Location -Name $NsgName -SecurityRules $NsgRuleRDP,$NsgRuleSQL
$Interface = New-AzNetworkInterface -Name $InterfaceName -ResourceGroupName $ResourceGroupName -Location $Location -SubnetId $VNet.Subnets[0].Id -PublicIpAddressId $PublicIp.Id -NetworkSecurityGroupId $Nsg.Id

# Compute
$VirtualMachine = New-AzVMConfig -VMName $VMName -VMSize $VMSize
$Credential = Get-Credential -Message "Type the name and password of the local administrator account."
$VirtualMachine = Set-AzVMOperatingSystem -VM $VirtualMachine -Windows -ComputerName $ComputerName -Credential $Credential -ProvisionVMAgent -EnableAutoUpdate #-TimeZone = $TimeZone
$VirtualMachine = Add-AzVMNetworkInterface -VM $VirtualMachine -Id $Interface.Id
$OSDiskUri = $StorageAccount.PrimaryEndpoints.Blob.ToString() + "vhds/" + $OSDiskName + ".vhd"
$VirtualMachine = Set-AzVMOSDisk -VM $VirtualMachine -Name $OSDiskName -VhdUri $OSDiskUri -Caching ReadOnly -CreateOption FromImage

# Image
$VirtualMachine = Set-AzVMSourceImage -VM $VirtualMachine -PublisherName $PublisherName -Offer $OfferName -Skus $Sku -Version $Version

# Create the VM in Azure
New-AzVM -ResourceGroupName $ResourceGroupName -Location $Location -VM $VirtualMachine

# Add the SQL IaaS Agent Extension, and choose the license type
New-AzSqlVM -ResourceGroupName $ResourceGroupName -Name $VMName -Location $Location -LicenseType <PAYG/AHUB>

Next steps

After the virtual machine is created, you can:

Connect to the virtual machine using RDP
Configure SQL Server settings in the portal for your VM, including:
- Storage settings
- Automated management tasks
Configure connectivity
Connect clients and applications to the new SQL Server instance

Use Azure PowerShell to create a SQL Server on Azure VM

Configure your subscription

Define image variables

Location and resource group

Storage properties

Network properties

Virtual machine properties

Choose a SQL Server image

Create a resource group

Create a storage account

Create network resources

Create a virtual network subnet configuration

Create a virtual network

Create the public IP address

Create the network security group

Create the network interface

Configure a VM object

Create the VM object

Create a credential object to hold the name and password for the local administrator credentials

Set the operating system properties for the virtual machine

Add the network interface to the virtual machine

Set the blob storage location for the disk to be used by the virtual machine

Set the operating system disk properties for the virtual machine

Specify the platform image for the virtual machine

Create the SQL VM

Install the SQL IaaS Agent extension

Stop or remove a VM

Example script

Next steps

Feedback

Feedback

Additional resources