Azure Database - Import Create New DTU

To use this template in Octopus Deploy, copy the JSON below and paste it into the Library → Step templates → Import dialog.

{
  "Id": "cc3b7dd9-f107-4477-acc4-4d1ffdf9e820",
  "Name": "Azure Database - Import Create New DTU",
  "Description": "restores a bacpac to a new database\n\n*Depends on az cli*\n\n*Source database requires 'Allow Azure services and resources to access this server' option turn on in the SQL server firewall*",
  "Version": 1,
  "ExportedAt": "2020-06-23T20:12:56.412Z",
  "ActionType": "Octopus.AzurePowerShell",
  "Author": "xtreampb",
  "Packages": [],
  "Parameters": [
    {
      "Id": "918c8c99-4afe-4d8b-8c3d-3f7d5443a951",
      "Name": "azDbImportNewDTU.azAccount",
      "Label": "Azure Account",
      "HelpText": "Azure account with permissions to the subscription and resource group",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "AzureAccount"
      }
    },
    {
      "Id": "dcf78060-568c-4717-87d5-d1d2d1044418",
      "Name": "azDbImportNewDTU.resourceGroup",
      "Label": "Resource Group",
      "HelpText": "Resource group name housing the target SQL server",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "2a35cc98-c991-4bbf-bf80-b6fb74b5d066",
      "Name": "azDbImportNewDTU.server",
      "Label": "SQL Server",
      "HelpText": "Name of the Azure SQL server",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "aad2ddb7-ea11-4e7b-b90a-8ccb354e4a60",
      "Name": "azDbImportNewDTU.dbName",
      "Label": "Database Name",
      "HelpText": "Name of the database that will be created",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "1c794a0d-d7f2-4757-875a-e2e0148e8fd1",
      "Name": "azDbImportNewDTU.serviceObjective",
      "Label": "Service Objective",
      "HelpText": "The service objective for the new database",
      "DefaultValue": "Basic",
      "DisplaySettings": {
        "Octopus.ControlType": "Select",
        "Octopus.SelectOptions": "Basic|Basic\nS0|S0\nS1|S1\nS3|S3\nS4|S4\nS6|S6\nS7|S7\nS9|S9\nS12|S12\nP1|P1\nP2|P2\nP4|P4\nP6|P6\nP11|P11\nP15|P15"
      }
    },
    {
      "Id": "81cef05c-98d2-4e82-bc78-f1bcb6536291",
      "Name": "azDbImportNewDTU.elasticPool",
      "Label": "Elastic Pool",
      "HelpText": "The name or resource id of the elastic pool to create the database in",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "48128da8-94e8-4560-8fa7-7c30e68d1849",
      "Name": "azDbImportNewDTU.readScale",
      "Label": "Read Scale",
      "HelpText": "If enabled, connections that have application intent set to readonly in their connection string may be routed to a readonly secondary replica.  This property is only settable for Premium and Business Critical databases",
      "DefaultValue": "false",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Id": "4a52b908-7515-4089-91ad-0736e6bb687c",
      "Name": "azDbImportNewDTU.zoneRedundant",
      "Label": "Zone Redundant",
      "HelpText": "Specifies whether to enable zone redundancy",
      "DefaultValue": "false",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Id": "948d5fa8-7d9a-4d51-bcd3-6ac6eaf37d3b",
      "Name": "azDbImportNewDTU.maxsize",
      "Label": "Max Storage Size",
      "HelpText": "The max storage size.  If no unit is specified, defaults to bytes (B)",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "4bc52675-3963-4bdd-9ca4-b2611dc968eb",
      "Name": "azDbImportNewDTU.tags",
      "Label": "Tags",
      "HelpText": "Space-separated tags.  `key[=value] key[=value]`",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "40715e88-4892-48c6-83f2-603c0193439b",
      "Name": "azDbImportNewDTU.blobAccessKey",
      "Label": "Azure Blob Access Key",
      "HelpText": "the access key (Shared Access Key or Storage Access Key) to grant access to the storage account",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    },
    {
      "Id": "fcb94050-5f81-410e-9ed9-c5f52653f9c6",
      "Name": "azDbImportNewDTU.accessKeyType",
      "Label": "Access Key Type",
      "HelpText": "The type of key the access key represents",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Select",
        "Octopus.SelectOptions": "SharedAccessKey|Shared Access Key\nStorageAccessKey|Storage Access Key"
      }
    },
    {
      "Id": "a8631db7-556e-4532-aacd-595266c4347f",
      "Name": "azDbImportNewDTU.ContainerUri",
      "Label": "Container Uri",
      "HelpText": "The URI of the container to save the exported database in.  Format is: `https://{StorageAccountName}.blob.core.windows.net/{ContainerName}`",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "85686923-5e57-4b1d-8dd7-6b5ca263ba42",
      "Name": "azDbImportNewDTU.backupName",
      "Label": "Backup Name",
      "HelpText": "The name of the file being saved.  Defaults to DB Name",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "6da5679d-1c84-4fb4-8f91-22fd91b50baa",
      "Name": "azDbImportNewDTU.adminName",
      "Label": "SQL Server Admin Name",
      "HelpText": "The admin name of the SQL server containing the database you wish to import",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "44e1a2a3-6b9b-44a5-893d-d26f14166b52",
      "Name": "azDbImportNewDTU.adminPwd",
      "Label": "Admin Password",
      "HelpText": "The admin password of the SQL server containing the database you wish to import",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    }
  ],
  "Properties": {
    "Octopus.Action.Script.ScriptSource": "Inline",
    "Octopus.Action.Script.Syntax": "PowerShell",
    "Octopus.Action.Script.ScriptBody": "#get variables into easy to use format\n# Create DB Variables\n$databaseName = $OctopusParameters[\"azDbImportNewDTU.dbName\"]\n$sqlServer = $OctopusParameters[\"azDbImportNewDTU.server\"]\n$rgName = $OctopusParameters[\"azDbImportNewDTU.resourceGroup\"]\n$elasticPool = $OctopusParameters[\"azDbImportNewDTU.elasticPool\"]\n$readScaleTruthy = $OctopusParameters[\"azDbImportNewDTU.readScale\"]\n$serviceObjective = $OctopusParameters[\"azDbImportNewDTU.serviceObjective\"]\n$tags = $OctopusParameters[\"azDbImportNewDTU.tags\"]\n$zoneRedundant = $OctopusParameters[\"azDbImportNewDTU.zoneRedundant\"]\n$maxSize = $OctopusParameters[\"azDbImportNewDTU.maxSize\"]\n\n# Import bacpac variables\n$adminName = $OctopusParameters[\"azDbImportNewDTU.adminName\"]\n$adminPwd = $OctopusParameters[\"azDbImportNewDTU.adminPwd\"]\n$accessKey = $OctopusParameters[\"azDbImportNewDTU.blobAccessKey\"]\n$accessKeyType = $OctopusParameters[\"azDbImportNewDTU.accessKeyType\"]\n$containerUri = $OctopusParameters[\"azDbImportNewDTU.ContainerUri\"]\n$backupName = $OctopusParameters[\"azDbImportNewDTU.backupName\"]\n$backupUri = \"$containerUri/$backupName.bacpac\"\n\n$readScaleValue = \"Disabled\"\n\nif($readScaleTruthy -eq \"true\") { $readScalevalue = \"Enabled\" }\n\n$ServiceObjectiveSizes = @{Basic = 2GB; S0 = 250GB; S1 = 250GB; S2 = 250GB; S3 = 1TB; S4 = 1TB; S6 = 1TB; S7 = 1TB; S9 = 1TB; S12 = 1TB; P1 = 1TB; P2 = 1TB; P4 = 1TB; P6 = 1TB; P11 = 4TB; P15 = 4TB}\n\nif($null -eq (az sql server list --query \"[?Name==$sqlServer]\" | ConvertFrom-Json))\n{\n    throw \"$sqlServer doesn't exist or the selected azure account doesn't have access to it.\"\n}\n\nif($null -ne (az sql db list --resource-group $rgName --server $sqlServer --query \"[?Name==$databaseName]\" | ConvertFrom-Json))\n{\n    throw \"$databaseName already exists\"\n}\n\n#validate parameters\n\nif(($maxSize / 1GB) -gt ($ServiceObjectiveSizes[$serviceObjective] / 1GB))\n{\n    Write-Warning \"Desired max size of $($maxSize / 1GB)GB exceeds max size of $($ServiceObjectiveSizes[$serviceObjective] / 1GB)GB for selected service objective: $serviceObjective\"\n    Write-Warning \"Setting max size to $($ServiceObjectiveSizes[$serviceObjective] / 1GB)GB\"\n    $maxSize = \"$($ServiceObjectiveSizes[$serviceObjective] / 1GB)GB\"\n}\n\nif([string]::IsNullOrEmpty($rgname))\n{\n\tthrow \"resource group name is not provided\"\n}\n\nif([string]::IsNullOrEmpty($sqlServer))\n{\n\tthrow \"sql server name is not provided\"\n}\n\nif([string]::IsNullOrEmpty($databaseName))\n{\n\tthrow \"database name not provided\"\n}\n\n# admin name, password and access key will not be validated in favor of security\n\nif([string]::IsNullOrEmpty($accessKeyType))\n{\n\tthrow \"access key type not provided\"\n}\n\nif([string]::IsNullOrEmpty($containerUri))\n{\n\tthrow \"containerUri not provided\"\n}\n\nif([string]::IsNullOrEmpty($backupName))\n{\n\tthrow \"backup name not provided\"\n}\n\n# validate premium SKU settings\nif(!$serviceObjective.Contains('P'))\n{\n    if($readScaleValue -eq \"Enabled\")\n    {\n        Write-Warning \"Read Scaling only available for premium SKUs. Setting database read scale to disabled\"\n        $readScaleValue = \"Disabled\"\n    }\n    if($zoneRedundant -eq \"true\")\n    {\n        Write-Warning \"Zone redundant only available for premium SKUs. Setting database zone redundant to false\"\n        $zoneRedundant = \"false\"\n    }\n}\n\n$cliArgs = \"--name $databaseName --resource-group $rgName --server $sqlServer\"\n\nif($elasticPool) {$cliArgs += \" --elastic-pool $elasticPool\"}\nelse {$cliArgs += \" --max-size $maxSize --service-objective $serviceObjective  --zone-redundant $zoneRedundant\"}\n\nif($tags) {$cliArgs += \" --tags $tags\"}\nif($readScale) {$cliArgs += \" --read-scale $readScaleValue\"}\n\n\n$cmd = \"az sql db create $cliArgs\"\n\nwrite-verbose \"cmd is: $cmd\"\n\nWrite-Host \"Creating Database\"\nInvoke-Expression $cmd\n\nwrite-host \"starting db import\"\nwrite-verbose \"import cmd az sql db import --resource-group $rgname --server $sqlServer --name $databaseName --admin-password $adminPwd --admin-user $adminName --storage-key $accessKey --storage-key-type $accessKeyType --storage-uri $backupUri\"\n\naz sql db import --resource-group $rgname --server $sqlServer --name $databaseName --admin-password $adminPwd --admin-user $adminName --storage-key $accessKey --storage-key-type $accessKeyType --storage-uri $backupUri\n",
    "Octopus.Action.Azure.AccountId": "azureserviceprincipal-chris-azure-account-spaces-1"
  },
  "Category": "Azure",
  "HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/azure-database-import-create-new-DTU.json",
  "Website": "/step-templates/cc3b7dd9-f107-4477-acc4-4d1ffdf9e820",
  "Logo": "iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAMAAACahl6sAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAADNQTFRF////AHjXf7vrv931QJrh7/f8EIDaIIncMJHfYKvmz+b3n8zw3+76j8Ttr9XycLPpUKLkkKvYFAAABGZJREFUeNrsnNmCqjoQRc1MEiD8/9cer7Yt2KBJZQC8ez07sKlKTQlcLgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAzoUSnt8YxXlFuGHSbIaxvj+fip4btkLn1blkWLaF5v03yLhLOYlVuGYfMOMZzNGxCOzhjTJqFkXnjq3Dr1yyvPI3hGl3Ih3zzHHNKudRstRhX5O58vIcShY67Gq6EPIESlzUWvazaGAOGbvU7ArDu/g8M4o8opDZWvbvPzlL/MMBE8jT9T9W7PbAJlHPTBFRf9yVTEcs63msXz2UHLSgf650G/d5t+wjbxxB2UCMqGrk8/LFSD7uJMeNt5bcJCyQZyAe5Fo9KYfWS2flQrr4b4tpuzaeWjYs49rt9LHf9uZD7+VbyVi9EBNrjYjuq2sxQOrl+p+HuBVu45qvqfq691ttYFQ5KyKbyJgaIY/NGxrlWZwlwGvmvu1oY3PuAv0niTq6tZ78jk//9uc1r1r4lQki7y7sp2Tu4V1y2iLoqFTqi1lIGcpFiebrZNZ1dOkF0cCIlO8jQ47nCkam9Lilz9GhDF1I6XGLzfnhwDIIZVfI7+8SSgfHsijqXENOGJF5QorG4EcW0OrScqX/dDrXpr70Ut/BII+1OfECPuYz/NWxYmgrCsUskxPvyhgmrw+WGZ6lGTuOlIyCYWTFyWjpM5KIZRUIOwjRNYRQ6tZF9BXtk8hWAHPtLNJ727Fq0JSkC1FDRRF0Jalj0d5qVh2KEpM2TuSsCYTCT6ZkdmFYI9LrYp5QayWbo6NXlZwcRD/61pth5Fq5EX423QQxNjhqWvvklkljOLkYjrmphXPZOJOk6Pg7HKMsrtQKcowzZoK3rx1ZUelGMdQA/HaKkjAt2RgqpZeYqbNbH7Hp2ct4nqfSPOfe0ftiSTZJydOV6rG5bQbyLK+nRuCC0343PzDgiOXyQA5c14BTZi98uR/5KJ1SnatLdoO50WWBQZPTq0VgsklU3h932actuo17ayrHrb/3ykiegd3KbqF2wbV6RrlsJ07yLcpsWFTul9RyK6ZScr+tk7oNrFj0o7HQUlj4EiEvJ6rPLKSmlMZCrksl1OnLaRkxc+/HB1naMhNtT/6yM2bDs6azCRHrM3aVPN7aW8irD/10B8njpAMcsl8okXcdKrl4sPsLmQVy/Sj90ucPRc/d/Bxxj+dXSpCayen32D+hLi16MsIV8gfCXrYp6ySsiJKRUF0XXiLpVbFU+fNv4r7mOwhFsX4ZdwpSi1DYs2jb6ebZ9788cblTzMrYhu7sf/17IFdtuviJ2ioHA6pMHkoH4CLUeMBU7iGkxuM/YgcdderF9ibRdc7O982F1HpYhjfWUe+x5a6pjop9iNLfoePvlsdZdTSMwfxSmTY20Q0eHnUNzga1edeNmmqbg18aMVR1L9vwSXHF9TfIWBxpKLs2hj3eQeBC0USvp2HHF3eIkRdhFOd6ER8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA/I/4J8AAo/80BciBec4AAAAASUVORK5CYII=",
  "$Meta": {
    "Type": "ActionTemplate"
  }
}

History