azure - 使用 AzureRM Powershell 导入数据库

Question

有一个名为 Import a BACPAC file to create a new Azure SQL database using PowerShell 的文档涵盖如何在 ASM 下将 bacpac 文件导入 SQL Server。

是否可以使用 Azure 资源管理 cmdlet 将 bacpac 文件导入 Azure SQL Server。

根据 @juvchan 的回答，我一直在尝试让以下内容发挥作用。

$update = @{
            "operationMode" = "Import"
            "storageKey"= "Key"
            "storageKeyType" = "Primary"
            "administratorLogin"= "adminlogin"
            "administratorLoginPassword"= "adminpassword"
            "storageUri"= "https://example.blob.core.windows.net/sql/exampleIOSQL-2016-1-23-12-26.bacpac"
            }
New-AzureRmResource -ResourceGroupName "resourcegroupname" `
                -ResourceType "Microsoft.Sql/servers" `
                -Name "sqldbsvr" `
                -PropertyObject $update `
                -ApiVersion 2015-08-01 `
                -Force -Location "westeurope" 

不幸的是，除了这条非常有用的错误消息之外，我什么也得不到 -

New-AzureRmResource : {"code":"","message":"An error occurred while processing this request.","target":null,"details":[],"innererror":[]}

Answer 1

目前，最新的 Microsoft Azure PowerShell - 2016 年 1 月(版本 1.1) 的 Azure RM 模块没有有任何 cmdlet哪个可以支持 Azure SQL 数据库导入，例如 Azure 服务管理的 cmdlet，即 Start-AzureSqlDatabaseImport

但是，有一种解决方法可以在 Azure 资源管理器 (ARM) 上下文中实现此目的。

解决方法是使用包含数据库导入资源类型的用户定义的 ARM 模板进行Azure 资源组模板部署。

建议的解决方法包括示例 PowerShell 脚本、示例 ARM 模板 json 和示例 ARM 模板参数 json，如下所示:

PowerShell示例代码如下:

Login-AzureRmAccount

$tenantId = "your_tenant_id"
$subscriptionId = "your_subscription_id"

$rgName = "your_rg_name"
$location = "your_location"
$templateFile = "YourARMTemplate.json"
$templateParamFile = "YourARMTemplate.Parameters.json"

Set-AzureRmContext -SubscriptionId $subscriptionId -TenantId $tenantId

New-AzureRmResourceGroup -Location $location -Name $rgName -Force

$deployment = New-AzureRmResourceGroupDeployment -ResourceGroupName $rgName -TemplateFile $templateFile -TemplateParameterFile $templateParamFile -Mode Incremental -Force

用于 Azure SQL 数据库导入的示例 ARM 模板 json 如下:

{
  "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "variables": {
    "dbApiVersion": "2014-04-01-preview",
    "resourceGroupLocation": "[resourceGroup().location]",
    "dbServerNameTidy": "[toLower(trim(parameters('dbServerName')))]",
    "masterDbNameTidy": "[toLower(trim(parameters('masterDbName')))]"
  },
  "parameters": {
    "dbServerName": {
      "type": "string"
    },
    "dbLogin": {
      "type": "string"
    },
    "dbPassword": {
      "type": "string"
    },
    "dbServerVersion": {
      "type": "string",
      "defaultValue": "12.0"
    },
    "dbCollation": {
      "type": "string",
      "defaultValue": "SQL_Latin1_General_CP1_CI_AS"
    },
    "dbEdition": {
      "type": "string",
      "defaultValue": "Standard"
    },
    "dbMaxSize": {
      "type": "string",
      "defaultValue": "10737418240"
    },
    "dbServiceObjectiveLevel": {
      "type": "string",
      "defaultValue": "455330E1-00CD-488B-B5FA-177C226F28B7"
    },
    "bacpacStorageKey": {
      "type": "string"
    },
    "masterDbName": {
      "type": "string"
    },
    "masterBacpacUrl": {
      "type": "string"
    },
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "[variables('dbApiVersion')]",
      "properties": {
        "administratorLogin": "[parameters('dbLogin')]",
        "administratorLoginPassword": "[parameters('dbPassword')]",
        "version": "[parameters('dbServerVersion')]"
      },
      "name": "[variables('dbServerNameTidy')]",
      "location": "[variables('resourceGroupLocation')]",
      "resources": [
        {
          "type": "firewallrules",
          "apiVersion": "[variables('dbApiVersion')]",
          "properties": {
            "endIpAddress": "0.0.0.0",
            "startIpAddress": "0.0.0.0"
          },
          "name": "AllowAllWindowsAzureIps",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', variables('dbServerNameTidy'))]"
          ]
        },
        {
          "type": "databases",
          "apiVersion": "[variables('dbApiVersion')]",
          "properties": {
            "edition": "[parameters('dbEdition')]",
            "collation": "[parameters('dbCollation')]",
            "maxSizeBytes": "[parameters('dbMaxSize')]",
            "requestedServiceObjectiveId": "[parameters('dbServiceObjectiveLevel')]"
          },
          "name": "[variables('webDbNameTidy')]",
          "location": "[variables('resourceGroupLocation')]",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', variables('dbServerNameTidy'))]"
          ],
          "resources": [
            {
              "type": "extensions",
              "apiVersion": "[variables('dbApiVersion')]",
              "properties": {
                "operationMode": "Import",
                "storageKey": "[parameters('bacpacStorageKey')]",
                "storageKeyType": "Primary",
                "administratorLogin": "[parameters('dbLogin')]",
                "administratorLoginPassword": "[parameters('dbPassword')]",
                "storageUri": "[parameters('masterBacpacUrl')]"
              },
              "name": "Import",
              "dependsOn": [
                "[resourceId('Microsoft.Sql/servers/databases', variables('dbServerNameTidy'), variables('masterDbNameTidy'))]"
              ]
            }
          ]
        }
      ]
    }
  ]
}

用于 Azure SQL 数据库导入的示例 ARM 模板参数 json 如下:

{
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "dbServerName": {
            "value": "<your DB Server Name>"
        },
        "dbLogin": {
            "value": "<Your DB server login name>"
        },
        "dbPassword": {
            "value": "<Your DB server login password>"
        },
        "bacpacStorageKey": {
            "value": "<Your Azure Storage Account Primary key which stores the bacpac blob>"
        },
        "masterDbName": {
            "value": "<your Azure Sql Db name>"
        },
        "masterBacpacUrl": {
            "value": "<Your Azure storage account Bacpac blob file full Url>"
        }
    }
}

希望这对您有所帮助并澄清。