教程：使用 ARM 模板导入 SQL BACPAC 文件Tutorial: Import SQL BACPAC files with ARM templates

04/30/2020

了解如何使用 Azure SQL 数据库扩展，以通过 Azure 资源管理器 (ARM) 模板导入 BACPAC 文件。Learn how to use Azure SQL Database extensions to import a BACPAC file with Azure Resource Manager (ARM) templates. 部署项目包括主模板文件以及完成部署所需的任何文件。Deployment artifacts are any files, in addition to the main template files, that are needed to complete a deployment. BACPAC 文件是一个项目。The BACPAC file is an artifact.

在本教程中，我们将创建一个模板来部署 Azure SQL Server 和 SQL 数据库并导入一个 BACPAC 文件。In this tutorial, you create a template to deploy an Azure SQL server and a SQL database and import a BACPAC file. 要了解如何使用 ARM 模板来部署 Azure 虚拟机扩展，请参阅教程：使用 ARM 模板部署虚拟机扩展。For information about how to deploy Azure virtual machine extensions by using ARM templates, see Tutorial: Deploy virtual machine extensions with ARM templates.

本教程涵盖以下任务：This tutorial covers the following tasks:

准备 BACPAC 文件。Prepare a BACPAC file.
打开快速入门模板。Open a quickstart template.
编辑模板。Edit the template.
部署模板。Deploy the template.
验证部署。Verify the deployment.

如果没有 Azure 订阅，请在开始前创建一个试用帐户。If you don't have an Azure subscription, create a trial account before you begin.

先决条件Prerequisites

若要完成本文，需要做好以下准备：To complete this article, you need:

包含资源管理器工具扩展的 Visual Studio Code。Visual Studio Code with the Resource Manager Tools extension. 请参阅使用 Visual Studio Code 创建 ARM 模板。See Use Visual Studio Code to create ARM templates.
若要增强安全性，请使用为 Azure SQL Server 管理员帐户生成的密码。To increase security, use a generated password for the Azure SQL Server administrator account. 下面是一个可用于生成密码的示例：Here's a sample you can use to generate a password:
```
openssl rand -base64 32
```
Azure Key Vault 旨在保护加密密钥和其他机密。Azure Key Vault is designed to safeguard cryptographic keys and other secrets. 有关详细信息，请参阅教程：在 ARM 模板部署中集成 Azure Key Vault。For more information, see Tutorial: Integrate Azure Key Vault in ARM template deployment. 我们还建议你每三个月更新一次密码。We also recommend you to update your password every three months.

准备 BACPAC 文件Prepare a BACPAC file

BACPAC 文件在 GitHub 中共享。A BACPAC file is shared in GitHub. 若要创建自己的文件，请参阅将 Azure SQL 数据库导出到 BACPAC 文件。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.

必须先将 BACPAC 文件存储在 Azure 存储帐户中，然后才能使用 ARM 模板导入该文件。The BACPAC file must be stored in an Azure Storage account before it can be imported by using an ARM template. 下面的 PowerShell 脚本通过以下步骤准备 BACPAC 文件：The following PowerShell script prepares the BACPAC file with these steps:

下载 BACPAC 文件。Download the BACPAC file.
创建 Azure 存储帐户。Create an Azure Storage account.
创建存储帐户 Blob 容器。Create a storage account blob container.
将 BACPAC 文件上传到该容器。Upload the BACPAC file to the container.
显示存储帐户密钥和 blob URL。Display the storage account key and the blob URL.

将以下 PowerShell 脚本复制并粘贴到 PowerShell 窗口中。Copy and paste the following PowerShell script into the PowerShell window.

# Sign in the Azure China Cloud
Connect-AzAccount -Environment AzureChinaCloud

$projectName = Read-Host -Prompt "Enter a project name that is used to generate Azure resource names"
$location = Read-Host -Prompt "Enter the location (i.e. chinaeast)"

$resourceGroupName = "${projectName}rg"
$storageAccountName = "${projectName}store"
$containerName = "bacpacfiles"
$bacpacFileName = "SQLDatabaseExtension.bacpac"
$bacpacUrl = "https://github.com/Azure/azure-docs-json-samples/raw/master/tutorial-sql-extension/SQLDatabaseExtension.bacpac"

