Skip to content

sql-vm-create-powershell-quickstart.md

Latest commit

 

History

History
191 lines (131 loc) · 7.87 KB

sql-vm-create-powershell-quickstart.md

File metadata and controls

191 lines (131 loc) · 7.87 KB
title Create SQL Server on a Windows virtual machine with Azure PowerShell
description This tutorial shows how to use Azure PowerShell to create a Windows virtual machine running SQL Server 2017.
author bluefooted
ms.author pamela
ms.reviewer mathoma
ms.date 12/21/2018
ms.service virtual-machines-sql
ms.subservice deployment
ms.topic quickstart
ms.custom
devx-track-azurepowershell
mode-api
tags azure-resource-manager

Quickstart: Create SQL Server on a Windows virtual machine with Azure PowerShell

[!INCLUDEappliesto-sqlvm]

This quickstart steps through creating a SQL Server virtual machine (VM) with Azure PowerShell.

Tip

Get an Azure subscription

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

Get Azure PowerShell

[!INCLUDE updated-for-az.md]

Configure PowerShell

  1. Open PowerShell and establish access to your Azure account by running the Connect-AzAccount command.

    Connect-AzAccount

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

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

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

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

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

    [!TIP] 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 need to register with the extension in full mode. 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. 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

  1. After signing in to the Remote Desktop session, launch SQL Server Management Studio 2017 from the start menu.

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

Next steps

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.

[!div class="nextstepaction"] Migration guide: SQL Server to SQL Server on Azure Virtual Machines