bdc-02-adstudio.ipynb

{
    "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": [
                "<img src=\"https://github.com/Microsoft/sqlworkshops/blob/master/graphics/solutions-microsoft-logo-small.png?raw=true\" alt=\"Microsoft\">\r\n",
                "<br>\r\n",
                "\r\n",
                "# **SQL Server 2019 big data cluster Tutorial**\r\n",
                "## **02 - How to view the status of a big data cluster**\r\n",
                "\r\n",
                "In this tutorial, you'll learn how to access the service endpoints and view the status of a SQL Server big data cluster components. You can use both Azure Data Studio and azdata, and this article covers both techniques. \r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "0ce14a6c-0809-4ded-821c-58e681aaf1e6"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Use Azure Data Studio**\r\n",
                "After downloading the latest insiders build of [Azure Data Studio](https://aka.ms/getazuredatastudio), you can view service endpoints and the status of a big data cluster with the SQL Server big data cluster dashboard. Some of the features below are only first available in the insiders build of Azure Data Studio."
            ],
            "metadata": {
                "azdata_cell_guid": "02736b68-d9d7-4d69-84fe-b190fdbff305"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "First, create a connection to your big data cluster in Azure Data Studio. For more information, see [Connect to a SQL Server big data cluster with Azure Data Studio](https://docs.microsoft.com/en-us/sql/big-data-cluster/connect-to-big-data-cluster?view=sqlallproducts-allversions)."
            ],
            "metadata": {
                "azdata_cell_guid": "0a01a2ca-cb56-4e18-b3d6-b9d56f865019"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Right-click on the big data cluster endpoint, and click **Manage**.\r\n",
                "\r\n",
                "![right-click-manage](https://docs.microsoft.com/en-us/sql/big-data-cluster/media/view-cluster-status/right-click-manage.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "051a9f64-9c54-4be2-a267-fcfee914702f"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Select the SQL Server Big Data Cluster tab to access the big data cluster dashboard.\r\n",
                "![bdc-dashboard](https://docs.microsoft.com/en-us/sql/big-data-cluster/media/view-cluster-status/bdc-dashboard.png?view=sqlallproducts-allversions)"
            ],
            "metadata": {
                "azdata_cell_guid": "29f42ec8-04a9-41be-a497-191847426558"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### **Service endpoints**\r\n",
                "It is important to be able to easily access the various services within a big data cluster. The big data cluster dashboard provides a service endpoints table that allows you to see and copy the service endpoints.\r\n",
                "\r\n",
                "![service-endpoints](https://docs.microsoft.com/en-us/sql/big-data-cluster/media/view-cluster-status/service-endpoints.png?view=sqlallproducts-allversions)\r\n",
                "\r\n",
                "These services list the endpoints that can be copied and pasted when you need the endpoint for connecting to those services. For example, you can click the copy icon to the right of the endpoint and then paste it in a text window requesting that endpoint. The Cluster Management Service endpoint is necessary to run the [cluster status notebook](https://docs.microsoft.com/en-us/sql/big-data-cluster/view-cluster-status?view=sqlallproducts-allversions#notebook)."
            ],
            "metadata": {
                "azdata_cell_guid": "df2271ce-b5fe-4ab1-aad6-038dbf2578c6"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### **Dashboards**\r\n",
                "The service endpoints table also exposes several dashboards for monitoring:\r\n",
                "\r\n",
                "- Metrics (Grafana)\r\n",
                "- Logs (Kibana)\r\n",
                "- Spark Job Monitoring\r\n",
                "- Spark Resource Management\r\n",
                "\r\n",
                "You can directly click on these links. You will be required to authenticate when accessing these dashboards. For the metrics and logs dashboards, provide controller admin credentials that you set at deployment time using environment variables **AZDATA_USERNAME** and **AZDATA_PASSWORD**. Spark dashboards will use gateway (Knox) credentials: either AD identity in a cluster integrated with AD or user **root** and **AZDATA_PASSWORD** if using basic authentication in your cluster."
            ],
            "metadata": {
                "azdata_cell_guid": "3a0d11bd-7908-45ca-9108-9c15a5de24c3"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### **Cluster Status notebook**\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "2dce5618-0578-4d2b-8ca5-173a838cbd25"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "1. You can also view cluster status of the big data cluster by launching the Cluster Status notebook. To launch the notebook, click the Cluster Status task.\r\n",
                "![cluster-status-launch](https://docs.microsoft.com/en-us/sql/big-data-cluster/media/view-cluster-status/cluster-status-launch.png?view=sqlallproducts-allversions)"
            ],
            "metadata": {
                "azdata_cell_guid": "d71a7481-9944-4c64-821c-ccbff279937f"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "2. Before you begin, you will need the following items:\r\n",
                "- Big data cluster name\r\n",
                "- Controller username\r\n",
                "- Controller password\r\n",
                "- Controller endpoints\r\n",
                "\r\n",
                "The default big data cluster name is **mssql-cluster** unless you customized it during your deployment. You can find the controller endpoint from the big data cluster dashboard in the Service Endpoints table. The endpoint is listed as **Cluster Management Service**. If you do not know the credentials, ask the admin who deployed your cluster."
            ],
            "metadata": {
                "azdata_cell_guid": "3874c709-2292-41e2-92b1-634f6a387d5a"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "3. Click **Run Cells** on the top toolbar."
            ],
            "metadata": {
                "azdata_cell_guid": "6639f2e3-f9c7-4a80-a802-aad8faaf5fd9"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "4. Follow the prompt for your credentials. Press press ENTER after you type each credential for the big data cluster name, controller username, and controller password."
            ],
            "metadata": {
                "azdata_cell_guid": "cb23961e-2125-49b0-939e-9de0f9766f36"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "5. If you connected successfully, the rest of the notebook will show the output of each component of the big data cluster. When you want to rerun a certain code cell, hover over the code cell and click the **Run** icon."
            ],
            "metadata": {
                "azdata_cell_guid": "9fc5a3cf-bc8f-4720-978f-fd0d47023dfb"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Use azdata**\r\n",
                "You can also use [azdata](https://docs.microsoft.com/en-us/sql/big-data-cluster/deploy-install-azdata?view=sqlallproducts-allversions) commands to view both endpoints and the cluster status."
            ],
            "metadata": {
                "azdata_cell_guid": "4014a81e-f0d0-4b90-8c0f-dede69669dd5"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### **Service endpoints**"
            ],
            "metadata": {
                "azdata_cell_guid": "6872019a-5a78-4894-9651-9f1a52a9da35"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Log in to the big data cluster with [azdata login](https://docs.microsoft.com/en-us/sql/big-data-cluster/reference-azdata?view=sqlallproducts-allversions). Set the **--controller-endpoint** parameter to the external IP address of the controller endpoint."
            ],
            "metadata": {
                "azdata_cell_guid": "8726fefe-a5f1-4b20-b748-dc399f271283"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "azdata login --endpoint https://<ip-address-of-controller-svc-external>:30080 --username <user-name>"
            ],
            "metadata": {
                "azdata_cell_guid": "97733a91-2261-403a-95d5-48da3c3d0768"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Specify the username and password that you configured for the controller (AZDATA_USERNAME and AZDATA_PASSWORD) during deployment. For AD authentication, the command is:"
            ],
            "metadata": {
                "azdata_cell_guid": "9e6101d3-a175-4a19-98a5-73a14a60c204"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "azdata login --endpoint https://<control_domain_name>:30080 --auth ad"
            ],
            "metadata": {
                "azdata_cell_guid": "6455d4c0-d964-4636-b4c7-b55ed84dfe04"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Run [azdata bdc endpoint list](https://docs.microsoft.com/en-us/sql/big-data-cluster/reference-azdata-bdc-endpoint?view=sqlallproducts-allversions) to get a list with a description of each endpoint and their corresponding IP address and port values."
            ],
            "metadata": {
                "azdata_cell_guid": "3d765e94-191d-42a7-a899-e834c928ce36"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "azdata bdc endpoint list -o table"
            ],
            "metadata": {
                "azdata_cell_guid": "d32e2cc3-fc1f-4e1f-b1b6-6fba494cbfda"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### **View cluster status**\r\n",
                "You can view the status of the cluster with the [azdata bdc status](https://docs.microsoft.com/en-us/sql/big-data-cluster/reference-azdata-bdc-status?view=sqlallproducts-allversions) show command."
            ],
            "metadata": {
                "azdata_cell_guid": "5fb52caa-8792-4ce6-849e-e081b1c2d0d2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "azdata bdc status show"
            ],
            "metadata": {
                "azdata_cell_guid": "60b29ad5-72cf-4c6c-8901-5f0ee64603c4"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### **View specific resource status**\r\n",
                "You can view the status of a specific resource within the cluster with the azdata bdc status show command. When you use this command you can filter using --resource parameter. Few examples of inputs for --resource parameter are:\r\n",
                "\r\n",
                "- master\r\n",
                "- control\r\n",
                "- compute-0\r\n",
                "- storage-0\r\n",
                "- gateway"
            ],
            "metadata": {
                "azdata_cell_guid": "87bb3b9d-0823-4db7-aa60-9cb670351ea5"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "For example, the following command displays the status of the storage pool:"
            ],
            "metadata": {
                "azdata_cell_guid": "fecdc2ff-3239-436c-a409-b258046347a7"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "azdata bdc status show --all --resource storage-0"
            ],
            "metadata": {
                "azdata_cell_guid": "2bf8a604-18ba-4244-b9d6-8283caa73071"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "To see the status of all components that are running a specific service you must use the corresponding command group azdata bdc \\<serviceName> status show. For example:\r\n",
                "\r\n",
                "- azdata bdc sql status show --all\r\n",
                "- azdata bdc hdfs status show --all\r\n",
                "- azdata bdc spark status show --all"
            ],
            "metadata": {
                "azdata_cell_guid": "f69a0f1a-824c-400e-aab7-7059923b82a2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "azdata bdc spark status show --all"
            ],
            "metadata": {
                "azdata_cell_guid": "046e37cc-e56b-4b96-87fa-4156a22fca73"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "azdata bdc hdfs status show --all"
            ],
            "metadata": {
                "azdata_cell_guid": "5e92a3d1-2f42-41f1-ac03-7b256476cf8b"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Here is an sample output:\r\n",
                "```\r\n",
                "Spark: ready                                                                                                                                                                                                        Health Status:  healthy\r\n",
                "===========================================================================================================================================================================================================================================\r\n",
                "Resources: ready                                                                                                                                                                                                    Health Status:  healthy\r\n",
                "-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n",
                "Resourcename    State    Healthstatus    Details\r\n",
                "\r\n",
                "sparkhead       ready    healthy         StatefulSet sparkhead is healthy\r\n",
                "storage-0       ready    healthy         StatefulSet storage-0 is healthy\r\n",
                "\r\n",
                "\r\n",
                "Sparkhead Resources: running                                                                                                                                                                                        Health Status:  healthy\r\n",
                "-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n",
                "Instancename    State    Healthstatus    Details\r\n",
                "\r\n",
                "sparkhead-0     running  healthy         Pod sparkhead-0 is healthy\r\n",
                "sparkhead-1     running  healthy         Pod sparkhead-1 is healthy\r\n",
                "\r\n",
                "\r\n",
                "Dashboards\r\n",
                "--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n",
                "Name            Url\r\n",
                "\r\n",
                "nodeMetricsUrl  https://13.91.50.9:30777/api/v1/bdc/instances/sparkhead-1/status/nodemetrics/ui\r\n",
                "sqlMetricsUrl   https://13.91.50.9:30777/api/v1/bdc/instances/sparkhead-1/status/sqlmetrics/ui\r\n",
                "logsUrl         https://13.91.50.9:30777/api/v1/bdc/instances/sparkhead-1/status/logs/ui\r\n",
                "\r\n",
                "\r\n",
                "Storage-0 Resources: running                                                                                                                                                                                        Health Status:  healthy\r\n",
                "-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n",
                "Instancename    State    Healthstatus    Details\r\n",
                "\r\n",
                "storage-0-0     running  healthy         Pod storage-0-0 is healthy\r\n",
                "storage-0-1     running  healthy         Pod storage-0-1 is healthy\r\n",
                "\r\n",
                "\r\n",
                "Dashboards\r\n",
                "--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n",
                "Name            Url\r\n",
                "\r\n",
                "nodeMetricsUrl  https://13.91.50.9:30777/api/v1/bdc/instances/storage-0-1/status/nodemetrics/ui\r\n",
                "sqlMetricsUrl   https://13.91.50.9:30777/api/v1/bdc/instances/storage-0-1/status/sqlmetrics/ui\r\n",
                "logsUrl         https://13.91.50.9:30777/api/v1/bdc/instances/storage-0-1/status/logs/ui\r\n",
                "```"
            ],
            "metadata": {
                "azdata_cell_guid": "fac3e5a3-fccc-479e-8aee-d8355b9b1348"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "The ```logsUrl``` value links to a Kibana dashboard:  \r\n",
                "![kibana-dashboard](https://docs.microsoft.com/en-us/sql/big-data-cluster/media/view-cluster-status/kibana-dashboard.png?view=sqlallproducts-allversions)\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "4cb65a87-d796-452b-a515-ceb9af51c243"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "The ```nodeMetricsUrl``` and ```sqlMetricsUrl``` values link to a Grafana dashboard for monitoring Kubernetes node metrics and big data cluster service metrics:\r\n",
                "![grafana-dashboard](https://docs.microsoft.com/en-us/sql/big-data-cluster/media/view-cluster-status/grafana-dashboard.png?view=sqlallproducts-allversions)\r\n",
                "![grafana-cluster-status](https://docs.microsoft.com/en-us/sql/big-data-cluster/media/view-cluster-status/grafana-sql-status.png?view=sqlallproducts-allversions)"
            ],
            "metadata": {
                "azdata_cell_guid": "51ed1d8a-e728-481d-9abf-2a338e1012c0"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### **View controller status**\r\n",
                "You can view the controller status with the [azdata bdc control](https://docs.microsoft.com/en-us/sql/big-data-cluster/reference-azdata-bdc-control-status?view=sqlallproducts-allversions) status show command. It provides similar links to the monitoring dashboards related to the controller components of the big data cluster."
            ],
            "metadata": {
                "azdata_cell_guid": "95b1abcf-ecea-4d44-92e6-a812fdf5a889"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Next Step: Working with the SQL Server 2019 big data cluster Master Instance**\n",
                "Now you're ready to open the next Jupyter Notebook(optional) - [bdc-03-sqlserver-master.ipynb](bdc-03-sqlserver-master.ipynb) - to learn how to view the status of a big data cluster using azdata and azure data studio."
            ],
            "metadata": {
                "azdata_cell_guid": "f99426b9-0f5f-4160-bff5-0c08ca578191"
            }
        }
    ]
}