# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .ps1
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: PowerShell
#     name: powershell
# ---

# + [markdown] azdata_cell_guid="baa1b762-f2a9-461f-a126-49f0d3b5e4f0"
# # Import Existing Azure SQL Server Resources
#

# + [markdown] azdata_cell_guid="1ccbf203-d568-408c-a641-7e5cfa93802a"
# The notebook will help accomplish the below steps as a part of Importing existing Azure SQL Server Resources
#
# -   [ ]  Connect to Azure Subscription
# -   [ ]  Choose Resource Group (Read access required)
# -   [ ]  Choose Microsoft SQL Server Resources to import
# -   [ ]  Choose/Create Migration Storage
# -   [ ]  Install Application + Data Portability function
# -   [ ]  Install ADP Azure Batch processing pipeline
# -   [ ]  Store SqlPackage.exe in Migration Storage for ADP function to hand to Az Batch
#
# Execute:
#
# -   [ ]  Check all prerequisites
# -   [ ]  Kick off ADP service
#
# Monitor:
#
# -   [ ]  Check import status.

# + [markdown] azdata_cell_guid="09f648ab-67e8-4127-94bd-649d5e970321"
# ## Set Variables for the Notebook

# + azdata_cell_guid="01888595-0d1c-445b-ba85-dd12caa30192" tags=[]
# ADP Resource 
$AdpSubscription     = ""                     # Azure Subscription ID/Name # The bacpac files and ADP Resources are assumed to be in the same subscription
$AdpResourceGroup    = ""                                            # Azure Resource Group which contains the ADP Resources

# SQL Server 
$TargetResourceGroupName    = ""                                                # Azure ResourceGroup into which the sql server backup needs to be restored
$StorageAccountName         = ""
$ContainerName              = ""
$LogicalSQLServerName       = ""                                                # New sql server name
$LSqlServerPassword          = ""

# Set Variables for ADP Resources
$AdpFunc            = $AdpResourceGroup + "Control"   
$AdpBatch           = $AdpResourceGroup.ToLower() + "batch"
$AdpVNET            = $AdpResourceGroup + "Vnet"

# + [markdown] azdata_cell_guid="4a35cf8e-6598-43e8-a0da-2c0c369df548"
# ## Notebook Functions
# 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. 

# + azdata_cell_guid="4730aec5-7aa6-4a2a-baf4-696dc74aa898" tags=["hide_input"]
# Expand cell to view framework

function Login-Azure
{    
    # query azure locations to test for existing az login session exists with valid access tocken
    $azureLocations = az account list-locations -o JSON 2>$null | ConvertFrom-Json
    
    if (!$azureLocations){ #If there are no az locations, there is no existing az login session
        $subscriptions = az login -o JSON | ConvertFrom-Json      # Login   
    }
    else {
        $subscriptions = az account list -o JSON | ConvertFrom-Json # getting subscriptions for the user to use in gridview
    }

    if(![string]::IsNullOrWhiteSpace($AdpSubscription)) #If there is a subscription specified by user in the variables section
    {
        $specified_Subscription= az account show --subscription $AdpSubscription -o json |ConvertFrom-Json 
        if (!$specified_Subscription) #if specified subscription is not valid
        {       
            $currentUser= az ad signed-in-user show --query "{displayName:displayName,UPN:userPrincipalName}" -o json|ConvertFrom-Json # get current logged in user infomration
            Write-Host "Refer below for the list of subscriptions for logged in account '$($currentUser.UPN)'`n"
            az account list --query "[].{Name:name,SubscriptionID:id}" -o table          # list subscriptions under current logged in account
        }
        else { # if specified subscription is valid
            Write-Output "Using subscription... '$($specified_Subscription.name)' ... '$($specified_Subscription.id)'" 
        }
    }
    else { # if no subscription is specified, users are given a gridview to select subscription from

        $selectedSubscription = $subscriptions | Select-Object -Property Name, Id | Out-GridView -PassThru
        $SubscriptionId = $selectedSubscription.Id
        $Subscription = $selectedSubscription.Name   
        $AdpSubscription = $subscription  
        Write-Output "Using subscription... '$AdpSubscription' ... '$SubscriptionId'"    
    }    
}