# Download the bacpac file
Invoke-WebRequest -Uri $bacpacUrl -OutFile "$HOME/$bacpacFileName"

# Create a resource group
New-AzResourceGroup -Name $resourceGroupName -Location $location

# Create a storage account
$storageAccount = New-AzStorageAccount -ResourceGroupName $resourceGroupName `
                                       -Name $storageAccountName `
                                       -SkuName Standard_LRS `
                                       -Location $location
$storageAccountKey = (Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName `
                                              -Name $storageAccountName).Value[0]

# Create a container
New-AzStorageContainer -Name $containerName -Context $storageAccount.Context

# Upload the BACPAC file to the container
Set-AzStorageBlobContent -File $HOME/$bacpacFileName `
                         -Container $containerName `
                         -Blob $bacpacFileName `
                         -Context $storageAccount.Context

Write-Host "The storage account key is $storageAccountKey"
Write-Host "The BACPAC file URL is https://$storageAccountName.blob.core.chinacloudapi.cn/$containerName/$bacpacFileName"
Write-Host "Press [ENTER] to continue ..."

记下存储帐户密钥和 BACPAC 文件 URL。Write down the storage account key and the BACPAC file URL. 部署模板时需要这些值。You need these values when you deploy the template.

打开快速入门模板Open a quickstart template

本教程中使用的模板存储在 GitHub 中。The template used in this tutorial is stored in GitHub.

在 Visual Studio Code 中，选择“文件” > “打开文件”。 From Visual Studio Code, select File > Open File.

在“文件名”中粘贴以下 URL： In File name, paste the following URL:

https://raw.githubusercontent.com/Azure/azure-docs-json-samples/master/tutorial-sql-extension/azuredeploy.json

选择“打开”以打开该文件。 Select Open to open the file.

模板中定义了两个资源：There are two resources defined in the template:
- Microsoft.Sql/servers。Microsoft.Sql/servers.
- Microsoft.SQL.servers/databases。Microsoft.SQL.servers/databases.
```
 It's helpful to get some basic understanding of the template before you customize it.
