Tutorial: Import SQL BACPAC files with Azure Resource Manager templates

Learn how to use Azure SQL Database extensions to import a BACPAC file. In this tutorial, you create a template to deploy an Azure SQL Server, a SQL Database, and a BACPAC file. For information about deploying Azure virtual machine extensions using Azure Resource Manager templates, see # Tutorial: Deploy virtual machine extensions with Azure Resource Manager templates.

This tutorial covers the following tasks:

  • Prepare a BACPAC file
  • Open a Quickstart template
  • Edit the template
  • Deploy the template
  • Verify the deployment

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

Prerequisites

To complete this article, you need:

Prepare a BACPAC file

A BACPAC file is shared on an Azure Storage account with the public access. To create your own, see Export an Azure SQL database to a BACPAC file. If you choose to publish the file to your own location, you must update the template later in the tutorial.

Open a Quickstart template

Azure QuickStart Templates is a repository for Resource Manager templates. Instead of creating a template from scratch, you can find a sample template and customize it. The template used in this tutorial is called Deploy an Azure SQL Server with Threat Detection.

  1. From Visual Studio Code, select File>Open File.
  2. In File name, paste the following URL:

    https://raw.githubusercontent.com/Azure/azure-quickstart-templates/master/201-sql-threat-detection-server-policy-optional-db/azuredeploy.json
    
  3. Select Open to open the file.

    There are three resources defined in the template:

    • Microsoft.Sql/servers. See the template reference.
    • Microsoft.SQL/servers/securityAlertPolicies. See the template reference.
    • Microsoft.SQL.servers/databases. See the template reference. It is helpful to get some basic understanding of the template before customizing it.
  4. Select File>Save As to save a copy of the file to your local computer with the name azuredeploy.json.

Edit the template

You need to add two additional resources to the template.

  • To allow the SQL database extension to import BACPAC files, you need to allow access to Azure services. Add the following JSON to the SQL server definition:

    {
        "type": "firewallrules",
        "name": "AllowAllAzureIps",
        "location": "[parameters('location')]",
        "apiVersion": "2014-04-01",
        "dependsOn": [
            "[variables('databaseServerName')]"
        ],
        "properties": {
            "startIpAddress": "0.0.0.0",
            "endIpAddress": "0.0.0.0"
        }
    }
    

    The template shall look like:

    Azure Resource Manager deploy sql extensions BACPAC

  • Add a SQL Database extension resource to the database definition with the following JSON:

    "resources": [
        {
            "name": "Import",
            "type": "extensions",
            "apiVersion": "2014-04-01",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers/databases', variables('databaseServerName'), variables('databaseName'))]"
            ],
            "properties": {
                "storageKeyType": "SharedAccessKey",
                "storageKey": "?",
                "storageUri": "https://armtutorials.blob.core.windows.net/sqlextensionbacpac/SQLDatabaseExtension.bacpac",
                "administratorLogin": "[variables('databaseServerAdminLogin')]",
                "administratorLoginPassword": "[variables('databaseServerAdminLoginPassword')]",
                "operationMode": "Import",
            }
        }
    ]
    

    The template shall look like:

    Azure Resource Manager deploy sql extensions BACPAC

    To understand the resource definition, see the SQL Database extension reference. The following are some important elements:

    • dependsOn: The extension resource must be created after the SQL database has been created.
    • storageKeyType: The type of the storage key to use. The value can be either StorageAccessKey or SharedAccessKey. Because the provided BACPAC file is shared on an Azure Storage account with public access, `SharedAccessKey' is used here.
    • storageKey: The storage key to use. If storage key type is SharedAccessKey, it must be preceded with a "?."
    • storageUri: The storage uri to use. If you choose not to use the BACPAC file provided, you need to update the values.
    • administratorLoginPassword: The password of the SQL administrator. It is recommended to use a generated password. See Prerequisites.

Deploy the template

Refer to the Deploy the template section for the deployment procedure. Use the following PowerShell deployment script instead:

$deploymentName = Read-Host -Prompt "Enter the name for this deployment"
$resourceGroupName = Read-Host -Prompt "Enter the Resource Group name"
$location = Read-Host -Prompt "Enter the location (i.e. centralus)"
$adminUsername = Read-Host -Prompt "Enter the virtual machine admin username"
$adminPassword = Read-Host -Prompt "Enter the admin password" -AsSecureString

New-AzureRmResourceGroup -Name $resourceGroupName -Location $location
New-AzureRmResourceGroupDeployment -Name $deploymentName `
    -ResourceGroupName $resourceGroupName `
    -adminUser $adminUsername `
    -adminPassword $adminPassword `
    -TemplateFile azuredeploy.json

It is recommended to use a generated password. See Prerequisites.

Verify the deployment

In the portal, select the SQL database from the newly deployed resource group. Select Query editor (preview), and then enter the administrator credentials. You shall see two tables imported into the database:

Azure Resource Manager deploy sql extensions BACPAC

Clean up resources

When the Azure resources are no longer needed, clean up the resources you deployed by deleting the resource group.

  1. From the Azure portal, select Resource group from the left menu.
  2. Enter the resource group name in the Filter by name field.
  3. Select the resource group name. You shall see a total of six resources in the resource group.
  4. Select Delete resource group from the top menu.

Next steps

In this tutorial, you deployed a SQL Server, a SQL Database, and imported a BACPAC file. To learn how to deploy Azure resources across multiple regions, and how to use safe deployment practices, see