Quickstart: Create SQL Server on a Windows virtual machine with Azure PowerShell
This quickstart steps through creating a SQL Server virtual machine (VM) with Azure PowerShell.
- This quickstart provides a path for quickly provisioning and connecting to a SQL VM. For more information about other Azure PowerShell options for creating SQL VMs, see the Provisioning guide for SQL Server VMs with Azure PowerShell.
- If you have questions about SQL Server virtual machines, see the Frequently Asked Questions.
Get an Azure subscription
If you don't have an Azure subscription, create a free account before you begin.
Get Azure PowerShell
This article has been updated to use the Azure Az PowerShell module. The Az PowerShell module 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.
Open PowerShell and establish access to your Azure account by running the Connect-AzAccount command.
When you see the sign-in window, enter your credentials. Use the same email and password that you use to sign in to the Azure portal.
Create a resource group
Define a variable with a unique resource group name. To simplify the rest of the quickstart, the remaining commands use this name as a basis for other resource names.
$ResourceGroupName = "sqlvm1"
Define a location of a target Azure region for all VM resources.
$Location = "East US"
Create the resource group.
New-AzResourceGroup -Name $ResourceGroupName -Location $Location
Configure network settings
Create a virtual network, subnet, and a public IP address. These resources are used to provide network connectivity to the virtual machine and connect it to the internet.
$SubnetName = $ResourceGroupName + "subnet" $VnetName = $ResourceGroupName + "vnet" $PipName = $ResourceGroupName + $(Get-Random) # Create a subnet configuration $SubnetConfig = New-AzVirtualNetworkSubnetConfig -Name $SubnetName -AddressPrefix 192.168.1.0/24 # Create a virtual network $Vnet = New-AzVirtualNetwork -ResourceGroupName $ResourceGroupName -Location $Location ` -Name $VnetName -AddressPrefix 192.168.0.0/16 -Subnet $SubnetConfig # Create a public IP address and specify a DNS name $Pip = New-AzPublicIpAddress -ResourceGroupName $ResourceGroupName -Location $Location ` -AllocationMethod Static -IdleTimeoutInMinutes 4 -Name $PipName
Create a network security group. Configure rules to allow remote desktop (RDP) and SQL Server connections.
# Rule to allow remote desktop (RDP) $NsgRuleRDP = New-AzNetworkSecurityRuleConfig -Name "RDPRule" -Protocol Tcp ` -Direction Inbound -Priority 1000 -SourceAddressPrefix * -SourcePortRange * ` -DestinationAddressPrefix * -DestinationPortRange 3389 -Access Allow #Rule to allow SQL Server connections on port 1433 $NsgRuleSQL = New-AzNetworkSecurityRuleConfig -Name "MSSQLRule" -Protocol Tcp ` -Direction Inbound -Priority 1001 -SourceAddressPrefix * -SourcePortRange * ` -DestinationAddressPrefix * -DestinationPortRange 1433 -Access Allow # Create the network security group $NsgName = $ResourceGroupName + "nsg" $Nsg = New-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroupName ` -Location $Location -Name $NsgName ` -SecurityRules $NsgRuleRDP,$NsgRuleSQL
Create the network interface.
$InterfaceName = $ResourceGroupName + "int" $Interface = New-AzNetworkInterface -Name $InterfaceName ` -ResourceGroupName $ResourceGroupName -Location $Location ` -SubnetId $VNet.Subnets.Id -PublicIpAddressId $Pip.Id ` -NetworkSecurityGroupId $Nsg.Id
Create the SQL VM
Define your credentials to sign in to the VM. The username is "azureadmin". Make sure you change <password> before running the command.
# Define a credential object $SecurePassword = ConvertTo-SecureString '<password>' ` -AsPlainText -Force $Cred = New-Object System.Management.Automation.PSCredential ("azureadmin", $securePassword)
Create a virtual machine configuration object and then create the VM. The following command creates a SQL Server 2017 Developer Edition VM on Windows Server 2016.
# Create a virtual machine configuration $VMName = $ResourceGroupName + "VM" $VMConfig = New-AzVMConfig -VMName $VMName -VMSize Standard_DS13_V2 | Set-AzVMOperatingSystem -Windows -ComputerName $VMName -Credential $Cred -ProvisionVMAgent -EnableAutoUpdate | Set-AzVMSourceImage -PublisherName "MicrosoftSQLServer" -Offer "SQL2017-WS2016" -Skus "SQLDEV" -Version "latest" | Add-AzVMNetworkInterface -Id $Interface.Id # Create the VM New-AzVM -ResourceGroupName $ResourceGroupName -Location $Location -VM $VMConfig
It takes several minutes to create the VM.
Register with SQL VM RP
To get portal integration and SQL VM features, you must register with the SQL IaaS Agent extension.
To get full functionality, you will need to register with the extension in full mode. However, doing so restarts the SQL Server service, so the recommended approach is to register in lightweight mode and then upgrade to full during a maintenance window.
First, register your SQL Server VM in lightweight mode:
# Get the existing compute VM $vm = Get-AzVM -Name <vm_name> -ResourceGroupName <resource_group_name> # Register SQL VM with 'Lightweight' SQL IaaS agent New-AzSqlVM -Name $vm.Name -ResourceGroupName $vm.ResourceGroupName -Location $vm.Location ` -LicenseType PAYG -SqlManagementType LightWeight
Then during a maintenance window, upgrade to full mode:
# Get the existing Compute VM $vm = Get-AzVM -Name <vm_name> -ResourceGroupName <resource_group_name> # Register with SQL IaaS Agent extension in full mode Update-AzSqlVM -Name $vm.Name -ResourceGroupName $vm.ResourceGroupName -SqlManagementType Full
Remote desktop into the VM
Use the following command to retrieve the public IP address for the new VM.
Get-AzPublicIpAddress -ResourceGroupName $ResourceGroupName | Select IpAddress
Pass the returned IP address as a command-line parameter to mstsc to start a Remote Desktop session into the new VM.
When prompted for credentials, choose to enter credentials for a different account. Enter the username with a preceding backslash (for example,
\azureadmin), and the password that you set previously in this quickstart.
Connect to SQL Server
After signing in to the Remote Desktop session, launch SQL Server Management Studio 2017 from the start menu.
In the Connect to Server dialog box, keep the defaults. The server name is the name of the VM. Authentication is set to Windows Authentication. Select Connect.
You're now connected to SQL Server locally. If you want to connect remotely, you must configure connectivity from the Azure portal or manually.
Clean up resources
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.
In this quickstart, you created a SQL Server 2017 virtual machine using Azure PowerShell. To learn more about how to migrate your data to the new SQL Server, see the following article.