{
    "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": [
                "Create Azure SQL Database\r\n",
                "==================================\r\n",
                "\r\n",
                "Description\r\n",
                "-----------\r\n",
                "This Azure CLI script example creates database in Azure SQL Database and configures a server-level firewall rule. After the script has been successfully run, the database can be accessed from all Azure services and the configured IP address\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "c9e3ccf4-199a-486d-9e19-7f448bd03f00"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Steps of this procedure include:\r\n",
                "1. Connect to Azure subscription\r\n",
                "1. Provision resource group for SQL Managed Instance\r\n",
                "2. Create Sql Server\r\n",
                "3. Provision firewall rules to allow access\r\n",
                "4. Create Sql Database"
            ],
            "metadata": {
                "azdata_cell_guid": "ac87ba4f-d818-4fb3-8041-ee29ffd17294"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "#### Sample values for below code block\r\n",
                "| Variables | Sample Values |\r\n",
                "| ------------ | --------- |\r\n",
                "| $Env:BOOTSTRAP_Subscription | Subscription Name or ID  |\r\n",
                "| $Env:BOOTSTRAP_ResourceGroup | Intended Resource Group Name |\r\n",
                "| $location | Valid location from Azure... See appendix at bottom |\r\n",
                "| $randomIdentifier | Simple text... For example \"random123\"|\r\n",
                "| $server | Name of intended Sql Server in simple text... For example \"server\" |\r\n",
                "| $database | Name of intended database in simple text... For example \"database\" |\r\n",
                "| $login | Sql Database Login Name... For example \"sampleLogin\" |\r\n",
                "| \"password\" | This could be alphanumeric charecters of choice | \r\n",
                "| $startIP | Intended Start Ip...For example 165.197.220.224|\r\n",
                "| $endIP | Intended End Ip...For example 165.197.220.225 |\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "a199a24e-220c-4a8b-a090-37e38e725ab3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$Env:BOOTSTRAP_Subscription     = \"\"                     # Azure Subscription ID/Name for the ADP Resource Group # Both RG are assumed to be in the same subscription\r\n",
                "$Env:BOOTSTRAP_ResourceGroup    = \"\"                     # Azure Resource Group which contains the ADP Resources\r\n",
                "\r\n",
                "# SQL Server \r\n",
                "$location  =\"\"\r\n",
                "$randomIdentifier =\"\"\r\n",
                "\r\n",
                "$server=\"server-$randomIdentifier\"\r\n",
                "$database=\"database-$randomIdentifier\"\r\n",
                "\r\n",
                "$login=\"\"\r\n",
                "$password=\"\"\r\n",
                "\r\n",
                "$startIP=\"\"\r\n",
                "$endIP=\"\""
            ],
            "metadata": {
                "azdata_cell_guid": "0b2af740-99df-4f44-8cb7-fd00a78f8b9a",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Connect to Azure and Select Subscription\r\n",
                "Run the below cell to login to an Azure account. <b>Be sure to check the Windows Taskbar for a subscription selection dialog box.</b>\r\n",
                "\r\n",
                "_Note: the dialog box window may appear behind the active Azure Data Studio window._"
            ],
            "metadata": {
                "azdata_cell_guid": "acd95a93-f820-48cc-8699-975964225658"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "az login\r\n",
                "az account set --subscription $Env:BOOTSTRAP_Subscription"
            ],
            "metadata": {
                "azdata_cell_guid": "2a11cdb2-9549-4dab-a2fe-f0fd37474b6d"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Create Resource Group\r\n",
                "The Data Portability Notebooks provision several resources to enable import and export of Azure SQL DB databases and Azure SQL Managed Instance databases.  These resources include Storage, Azure Batch, Azure Function resources and their dependent Azure resources.  All resources created by this notebook are associated with a common Azure Resource Group to enable easy management of resources related to Data Portability.\r\n",
                "\r\n",
                "_Note: this notebook will reuse the Resource Group specified if it already exists._"
            ],
            "metadata": {
                "azdata_cell_guid": "a3a0c72c-04cb-4bb2-8dbc-4d96e7482ce9"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "echo \"Creating $resource...\"\r\n",
                "az group create --name $Env:BOOTSTRAP_ResourceGroup --location \"$location\""
            ],
            "metadata": {
                "azdata_cell_guid": "a9272913-cb61-4536-b89a-7826baa7465d"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Create Sql Server\r\n",
                "To create an Azure SQL Database using the Azure CLI 2.0,create an Azure SQL Server first, which can be done by running the following command"
            ],
            "metadata": {
                "azdata_cell_guid": "70e04ce4-c5ff-4cde-9a21-05fac7275dbf"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "echo \"Creating $server in $location...\"\r\n",
                "az sql server create --name $server --resource-group $Env:BOOTSTRAP_ResourceGroup --location \"$location\" --admin-user $login --admin-password $password"
            ],
            "metadata": {
                "azdata_cell_guid": "dbd81221-d61e-4441-ad6d-bb4dcef91f0c"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Configure Firewall Rule\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "22bbc194-c946-493d-ab65-2750246596c3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "echo \"Configuring firewall...\"\r\n",
                "az sql server firewall-rule create --resource-group $Env:BOOTSTRAP_ResourceGroup --server $server -n AllowYourIp --start-ip-address $startIP --end-ip-address $endIP"
            ],
            "metadata": {
                "azdata_cell_guid": "7dbbedfd-32cc-467c-b65a-aaf9ece946b7"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Create Database on server\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b002d01a-6b2d-4b45-bd77-0d139fbe5503"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "echo \"Creating $database on $server...\"\r\n",
                "az sql db create --resource-group $Env:BOOTSTRAP_ResourceGroup --server $server --name $database --sample-name AdventureWorksLT --edition GeneralPurpose --family Gen5 --capacity 2 --zone-redundant false # zone redundancy is only supported on premium and business critical service tiers"
            ],
            "metadata": {
                "azdata_cell_guid": "dd8d1b41-8ee7-4823-9655-a9d871b9fe97"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Appendices\r\n",
                "These sections are not vital for the execution of this notebook but provide valuable background information.\r\n",
                "\r\n",
                "### Appendix: Locations\r\n",
                "See the <a href=\"https://azure.microsoft.com/en-us/global-infrastructure/locations/\">Azure locations</a> page for a complete list of Azure regions along with their general physical location. The following is a list of common North American location settings for this guide:\r\n",
                "\r\n",
                "#### US Regions\r\n",
                "| Setting | Location |\r\n",
                "| ------------ | --------- |\r\n",
                "| Central US | Iowa |\r\n",
                "| East US | Virginia |\r\n",
                "| East US 2 | Virginia |\r\n",
                "| North Central US | Illinois |\r\n",
                "| South Central US | Texas |\r\n",
                "| West US 2 | Washington |\r\n",
                "| West Central US | Wyoming |\r\n",
                "| West US | California | \r\n",
                "| Canada Central | Toronto |\r\n",
                "| Canada East | Quebec City |\r\n",
                "| Brazil South | Sao Paulo |\r\n",
                "| Mexico Central | Queretaro |"
            ],
            "metadata": {
                "azdata_cell_guid": "52dd52ff-cabd-40cc-9b34-5a5ebd7ef0c3"
            }
        }
    ]
}