Simple Demo/Parameterization/SQLDBLog/AzureAutomationRunbookTutorial.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "powershell",
            "display_name": "PowerShell",
            "language": "powershell"
        },
        "language_info": {
            "name": "powershell",
            "codemirror_mode": "shell",
            "mimetype": "text/x-sh",
            "file_extension": ".ps1"
        },
        "extensions": {
            "azuredatastudio": {
                "version": 1,
                "views": []
            }
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Automating Azure SQL Diagnostic notebook execution"
            ],
            "metadata": {
                "azdata_cell_guid": "9fb4903e-f618-45b5-b728-4c6706591649",
                "extensions": {
                    "azuredatastudio": {
                        "views": []
                    }
                }
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "This notebook outlines how you can use Azure Automation to schedule SQL Notebook execution. \n",
                "\n",
                "## Setup pre-requisites\n",
                "\n",
                "1. In Azure Automation, create a Runbook and choose PowerShell as the Runbook type.\n",
                "2. Create a service principal as per [Create Azure AD users using service principals - Azure SQL Database | Microsoft Docs](https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial#create-the-service-principal-user-in-azure-sql-database). The name of the Azure Automation is the user name that you will have to create in the service database. \n",
                "3. Azure Automation Runbook is using Managed Identity for execution. More info about Managed Identity is [here](https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps&source=docs#example-11--connect-to-azure-sql-database--or-managed-instance--using-an-access-token)\n",
                "4. Create an Azure Key Vault Secrets to access the blob storage where the output notebook will be stored. Setup Access Policy as per [Assign an Azure Key Vault access policy (Portal) | Microsoft Docs](https://docs.microsoft.com/en-us/azure/key-vault/general/assign-access-policy-portal) to link the Azure Automation account to the blob storage.\n",
                "\n",
                "The DBDiagnostic notebook is located [here](https://github.com/MsSQLGirl/jubilant-data-wizards/blob/main/Simple%20Demo/SQL%20Notebooks/DBDiagnostics.ipynb) and the equivalent raw path of this notebook which is fetched from the code below is https://raw.githubusercontent.com/MsSQLGirl/jubilant-data-wizards/main/Simple%20Demo/SQL%20Notebooks/DBDiagnostics.ipynb.\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "e604eb40-d2fe-4dc6-9d10-c2d10a79b042",
                "extensions": {
                    "azuredatastudio": {
                        "views": []
                    }
                }
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "## The logic / approach\r\n",
                "\r\n",
                "Below is the Azure Automation Runbook definition, which takes an input paramter called `$Title`.\r\n",
                "\r\n",
                "The flow is as follows:\r\n",
                "1. parses the parameter `$Title` which contains server name and database name in square braces. Example:\r\n",
                "`Slow Query [SQLInstanceName][DatabaseName]`\r\n",
                "\r\n",
                "2. Fetch the notebook to run from https://raw.githubusercontent.com/MsSQLGirl/jubilant-data-wizards/main/Simple%20Demo/SQL%20Notebooks/DBDiagnostics.ipynb. \r\n",
                "\r\n",
                "3. Get access token from Azure Automation to connect to the SQL Instance. \r\n",
                "\r\n",
                "4. Invoke it against the SQL instance and database as retreved from Step 1, using `Invoke-SqlNotebook` with the access token from Step 3. \r\n",
                "\r\n",
                "5. Get the SAS Key to access the storage account. \r\n",
                "\r\n",
                "6. Save the output notebook to Blob Storage using the SAS Key.\r\n",
                "\r\n",
                "7. Return the path to the output notebook.\r\n",
                "\r\n",
                "Below code cell implements the approach above. \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b3cce568-3111-466f-91f7-ed05f7007105",
                "extensions": {
                    "azuredatastudio": {
                        "views": []
                    }
                }
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "param (\r\n",
                "      [Parameter(Mandatory=$true)][String]$Title      \r\n",
                "   )\r\n",
                "       \r\n",
                "    if($Title -match \"\\[([^\\]]+)\\]\\[([^\\]]+)\\]\")\r\n",
                "    {\r\n",
                "        # Server and database\r\n",
                "        $ServerName = $matches[1]\r\n",
                "        $DatabaseName = $matches[2]\r\n",
                "\r\n",
                "        Import-Module SQLServer\r\n",
                "        Import-Module Az.Accounts -MinimumVersion 2.2.0\r\n",
                "\r\n",
                "        # Logging in \r\n",
                "        $ac = Connect-AzAccount -Identity\r\n",
                "\r\n",
                "        # Set the file name\r\n",
                "        $inputNB = \"DBDiagnostic.ipynb\"\r\n",
                "        $outputNB = \"DBDiagnostic_$(get-date -f yyyy-MM-dd).ipynb\"\r\n",
                "\r\n",
                "        # Downloading the notebook that we want to run and store it in inputNB     \r\n",
                "        $sourceNB = \"https://raw.githubusercontent.com/MsSQLGirl/jubilant-data-wizards/main/Simple%20Demo/SQL%20Notebooks/DBDiagnostics.ipynb\"\r\n",
                "        Invoke-WebRequest $sourceNB -OutFile $inputNB\r\n",
                "\r\n",
                "        # Getting access token for this app to connect to Azure SQL         \r\n",
                "        $sqlAccessToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token\r\n",
                "\r\n",
                "        # Now that we have the token, we use it to connect to an Azure SQL DB and run the notebook\r\n",
                "        # inputNB and save the executed notebook as outputNB        \r\n",
                "        $result = Invoke-SqlNotebook -ServerInstance $ServerName -Database $DatabaseName -AccessToken $sqlAccessToken `\r\n",
                "                        -InputFile $inputNB -OutputFile $outputNB  \r\n",
                "\r\n",
                "        # Get the SAS token for Azure Storage               \r\n",
                "        $secret = Get-AzKeyVaultSecret -VaultName \"myAKV\" -Name \"mySASKey\"\r\n",
                "        $ssPtr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($secret.SecretValue)\r\n",
                "        try {\r\n",
                "            $storageAccountKey = [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($ssPtr)\r\n",
                "        } finally {\r\n",
                "            [System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($ssPtr)\r\n",
                "        }\r\n",
                "\r\n",
                "        # Write to Storage Account\r\n",
                "        $storageAccount = \"mystorageaccount\"        \r\n",
                "        $blob = \"myblob\"\r\n",
                "\r\n",
                "        # Saving to blob and return the Uri\r\n",
                "        $result = New-AzStorageContext -StorageAccountName $storageAccount -StorageAccountKey $storageAccountKey `\r\n",
                "            | Set-AzStorageBlobContent  -Container $blob  -File $outputNB -Blob $outputNB -Force\r\n",
                "\r\n",
                "        $blobUri = $result.ICloudBlob.Uri.AbsoluteUri\r\n",
                "\r\n",
                "        # Return the URI of the output notebook\r\n",
                "        $blobUri\r\n",
                "    }\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "370bd39c-0884-42e7-819c-1d03dedf98eb",
                "extensions": {
                    "azuredatastudio": {
                        "views": []
                    }
                }
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}