{ "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": [ "# Import Existing Azure SQL Server Resources\r\n", "" ], "metadata": { "azdata_cell_guid": "baa1b762-f2a9-461f-a126-49f0d3b5e4f0" } }, { "cell_type": "markdown", "source": [ "The notebook will help accomplish the below steps as a part of Importing existing Azure SQL Server Resources\n", "\n", "- [ ] Connect to Azure Subscription\n", "- [ ] Choose Resource Group (Read access required)\n", "- [ ] Choose Microsoft SQL Server Resources to import\n", "- [ ] Choose/Create Migration Storage\n", "- [ ] Install Application + Data Portability function\n", "- [ ] Install ADP Azure Batch processing pipeline\n", "- [ ] Store SqlPackage.exe in Migration Storage for ADP function to hand to Az Batch\n", "\n", "Execute:\n", "\n", "- [ ] Check all prerequisites\n", "- [ ] Kick off ADP service\n", "\n", "Monitor:\n", "\n", "- [ ] Check import status." ], "metadata": { "azdata_cell_guid": "1ccbf203-d568-408c-a641-7e5cfa93802a" } }, { "cell_type": "markdown", "source": [ "## Set Variables for the Notebook" ], "metadata": { "azdata_cell_guid": "09f648ab-67e8-4127-94bd-649d5e970321" } }, { "cell_type": "code", "source": [ "# ADP Resource \r\n", "$AdpSubscription = \"\" # Azure Subscription ID/Name # The bacpac files and ADP Resources are assumed to be in the same subscription\r\n", "$AdpResourceGroup = \"\" # Azure Resource Group which contains the ADP Resources\r\n", "\r\n", "# SQL Server \r\n", "$TargetResourceGroupName = \"\" # Azure ResourceGroup into which the sql server backup needs to be restored\r\n", "$StorageAccountName = \"\"\r\n", "$ContainerName = \"\"\r\n", "$LogicalSQLServerName = \"\" # New sql server name\r\n", "$LSqlServerPassword = \"\"\r\n", "\r\n", "# Set Variables for ADP Resources\r\n", "$AdpFunc = $AdpResourceGroup + \"Control\" \r\n", "$AdpBatch = $AdpResourceGroup.ToLower() + \"batch\"\r\n", "$AdpVNET = $AdpResourceGroup + \"Vnet\"" ], "metadata": { "azdata_cell_guid": "01888595-0d1c-445b-ba85-dd12caa30192", "tags": [] }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Notebook 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": "4a35cf8e-6598-43e8-a0da-2c0c369df548" } }, { "cell_type": "code", "source": [ "# Expand cell to view framework\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 Verify-ADPResources\r\n", "{ \r\n", " [CmdletBinding()]\r\n", " param(\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$Subscription,\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ADPResourceGroupName,\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$FunctionName, \r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BatchAccountName,\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$VNetName,\r\n", " [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$ApplicationName=\"SqlPackageWrapper\", \r\n", " [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$ApplicationPackageVersionName=\"1\",\r\n", " [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$SubNetName=\"default\" \r\n", " ) \r\n", "\r\n", "# validate 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 information\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", "# validate ResourceGroup \r\n", "$specified_ResourceGroup= az group show -n $ADPResourceGroupName --subscription $Subscription -o json | ConvertFrom-Json\r\n", "if(!$specified_ResourceGroup) { \r\n", " return\r\n", " } \r\n", "\r\n", "$Installed = [ordered]@{} # ordered hash to store status of installation\r\n", "$countError=0\r\n", "\r\n", "#Verify if VNet exists \r\n", "$specified_VNet= az network vnet show -n $VNetName -g $ADPResourceGroupName --subscription $Subscription -o JSON 2>$null |ConvertFrom-Json \r\n", "if(!$specified_VNet) {\r\n", " $Installed['VNET']=\"Not Found\"\r\n", " $countError++\r\n", "} \r\n", "else { \r\n", " $existingVnetSubnet = az network vnet subnet show -n $SubNetName --vnet-name $VNetName -g $ADPResourceGroupName --subscription $Subscription -o JSON 2>$null |ConvertFrom-Json\r\n", " if(!$existingVnetSubnet){\r\n", " $Installed['VNET']=\"Default Subnet under\"+ $VNetName + \"Not Found\"\r\n", " $countError++\r\n", " }\r\n", " else {\r\n", " $Installed['VNET']=\"Installed\"\r\n", " }\r\n", " }\r\n", "\r\n", "#Verify if FunctionApp Exists\r\n", "$specified_FunctionApp = az functionapp show -n $FunctionName -g $ADPResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json\r\n", "if(!$specified_FunctionApp)\r\n", "{\r\n", " $Installed['FunctionApp']=\"Not Installed\"\r\n", " $countError++\r\n", "}\r\n", "else\r\n", "{\r\n", " $Installed['FunctionApp']=\"Installed\"\r\n", "} \r\n", "\r\n", "#check if Batch account exists\r\n", "$specified_BatchAccount = az batch account show -n $BatchAccountName -g $ADPResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json\r\n", "if(!$specified_BatchAccount)\r\n", "{\r\n", " $Installed['Batch']=\"Not Installed\"\r\n", " $countError++\r\n", "}\r\n", "else\r\n", "{\r\n", " $appPackageInstalled = az batch application package show --application-name $ApplicationName --version-name $ApplicationPackageVersionName -n $BatchAccountName -g $ADPResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json\r\n", " $connectedToStorage= $specified_BatchAccount.autoStorage \r\n", " if($connectedToStorage -and $appPackageInstalled){ # BatchAccount connected to storageaccount and applicationpackage is installed\r\n", " $Installed['Batch']=\"Installed\"\r\n", " $Installed['Batch_ApplicationPackage']=\"Installed\"\r\n", " $Installed['Batch_StorageAccount']=\"Connected to storage- \"+$connectedToStorage.storageAccountId.Split(\"/\")[-1]\r\n", " }\r\n", " if(!$connectedToStorage)\r\n", " {\r\n", " $Installed['Batch_StorageAccount']='Not Found'\r\n", " $countError++\r\n", " } \r\n", " if(!$appPackageInstalled)\r\n", " {\r\n", " $Installed['Batch_ApplicationPackage']=\"Not Found\"\r\n", " $countError++\r\n", " } \r\n", "}\r\n", "if ($countError -gt 0){\r\n", " Write-Output \"ADP Resources are not installed correctly. Please refer the list below and use the Bootstrap NB to install ADP Resources\"\r\n", "}\r\n", "$Installed\r\n", "if ($countError -eq 0){\r\n", " Write-Output \"`nFound all ADP Resources.\"\r\n", "}\r\n", "}\r\n", "\r\n", "function Prepare-InputForImportFunction\r\n", "{ \r\n", " [CmdletBinding()]\r\n", " param(\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$Subscription,\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ADPResourceGroupName,\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$FunctionName, \r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BatchAccountName,\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BackupFiles_StorageAccount,\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BackupFiles_ContainerName,\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$VNetName, \r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$TargetRGName,\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SqlServerName,\r\n", " [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SqlServerPassword\r\n", " )\r\n", " \r\n", " $Result = @{}\r\n", " # Build Header \r\n", " ## get Function key\r\n", " $FunctionAppID =az functionapp show -n $FunctionName -g $ADPResourceGroupName --subscription $Subscription --query \"[id]\" -o JSON 2>$null | ConvertFrom-Json\r\n", " $DefaultHostKey = az rest --method post --uri \"$FunctionAppID/host/default/listKeys?api-version=2018-11-01\" --query \"[functionKeys.default]\" -o JSON 2>$null | ConvertFrom-Json\r\n", " ## Build Json Object for Headers\r\n", " $headers = @{\r\n", " 'x-functions-key' = $DefaultHostKey\r\n", " }\r\n", " $Result['Header']=$headers\r\n", "\r\n", " # Build string for Function URL \r\n", " $specified_Subscription= az account show --subscription $Subscription -o json |ConvertFrom-Json #Get SpecifiedSubscriptionID\r\n", " $SubscriptionID= $specified_Subscription.id\r\n", " $FunctionUrl = 'https://'+ $FunctionName +'.azurewebsites.net/api/subscriptions/'+ $SubscriptionID +'/resourceGroups/' + $ADPResourceGroupName + '/Import'\r\n", " $Result['FunctionURL']=$FunctionUrl\r\n", "\r\n", " # Set parameter variables for Body\r\n", " ## Get BatchAccountURL \r\n", " $specified_Batch = az batch account show -n $BatchAccountName -g $ADPResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json\r\n", " $BatchAccountURL = 'https://' + $specified_Batch.accountEndpoint\r\n", "\r\n", " ## Get default SubNet ID for specified VNet\r\n", " $specified_VNet_SubNet = az network vnet subnet show -g $ADPResourceGroupName --vnet-name $VNetName -n 'default' --subscription $Subscription -o JSON |ConvertFrom-Json\r\n", " $VNetSubNetID = $specified_VNet_SubNet.id\r\n", "\r\n", " ## Create access token to source sql server\r\n", " $targetAccessToken = az account get-access-token --resource=https://database.windows.net --query accessToken\r\n", " $targetAccessToken\r\n", "\r\n", " ## Build JSon object for Body\r\n", " $Body = @{\r\n", " batchAccountUrl = $BatchAccountURL\r\n", " VNetSubnetId= $VNetSubNetID\r\n", " storageAccountName = $BackupFiles_StorageAccount\r\n", " containerName = $BackupFiles_ContainerName\r\n", " targetSqlServerResourceGroupName = $TargetRGName\r\n", " targetSqlServerName = $SQLServerName \r\n", " userName = $SqlServerLogin \r\n", " targetAccessToken = $targetAccessToken\r\n", " sqlAdminPassword = $SqlServerPassword\r\n", " }\r\n", " $json = $Body | ConvertTo-Json\r\n", " $Result['Body']=$json\r\n", "\r\n", " $Result\r\n", " \r\n", "}\r\n", "\r\n", "function Provision-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]$FunctionName,\r\n", " [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string]$ScopeRGName,\r\n", " [Parameter(Mandatory=$false)][ValidateNotNullOrEmpty()][string]$Role=\"Contributor\"\r\n", " )\r\n", "\r\n", " # Get the scope resource group's ID\r\n", " $scopeID = az group show --resource-group $ScopeRGName --subscription $Subscription --query \"[id]\" -o JSON | ConvertFrom-Json \r\n", " if(!$scopeID) {\r\n", " Write-Output \"Provision-FuncRBAC failed.\" \r\n", " return }\r\n", " else { Write-Output \"Found scope '$ScopeRGName' with ID... '$scopeID'\"\r\n", " }\r\n", "\r\n", " # Get the az function principal id\r\n", " $app_PrincipalID = az functionapp show -n $FunctionName --resource-group $ResourceGroupName --subscription $Subscription --query \"[identity.principalId]\" -o JSON | ConvertFrom-Json \r\n", " if(!$app_PrincipalID) {\r\n", " Write-Output \"Provision-FuncRBAC failed.\" \r\n", " return }\r\n", " else { Write-Output \"Found principal id of Azure function '$FunctionName'... '$app_PrincipalID'\"\r\n", " }\r\n", "\r\n", " # Verify if a role assignment has been created for function\r\n", " $app_RoleAssignmentDefinition= az role assignment list --subscription $Subscription --assignee $app_PrincipalID --scope $scopeID --query \"[].roleDefinitionName\" -o JSON 2>$null | ConvertFrom-Json\r\n", "\r\n", " if($app_RoleAssignmentDefinition -eq $Role)\r\n", " {\r\n", " Write-Output \"Found Role Assignment for Principal ID.. '$app_PrincipalID' with Role.. '$app_RoleAssignmentDefinition' . No work needed\"\r\n", " }\r\n", " else\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 -n $FunctionName --role $Role -g $ResourceGroupName --scope $scopeID --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 -n $FunctionName --role $Role -g $ResourceGroupName --scope $scopeID --subscription $Subscription \r\n", " }\r\n", "}\r\n", "Write-Host \"Helper Functions Created successfully\" " ], "metadata": { "azdata_cell_guid": "4730aec5-7aa6-4a2a-baf4-696dc74aa898", "tags": [ "hide_input" ] }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Connect to Azure Account\r\n", "Run the below cell to login to an Azure account. Be sure to check the Windows Taskbar for a login dialog box underneath the notebook or other windows or by pressing Alt+TAB." ], "metadata": { "azdata_cell_guid": "5cd37536-c2c5-4b97-8383-fa761d7cbda3" } }, { "cell_type": "code", "source": [ "Login-Azure " ], "metadata": { "azdata_cell_guid": "e9cd7ac2-ff0b-43b4-baf8-b71d3128885c" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Verify ADP Resources \r\n", "Verify if ADP resources exists in specified Resource Group" ], "metadata": { "azdata_cell_guid": "bec05e08-67ba-4071-8459-5b32dc7f876a" } }, { "cell_type": "code", "source": [ "Verify-ADPResources -Subscription $AdpSubscription -ADPResourceGroupName $AdpResourceGroup `\r\n", " -BatchAccountName $AdpBatch -FunctionName $AdpFunc -VNetName $AdpVNET " ], "metadata": { "azdata_cell_guid": "e89f6eb9-fcbc-4b7d-bcd1-37f1eb52cc02", "tags": [ "hide_input" ] }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Verify RBAC of Azure Function\r\n", "Roles based access control is a function of Azure that assigns services to a role with a specific access scope (or area of access). The ADP Orchestrator function requires Contributor access over the Resource Group where the SQL Server to be exported exists. The function below will 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": "0c95bb17-b3cf-4a8b-8aa6-690ac6139e37" } }, { "cell_type": "code", "source": [ "Provision-FuncRBAC -FunctionName $AdpFunc -ScopeRGName $TargetResourceGroupName -ResourceGroupName $AdpResourceGroup -Subscription $AdpSubscription" ], "metadata": { "azdata_cell_guid": "c374e57c-51ec-4a3f-9966-1e50cefc8510" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Prepare input variable for Orchestrator Import Function" ], "metadata": { "azdata_cell_guid": "b517742f-fa3d-4a4f-9ec0-b320c71738d4" } }, { "cell_type": "code", "source": [ "$InputForImportFunction = Prepare-InputForImportFunction -Subscription $AdpSubscription -ADPResourceGroupName $AdpResourceGroup `\r\n", " -BatchAccountName $AdpBatch -FunctionName $AdpFunc -TargetRGName $TargetResourceGroupName `\r\n", " -VNetName $AdpVNET -BackupFiles_StorageAccount $StorageAccountName -BackupFiles_ContainerName $ContainerName `\r\n", " -SqlServerName $LogicalSQLServerName -SqlServerPassword $LSqlServerpassword\r\n", "Write-Host \"Setting parameter variables for Import Function Call...\"\r\n", "$InputForImportFunction.Header\r\n", "$InputForImportFunction.FunctionURL\r\n", "$InputForImportFunction.Body" ], "metadata": { "azdata_cell_guid": "bfba288e-3466-4c57-9f3c-5281753601cf", "tags": [ "hide_input" ] }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Start Import of SQL Server \r\n", "Run the cell to start import from specified backup files" ], "metadata": { "azdata_cell_guid": "8f615b19-1e1d-405f-9f4a-ad7cc303487a" } }, { "cell_type": "code", "source": [ "$importResponse = Invoke-RestMethod -Method 'Post' -Headers $InputForImportFunction.Header -Uri $InputForImportFunction.FunctionURL -Body $InputForImportFunction.Body -ContentType 'application/json'\r\n", "$importResponse" ], "metadata": { "azdata_cell_guid": "7e251aa5-7a92-4212-8c81-394c2058f1fa", "tags": [ "hide_input" ] }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Get Status of Import Operation\r\n", "Run the cell to get import operation status" ], "metadata": { "azdata_cell_guid": "cb1988a9-9797-49f0-8ddb-1634df48f027" } }, { "cell_type": "code", "source": [ "$statusCheckResponse = Invoke-RestMethod -Method 'Get' -Uri $importResponse.statusQueryGetUri\r\n", "\r\n", "\r\n", "Write-Host \"Orchestrator Request: \" $statusCheckResponse.name\r\n", "Write-Host \"`tOrchestrator Status: \" $statusCheckResponse.runtimeStatus\r\n", "\r\n", "$outputParams = $statusCheckResponse.output \r\n", "if ($outputParams)\r\n", "{\r\n", " $batchJobID = $outputParams.Item2[2]\r\n", " $containerUrl = $outputParams.Item2[3]\r\n", "\r\n", " Write-Host \"`tCreated Import Batch Job ID: \" $batchJobId\r\n", " $azBatchLogin = az batch account login --name $AdpBatch --resource-group $AdpResourceGroup -o JSON | ConvertFrom-Json\r\n", " $jobStatus = az batch job show --job-id $batchJobID -o JSON | ConvertFrom-Json\r\n", " Write-Host \"Import Job running on Pool: \" $jobStatus.poolInfo.poolId\r\n", " Write-Host \"`Import Request Status: \" $jobStatus.state\r\n", "\r\n", " $taskList = az batch task list --job-id $batchJobId -o JSON | ConvertFrom-Json\r\n", " if ($taskList)\r\n", " {\r\n", " foreach ($task in $taskList)\r\n", " {\r\n", " Write-Host \"`tDatabase Import Task ID: \" $task.id \r\n", " Write-Host \"`t`tStatus: \" $task.state\r\n", " $taskExecution = $task.executionInfo\r\n", " if ($taskExecution)\r\n", " {\r\n", " Write-Host \"`t`tResult: \" $taskExecution.result\r\n", " }\r\n", " }\r\n", " }\r\n", "}\r\n", "" ], "metadata": { "azdata_cell_guid": "3c59dc7b-2648-46ee-b57e-d9e99580a093", "tags": [ "hide_input" ] }, "outputs": [], "execution_count": null } ] }