Quickstart: Create a single database in Azure SQL Database using the Azure Resource Manager template

APPLIES TO: yesAzure SQL Database

Creating a single database is the quickest and simplest option for creating a database in Azure SQL Database. This quickstart shows you how to create a single database using the Azure Resource Manager template.

Resource Manager template is a JavaScript Object Notation (JSON) file that defines the infrastructure and configuration for your project. The template uses declarative syntax, which lets you state what you intend to deploy without having to write the sequence of programming commands to create it. If you want to learn more about developing Resource Manager templates, see Resource Manager documentation and the template reference.

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

Prerequisites

None

Create a single database

A single database has a defined set of compute, memory, IO, and storage resources using one of two purchasing models. When you create a single database, you also define a server to manage it and place it within Azure resource group in a specified region.

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": {
    "serverName": {
      "type": "string",
      "defaultValue":"[uniqueString('sql', resourceGroup().id)]",
      "metadata": {
        "description": "The name of the SQL logical server."
      }
    },
    "location": {
      "type": "string",
      "defaultValue": "[resourceGroup().location]",
      "metadata": {
        "description": "Location for all resources."
      }
    },
    "administratorLogin": {
      "type": "string",
      "metadata": {
        "description": "The administrator username of the SQL logical server."
      }
    },
    "administratorLoginPassword": {
      "type": "securestring",
      "metadata": {
        "description": "The administrator password of the SQL logical server."
      }
    },
    "enableADS": {
      "defaultValue": true,
      "type": "bool",
      "metadata": {
        "description": "Enable Advanced Data Security, the user deploying the template must have an administrator or owner permissions."
      }
    },
    "allowAzureIPs": {
      "defaultValue": true,
      "type": "bool",
      "metadata": {
        "description": "Allow Azure services to access server."
      }
    },
    "connectionType": {
      "defaultValue": "Default",
      "allowedValues": [ "Default", "Redirect", "Proxy" ],
      "type": "string",
      "metadata": {
        "description": "SQL logical server connection type."
      }
    }
  },
  "variables": {
     "serverResourceGroupName": "[resourceGroup().name]",
     "subscriptionId": "[subscription().subscriptionId]",
     "uniqueStorage": "[uniqueString(variables('subscriptionId'), variables('serverResourceGroupName'), parameters('location'))]",
     "storageName": "[tolower(concat('sqlva', variables('uniqueStorage')))]",
     "uniqueRoleGuid": "[guid(resourceId('Microsoft.Storage/storageAccounts', variables('storageName')), variables('storageBlobContributor'), resourceId('Microsoft.Sql/servers', parameters('serverName')))]",
     "StorageBlobContributor": "[subscriptionResourceId('Microsoft.Authorization/roleDefinitions', 'ba92f5b4-2d11-453d-a403-e96b0029c9fe')]"
   },
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2018-06-01-preview",
      "name": "[parameters('serverName')]",
      "location": "[parameters('location')]",
      "identity": "[if(parameters('enableADS'), json('{\"type\":\"SystemAssigned\"}'), json('null'))]",
      "properties": {
        "administratorLogin": "[parameters('administratorLogin')]",
        "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
        "version": "12.0"
      },
      "resources": [
        {
          "condition": "[parameters('allowAzureIPs')]",
          "type": "firewallRules",
          "apiVersion": "2018-06-01-preview",
          "name": "AllowAllWindowsAzureIps",
          "location": "[parameters('location')]",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
          ],
          "properties": {
             "endIpAddress": "0.0.0.0",
            "startIpAddress": "0.0.0.0"
          }
        },
        {
          "condition": "[parameters('enableADS')]",
          "type": "securityAlertPolicies",
          "apiVersion": "2017-03-01-preview",
          "name": "Default",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
          ],
          "properties": {
            "state": "Enabled",
            "emailAccountAdmins": true
          }
        },
        {
          "condition": "[parameters('enableADS')]",
          "type": "vulnerabilityAssessments",
          "apiVersion": "2018-06-01-preview",
          "name": "Default",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
            "[resourceId('Microsoft.Sql/servers/securityAlertPolicies', parameters('serverName'), 'Default')]",
            "[resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))]"
          ],
          "properties": {
            "storageContainerPath": "[if(parameters('enableADS'), concat(reference(resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))).primaryEndpoints.blob, 'vulnerability-assessment'), json('null'))]",
            "recurringScans": {
              "isEnabled": true,
              "emailSubscriptionAdmins": true
            }
          }
        },
        {
          "type": "connectionPolicies",
          "apiVersion": "2014-04-01",
          "name": "Default",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
          ],
          "properties": {
            "connectionType": "[parameters('connectionType')]"
          }
        }
      ]
    },
    {
      "condition": "[parameters('enableADS')]",
      "type": "Microsoft.Storage/storageAccounts",
      "apiVersion": "2019-06-01",
      "name": "[variables('storageName')]",
      "location": "[parameters('location')]",
      "sku": {
        "name": "Standard_LRS"
      },
      "kind": "StorageV2",
      "resources": [
        {
          "condition": "[parameters('enableADS')]",
          "type": "Microsoft.Storage/storageAccounts/providers/roleAssignments",
          "apiVersion": "2018-09-01-preview",
          "name": "[concat(variables('storageName'), '/Microsoft.Authorization/', variables('uniqueRoleGuid') )]",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
            "[resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))]"
          ],
          "properties": {
            "roleDefinitionId": "[variables('StorageBlobContributor')]",
            "principalId": "[reference(resourceId('Microsoft.Sql/servers', parameters('serverName')), '2018-06-01-preview', 'Full').identity.principalId]",
            "scope": "[resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))]",
            "principalType": "ServicePrincipal"
          }
        }
      ]
    }
  ]
}

These resources are defined in the template:

More Azure SQL Database template samples can be found in Azure Quickstart Templates.

Deploy the template

Select Try it from the following PowerShell code block to open Azure Cloud Shell.

$projectName = Read-Host -Prompt "Enter a project name that is used for generating resource names"
$location = Read-Host -Prompt "Enter an Azure location (i.e. centralus)"
$adminUser = Read-Host -Prompt "Enter the SQL server administrator username"
$adminPassword = Read-Host -Prompt "Enter the SQl server administrator password" -AsSecureString

$resourceGroupName = "${projectName}rg"

New-AzResourceGroup -Name $resourceGroupName -Location $location
New-AzResourceGroupDeployment -ResourceGroupName $resourceGroupName -TemplateUri "https://raw.githubusercontent.com/Azure/azure-quickstart-templates/master/101-sql-logical-server/azuredeploy.json" -administratorLogin $adminUser -administratorLoginPassword $adminPassword

Read-Host -Prompt "Press [ENTER] to continue ..."

Validate the deployment

To query the database, see Query the database.

Clean up resources

Keep this resource group, server, and single database if you want to go to the Next steps. The next steps show you how to connect and query your database using different methods.

To delete the resource group:

$resourceGroupName = Read-Host -Prompt "Enter the Resource Group name"
Remove-AzResourceGroup -Name $resourceGroupName

Next steps