{
    "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": [
                "Migrate SQL Server Instance to Azure SQL Server VM\n",
                "================================"
            ],
            "metadata": {
                "azdata_cell_guid": "f706da59-22c3-4317-bf41-c00dde794097"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Source SQL Instance\r\n",
                "The following code is used to specify the source SQL Server instance.  Data and Server objects will be copied from this server to the target SQL Server instance.  The code below will first prompt for the name of the source SQL Server instance.  Secondarily, the code will use a secure credential prompt for the login credentials for the source instance. \r\n",
                "\r\n",
                "*Note: the notebook currently is setup for SQL Authentication.  Future updates will add support for multiple authentication types.*\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "2ac081f4-853a-4381-a303-e6ca557503fb"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# set the name or IP address of the source SQL Server instance\n",
                "Add-Type -Assemblyname PresentationFramework\n",
                "$sourceInfoBox = New-Object AnyBox.AnyBox\n",
                "\n",
                "$sourceInfoBox.Prompts = New-AnyBoxPrompt -Name \"serverName\" -Message \"Source SQL Instance name or IP address:\" -ValidateNotEmpty\n",
                "$sourceInfoBox.Buttons = New-AnyBoxButton -Name 'submit' -Text \"OK\" -IsDefault\n",
                "\n",
                "$sourceInfoBox.Topmost = $true\n",
                "$sourceInfoBox.WindowStyle = 'None'\n",
                "\n",
                "$sourceInfoResponse = $sourceInfoBox | Show-AnyBox\n",
                "$sourceServerName = $sourceInfoResponse['serverName']\n",
                "\n",
                "# prompt for username and pw for authentication - supports SQL Authentication currently\n",
                "$sourceCredential = Get-Credential -Message \"Enter Source Login Credentials\"\n",
                "\n",
                "# test the connectivity and display instance information\n",
                "$sourceTest = Test-DbaConnection -SqlInstance $sourceServerName -SqlCredential $sourceCredential\n",
                "$sourceTest\n",
                "\n",
                "# sourceConnection is used in the SQL migration cell below\n",
                "$sourceConnection = Connect-DbaInstance -SqlInstance $sourceServerName -SqlCredential $sourceCredential"
            ],
            "metadata": {
                "azdata_cell_guid": "76a50416-b804-46ae-a49c-99baaeb31f7d",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Target SQL Instance\r\n",
                "The following code is used to specify the target SQL Server instance.  Data and Server objects will be copied to this server from the source SQL Server instance.  The code below will first prompt for the name of the target SQL Server instance.  Secondarily, the code will use a secure credential prompt for the login credentials for the target instance. \r\n",
                "\r\n",
                "*Note: the notebook currently is setup for SQL Authentication.  Future updates will add support for multiple authentication types.*\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "43751f97-d545-4e84-ac99-109c719a048d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# set the name or IP address of the source SQL Server instance\r\n",
                "Add-Type -Assemblyname PresentationFramework\r\n",
                "$targetInfoBox = New-Object AnyBox.AnyBox\r\n",
                "\r\n",
                "$targetInfoBox.Prompts = New-AnyBoxPrompt -Name \"serverName\" -Message \"Target SQL Instance name or IP address:\" -ValidateNotEmpty\r\n",
                "$targetInfoBox.Buttons = New-AnyBoxButton -Name 'submit' -Text \"OK\" -IsDefault\r\n",
                "\r\n",
                "$targetInfoBox.Topmost = $true\r\n",
                "$targetInfoBox.WindowStyle = 'None'\r\n",
                "\r\n",
                "$targetInfoResponse = $targetInfoBox | Show-AnyBox\r\n",
                "$targetServerName = $targetInfoResponse['serverName']\r\n",
                "\r\n",
                "# prompt for username and pw for authentication - supports SQL Authentication currently\r\n",
                "$targetCredential = Get-Credential -Message \"Enter Target Login Credentials\"\r\n",
                "\r\n",
                "## PowerShell Environment \r\n",
                "$targetTest = Test-DbaConnection -SqlInstance $targetServerName -SqlCredential $targetCredential\r\n",
                "$targetTest\r\n",
                "$targetConnection = Connect-DbaInstance -SqlInstance $targetServerName -SqlCredential $targetCredential"
            ],
            "metadata": {
                "azdata_cell_guid": "4f74315c-1147-4fe3-8959-13eb24cb0957"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Login to Microsoft Azure\r\n",
                "To configure and provision resources you must log into your Azure account and set the current subscription that is being used for the target SQL Server instance.  The following code will help you connect your account and choose the correct subscription.  When presented with the list of subscriptions, click on the desired subscription and press OK."
            ],
            "metadata": {
                "azdata_cell_guid": "2ffbcba3-934e-4498-87ed-7030124d3af2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Connect-AzAccount\r\n",
                "$migrationSubscription = Get-AzSubscription | Select-Object -Property Name, Id | Out-GridView -PassThru\r\n",
                "Set-AzContext -SubscriptionId $migrationSubscription.Id"
            ],
            "metadata": {
                "azdata_cell_guid": "8b23dcba-8d28-44eb-abbb-c7a54ef6b003",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Verify No Active Connections"
            ],
            "metadata": {
                "azdata_cell_guid": "81259d7e-62ac-4cdd-9e1b-2cb4ddb3d3b2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "#TODO - filter connected proceesses for user connections\r\n",
                "\r\n",
                "\r\n",
                "Get-DbaProcess -SqlInstance $SourceServerName -SqlCredential $sourceLogin | \r\n",
                "Select Host, login, Program"
            ],
            "metadata": {
                "azdata_cell_guid": "28393e59-4ea1-4f0f-8f9f-8a504f15e723",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Temporary Storage for Data Movement\r\n",
                "\r\n",
                "Offline data migration attempts to use backup to URL and restore from URL as the mechanism for moving data from the source instance to the target instance.  This code will check existance of the specified storage account and container to use for data migration. If the resources do not exist they will be created."
            ],
            "metadata": {
                "azdata_cell_guid": "cc18027e-4636-465d-abaf-f3de88fea406"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# TODO - add interactive support for these values\r\n",
                "$resourceGroup = \"sqlmig\"\r\n",
                "$blobStorageAccount = \"tempsqlmigstorage\"\r\n",
                "$containerName = \"backups\"\r\n",
                "$location = \"West US 2\"\r\n",
                "\r\n",
                "# Storage Account\r\n",
                "$storageAccount = Get-AzStorageAccount -ResourceGroupName $resourceGroup -Name $blobStorageAccount\r\n",
                "if ($storageAccount -eq $null)\r\n",
                "{\r\n",
                "    # specified storage account does not yet exist, attempt to create it\r\n",
                "    $storageAccount = New-AzStorageAccount -ResourceGroupName $resourceGroup -Name $blobStorageAccount -Location $location -SkuName Standard_LRS -Kind StorageV2\r\n",
                "}\r\n",
                "$storageAccount\r\n",
                "\r\n",
                "# Container\r\n",
                "$storageContext = $storageAccount.Context\r\n",
                "$storageContainer = Get-AzStorageContainer -Name $containerName -Context $storageContext\r\n",
                "if ($storageContainer -eq $null)\r\n",
                "{\r\n",
                "    #specified storage container does not yet exist, attempt to create it\r\n",
                "    $storageContainer = New-AzStorageContainer -Name $containerName -Context $storageContext -Permission Container\r\n",
                "}\r\n",
                "$storageContainer\r\n",
                "\r\n",
                "# Provide source instance with SAS token for blob access\r\n",
                "$sourceSAS = (New-AzStorageAccountSASToken -Service Blob -ResourceType Object -Permission \"rw\" -Context $storageContext).TrimStart('?')\r\n",
                "$sourceCred = New-DbaCredential -SqlInstance $sourceConnection -Name \"https://$blobStorageAccount.blob.core.windows.net/$containerName\" -Identity \"SHARED ACCESS SIGNATURE\" -SecurePassword (ConvertTo-SecureString $sourceSAS -AsPlainText -Force) -Force\r\n",
                "$sourceCred\r\n",
                "\r\n",
                "$targetSAS = (New-AzStorageAccountSASToken -Service Blob -ResourceType Object -Permission \"rw\" -Context $storageContext).TrimStart('?') # -ResourceType Container,Object\r\n",
                "$targetCred = New-DbaCredential -SqlInstance $targetConnection -Name \"https://$blobStorageAccount.blob.core.windows.net/$containerName\" -Identity \"SHARED ACCESS SIGNATURE\" -SecurePassword (ConvertTo-SecureString $targetSAS -AsPlainText -Force) -Force\r\n",
                "$targetCred\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "f7d53cb1-a55d-4634-95f7-d3e8cf9fab52",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Start the Server Migration\r\n",
                "\r\n",
                "The follow code executes the migration of objects from the source SQL Server instance to the target SQL Server instance.  There are a couple of options of interest:\r\n",
                "\r\n",
                "|Parameter|Description|\r\n",
                "|---|---|\r\n",
                "|Verbose|Include detailed logging information during the migration process|\r\n",
                "|WhatIf|Display what the migration process is going to do but do **not** actually perform the migration|\r\n",
                "\r\n",
                "The above parameters can be turned off by changing the $true value following the parameter to $false in the code cell below.  "
            ],
            "metadata": {
                "azdata_cell_guid": "518662fe-3ccf-4ead-80a7-3b890a394975"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$migrationParams = @{\r\n",
                "    Verbose = $true\r\n",
                "    WhatIf = $true\r\n",
                "    Source = $sourceConnection\r\n",
                "    Destination = $targetConnection\r\n",
                "    BackupRestore = $true\r\n",
                "    SharedPath = \"https://$blobStorageAccount.blob.core.windows.net/$containerName\" \r\n",
                "}\r\n",
                "\r\n",
                "Start-DbaMigration @migrationParams"
            ],
            "metadata": {
                "azdata_cell_guid": "b3a291f0-d9c4-438d-8b34-2c42a1a3b373",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}