{
    "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 Database 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.  In the following code cell set the following parameters:\r\n",
                "\r\n",
                "*Note: the notebook currently is setup for SQL Authentication.  Future updates will add support for multiple authentication types.*\r\n",
                "\r\n",
                "|Parameter|Description|\r\n",
                "|---|---|\r\n",
                "|sourceServerName| The name or IP address of the source instance|\r\n",
                "|sourceLogin| sql login to connect to source instance with |\r\n",
                "\r\n",
                "*Note: source password should be set in the environment variable SQLMIG_SourcePassword.  This is to avoid persisting the environment variable in the notebook file.*\r\n",
                "\r\n",
                "Edit the code below to specify the above parameters to test connectivity to the source instance.\r\n",
                "\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "2ac081f4-853a-4381-a303-e6ca557503fb"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$sourceServerName = 'sqltools2016-3'\r\n",
                "$sourceLogin = 'migtest'\r\n",
                "\r\n",
                "## TEMP - REMOVE BEFORE PUSHING CHANGES\r\n",
                "$env:SQLMIG_SourcePassword = 'Yukon900'\r\n",
                "\r\n",
                "## PowerShell Environment \r\n",
                "$sourceLoginPassword = ConvertTo-SecureString $env:SQLMIG_SourcePassword -AsPlaintext -Force\r\n",
                "$sourceCredential = New-Object System.Management.Automation.PSCredential ('migtest', $sourceLoginPassword)\r\n",
                "$sourceTest = Test-DbaConnection -SqlInstance $sourceServerName -SqlCredential $sourceCredential\r\n",
                "$sourceTest\r\n",
                "$sourceConnection = Connect-DbaInstance -SqlInstance $sourceServerName -SqlCredential $sourceCredential"
            ],
            "metadata": {
                "azdata_cell_guid": "76a50416-b804-46ae-a49c-99baaeb31f7d"
            },
            "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.  This is the SQL Server instance that Data and Server objects will be copied to.  In the following code cell set the following parameters:\r\n",
                "\r\n",
                "*Note: the notebook currently is setup for SQL Authentication.  Future updates will add support for multiple authentication types.*\r\n",
                "\r\n",
                "|Parameter|Description|\r\n",
                "|---|---|\r\n",
                "|targetServerName| The name or IP address of the target instance|\r\n",
                "|targetLogin| sql login to connect to target instance with |\r\n",
                "\r\n",
                "*Note: the target login password should be set in the environment variable SQLMIG_TargetPassword.  Thisis to avoid persisting the environment variable in the notebook file.*\r\n",
                "\r\n",
                "Edit the code below to specify the above parameters to test connectivity to the target instance."
            ],
            "metadata": {
                "azdata_cell_guid": "43751f97-d545-4e84-ac99-109c719a048d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$targetServerName = 'sqlmig.westus2.cloudapp.azure.com'\r\n",
                "$targetLogin = 'cloudsa'\r\n",
                "\r\n",
                "## TEMP - REMOVE BEFORE PUSHING CHANGES\r\n",
                "$env:SQLMIG_TargetPassword = 'Yukon900Yukon900'\r\n",
                "\r\n",
                "## PowerShell Environment \r\n",
                "$targetLoginPassword = ConvertTo-SecureString $env:SQLMIG_TargetPassword -AsPlaintext -Force\r\n",
                "$targetCredential = New-Object System.Management.Automation.PSCredential ('migtest', $targetLoginPassword)\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": [
                "$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"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Copy Databases to Target Server\r\n",
                "\r\n",
                "The following code will present a list of the databases from the source SQL Server instance.  Select the list of databases to copy and press OK.  The Copy-DbaDatabase CMDLET will take a backup of each database using the azure storage account information above.  Each database backup will then be restored from the blob storage account.  The database restore will use server defaults for database file location and structure.\r\n",
                "\r\n",
                "If the database being restored already exists on the target instance, the *Force* parameter determines the behavior of the notebook:\r\n",
                "\r\n",
                "|Force|Description|\r\n",
                "|---|---|\r\n",
                "|$true| Overwrite the existing database on the target instance|\r\n",
                "|$false| Do not restore the database if it already exists on the target instance|\r\n",
                "\r\n",
                "*Note: This can be a very long running process based on the size of the databases being copied.  The notebook should be allowed to run until the CMDLET completes.*\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "a82b0092-53a1-4dc3-8d73-16fae8c59ff7"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$databasesToCopy = Get-DbaDatabase -SqlInstance $sourceConnection | Select-Object -Property Name | Out-GridView -PassThru\r\n",
                "$databaseList = New-Object System.Collections.ArrayList\r\n",
                "foreach ($db in $databasesToCopy)\r\n",
                "{\r\n",
                "    $databaseList.Add($db.Name)    \r\n",
                "}\r\n",
                "\r\n",
                "$copyDatabaseParams = @{\r\n",
                "    Database = $databaseList\r\n",
                "    Source = $sourceConnection\r\n",
                "    Destination = $targetConnection\r\n",
                "    BackupRestore = $true\r\n",
                "    SharedPath =  \"https://$blobStorageAccount.blob.core.windows.net/$containerName\"\r\n",
                "    Force = $true\r\n",
                "    Verbose = $false     \r\n",
                "}\r\n",
                "\r\n",
                "Copy-DbaDatabase @copyDatabaseParams"
            ],
            "metadata": {
                "azdata_cell_guid": "5614e073-0a94-45af-8432-e7a6bf9121ea"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}