Guide to creating SQL Server VM with PowerShell

Applies to: SQL Server on Azure VM

This guide covers options for using PowerShell to create 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, or for an end-to-end script, see Create SQL Server VM with PowerShell script.

Prerequisites

To complete this guide, you should have the following:

• An Azure subscription. If you don't have an Azure subscription, create a Trial Subscription before you begin.
• The latest version of Azure PowerShell

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

Start by defining the parameters to use throughout the script, such as the location, name of the resource group, the SQL Server image and storage account you want to use, as well as the network and virtual machine properties.

Location and resource group

Define the data region, resource group, and subscription where you want to create your SQL Server VM and associated resources.

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

$SubscriptionId = "<Enter Subscription ID>"
$Location = "<Enter Location>"
$ResourceGroupName = "<Enter Resource Group Name>"
$userName = "<Enter User Name for the virtual machine"

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 = "sqlvm" + "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

It's possible to deploy an older image of SQL Server that isn't available in the Azure portal by using PowerShell.

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

1. List all of the SQL Server image offerings with the Get-AzVMImageOffer command to list the current available images in the Azure portal as well as older images that you can only deploy 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.

2. List the available editions for your offer with the Get-AzVMImageSku.

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

For this tutorial, use the SQL Server 2022 Developer edition (SQLDEV-GEN2) on Windows Server 2022. The Developer edition is freely licensed for testing and development, and you only pay for the cost of running the VM:

$OfferName = "SQL2022-WS2022"
$PublisherName = "MicrosoftSQLServer"
$Version = "latest"
$Sku = "SQLDEV-GEN2"
$License = 'PAYG'

# Define a credential object
$SecurePassword = ConvertTo-SecureString '<strong password>' -AsPlainText -Force
$Cred = New-Object System.Management.Automation.PSCredential ($userName, $securePassword)

Create a resource group

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

After you've established subscription context, the first object that you create is the resource group. Use the Connect-AzAccount command to connect to Azure, and set your subscription context with Set-AzContext. 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 connect to Azure, establish subscription context and create your new resource group:

# Set subscription context
Connect-AzAccount -Environment AzureChinaCloud
$subscriptionContextParams = @{
    SubscriptionId = $SubscriptionId
}
Set-AzContext @subscriptionContextParams

# Create a resource group
$resourceGroupParams = @{
    Name = $resourceGroupName
    Location = $Location
    Tag = @{Owner="SQLDocs-Samples"}
}
$resourceGroup = New-AzResourceGroup @resourceGroupParams

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

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.

Run this cmdlet to create your virtual subnet configuration.

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

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:

# Create a 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 to connect 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.

Run this cmdlet to create your public IP address.

# Create a 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.

1. Create two network security group rules by using New-AzNetworkSecurityRuleConfig, a rule for remote desktop (RDP) to allow RDP connections, and a rule that allows traffic on TCP port 1433. Doing so enables connections to SQL Server over the internet.

# Create a network security group rule
$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

1. Create the network security group by using New-AzNetworkSecurityGroup.

# Create a 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.

# Create a 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 SQL Server VM

To create your SQL Server VM, first create a credential object, and then create the VM.

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.

# Define a credential object
$SecurePassword = ConvertTo-SecureString '<strong password>' -AsPlainText -Force
$Cred = New-Object System.Management.Automation.PSCredential ($userName, $securePassword)

Define properties and create the VM

Now you're ready to set the virtual machine's operating system properties with New-AzVMConfig, create the VM with New-AzVM, and use Add-AzVMNetworkInterface cmdlet to add the network interface using the variable that you defined earlier.

The sample script does the following:

• Require the virtual machine agent to be installed.
• Specifies that the cmdlet enables auto update.
• Specifies 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.

# Create a virtual machine configuration
$VMName = $ResourceGroupName + "VM"
$VMConfig = New-AzVMConfig -VMName $VMName -VMSize $VMSize |
    Set-AzVMOperatingSystem -Windows -ComputerName $VMName -Credential $Cred -ProvisionVMAgent -EnableAutoUpdate |
    Set-AzVMSourceImage -PublisherName $PublisherName -Offer $OfferName -Skus $Sku  -Version $Version |
    Add-AzVMNetworkInterface -Id $Interface.Id

# Create the VM
New-AzVM -ResourceGroupName $ResourceGroupName -Location $Location -VM $VMConfig

The virtual machine is created.

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 pay-as-you-go (PAYG), bring-your-own-license via the Azure Hybrid Benefit (AHUB). For more information about licensing, see licensing model.

To register your SQL Server VM with the SQL IaaS Agent extension, first register your subscription with the resource provider by using Register-AzResourceProvider, and then register your SQL Server VM with the SQL IaaS Agent extension by using New-AzSqlVM:

# Register the SQL IaaS Agent extension to your subscription
Register-AzResourceProvider -ProviderNamespace Microsoft.SqlVirtualMachine

# Register SQL Server VM with the extension
New-AzSqlVM -Name $VMName -ResourceGroupName $ResourceGroupName -Location $Location -LicenseType $License

There are three ways to register with the extension:

• Automatically for all current and future VMs in a subscription
• Manually for a single VM
• Manually for multiple VMs in bulk

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.

Full script

For a full PowerShell script that provides an end-to-end experience, see Deploy SQL Server on Azure VM with PowerShell.

Related content

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