```
选择“文件” > “另存为”，将该文件的副本保存到名为 azuredeploy.json 的本地计算机。 Select File > Save As to save a copy of the file to your local computer with the name azuredeploy.json.

编辑模板Edit the template

在“parameters”节的末尾再添加两个参数，以设置存储帐户密钥和 BACPAC URL 。Add two more parameters at the end of the parameters section to set the storage account key and the BACPAC URL.
```
"storageAccountKey": {
  "type":"string",
  "metadata":{
    "description": "Specifies the key of the storage account where the BACPAC file is stored."
  }
},
"bacpacUrl": {
  "type":"string",
  "metadata":{
    "description": "Specifies the URL of the BACPAC file."
  }
}
```
在“adminPassword”后面添加一个逗号。Add a comma after adminPassword. 若要从 Visual Studio Code 设置 JSON 文件的格式，请选择 Shift+Alt+F。To format the JSON file from Visual Studio Code, select Shift+Alt+F.

若要获取这两个值，请参阅准备 BACPAC 文件。To get these two values, see Prepare a BACPAC file.
向模板添加两个其他资源。Add two additional resources to the template.
- 若要允许 SQL 数据库扩展导入 BACPAC 文件，需允许来自 Azure 服务的流量通过。To allow the SQL Database extension to import BACPAC files, you need to allow traffic from Azure services. 在 SQL Server 定义下添加以下防火墙规则定义：Add the following firewall rule definition under the SQL server definition:
```
"resources": [
  {
    "type": "firewallrules",
    "apiVersion": "2015-05-01-preview",
    "name": "AllowAllAzureIps",
    "location": "[parameters('location')]",
    "dependsOn": [
      "[parameters('databaseServerName')]"
    ],
    "properties": {
      "startIpAddress": "0.0.0.0",
      "endIpAddress": "0.0.0.0"
    }
  }
]
```
  该模板如下所示：The template looks like:
- 使用以下 JSON 将 SQL 数据库扩展资源添加到数据库定义：Add a SQL Database extension resource to the database definition with the following JSON:
```
"resources": [
  {
    "type": "extensions",
    "apiVersion": "2014-04-01",
    "name": "Import",
    "dependsOn": [
      "[resourceId('Microsoft.Sql/servers/databases', parameters('databaseServerName'), parameters('databaseName'))]"
    ],
    "properties": {
      "storageKeyType": "StorageAccessKey",
      "storageKey": "[parameters('storageAccountKey')]",
      "storageUri": "[parameters('bacpacUrl')]",
      "administratorLogin": "[parameters('adminUser')]",
      "administratorLoginPassword": "[parameters('adminPassword')]",
      "operationMode": "Import"
    }
  }
]
```
  该模板如下所示：The template looks like:
  
  若要了解资源定义，请参阅 SQL 数据库扩展参考。To understand the resource definition, see the SQL Database extension reference. 下面是一些重要元素：The following are some important elements:
  - dependsOn：必须在创建 SQL 数据库以后才能创建扩展资源。dependsOn: The extension resource must be created after the SQL database has been created.
  - storageKeyType：指定要使用的存储密钥的类型。storageKeyType: Specify the type of the storage key to use. 值可以是 StorageAccessKey 或 SharedAccessKey。The value can be either StorageAccessKey or SharedAccessKey. 在本教程中使用 StorageAccessKey。Use StorageAccessKey in this tutorial.
  - storageKey：指定存储 BACPAC 文件的存储帐户的密钥。storageKey: Specify the key for the storage account where the BACPAC file is stored. 如果存储密钥类型为 SharedAccessKey，则它前面必须是“?”。If the storage key type is SharedAccessKey, it must be preceded with a "?".
  - storageUri：指定存储帐户中存储的 BACPAC 文件的 URL。storageUri: Specify the URL of the BACPAC file stored in a storage account.
  - administratorLoginPassword：SQL 管理员的密码。administratorLoginPassword: The password of the SQL administrator. 使用生成的密码。Use a generated password. 请参阅先决条件。See Prerequisites.

完成的模板如下所示：The completed template looks like:

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "databaseServerName": {
      "type": "string",
      "defaultValue": "[concat('server-', uniqueString(resourceGroup().id, deployment().name))]",
      "metadata": {
        "description": "Specifies the name for the SQL server"
      }
    },
    "databaseName": {
      "type": "string",
      "defaultValue": "[concat('db-', uniqueString(resourceGroup().id, deployment().name), '-1')]",
      "metadata": {
        "description": "Specifies the name for the SQL database under the SQL server"
      }
    },
    "location": {
      "type": "string",
      "defaultValue": "[resourceGroup().location]",
      "metadata": {
        "description": "Specifies the location for server and database"
      }
    },
    "adminUser": {
      "type": "string",
      "metadata": {
        "description": "Specifies the username for admin"
      }
    },
    "adminPassword": {
      "type": "securestring",
      "metadata": {
        "description": "Specifies the password for admin"
      }
    },
    "storageAccountKey": {
      "type": "string",
      "metadata": {
        "description": "Specifies the key of the storage account where the BACPAC file is stored."
      }
    },
    "bacpacUrl": {
      "type": "string",
      "metadata": {
        "description": "Specifies the URL of the BACPAC file."
      }
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2015-05-01-preview",
      "name": "[parameters('databaseServerName')]",
      "location": "[parameters('location')]",
      "properties": {
        "administratorLogin": "[parameters('adminUser')]",
        "administratorLoginPassword": "[parameters('adminPassword')]",
        "version": "12.0"
      },
      "resources": [
        {
          "type": "firewallrules",
          "apiVersion": "2015-05-01-preview",
          "name": "AllowAllAzureIps",
          "location": "[parameters('location')]",
          "dependsOn": [
            "[parameters('databaseServerName')]"
          ],
          "properties": {
            "startIpAddress": "0.0.0.0",
            "endIpAddress": "0.0.0.0"
          }
        }
      ]
    },
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2017-10-01-preview",
      "name": "[concat(string(parameters('databaseServerName')), '/', string(parameters('databaseName')))]",
      "location": "[parameters('location')]",
      "dependsOn": [
        "[concat('Microsoft.Sql/servers/', parameters('databaseServerName'))]"
      ],
      "resources": [
        {
          "type": "extensions",
          "apiVersion": "2014-04-01",
          "name": "Import",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers/databases', parameters('databaseServerName'), parameters('databaseName'))]"
          ],
          "properties": {
            "storageKeyType": "StorageAccessKey",
            "storageKey": "[parameters('storageAccountKey')]",
            "storageUri": "[parameters('bacpacUrl')]",
            "administratorLogin": "[parameters('adminUser')]",
            "administratorLoginPassword": "[parameters('adminPassword')]",
            "operationMode": "Import"
          }
        }
      ]
    }
  ]
}

部署模板Deploy the template

Note

本文进行了更新，以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块，至少在 2020 年 12 月之前，它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性，请参阅新 Azure Powershell Az 模块简介。To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明，请参阅安装 Azure PowerShell。For Az module installation instructions, see Install Azure PowerShell.

有关部署过程，请参阅部署模板部分。Refer to the Deploy the template section for the deployment procedure. 改用以下 PowerShell 部署脚本：Use the following PowerShell deployment script instead:

$projectName = Read-Host -Prompt "Enter the same project name that is used earlier"
$location = Read-Host -Prompt "Enter the location (i.e. chinaeast)"
$adminUsername = Read-Host -Prompt "Enter the SQL admin username"
$adminPassword = Read-Host -Prompt "Enter the admin password" -AsSecureString
$storageAccountKey = Read-Host -Prompt "Enter the storage account key"
$bacpacUrl = Read-Host -Prompt "Enter the URL of the BACPAC file"
$resourceGroupName = "${projectName}rg"

New-AzResourceGroup -Name $resourceGroupName -Location $location
New-AzResourceGroupDeployment `
    -ResourceGroupName $resourceGroupName `
    -adminUser $adminUsername `
    -adminPassword $adminPassword `
    -TemplateFile "$HOME/azuredeploy.json" `
    -storageAccountKey $storageAccountKey `
    -bacpacUrl $bacpacUrl

Write-Host "Press [ENTER] to continue ..."

请考虑使用准备 BACPAC 文件时所用的项目名称，以便将所有资源存储在同一资源组内。Consider using the same project name that you used when you prepared the BACPAC file so that all the resources are stored within the same resource group. 这样，就可以更轻松地管理资源任务，例如清理资源。In this way, it's easier to manage resource tasks, such as cleaning up the resources. 如果使用相同的项目名称，则可以从脚本中删除 New-AzResourceGroup 命令，或者在系统询问是否要更新现有资源组时回答是 (y) 或否 (n)。If you use the same project name, you can either remove the New-AzResourceGroup command from the script or answer yes (y) or no (n) when you're asked whether you want to update the existing resource group.

使用生成的密码。Use a generated password. 请参阅先决条件。See Prerequisites.

验证部署Verify the deployment

若要从客户端计算机访问 SQL Server，需要添加其他防火墙规则。To access the SQL server from your client computer, you need to add an additional firewall rule. 有关详细信息，请参阅创建和管理 IP 防火墙规则。For more information, see Create and manage IP firewall rules.

在 Azure 门户中，从新部署的资源组中选择 SQL 数据库。In the Azure portal, select the SQL database from the newly deployed resource group. 选择“查询编辑器(预览)”，然后输入管理员凭据。 Select Query editor (preview), and then enter the administrator credentials. 此时会看到两个表导入到数据库中。You'll see two tables imported into the database.

查询编辑器（预览版）

清理资源Clean up resources

不再需要 Azure 资源时，请通过删除资源组来清理部署的资源。When the Azure resources are no longer needed, clean up the resources you deployed by deleting the resource group.

在 Azure 门户上的左侧菜单中选择“资源组” 。In the Azure portal, select Resource group from the left menu.
在“按名称筛选”字段中输入资源组名称。 Enter the resource group name in the Filter by name field.
选择资源组名称。Select the resource group name. 应会看到，该资源组中总共有六个资源。You'll see a total of six resources in the resource group.
在顶部菜单中选择“删除资源组”。 Select Delete resource group from the top menu.

后续步骤Next steps

在本教程中，你部署了 SQL Server 和 SQL 数据库并导入了 BACPAC 文件。In this tutorial, you deployed a SQL server and a SQL database and imported a BACPAC file. 若要了解如何排查模板部署问题，请参阅：To learn how to troubleshoot template deployment, see:

排查 ARM 模板部署问题Troubleshoot ARM template deployments