Quickstart: Create SQL Server VM using an ARM template

Use this Azure Resource Manager template (ARM template) to deploy a SQL Server on Azure Virtual Machine (VM).

An ARM template is a JavaScript Object Notation (JSON) file that defines the infrastructure and configuration for your project. The template uses declarative syntax. In declarative syntax, you describe your intended deployment without writing the sequence of programming commands to create the deployment.

If your environment meets the prerequisites and you're familiar with using ARM templates, select the Deploy to Azure button. The template will open in the Azure portal.

Deploy to Azure

Prerequisites

The SQL Server VM ARM template requires the following:

Review the template

The template used in this quickstart is from Azure Quickstart Templates.

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "virtualMachineName": {
      "type": "String",
      "defaultValue": "myVM",
      "metadata": {
        "description": "The name of the VM"
      }
    },
    "virtualMachineSize": {
      "type": "String",
      "defaultValue": "Standard_D8s_v3",
      "metadata": {
        "description": "The virtual machine size."
      }
    },
    "existingVirtualNetworkName": {
      "type": "String",
      "metadata": {
        "description": "Specify the name of an existing VNet in the same resource group"
      }
    },
    "existingVnetResourceGroup": {
      "type": "String",
      "defaultValue": "[resourceGroup().name]",
      "metadata": {
        "description": "Specify the resrouce group of the existing VNet"
      }
    },
    "existingSubnetName": {
      "type": "String",
      "metadata": {
        "description": "Specify the name of the Subnet Name"
      }
    },
    "imageOffer": {
      "type": "String",
      "defaultValue": "sql2019-ws2019",
      "allowedValues": [
        "sql2019-ws2019",
        "sql2017-ws2019",
        "SQL2017-WS2016",
        "SQL2016SP1-WS2016",
        "SQL2016SP2-WS2016",
        "SQL2014SP3-WS2012R2",
        "SQL2014SP2-WS2012R2"
      ],
      "metadata": {
        "description": "Windows Server and SQL Offer"
      }
    },
    "sqlSku": {
      "type": "String",
      "defaultValue": "Standard",
      "allowedValues": [
        "Standard",
        "Enterprise",
        "SQLDEV",
        "Web",
        "Express"
      ],
      "metadata": {
        "description": "SQL Server Sku"
      }
    },
    "adminUsername": {
      "type": "String",
      "metadata": {
        "description": "The admin user name of the VM"
      }
    },
    "adminPassword": {
      "type": "SecureString",
      "metadata": {
        "description": "The admin password of the VM"
      }
    },
    "storageWorkloadType": {
      "type": "String",
      "defaultValue": "General",
      "allowedValues": [
        "General",
        "OLTP",
        "DW"
      ],
      "metadata": {
        "description": "SQL Server Workload Type"
      }
    },
    "sqlDataDisksCount": {
      "type": "int",
      "defaultValue": 1,
      "minValue": 1,
      "maxValue": 8,
      "metadata": {
        "description": "Amount of data disks (1TB each) for SQL Data files"
      }
    },
    "dataPath": {
      "type": "String",
      "defaultValue": "F:\\SQLData",
      "metadata": {
        "description": "Path for SQL Data files. Please choose drive letter from F to Z, and other drives from A to E are reserved for system"
      }
    },
    "sqlLogDisksCount": {
      "type": "int",
      "defaultValue": 1,
      "minValue": 1,
      "maxValue": 8,
      "metadata": {
        "description": "Amount of data disks (1TB each) for SQL Log files"
      }
    },
    "logPath": {
      "type": "String",
      "defaultValue": "G:\\SQLLog",
      "metadata": {
        "description": "Path for SQL Log files. Please choose drive letter from F to Z and different than the one used for SQL data. Drive letter from A to E are reserved for system"
      }
    },
    "location": {
      "type": "string",
      "defaultValue": "[resourceGroup().location]",
      "metadata": {
        "description": "Location for all resources."
      }
    }
  },
  "variables": {
    "networkInterfaceName": "[concat(parameters('virtualMachineName'), '-nic')]",
    "networkSecurityGroupName": "[concat(parameters('virtualMachineName'), '-nsg')]",
    "networkSecurityGroupRules": [
      {
        "name": "RDP",
        "properties": {
          "priority": 300,
          "protocol": "TCP",
          "access": "Allow",
          "direction": "Inbound",
          "sourceAddressPrefix": "*",
          "sourcePortRange": "*",
          "destinationAddressPrefix": "*",
          "destinationPortRange": "3389"
        }
      }
    ],
    "publicIpAddressName": "[concat(parameters('virtualMachineName'), '-publicip-', uniqueString(parameters('virtualMachineName')))]",
    "publicIpAddressType": "Dynamic",
    "publicIpAddressSku": "Basic",
    "diskConfigurationType": "NEW",
    "nsgId": "[resourceId('Microsoft.Network/networkSecurityGroups', variables('networkSecurityGroupName'))]",
    "subnetRef": "[resourceID(parameters('existingVNetResourceGroup'), 'Microsoft.Network/virtualNetWorks/subnets', parameters('existingVirtualNetworkName'), parameters('existingSubNetName'))]",
    "dataDisksLuns": "[array(range(0 ,parameters('sqlDataDisksCount')))]",
    "logDisksLuns": "[array(range(parameters('sqlDataDisksCount'), parameters('sqlLogDisksCount')))]",
    "dataDisks": {
      "createOption": "empty",
      "caching": "ReadOnly",
      "writeAcceleratorEnabled": false,
      "storageAccountType": "Premium_LRS",
      "diskSizeGB": 1023
    },
    "tempDbPath": "D:\\SQLTemp"
  },
  "resources": [
    {
      "type": "Microsoft.Network/publicIpAddresses",
      "apiVersion": "2020-06-01",
      "name": "[variables('publicIpAddressName')]",
      "location": "[parameters('location')]",
      "sku": {
        "name": "[variables('publicIpAddressSku')]"
      },
      "properties": {
        "publicIpAllocationMethod": "[variables('publicIpAddressType')]"
      }
    },
    {
      "type": "Microsoft.Network/networkSecurityGroups",
      "apiVersion": "2020-06-01",
      "name": "[variables('networkSecurityGroupName')]",
      "location": "[parameters('location')]",
      "properties": {
        "securityRules": "[variables('networkSecurityGroupRules')]"
      }
    },
    {
      "type": "Microsoft.Network/networkInterfaces",
      "apiVersion": "2020-06-01",
      "name": "[variables('networkInterfaceName')]",
      "location": "[parameters('location')]",
      "dependsOn": [
        "[resourceId('Microsoft.Network/networkSecurityGroups/', variables('networkSecurityGroupName'))]",
        "[resourceId('Microsoft.Network/publicIpAddresses/', variables('publicIpAddressName'))]"
      ],
      "properties": {
        "ipConfigurations": [
          {
            "name": "ipconfig1",
            "properties": {
              "subnet": {
                "id": "[variables('subnetRef')]"
              },
              "privateIPAllocationMethod": "Dynamic",
              "publicIpAddress": {
                "id": "[resourceId('Microsoft.Network/publicIpAddresses', variables('publicIpAddressName'))]"
              }
            }
          }
        ],
        "enableAcceleratedNetworking": true,
        "networkSecurityGroup": {
          "id": "[variables('nsgId')]"
        }
      }
    },
    {
      "type": "Microsoft.Compute/virtualMachines",
      "apiVersion": "2020-06-01",
      "name": "[parameters('virtualMachineName')]",
      "location": "[parameters('location')]",
      "dependsOn": [
        "[resourceId('Microsoft.Network/networkInterfaces/', variables('networkInterfaceName'))]"
      ],
      "properties": {
        "hardwareProfile": {
          "vmSize": "[parameters('virtualMachineSize')]"
        },
        "storageProfile": {
          "osDisk": {
            "createOption": "fromImage",
            "managedDisk": {
              "storageAccountType": "Premium_LRS"
            }
          },
          "imageReference": {
            "publisher": "MicrosoftSQLServer",
            "offer": "[parameters('imageOffer')]",
            "sku": "[parameters('sqlSku')]",
            "version": "latest"
          },
          "copy": [
            {
              "name": "dataDisks",
              "count": "[add(parameters('sqlDataDisksCount'), parameters('sqlLogDisksCount'))]",
              "input": {
                "lun": "[copyIndex('dataDisks')]",
                "createOption": "[variables('dataDisks').createOption]",
                "caching": "[if(greaterOrEquals(copyIndex('dataDisks'), parameters('sqlDataDisksCount')) ,'None', variables('dataDisks').caching )]",
                "writeAcceleratorEnabled": "[variables('dataDisks').writeAcceleratorEnabled]",
                "diskSizeGB": "[variables('dataDisks').diskSizeGB]",
                "managedDisk": {
                  "storageAccountType": "[variables('dataDisks').storageAccountType]"
                }
              }
            }
          ]
        },
        "networkProfile": {
          "networkInterfaces": [
            {
              "id": "[resourceId('Microsoft.Network/networkInterfaces', variables('networkInterfaceName'))]"
            }
          ]
        },
        "osProfile": {
          "computerName": "[parameters('virtualMachineName')]",
          "adminUsername": "[parameters('adminUsername')]",
          "adminPassword": "[parameters('adminPassword')]",
          "windowsConfiguration": {
            "enableAutomaticUpdates": true,
            "provisionVmAgent": true
          }
        }
      }
    },
    {
      "type": "Microsoft.SqlVirtualMachine/SqlVirtualMachines",
      "apiVersion": "2017-03-01-preview",
      "name": "[parameters('virtualMachineName')]",
      "location": "[parameters('location')]",
      "dependsOn": [
        "[resourceId('Microsoft.Compute/virtualMachines', parameters('virtualMachineName'))]"
      ],
      "properties": {
        "virtualMachineResourceId": "[resourceId('Microsoft.Compute/virtualMachines', parameters('virtualMachineName'))]",
        "sqlManagement": "Full",
        "SqlServerLicenseType": "PAYG",
        "StorageConfigurationSettings": {
          "DiskConfigurationType": "[variables('diskConfigurationType')]",
          "StorageWorkloadType": "[parameters('storageWorkloadType')]",
          "SQLDataSettings": {
            "LUNs": "[variables('dataDisksLUNs')]",
            "DefaultFilePath": "[parameters('dataPath')]"
          },
          "SQLLogSettings": {
            "Luns": "[variables('logDisksLUNs')]",
            "DefaultFilePath": "[parameters('logPath')]"
          },
          "SQLTempDbSettings": {
            "DefaultFilePath": "[variables('tempDbPath')]"
          }
        }
      }
    }
  ],
  "outputs": {
    "adminUsername": {
      "type": "String",
      "value": "[parameters('adminUsername')]"
    }
  }
}