function Verify-ADPResources
{    
    [CmdletBinding()]
    param(
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$Subscription,
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ADPResourceGroupName,
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$FunctionName,  
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BatchAccountName,
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$VNetName,
        [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$ApplicationName="SqlPackageWrapper",  
        [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$ApplicationPackageVersionName="1",
        [Parameter (Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$SubNetName="default" 
    )    

# validate Subscription
$specified_Subscription= az account show --subscription $Subscription -o json | ConvertFrom-Json
if(!$specified_Subscription){
    $currentUser= az ad signed-in-user show --query "{displayName:displayName,UPN:userPrincipalName}" -o json|ConvertFrom-Json # get current logged in user information
    Write-Host "Refer below for the list of subscriptions for logged in account '$($currentUser.UPN)'`n"
    az account list --query "[].{Name:name,SubscriptionID:id}" -o table          # list subscriptions under current logged in account
    return } 
# validate ResourceGroup    
$specified_ResourceGroup= az group show -n $ADPResourceGroupName --subscription $Subscription -o json | ConvertFrom-Json
if(!$specified_ResourceGroup) {        
        return
        } 

$Installed = [ordered]@{} # ordered hash to store status of installation
$countError=0

#Verify if VNet exists       
$specified_VNet= az network vnet show -n $VNetName -g $ADPResourceGroupName --subscription $Subscription -o JSON 2>$null  |ConvertFrom-Json   
if(!$specified_VNet) {
    $Installed['VNET']="Not Found"
    $countError++
}   
else { 
    $existingVnetSubnet =  az network vnet subnet show -n $SubNetName --vnet-name $VNetName -g $ADPResourceGroupName --subscription $Subscription -o JSON 2>$null |ConvertFrom-Json
    if(!$existingVnetSubnet){
        $Installed['VNET']="Default Subnet under"+ $VNetName + "Not Found"
        $countError++
        }
    else {
    $Installed['VNET']="Installed"
    }
 }

#Verify if FunctionApp Exists
$specified_FunctionApp = az functionapp show -n $FunctionName -g $ADPResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json
if(!$specified_FunctionApp)
{
    $Installed['FunctionApp']="Not Installed"
    $countError++
}
else
{
    $Installed['FunctionApp']="Installed"
} 

#check if Batch account exists
$specified_BatchAccount = az batch account show -n $BatchAccountName -g $ADPResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json
if(!$specified_BatchAccount)
{
    $Installed['Batch']="Not Installed"
    $countError++
}
else
{
    $appPackageInstalled = az batch application package show --application-name $ApplicationName --version-name $ApplicationPackageVersionName -n $BatchAccountName -g $ADPResourceGroupName --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json
    $connectedToStorage= $specified_BatchAccount.autoStorage 
    if($connectedToStorage -and $appPackageInstalled){ # BatchAccount connected to storageaccount and  applicationpackage is installed
            $Installed['Batch']="Installed"
            $Installed['Batch_ApplicationPackage']="Installed"
            $Installed['Batch_StorageAccount']="Connected to storage- "+$connectedToStorage.storageAccountId.Split("/")[-1]
        }
    if(!$connectedToStorage)
    {
        $Installed['Batch_StorageAccount']='Not Found'
        $countError++
    }  
    if(!$appPackageInstalled)
    {
        $Installed['Batch_ApplicationPackage']="Not Found"
        $countError++
    }    
}
if ($countError -gt 0){
    Write-Output "ADP Resources are not installed correctly. Please refer the list below and use the Bootstrap NB to install ADP Resources"
}
$Installed
if ($countError -eq 0){
    Write-Output "`nFound all ADP Resources."
}
}

function Prepare-InputForImportFunction
{    
    [CmdletBinding()]
    param(
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$Subscription,
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$ADPResourceGroupName,
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$FunctionName,  
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BatchAccountName,
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BackupFiles_StorageAccount,
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BackupFiles_ContainerName,
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$VNetName,        
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$TargetRGName,
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SqlServerName,
        [Parameter (Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SqlServerPassword
    )
    
    $Result = @{}
    # Build Header 
    ## get Function key
    $FunctionAppID =az functionapp show -n $FunctionName -g $ADPResourceGroupName --subscription $Subscription --query "[id]" -o JSON 2>$null | ConvertFrom-Json
    $DefaultHostKey = az rest --method post --uri "$FunctionAppID/host/default/listKeys?api-version=2018-11-01" --query "[functionKeys.default]" -o JSON 2>$null | ConvertFrom-Json
    ## Build Json Object for Headers
    $headers = @{
        'x-functions-key' = $DefaultHostKey
    }
    $Result['Header']=$headers

    # Build string for Function URL 
    $specified_Subscription= az account show --subscription $Subscription -o json |ConvertFrom-Json #Get SpecifiedSubscriptionID
    $SubscriptionID= $specified_Subscription.id
    $FunctionUrl = 'https://'+ $FunctionName +'.azurewebsites.net/api/subscriptions/'+ $SubscriptionID +'/resourceGroups/' + $ADPResourceGroupName + '/Import'
    $Result['FunctionURL']=$FunctionUrl

    # Set parameter variables for Body
    ## Get BatchAccountURL 
    $specified_Batch = az batch account show -n $BatchAccountName -g $ADPResourceGroupName  --subscription $Subscription -o JSON 2>$null | ConvertFrom-Json
    $BatchAccountURL = 'https://' + $specified_Batch.accountEndpoint

    ## Get default SubNet ID for specified VNet
    $specified_VNet_SubNet = az network vnet subnet show -g $ADPResourceGroupName --vnet-name $VNetName -n 'default' --subscription $Subscription -o JSON |ConvertFrom-Json
    $VNetSubNetID = $specified_VNet_SubNet.id

    ## Create access token to source sql server
    $targetAccessToken = az account get-access-token --resource=https://database.windows.net --query accessToken
    $targetAccessToken

    ## Build JSon object for Body
    $Body = @{
        batchAccountUrl = $BatchAccountURL
        VNetSubnetId= $VNetSubNetID
        storageAccountName = $BackupFiles_StorageAccount
        containerName = $BackupFiles_ContainerName
        targetSqlServerResourceGroupName = $TargetRGName
        targetSqlServerName = $SQLServerName 
        userName = $SqlServerLogin 
        targetAccessToken = $targetAccessToken
        sqlAdminPassword = <PASSWORD>
    }
    $json = $Body | ConvertTo-Json
    $Result['Body']=$json

    $Result
    
}

function Provision-FuncRBAC {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string]$Subscription,
        [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string]$ResourceGroupName,
        [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string]$FunctionName,
        [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string]$ScopeRGName,
        [Parameter(Mandatory=$false)][ValidateNotNullOrEmpty()][string]$Role="Contributor"
    )

    # Get the scope resource group's ID
    $scopeID = az group show --resource-group $ScopeRGName --subscription $Subscription --query "[id]" -o JSON | ConvertFrom-Json 
    if(!$scopeID) {
        Write-Output "Provision-FuncRBAC failed." 
        return }
    else { Write-Output "Found scope '$ScopeRGName' with ID... '$scopeID'"
    }

    # Get the az function principal id
    $app_PrincipalID = az functionapp show -n $FunctionName --resource-group $ResourceGroupName --subscription $Subscription --query "[identity.principalId]" -o JSON  | ConvertFrom-Json  
    if(!$app_PrincipalID) {
        Write-Output "Provision-FuncRBAC failed." 
        return }
    else {  Write-Output "Found principal id of Azure function '$FunctionName'... '$app_PrincipalID'"
    }

    # Verify if a role assignment has been created for function
    $app_RoleAssignmentDefinition= az role assignment list --subscription $Subscription --assignee $app_PrincipalID --scope $scopeID --query "[].roleDefinitionName" -o JSON 2>$null  | ConvertFrom-Json

    if($app_RoleAssignmentDefinition -eq $Role)
    {
        Write-Output "Found Role Assignment for Principal ID.. '$app_PrincipalID' with Role.. '$app_RoleAssignmentDefinition' . No work needed"
    }
    else
    {
        # Continue to setup RBAC, once we verify an assignment is not setup and all the resources exist
        Write-Output "Creating new role assignment by running: 'az functionapp identity assign -n $FunctionName --role $Role -g $ResourceGroupName --scope $scopeID --subscription $Subscription'"
        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"
        az functionapp identity assign -n $FunctionName --role $Role -g $ResourceGroupName --scope $scopeID --subscription $Subscription 
    }
}
Write-Host "Helper Functions Created successfully"  

# + [markdown] azdata_cell_guid="5cd37536-c2c5-4b97-8383-fa761d7cbda3"
# ## Connect to Azure Account
# 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.

# + azdata_cell_guid="e9cd7ac2-ff0b-43b4-baf8-b71d3128885c"
Login-Azure 

# + [markdown] azdata_cell_guid="bec05e08-67ba-4071-8459-5b32dc7f876a"
# ## Verify ADP Resources 
# Verify if ADP resources exists in specified Resource Group

# + azdata_cell_guid="e89f6eb9-fcbc-4b7d-bcd1-37f1eb52cc02" tags=["hide_input"]
Verify-ADPResources -Subscription $AdpSubscription -ADPResourceGroupName $AdpResourceGroup `
                     -BatchAccountName $AdpBatch -FunctionName $AdpFunc -VNetName $AdpVNET 

# + [markdown] azdata_cell_guid="0c95bb17-b3cf-4a8b-8aa6-690ac6139e37"
# ## Verify RBAC of Azure Function
# 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. 

# + azdata_cell_guid="c374e57c-51ec-4a3f-9966-1e50cefc8510"
Provision-FuncRBAC -FunctionName $AdpFunc -ScopeRGName $TargetResourceGroupName -ResourceGroupName $AdpResourceGroup -Subscription $AdpSubscription

# + [markdown] azdata_cell_guid="b517742f-fa3d-4a4f-9ec0-b320c71738d4"
# ## Prepare input variable for Orchestrator Import Function

# + azdata_cell_guid="bfba288e-3466-4c57-9f3c-5281753601cf" tags=["hide_input"]
$InputForImportFunction = Prepare-InputForImportFunction -Subscription $AdpSubscription -ADPResourceGroupName $AdpResourceGroup `
                             -BatchAccountName $AdpBatch -FunctionName $AdpFunc -TargetRGName $TargetResourceGroupName `
                             -VNetName $AdpVNET -BackupFiles_StorageAccount $StorageAccountName -BackupFiles_ContainerName $ContainerName `
                             -SqlServerName $LogicalSQLServerName -SqlServerPassword $<PASSWORD>
Write-Host "Setting parameter variables for Import Function Call..."
$InputForImportFunction.Header
$InputForImportFunction.FunctionURL
$InputForImportFunction.Body

# + [markdown] azdata_cell_guid="8f615b19-1e1d-405f-9f4a-ad7cc303487a"
# ## Start Import of SQL Server 
# Run the cell to start import from specified backup files

# + azdata_cell_guid="7e251aa5-7a92-4212-8c81-394c2058f1fa" tags=["hide_input"]
$importResponse = Invoke-RestMethod -Method 'Post' -Headers $InputForImportFunction.Header -Uri $InputForImportFunction.FunctionURL -Body $InputForImportFunction.Body -ContentType 'application/json'
$importResponse

# + [markdown] azdata_cell_guid="cb1988a9-9797-49f0-8ddb-1634df48f027"
# ## Get Status of Import Operation
# Run the cell to get import operation status

# + azdata_cell_guid="3c59dc7b-2648-46ee-b57e-d9e99580a093" tags=["hide_input"]
$statusCheckResponse = Invoke-RestMethod -Method 'Get' -Uri $importResponse.statusQueryGetUri


Write-Host "Orchestrator Request: " $statusCheckResponse.name
Write-Host "`tOrchestrator Status: " $statusCheckResponse.runtimeStatus

$outputParams = $statusCheckResponse.output 
if ($outputParams)
{
    $batchJobID = $outputParams.Item2[2]
    $containerUrl = $outputParams.Item2[3]

    Write-Host "`tCreated Import Batch Job ID: " $batchJobId
    $azBatchLogin = az batch account login --name $AdpBatch --resource-group $AdpResourceGroup -o JSON | ConvertFrom-Json
    $jobStatus = az batch job show --job-id $batchJobID -o JSON | ConvertFrom-Json
    Write-Host "Import Job running on Pool: " $jobStatus.poolInfo.poolId
    Write-Host "`Import Request Status: " $jobStatus.state

    $taskList = az batch task list --job-id $batchJobId -o JSON | ConvertFrom-Json
    if ($taskList)
    {
        foreach ($task in $taskList)
        {
            Write-Host "`tDatabase Import Task ID: " $task.id 
            Write-Host "`t`tStatus: " $task.state
            $taskExecution = $task.executionInfo
            if ($taskExecution)
            {
                Write-Host "`t`tResult: " $taskExecution.result
            }
        }
    }
}


StarcoderdataJupyterScriptsDedupFiltered