Azure SQL Virtual Machine

Create SQL Server on a Windows virtual machine with Azure PowerShell

The first thing to do is to take an Azure subscription. If you don't have one, create a free account before you begin.

Configure PowerShell

1. Open PowerShell. Enter Connect-AzAccount command and establish access to your Azure account. 

   Connect-AzAccount

2. Enter your credentials as soon as you see the sign-in window. Using the same email and password, sign in to the Azure portal.

Create a resource group

1. Define a variable by giving a unique resource group name. 

  $ResourceGroupName = "sqlvm1"

2. Define a location of a target Azure region for all VM resources.

  $Location = "East US"

3. Create the resource group.

   New-AzResourceGroup -Name $ResourceGroupName -Location $Location

Configure network settings

1. Make a virtual network, subnet, and public IP address. These resources 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

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

3. Create the network interface.

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

Create the SQL VM

1. To sign in to the VM, define your credentials. The username is "azureadmin". 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)

2. Create a virtual machine configuration object and then create the VM. 

# Create a virtual machine configuration
$VMName = $ResourceName + "VM"
$VMConfig = New-AzVMConfig -VMName $VMName -VMSize Standard_V2 |
   Set-AzVMOperatingSystem -Windows -ComputerName $VMName -Credential $ -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

Register with SQL VM RP

You must register with the SQL IaaS Agent extension to get portal integration and SQL VM features. You need to register with the extension in full mode to get full functionality. Otherwise, register in lightweight mode.

Remote desktop into the VM

1. Use the following command to retrieve the public IP address for the new VM.

Get-AzPublicIpAddress -ResourceGroupName $ResourceGroupName | Select IpAddress

2. Pass the returned IP address as a command-line parameter to mstsc to start a Remote Desktop session into the new VM.

mstsc /v:<publicIpAddress>

3. Enter credentials for a different account when prompted for credentials. Enter the username with a preceding backslash and the password you set in this quickstart.

Connect to SQL Server

1. Launch SQL Server Management Studio 2017 from the start menu after signing in to the Remote Desktop session
2. Keep the defaults in the Connect to Server dialog box. Select Connect.

You will be connected to SQL Server locally. You should configure connectivity from the Azure portal.

Clean up resources

You can avoid unnecessary charges by stopping it when not in use if you don't need the VM to run continuously.

Stop-AzVM -Name $VMName -ResourceGroupName $ResourceGroupName

If you want to permanently delete all resources associated with the virtual machine use the Remove-AzResourceGroup command.

Frequently Asked Questions

Can I install SQL Server on Azure VM?

Yes, provided there is a default instance on the VM.

Can I use my own SQL license in Azure?

Azure Hybrid Benefit allows you to use your own SQL Server license with a VM that's running SQL Server.

Is Azure SQL Database same as SQL Server?

Since Azure SQL is based on SQL Server, they share many similarities but they are not the same.

Conclusion

In this article, we have extensively discussed how to create SQL Server on a Windows virtual machine in the Azure portal and with Powershell. Having gone through this article, I am sure you must be excited to read similar blogs. Coding Ninjas has got you covered. Here are some similar blogs to redirect:  We hope this blog has helped you enhance your knowledge, and if you wish to learn more, check out our Coding Ninjas Blog site and visit our Library. You can also consider our Online Coding Courses such as the Machine Learning Course to give your career an edge over others.

Do upvote our blog to help other ninjas grow.

Happy Learning!