Five Azure resources are defined in the template:

More SQL Server on Azure VM templates can be found in the quickstart template gallery.

Deploy the template

  1. Select the following image to sign in to Azure and open a template. The template creates a virtual machine with the intended SQL Server version installed to it, and registered with the SQL IaaS Agent extension.

    Deploy to Azure

  2. Select or enter the following values.

    • Subscription: Select an Azure subscription.
    • Resource group: The prepared resource group for your SQL Server VM.
    • Region: Select a region. For example, Central US.
    • Virtual Machine Name: Enter a name for SQL Server virtual machine.
    • Virtual Machine Size: Choose the appropriate size for your virtual machine from the drop-down.
    • Existing Virtual Network Name: Enter the name of the prepared virtual network for your SQL Server VM.
    • Existing Vnet Resource Group: Enter the resource group where your virtual network was prepared.
    • Existing Subnet Name: The name of your prepared subnet.
    • Image Offer: Choose the SQL Server and Windows Server image that best suits your business needs.
    • SQL Sku: Choose the edition of SQL Server SKU that best suits your business needs.
    • Admin Username: The username for the administrator of the virtual machine.
    • Admin Password: The password used by the VM administrator account.
    • Storage Workload Type: The type of storage for the workload that best matches your business.
    • Sql Data Disks Count: The number of disks SQL Server uses for data files.
    • Data Path: The path for the SQL Server data files.
    • Sql Log Disks Count: The number of disks SQL Server uses for log files.
    • Log Path: The path for the SQL Server log files.
    • Location: The location for all of the resources, this value should remain the default of [resourceGroup().location].
  3. Select Review + create. After the SQL Server VM has been deployed successfully, you get a notification.

The Azure portal is used to deploy the template. In addition to the Azure portal, you can also use Azure PowerShell, the Azure CLI, and REST API. To learn other deployment methods, see Deploy templates.

Review deployed resources

You can use the Azure CLI to check deployed resources.

echo "Enter the resource group where your SQL Server VM exists:" &&
read resourcegroupName &&
az resource list --resource-group $resourcegroupName 

Clean up resources

When no longer needed, delete the resource group by using Azure CLI or Azure PowerShell:

echo "Enter the Resource Group name:" &&
read resourceGroupName &&
az group delete --name $resourceGroupName &&
echo "Press [ENTER] to continue ..."

Next steps

For a step-by-step tutorial that guides you through the process of creating a template, see:

For other ways to deploy a SQL Server VM, see:

To learn more, see an overview of SQL Server on Azure VMs.