{
    "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": [
                "### Load Required Modules\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "67e288c6-63df-475e-9cad-bab323d30c4e"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Import-Module dbatools\r\n",
                "Import-Module Az.Resources\r\n",
                "Import-Module Az.Storage"
            ],
            "metadata": {
                "azdata_cell_guid": "26df73fa-6f4f-40b4-8c47-10ce7e2db404",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Choose Migration Source\r\n",
                "\r\n",
                "Required parameters:\r\n",
                "\r\n",
                "- Server Name\r\n",
                "- Database Name\r\n",
                "- "
            ],
            "metadata": {
                "azdata_cell_guid": "1e70b806-c94d-4be2-87cf-ad73fb85821d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$Credential = Get-Credential -Message \"Type the name and password of the local administrator account.\"\r\n",
                "$Credential"
            ],
            "metadata": {
                "azdata_cell_guid": "41faae8f-6245-4acb-88d5-dbb0b92ad7f5"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "# Variables\r\n",
                "\r\n",
                "## Global\r\n",
                "$Location = \"West US 2\"\r\n",
                "$ResourceGroupName = \"sqlmig\"\r\n",
                "\r\n",
                "## Storage\r\n",
                "$StorageName = $ResourceGroupName + \"storage\"\r\n",
                "$StorageSku = \"Premium_LRS\"\r\n",
                "\r\n",
                "## Network\r\n",
                "$InterfaceName = $ResourceGroupName + \"ServerInterface\"\r\n",
                "$NsgName = $ResourceGroupName + \"nsg\"\r\n",
                "$VNetName = $ResourceGroupName + \"VNet\"\r\n",
                "$SubnetName = \"Default\"\r\n",
                "$VNetAddressPrefix = \"10.0.0.0/16\"\r\n",
                "$VNetSubnetAddressPrefix = \"10.0.0.0/24\"\r\n",
                "$TCPIPAllocationMethod = \"Dynamic\"\r\n",
                "$DomainName = $ResourceGroupName\r\n",
                "\r\n",
                "##Compute\r\n",
                "$VMName = $ResourceGroupName + \"VM\"\r\n",
                "$ComputerName = $ResourceGroupName + \"Server\"\r\n",
                "$VMSize = \"Standard_DS13_v2\"\r\n",
                "$OSDiskName = $VMName + \"OSDisk\"\r\n",
                "\r\n",
                "##Image\r\n",
                "$PublisherName = \"MicrosoftSQLServer\"\r\n",
                "$OfferName = \"SQL2017-WS2016\"\r\n",
                "$Sku = \"SQLDEV\"\r\n",
                "$Version = \"latest\"\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "7e251e62-b47c-4800-986d-b71be8bc0a21"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "# Resource Group\r\n",
                "New-AzResourceGroup -Name $ResourceGroupName -Location $Location\r\n",
                "$ResourceGroupName\r\n",
                "$Location"
            ],
            "metadata": {
                "azdata_cell_guid": "eea4904c-55f0-47a4-81a3-5daf0a864687"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "# Storage\r\n",
                "$StorageAccount = New-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageName -SkuName $StorageSku -Kind \"Storage\" -Location $Location\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b19f1450-0283-4772-862a-3e59b65d0e75"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "$StorageAccount = Get-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageName"
            ],
            "metadata": {
                "azdata_cell_guid": "42602cef-76ed-4c91-a2f0-a7a8a6132eff"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "# Network\r\n",
                "$SubnetConfig = New-AzVirtualNetworkSubnetConfig -Name $SubnetName -AddressPrefix $VNetSubnetAddressPrefix\r\n",
                "$VNet = New-AzVirtualNetwork -Name $VNetName -ResourceGroupName $ResourceGroupName -Location $Location -AddressPrefix $VNetAddressPrefix -Subnet $SubnetConfig\r\n",
                "$PublicIp = New-AzPublicIpAddress -Name $InterfaceName -ResourceGroupName $ResourceGroupName -Location $Location -AllocationMethod $TCPIPAllocationMethod -DomainNameLabel $DomainName\r\n",
                "#$NsgRuleRDP = New-AzNetworkSecurityRuleConfig -Name \"RDPRule\" -Protocol Tcp -Direction Inbound -Priority 1000 -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange 3389 -Access Allow\r\n",
                "$NsgRuleSQL = New-AzNetworkSecurityRuleConfig -Name \"MSSQLRule\"  -Protocol Tcp -Direction Inbound -Priority 1001 -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange 1433 -Access Allow\r\n",
                "#$Nsg = New-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroupName -Location $Location -Name $NsgName -SecurityRules $NsgRuleRDP,$NsgRuleSQL\r\n",
                "$Nsg = New-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroupName -Location $Location -Name $NsgName -SecurityRules $NsgRuleSQL\r\n",
                "$Interface = New-AzNetworkInterface -Name $InterfaceName -ResourceGroupName $ResourceGroupName -Location $Location -SubnetId $VNet.Subnets[0].Id -PublicIpAddressId $PublicIp.Id -NetworkSecurityGroupId $Nsg.Id\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "0435fd83-3e9c-4929-930a-2b7022db3f99"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "$StorageAccount"
            ],
            "metadata": {
                "azdata_cell_guid": "37e1869b-eb9f-465a-87d3-78120e4c6d06"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "# Compute\r\n",
                "$VirtualMachine = New-AzVMConfig -VMName $VMName -VMSize $VMSize\r\n",
                "#$Credential = Get-Credential -Message \"Type the name and password of the local administrator account.\"\r\n",
                "$VirtualMachine = Set-AzVMOperatingSystem -VM $VirtualMachine -Windows -ComputerName $ComputerName -Credential $Credential -ProvisionVMAgent -EnableAutoUpdate #-TimeZone = $TimeZone\r\n",
                "$VirtualMachine = Add-AzVMNetworkInterface -VM $VirtualMachine -Id $Interface.Id\r\n",
                "#$OSDiskUri = $StorageAccount.PrimaryEndpoints.Blob.ToString() + \"vhds/\" + $OSDiskName + \".vhd\"\r\n",
                "#$VirtualMachine = Set-AzVMOSDisk -VM $VirtualMachine -Name $OSDiskName -VhdUri $OSDiskUri -Caching ReadOnly -CreateOption FromImage\r\n",
                "$VirtualMachine = Set-AzVMSourceImage -VM $VirtualMachine -PublisherName $PublisherName -Offer $OfferName -Skus $Sku -Version $Version"
            ],
            "metadata": {
                "azdata_cell_guid": "d0a4da84-d591-436e-8b6c-d4cb2787a6e2"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "# Create the VM in Azure\r\n",
                "New-AzVM -ResourceGroupName $ResourceGroupName -Location $Location -VM $VirtualMachine\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "70967b75-1ef7-4d69-aad9-0184cfb44aa9"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "# Add the SQL IaaS Extension, and choose the license type\r\n",
                "New-AzSqlVM -ResourceGroupName $ResourceGroupName -Name $VMName -Location $Location -LicenseType \"PAYG\""
            ],
            "metadata": {
                "azdata_cell_guid": "a8db01b1-a009-4367-bd39-77187482afc3"
            },
            "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": [
                "\r\n",
                "$ServerName = \"sqltools2017-3\"\r\n",
                "$DatabaseName = \"Keep_WideWorldImporters\"\r\n",
                "\r\n",
                "Get-DbaProcess -SqlInstance $ServerName -Database $DatabaseName | \r\n",
                "Select Host, login, Program"
            ],
            "metadata": {
                "azdata_cell_guid": "28393e59-4ea1-4f0f-8f9f-8a504f15e723"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Create Target SQL Server VM"
            ],
            "metadata": {
                "azdata_cell_guid": "07d076d0-abf3-496c-8ecb-f85102c4104b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "6f190c2d-7361-4db8-819d-29087eae8aaa"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Create temporary resources for data movement"
            ],
            "metadata": {
                "azdata_cell_guid": "cc18027e-4636-465d-abaf-f3de88fea406"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$location = \"westus\"\r\n",
                "$resourceGroup = \"temp-sqlmigration\"\r\n",
                "$blobStorageAccount = \"temp-sqlmigration\"\r\n",
                "$containerName = \"backups\"\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "New-AzResourceGroup"
            ],
            "metadata": {
                "azdata_cell_guid": "f7d53cb1-a55d-4634-95f7-d3e8cf9fab52"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "$targetLogin = Get-Credential -Message \"Login to target SQL Server instance as:\""
            ],
            "metadata": {
                "azdata_cell_guid": "c3dbd1a7-5514-4fdc-9430-736c92e875a4"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "$myGwIp = Get-AzPublicIpAddress -Name $GwIP1 -ResourceGroup $RG1\r\n",
                "$myGwIp.IpAddress"
            ],
            "metadata": {
                "azdata_cell_guid": "bc6e8330-95bb-44a5-a117-020f657cad2b"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "a61ffe69-0929-4246-8ad0-846f540f4e0c"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}