{
"metadata": {
"kernelspec": {
"name": "powershell",
"display_name": "PowerShell"
},
"language_info": {
"name": "powershell",
"codemirror_mode": "shell",
"mimetype": "text/x-sh",
"file_extension": ".ps1"
}
},
"nbformat_minor": 2,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"source": [
"# Application and Data Portability Setup Notebook\n",
"\n",
"This notebook provisions SQL ADP services to an Azure Subscription for migration purposes such as importing and exporting Azure SQL Server database(s). The following scenarios are covered:\n",
"\n",
"- [x] Azure to Azure migration\n",
"- [x] Azure to Air-gapped Azure (highly secured Azure offer)\n",
"- [x] Azure to !Azure (3rd party or on-premises)\n",
"\n",
"This bootstrapper is designed to be ran from a client workstation or laptop with minimal modification. The notebook will attempt to locate three different Azure Resources in a customer subscription. If not found it will install the following to a given Resource Group. If the resources group specified doesn't exist, it will create a new one:\n",
"\n",
"1. A **Custom Azure ADP Function** to orchestrate the Azure Batch and Storage (see \\[Github\\](........\\\\Program Files\\\\Azure Data Studio\\\\resources\\\\app\\\\out\\\\vs\\\\code\\\\electron-browser\\\\workbench\\\\workbench.html))\n",
"2. An Azure **Storage Account** for storage\n",
"3. An **Azure Batch** resource\n",
"\n",
"This notebook will help accomplish the below steps as a part of Bootstrapping an ADP Resource Group\n",
"\n",
"\n",
"\n",
"1. Set Environment Variables\n",
"2. Define Notebook Functions\n",
"3. MS Azure Login\n",
"4. Validate/ Create ADP Resource Group\n",
"5. Provision Virtual Network\n",
"6. Provision ADP Storage Account\n",
"7. Provision ADP Portability Function\n",
"8. Provision ADP Batch Account\n",
"9. Provision Contributor access (RBAC) to Portability function over ADP Resource Group\n",
"\n",
"Setup _will not_ perform tasks on existing Azure assets such as Microsoft SQL Servers, databases, VMs, etc. Other notebooks cover the import or export operations. This notebook will only check for the existence of these ADP resources and their setup."
],
"metadata": {
"azdata_cell_guid": "c5d49b77-973b-47bc-92ef-7bcfe05e4885"
}
},
{
"cell_type": "code",
"source": [
"# Setup client environment variables that the rest of the notebook will use\r\n",
"$AdpResourceGroup = \"\" # Target Resource Group to bootstrap with ADP components - A new one will be created if the specified Resource Group doesn't exist\r\n",
"$AdpRegion = \"eastus\" # Region/Location of the resource group to be bootstrapped\r\n",
"\r\n",
"# Derived settings\r\n",
"$AdpSubscription = \"\" # Target Azure Subscription Name or ID to bootstrap data portability resources\r\n",
"$AdpFunc = $AdpResourceGroup + \"Control\"\r\n",
"$AdpStorage = $AdpResourceGroup.ToLower() + \"storage\"\r\n",
"$AdpBatch = $AdpResourceGroup.ToLower() + \"batch\"\r\n",
"$AdpVNET = $AdpResourceGroup + \"VNet\"\r\n",
"\r\n",
"# Bootstrapper URLs - Update with the recommended toolkit version and build\r\n",
"$BaseToolkitUrl = \"https://hybridtoolkit.blob.core.windows.net/components\"\r\n",
"$ReleaseVersion = \"0.13\"\r\n",
"$BuildNumber = \"74938\"\r\n",
"$AdpDownloadUrl = \"$BaseToolkitUrl/$ReleaseVersion/ADPControl-$BuildNumber.zip\"\r\n",
"$AdpWrapperUrl = \"$BaseToolkitUrl/$ReleaseVersion/BatchWrapper-$BuildNumber.zip\"\r\n",
"\r\n",
"Write-Output \"Setting the Environment:\"\r\n",
"Get-ChildItem Env: | Where-Object Name -Match \"BOOTSTRAP\""
],
"metadata": {
"azdata_cell_guid": "5896a9a8-5ba8-4888-967a-10c3ceaccf12",
"tags": []
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Define Helper Functions\r\n",
"Defines logical functions for the rest of the notebook. Function blocks are combined in a single cell that can be collapsed for readability or expanded for further examination. Nothing is executed until called later in the notebook. As a result, this cell is a requirement for any of the other cells below it. "
],
"metadata": {
"azdata_cell_guid": "7df7c213-9e97-46e4-acb5-547993b9b03a"
}
},
{
"cell_type": "code",
"source": [
"# Expand cell to view framework\r\n",
"\r\n",
"function Get-ConnectionStringProperties\r\n",
"{\r\n",
" param([Parameter (Mandatory=$true)] [string]$ConnectionString)\r\n",
" $hash = @{}\r\n",
" $attributes = $ConnectionString.Split(\";\"); \r\n",
" foreach($at in $attributes)\r\n",
" {\r\n",
" $attributeKeyValue=$at.Split(\"=\",2) \r\n",
" $hash[$attributeKeyValue[0]]=$attributeKeyValue[1] \r\n",
" }$hash \r\n",
"}\r\n",
"\r\n",
"function Login-Azure\r\n",
"{ \r\n",
" # query azure locations to test for existing az login session exists with valid access tocken\r\n",
" $azureLocations = az account list-locations -o JSON 2>$null | ConvertFrom-Json\r\n",
" \r\n",
" if (!$azureLocations){ #If there are no az locations, there is no existing az login session\r\n",
" $subscriptions = az login -o JSON | ConvertFrom-Json # Login \r\n",
" }\r\n",
" else {\r\n",
" $subscriptions = az account list -o JSON | ConvertFrom-Json # getting subscriptions for the user to use in gridview\r\n",
" }\r\n",
"\r\n",
" if(![string]::IsNullOrWhiteSpace($AdpSubscription)) #If there is a subscription specified by user in the variables section\r\n",
" {\r\n",
" $specified_Subscription= az account show --subscription $AdpSubscription -o json |ConvertFrom-Json \r\n",
" if (!$specified_Subscription) #if specified subscription is not valid\r\n",
" { \r\n",
" $currentUser= az ad signed-in-user show --query \"{displayName:displayName,UPN:userPrincipalName}\" -o json|ConvertFrom-Json # get current logged in user infomration\r\n",
" Write-Host \"Refer below for the list of subscriptions for logged in account '$($currentUser.UPN)'`n\"\r\n",
" az account list --query \"[].{Name:name,SubscriptionID:id}\" -o table # list subscriptions under current logged in account\r\n",
" }\r\n",
" else { # if specified subscription is valid\r\n",
" Write-Output \"Using subscription... '$($specified_Subscription.name)' ... '$($specified_Subscription.id)'\" \r\n",
" }\r\n",
" }\r\n",
" else { # if no subscription is specified, users are given a gridview to select subscription from\r\n",
"\r\n",
" $selectedSubscription = $subscriptions | Select-Object -Property Name, Id | Out-GridView -PassThru\r\n",
" $SubscriptionId = $selectedSubscription.Id\r\n",
" $Subscription = $selectedSubscription.Name \r\n",
" $AdpSubscription = $subscription \r\n",
" Write-Output \"Using subscription... '$AdpSubscription' ... '$SubscriptionId'\" \r\n",
" } \r\n",
"}\r\n",
"\r\n",
"function Bootstrap-AzResourceGroup\r\n",
"{\r\n",
" [CmdletBinding()]\r\n",
" param(\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$Subscription,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ResourceGroupName,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ResourceGroupLocation \r\n",
" ) \r\n",
" \r\n",
" #Validating parameter $Subscription\r\n",
" $specified_Subscription = az account show --subscription $Subscription -o json |ConvertFrom-Json\r\n",
" if(!$specified_Subscription){\r\n",
" $currentUser= az ad signed-in-user show --query \"{displayName:displayName,UPN:userPrincipalName}\" -o json|ConvertFrom-Json # get current logged in user infomration\r\n",
" Write-Host \"Refer below for the list of subscriptions for logged in account '$($currentUser.UPN)'`n\"\r\n",
" az account list --query \"[].{Name:name,SubscriptionID:id}\" -o table # list subscriptions under current logged in account\r\n",
" return\r\n",
" }\r\n",
" #Validating parameter $ResourceGroupLocation\r\n",
" $specified_RGLocation = az account list-locations --query \"[?name == '$ResourceGroupLocation']\" -o JSON | ConvertFrom-Json\r\n",
" if(!$specified_RGLocation) {\r\n",
" Write-Host \"ResourceGroupLocation '$ResourceGroupLocation' not found. Refer below for the list of locations for the account:`n\" \r\n",
" az account list-locations --query \"[].{Locations: name}\" -o table\r\n",
" return\r\n",
" }\r\n",
" # check if Resource group already exists under specified subscription\r\n",
" $existingResourceGroup = az group show -n $ResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json\r\n",
"\r\n",
" if ($existingResourceGroup){ #Found specified resource group \r\n",
" Write-Output \"Found Resource Group '$($existingResourceGroup.name)'. \" \r\n",
" } \r\n",
" else { \r\n",
" # Resource group not found under specified subscription. Create new Resource group \r\n",
" Write-Output \"Resource group '$($ResourceGroupName)' not found. `nCreating new resource group ... \"\r\n",
" $newResourceGroup = az group create -n $ResourceGroupName -l $ResourceGroupLocation --subscription $Subscription -o JSON | ConvertFrom-Json\r\n",
" Write-Output \"Created Resource Group '$($newResourceGroup.name)'\"\r\n",
" } \r\n",
"}\r\n",
"\r\n",
"function Bootstrap-AzVirtualNetwork\r\n",
"{ \r\n",
" [CmdletBinding()]\r\n",
" param(\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$Subscription,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ResourceGroupName,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$VNetName, \r\n",
" [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$SubNetName = \"default\" \r\n",
" )\r\n",
" \r\n",
" #Validating parameter $ResourceGroupName\r\n",
" $specified_RG = az group show -n $ResourceGroupName --subscription $Subscription 2>$null\r\n",
" if(!$specified_RG) {\r\n",
" Write-Host \"Invalid Input: ResourceGroupName. Resource Group $ResourceGroupName not found\"\r\n",
" return\r\n",
" } \r\n",
" \r\n",
" #Check if default subnet exists under specified Vnet \r\n",
" $existingVnetSubnet = az network vnet subnet show -n $SubNetName --vnet-name $VNetName -g $ResourceGroupName --subscription $Subscription -o JSON |ConvertFrom-Json\r\n",
" if ($existingVnetSubnet){ #Found default subnet under specified VNet\r\n",
" Write-Output \"Found default Subnet under specified VNet. `nSubNet ID '$($existingVnetSubnet.id)'\"\r\n",
" } \r\n",
" else { \r\n",
" #VNet or defaut subnet not found under specified resource group. Create new VNet with default Subnet /Add default subnet to existing VNet\r\n",
" Write-Output \"Creating new Virtual network with default Subnet ID ... \"\r\n",
" $newVNet = az network vnet create --name \"$AdpVNET\" --resource-group $AdpResourceGroup --subscription $AdpSubscription --subnet-name $SubNetName -o JSON |ConvertFrom-Json #vnet create/Update command: Bug: In this command, the output variable is not getting converted to PS objects.\r\n",
" $newVNet = az network vnet subnet show -g $AdpResourceGroup --vnet-name $AdpVNET -n $SubNetName --subscription $AdpSubscription -o JSON |ConvertFrom-Json # added this line due to above bug\r\n",
" Write-Output \"Created VNet with default Subnet - ID: '$($newVNet.id)'\"\r\n",
" }\r\n",
"}\r\n",
"\r\n",
"function Bootstrap-AzStorageAccount\r\n",
"{ \r\n",
" [CmdletBinding()]\r\n",
" param(\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$Subscription,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ResourceGroupName,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$StorageAccountName,\r\n",
" [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$StorageAccountKind = \"StorageV2\",\r\n",
" [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$StorageAccountSKU = \"Standard_LRS\" \r\n",
" )\r\n",
"\r\n",
" $ListStorageAccountKind = \"BlobStorage\", \"BlockBlobStorage\", \"FileStorage\", \"Storage\", \"StorageV2\"\r\n",
" $ListStorageAccountSKU = \"Premium_LRS\", \"Premium_ZRS\", \"Standard_GRS\", \"Standard_GZRS\", \"Standard_LRS\", \"Standard_RAGRS\", \"Standard_RAGZRS\", \"Standard_ZRS\"\r\n",
"\r\n",
" \r\n",
" #Validating parameter $ResourceGroupName\r\n",
" $specified_RG = az group show -n $ResourceGroupName --subscription $Subscription 2>$null\r\n",
" if(!$specified_RG) {\r\n",
" Write-Host \"Invalid Input: ResourceGroupName. Resource Group $ResourceGroupName not found\"\r\n",
" return\r\n",
" } \r\n",
" #Validating parameter $StorageAccountKind\r\n",
" if($StorageAccountKind -notin $ListStorageAccountKind) {\r\n",
" Write-Host \"StorageAccountKind '$StorageAccountKind' not valid. Accepted values: $ListStorageAccountKind\" \r\n",
" return\r\n",
" }\r\n",
" #Validating parameter $StorageAccountSKU\r\n",
" if($StorageAccountSKU -notin $ListStorageAccountSKU) {\r\n",
" Write-Host \"StorageAccountSKU '$StorageAccountSKU' not valid. Accepted values: $ListStorageAccountSKU\" \r\n",
" return\r\n",
" }\r\n",
" \r\n",
" #Check if storage account exists under specified resource group \r\n",
" $existingStorageAccount = az storage account show -n $StorageAccountName -g $ResourceGroupName --subscription $Subscription -o JSON| ConvertFrom-Json\r\n",
"\r\n",
" if ($existingStorageAccount){ #Found specified storage account\r\n",
" Write-Output \"Found storage account '$($existingStorageAccount.name)'\"\r\n",
" } \r\n",
" else { \r\n",
" #Storage account not found under specified resource group. Create new storage account\r\n",
" Write-Output \"Creating new storage account ... \"\r\n",
" $newStorageAccount = az storage account create -n $StorageAccountName --kind $StorageAccountKind --sku $StorageAccountSKU -g $ResourceGroupName --subscription $Subscription -o JSON | ConvertFrom-Json\r\n",
" Write-Output \"Created Storage Account '$($newStorageAccount.name)'\"\r\n",
" }\r\n",
"}\r\n",
"\r\n",
"function Bootstrap-AzFunctionApp\r\n",
"{\r\n",
" [CmdletBinding()]\r\n",
" param(\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$Subscription,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ResourceGroupName,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$FunctionName,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$StorageAccountName,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$FunctionAppPackageURL,\r\n",
" [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$FunctionAppVersion=\"3\",\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ConsumptionPlanLocation \r\n",
" ) \r\n",
" #Validating parameter $ResourceGroupName\r\n",
" $specified_RG = az group show -n $ResourceGroupName --subscription $Subscription 2>$null\r\n",
" if(!$specified_RG) {\r\n",
" Write-Host \"Invalid Input: ResourceGroupName. Resource Group $ResourceGroupName not found\"\r\n",
" return\r\n",
" } \r\n",
" #Validating parameter StorageAccountName\r\n",
" $specified_storageAccount = az storage account show -n $StorageAccountName -g $ResourceGroupName --subscription $Subscription 2>$null\r\n",
" if(!$specified_storageAccount){ \r\n",
" Write-Host \"Invalid Input: StorageAccount name. Storage Account $StorageAccountName not found\" \r\n",
" return\r\n",
" }\r\n",
" #Validating parameter FunctionAppVersion\r\n",
" if($FunctionAppVersion -notin (2,3)){\r\n",
" Write-Host \"FunctionAppVersion '$FunctionAppVersion' not valid. Accepted values: 2,3\" \r\n",
" return\r\n",
" }\r\n",
" #Validating parameter ConsumptionPlanLocation\r\n",
" $specified_Location = az functionapp list-consumption-locations --query \"[?name == '$ConsumptionPlanLocation']\" -o JSON | ConvertFrom-Json \r\n",
" if(!$specified_Location) { \r\n",
" Write-Host \"ComsumptionPlanLocation '$ConsumptionPlanLocation' not valid. Refer below for available options: `n\" \r\n",
" az functionapp list-consumption-locations --query \"[].{ConsumptionPlanLocations: name}\" -o table\r\n",
" return\r\n",
" }\r\n",
" \r\n",
" #Check if functionapp exists within the specified resource group\r\n",
" $existingFunctionApp = az functionapp show -n $FunctionName -g $ResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json\r\n",
"\r\n",
" if ($existingFunctionApp){ #ensure that function application is linked to the specified storage account\r\n",
"\r\n",
" #retrieve connection string of storage account linked to FunctionApp and check if the connected storage account is equal to the storage account passed to here\r\n",
" $funcApp_Storage_ConnString= az functionapp config appsettings list -n $FunctionName -g $ResourceGroupName --subscription $Subscription --query \"[?name == 'AzureWebJobsStorage'].value\" -o JSON 2>$null | ConvertFrom-Json\r\n",
" $connectionString = Get-ConnectionStringProperties -ConnectionString \"$funcApp_Storage_ConnString\"\r\n",
" $isConnected = $connectionString.AccountName -eq $StorageAccountName\r\n",
"\r\n",
" if($isConnected) { \r\n",
" Write-Output \"Found FunctionApp '$FunctionName'. `nConnected storage account is '$StorageAccountName'\"\r\n",
" } \r\n",
" else {\r\n",
" Write-Output \"Found FunctionApp '$FunctionName'. `nConnected storage account is NOT '$StorageAccountName', but '$($connectionString.AccountName)' \"\r\n",
" }\r\n",
" } \r\n",
" else { #Provision FunctionApp \r\n",
"\r\n",
" $newFuncApp= az functionapp create --name $FunctionName --resource-group $ResourceGroupName --subscription $Subscription --storage-account $StorageAccountName --functions-version $FunctionAppVersion --consumption-plan-location $ConsumptionPlanLocation -o JSON | ConvertFrom-JSON \r\n",
" Write-Output \"`nCreated Function Application '$($newFuncApp.name)'\"\r\n",
" $AppInsightsInstrumentationKey = az functionapp config appsettings list --name $FunctionName --resource-group $ResourceGroupName --subscription $Subscription --query \"[?name == 'APPINSIGHTS_INSTRUMENTATIONKEY'].value\" -o JSON | ConvertFrom-JSON \r\n",
" az functionapp config appsettings set --name $FunctionName --settings \"WEBSITE_RUN_FROM_PACKAGE=$FunctionAppPackageURL\" \"APPLICATIONINSIGHTS_CONNECTION_STRING=InstrumentationKey=$AppInsightsInstrumentationKey\" `\r\n",
" --resource-group $ResourceGroupName --subscription $Subscription -o none \r\n",
" Write-Output \"Set '$($newFuncApp.name)' to run from package '$FunctionAppPackageURL'\"\r\n",
" az functionapp identity assign -n $FunctionName -g $ResourceGroupName --subscription $Subscription -o none \r\n",
" Write-Output \"Enabled system assigned managed identity for '$($newFuncApp.name)'\" \r\n",
" }\r\n",
"}\r\n",
"\r\n",
"function Bootstrap-AzBatchAccount\r\n",
"{\r\n",
" [CmdletBinding()]\r\n",
" param(\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$Subscription,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ResourceGroupName,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BatchAccountLocation,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BatchAccountName,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$StorageAccountName,\r\n",
" [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ApplicationPackageURL,\r\n",
" [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$ApplicationName=\"SqlPackageWrapper\", \r\n",
" [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$ApplicationPackageVersionName=\"1\" \r\n",
" )\r\n",
" #Validating parameter $ResourceGroupName\r\n",
" $specified_RG = az group show -n $ResourceGroupName --subscription $Subscription 2>$null\r\n",
" if(!$specified_RG) {\r\n",
" Write-Host \"Invalid Input: ResourceGroupName. Resource Group $ResourceGroupName not found\"\r\n",
" return\r\n",
" } \r\n",
" #Validating parameter StorageAccountName\r\n",
" $specified_storageAccount = az storage account show -n $StorageAccountName -g $ResourceGroupName --subscription $Subscription 2>$null\r\n",
" if(!$specified_storageAccount){ \r\n",
" Write-Host \"Invalid Input: StorageAccount name. Storage Account $StorageAccountName not found\" \r\n",
" return\r\n",
" }\r\n",
" #Validating parameter $BatchAccountLocation\r\n",
" $specified_BLocation = az account list-locations --query \"[?name == '$BatchAccountLocation']\" -o JSON | ConvertFrom-Json\r\n",
" if(!$specified_BLocation) {\r\n",
" Write-Host \"BatchAccountLocation '$BatchAccountLocation' not found. Refer below for the list of locations for the account:`n\" \r\n",
" az account list-locations --query \"[].{Locations: name}\" -o table\r\n",
" return\r\n",
" } \r\n",
"\r\n",
" $SQLPackageWrapperLocal = \"$Env:USERPROFILE\" + \"\\Downloads\\batchApplicationPackage.zip\" #path to download application package\r\n",
" $downloaded= Test-Path $SQLPackageWrapperLocal \r\n",
"\r\n",
" #check if Batch account exists in the specified resource group\r\n",
" $existingBatchAccount = az batch account show -n $BatchAccountName -g $ResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json\r\n",
"\r\n",
" if ($existingBatchAccount){ #Batch Account exists, \r\n",
" Write-output \"Found Batch Account '$BatchAccountName'\"\r\n",
"\r\n",
" #checking if storage account is set for Batch Account\r\n",
" #$existingStorageAccount = az storage account show -n $StorageAccountName -g $ResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json # check if storage account set is ADP Storage A/c\r\n",
" $connectedToStorage= $existingBatchAccount.autoStorage #.storageAccountId -eq $existingStorageAccount.id \r\n",
"\r\n",
" # check if latest application package is installed \r\n",
" $appPackageInstalled = az batch application package show --application-name $ApplicationName --version-name $ApplicationPackageVersionName -n $BatchAccountName -g $ResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json\r\n",
"\r\n",
" if($connectedToStorage -and $appPackageInstalled){ # BatchAccount connected to specified storageaccount and latest applicationpackage is installed\r\n",
" $connectedStorageAccName = $existingBatchAccount.autoStorage.storageAccountId.Split(\"/\")[-1]\r\n",
" Write-Output \"Already connected to Storage account '$connectedStorageAccName' `nFound latest application package installed. `nReusing existing Resources\"\r\n",
" }\r\n",
" else {\r\n",
" if(!$connectedToStorage){ # Connect to ADPStorageAccount \r\n",
"\r\n",
" az batch account set --storage-account $StorageAccountName -n $BatchAccountName --resource-group $ResourceGroupName --subscription $Subscription -o none | ConvertFrom-JSON \r\n",
" Write-Output \"No Storage Account Found. Connected to storage account '$StorageAccountName'\"\r\n",
" }\r\n",
" if(!$appPackageInstalled) { #application package is not installed, installing application package \r\n",
" if(!$downloaded) { Invoke-WebRequest -Uri $ApplicationPackageURL -OutFile $SQLPackageWrapperLocal } #downloads package to current user's downloads folder\r\n",
" az batch application package create --application-name $ApplicationName --version-name $ApplicationPackageVersionName --package-file $SQLPackageWrapperLocal --name $BatchAccountName --resource-group $ResourceGroupName --subscription $Subscription -o none\r\n",
" Write-Output \"No Application package found. Installed application package under '$ApplicationName'\"\r\n",
" }\r\n",
" Write-Output \"Batch Account '$BatchAccountName' is provisioned as required\"\r\n",
" }\r\n",
" } else { #Provision Batch Account\r\n",
"\r\n",
" $newBatchAccount = az batch account create -n $BatchAccountName -l $BatchAccountLocation --resource-group $ResourceGroupName --subscription $Subscription -o JSON | ConvertFrom-JSON\r\n",
" Write-Output \"Created Batch Account '$($newBatchAccount.name)'\" \r\n",
" az batch account set --storage-account $StorageAccountName -n $BatchAccountName --resource-group $ResourceGroupName --subscription $Subscription -o none \r\n",
" Write-Output \"Set Storage Account to '$StorageAccountName'\" \r\n",
" if(!$downloaded) { Invoke-WebRequest -Uri $ApplicationPackageURL -OutFile $SQLPackageWrapperLocal } #downloads package to current user's downloads folder\r\n",
" az batch application package create --application-name $ApplicationName --version-name $ApplicationPackageVersionName --package-file $SQLPackageWrapperLocal --name $BatchAccountName --resource-group $ResourceGroupName --subscription $Subscription -o none \r\n",
" Write-Output \"Installed application package '$ApplicationName'\" \r\n",
" \r\n",
" } \r\n",
"}\r\n",
"\r\n",
"function Bootstrap-FuncRBAC {\r\n",
" [CmdletBinding()]\r\n",
" param (\r\n",
" [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string]$Subscription,\r\n",
" [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string]$ResourceGroupName,\r\n",
" [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string]$AzFunctionName,\r\n",
" [Parameter(Mandatory=$false)][ValidateNotNullOrEmpty()][string]$Role=\"Contributor\"\r\n",
" )\r\n",
"\r\n",
" # Get the resource group's scope\r\n",
" $rg = az group show --resource-group $ResourceGroupName --subscription $Subscription | ConvertFrom-Json\r\n",
" if (!$rg) {\r\n",
" Write-Error \"Failed to setup RBAC for $AzFunctionName... Resource Group '$ResourceGroupName' not found on subscription '$Subscription'\"\r\n",
" exit\r\n",
" }\r\n",
"\r\n",
" $scope = $rg.id\r\n",
" Write-Output \"Found scope... $scope\"\r\n",
" \r\n",
" # Get the az function principal id\r\n",
" $app = az functionapp show -n $AzFunctionName --resource-group $ResourceGroupName --subscription $Subscription | ConvertFrom-Json\r\n",
" if (!$app) {\r\n",
" Write-Error \"Failed to setup RBAC for $AzFunctionName... Az function not found!\"\r\n",
" exit\r\n",
" }\r\n",
"\r\n",
" $prid = $app.identity.principalId\r\n",
" Write-Output \"Found principal id of Azure function... $prid\"\r\n",
"\r\n",
" # Verify if a role assignment has been created\r\n",
" $assignments = az role assignment list --subscription $Subscription -g $ResourceGroupName | ConvertFrom-Json\r\n",
"\r\n",
" # Get the correct assignment\r\n",
" $svcPrincipal = $assignments | Where-Object {($_.scope -eq $scope) -and ($_.principalType -eq \"ServicePrincipal\") -and ($_.principalId -eq $prid)}\r\n",
" $id = $svcPrincipal.id\r\n",
" $def = $svcPrincipal.roleDefinitionName\r\n",
"\r\n",
" Write-Output \"Found service principal role... '$def'\" \r\n",
" # Check if the right role is assigned\r\n",
" if ($def -eq $Role)\r\n",
" { \r\n",
" Write-Output \"Service Principle '$prid' found with '$Role' role. No work needed\"\r\n",
" return\r\n",
" }\r\n",
"\r\n",
" # Continue to setup RBAC, once we verify an assignment is not setup and all the resources exist\r\n",
" Write-Output \"Creating new role assignment by running: 'az functionapp identity assign -g $ResourceGroupName -n $AzFunctionName --scope $scope --subscription $Subscription'\"\r\n",
" Write-Warning \"If your account does not have the access to assign new roles as Owner or User Access Administrator for the resource group, than you will need to contact your Azure AD Administrator to assign a service principle using the commands above\" \r\n",
" az functionapp identity assign -g $ResourceGroupName -n $AzFunctionName --scope $scope --subscription $Subscription \r\n",
"}\r\n",
"\r\n",
"Write-Host \"Helper Functions Created successfully\" "
],
"metadata": {
"azdata_cell_guid": "f524afff-ccca-4e19-94a1-dbe21aee2241",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Connect to Azure and Select Subscription\r\n",
"Run the below cell to login to an Azure account. Be sure to check the Windows Taskbar for a subscription selection dialog box.\r\n",
"\r\n",
"_Note: the dialog box window may appear behind the active Azure Data Studio window._"
],
"metadata": {
"azdata_cell_guid": "4952284c-dec7-4df3-9a31-044d795ef279"
}
},
{
"cell_type": "code",
"source": [
"Login-Azure"
],
"metadata": {
"azdata_cell_guid": "112d7a12-f66d-41c0-8a1c-0b234506d5ef",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Create Resource Group\r\n",
"The Data Portability Notebooks provision several resources to enable import and export of Azure SQL DB databases and Azure SQL Managed Instance databases. These resources include Storage, Azure Batch, Azure Function resources and their dependent Azure resources. All resources created by this notebook are associated with a common Azure Resource Group to enable easy management of resources related to Data Portability.\r\n",
"\r\n",
"_Note: this notebook will reuse the Resource Group specified if it already exists._"
],
"metadata": {
"azdata_cell_guid": "8298d187-3021-485c-b4d4-442da9aadd53"
}
},
{
"cell_type": "code",
"source": [
"Bootstrap-AzResourceGroup -ResourceGroupName $AdpResourceGroup -ResourceGroupLocation $AdpRegion -Subscription $AdpSubscription"
],
"metadata": {
"azdata_cell_guid": "9beb8d22-4560-4c7e-917b-5a3c0d58e1a2",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Provision Virtual Network\r\n",
"\r\n",
"The Data Portability resources include Virtual Machines created to execute the database import and export operations. To provision this in a secure mode, this notebook will provision a Virtual Network to attach the Virtual Machines to. These VMs do not need to be directly accessed so no publicly addressable ports should be created."
],
"metadata": {
"azdata_cell_guid": "8fd75088-78fa-4cf3-b0f2-166813d0bed1"
}
},
{
"cell_type": "code",
"source": [
"Bootstrap-AzVirtualNetwork -VNetName $AdpVNET -ResourceGroupName $AdpResourceGroup -Subscription $AdpSubscription"
],
"metadata": {
"azdata_cell_guid": "d014a6a6-57ff-4de7-8210-b3360bf34daa"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Provision Storage Account\r\n",
"\r\n",
"The Data Portability resources require a Storage Account to attach the Virtual Machines to. This storage account is used for maintaining log files and other Azure Batch related information. This Storage Account can be the same or different than the Storage Acount used during Import and Export operations to store the logical database (.bacpac) files."
],
"metadata": {
"azdata_cell_guid": "4e91a3f7-a676-4a4e-9d6f-3a343fcc0bc0"
}
},
{
"cell_type": "code",
"source": [
"Bootstrap-AzStorageAccount -StorageAccountName $AdpStorage -ResourceGroupName $AdpResourceGroup -Subscription $AdpSubscription"
],
"metadata": {
"azdata_cell_guid": "290498ee-3f31-4395-adab-a5fa93d28c80",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Provision Data Portability Orchestrator\r\n",
"\r\n",
"The Data Portability resources include an orchestration component which coordinates the import and export operations. The orchestrator is built as an Azure Function and provides the REST APIs that the Import and Export notebooks use to either import or export an Azure SQL DB Server and its databases."
],
"metadata": {
"azdata_cell_guid": "d1291c22-e5b7-42b3-bdd9-8a8d13fd3165"
}
},
{
"cell_type": "code",
"source": [
"Bootstrap-AzFunctionApp -FunctionName $AdpFunc -StorageAccountName $AdpStorage -FunctionAppPackageURL $AdpDownloadUrl `\r\n",
" -ConsumptionPlanLocation $AdpRegion -ResourceGroupName $AdpResourceGroup -Subscription $AdpSubscription"
],
"metadata": {
"azdata_cell_guid": "6fc2b5ec-c16f-4eb7-b2f9-c8c680d9a2df",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Provision Hosted SqlPackage Application\r\n",
"To Data Portability solution leverages SqlPackage and DacFx to perform the import and export of each database. To host SqlPackage the solution uses an Azure Batch application. Azure Batch provides the ability to create a pool of Virtual Machines that can perform the import and export operations. This notebook will provision a pool of 2 virutal machines. The machines are not actually allocated until the first Import or Export operation is performed using the Import or Export notebooks. "
],
"metadata": {
"azdata_cell_guid": "126a5abd-a3f4-46e0-b311-8cd1b8639127"
}
},
{
"cell_type": "code",
"source": [
"Bootstrap-AzBatchAccount -BatchAccountName $AdpBatch -StorageAccountName $AdpStorage -BatchAccountLocation $AdpRegion `\r\n",
" -ApplicationPackageURL $AdpWrapperUrl -ResourceGroupName $AdpResourceGroup -Subscription $AdpSubscription"
],
"metadata": {
"azdata_cell_guid": "489733c4-1162-479b-82b4-b0c18954b25b",
"tags": []
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Grant Data Portability Controller Access to Data Portability Resources\r\n",
"The Data Portability orchestrator requires Contributor access to the other Data Portability resources created. To do this, Azure RBAC (role based access control) is used to grant access to the orchestrator so that it can communicate with the hosted SqlPackage application. The function below will verify a Service Principal was created with the proper scope or attempt to create the role assignment. Any user executing this notebook will need to have Owner or User Access Administrator permissions to the Resource Group to assign the permission. Otherwise, contact your Azure AD Administrator. "
],
"metadata": {
"azdata_cell_guid": "3d03a987-4bec-4c0b-afdb-e6ae3c828252"
}
},
{
"cell_type": "code",
"source": [
"Bootstrap-FuncRBAC -AzFunctionName $AdpFunc -ResourceGroupName $AdpResourceGroup -Subscription $AdpSubscription"
],
"metadata": {
"azdata_cell_guid": "75882d3a-2004-4304-ab8f-e5146e14500c",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
}
]
}