Prerequisites

All droplets must be within an Azure Resource Group, configured with a valid name and location.

An existing Resource Group and network infrastructure already exists.

A storage account is required to host the BACPAC file.

A BACPAC file is required. An example BACPAC can be downloaded here.

Example Topology

This tutorial expands on the Azure SQL Private Link topology by walking through restoring a database backup from a BACPAC file to the primary Azure SQL Server.

The purpose of this tutorial is to demonstrate configuring Azure SQL BACPAC import into a new Azure SQL database.

Additional Azure SQL tutorials are available covering additional scenarios.

Configure an Azure Storage Account to host the BACPAC

The first step is to configure an Azure Storage Account to host the BACPAC file.

To do this run the following PowerShell against your desired Azure subscription. This will create a new Resource Group, an Azure Storage Account and configure a blob storage container to hold the BACPAC file. IMPORTANT: Replace the $bacpacUrl variable with the address of your BACPAC file. Make sure to copy the BACPAC file URL and Storage Account key for use later:

$ResourceGroup = Read-Host -Prompt "Enter the name of the Resource Group to host the Storage Account (i.e Resource Group A)"
$location = Read-Host -Prompt "Enter the location (i.e. Region A)"

$resourceGroupName = "$ResourceGroup"
$storageAccountName = "cmsqlbacpacstorageaccount"
$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.windows.net/$containerName/$bacpacFileName"
Write-Host "Press [ENTER] to continue ..."

Create an Azure SQL database using a BACPAC import

Select the Primary Azure SQL droplet in Resource Group A
Select 'Databases' and create a new database by clicking 'Add new Database'
Set the name for the database, e.g. primarydb (this can only be lowercase, numbers, and hyphens)
Set the 'Edition' property to Basic (the 'Edition' property is the main influence on the cost of deploying Azure SQL Server. Unless you have any specific requirements we recommend using the Basic edition)
Select 'Extensions' from the property tree under the database you just created
Click 'Add new Extension'
Set the Name property of the extension
Set the 'Administrator Login' to the one set earlier
Set the Secret Parameter for the Administrator Login Password
Set 'Authentication Type' to 'SQL'
Set 'Operation Mode' to 'Import'
Paste in the Storage Account Key from the PowerShell output
Set 'Storage Key Type' to StorageAccessKey
Set the 'Storage URL' to the bacpacUrl from the PowerShell output

That's it, your Azure SQL BACPAC is now configured to import your database when the database is created. Please follow the post-deployment tasks to ensure subsequent deployments continue to work.

Post-Deployment Tasks

You can now deploy your Cloud Maker design to Azure. Once deployed, you must remove the Import Extension from your Azure SQL database in your Cloud Maker blueprint and republish your design. This is to avoid deployment errors on subsequent deployments as BACPAC import can only occur during the initial database creation.