Examples of lagging results in PowerShell Notebooks with v0.1.2 of the powershell_kernel

DeadSimple_PSResults_Lag.ipynb

DockerDesktop-with-SQL-PowerShell-71.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/GodStorm91/mobydock/blob/master/mobydock/static/docker-logo.png?raw=true\" width=\"10%\"> ➕\r\n",
                "<img src=\"https://github.com/Microsoft/azuredatastudio/blob/master/samples/notebookSamples/Graphics/AzureDataStudioLogo.png?raw=true\" width=\"6.5%\"> = ❤\r\n",
                "\r\n",
                "# This PowerShell Notebook will walk you through how to setup a SQL-on-Linux instance in a Docker container, on your local [Windows] machine."
            ],
            "metadata": {
                "azdata_cell_guid": "0f57848d-3a45-415f-9874-161eac860afc"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Note: You do need Docker Desktop installed for this to work.  If you don't have it installed, you can use this Chocolatey command to install it:\r\n",
                "\r\n",
                "Note: You need to run that command above from an **elevated** PowerShell window (Run as administrator).\r\n",
                "```powershell\r\n",
                "choco install docker-desktop -y\r\n",
                "```"
            ],
            "metadata": {
                "azdata_cell_guid": "41b4a48c-f224-42c1-a421-1e5ad5efd02b"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Preparation Steps\r\n",
                "0A) Before any of this can work, you must have Docker Destop running. \r\n",
                "    You must also have the latest SqlServer module installed from the PowerShell Gallery.\r\n",
                "Install-Module SqlServe"
            ],
            "metadata": {
                "azdata_cell_guid": "e8c900fe-c443-4a1e-b86b-b59c17b642e6"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Install-Module SqlServer;\r\n",
                "Import-Module SqlServer;"
            ],
            "metadata": {
                "azdata_cell_guid": "12ca19b3-4b37-4409-a08a-8fc80c5bfd72"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": ""
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "0B) Use the code to download the AdventureWorks2016.bak file from GitHub"
            ],
            "metadata": {
                "azdata_cell_guid": "15a14e8d-c535-4e0a-9ac4-2d48fdfb8684"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$BakURL = \"https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak\";\r\n",
                "$BakFile = \"$($Home)/Downloads/AdventureWorks2016.bak\";\r\n",
                "Invoke-WebRequest -Uri $BakURL -OutFile $BakFile;"
            ],
            "metadata": {
                "azdata_cell_guid": "9970509e-857b-479e-9136-0859a5d2e74f"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "$BakURL = \"https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak\";\n$BakFile = \"$($Home)/Downloads/AdventureWorks2016.bak\";\nInvoke-WebRequest -Uri $BakURL -OutFile $BakFile;\n"
                }
            ],
            "execution_count": 35
        },
        {
            "cell_type": "markdown",
            "source": [
                "0C) Create a local directory for the container to use to store database files, outside of the container."
            ],
            "metadata": {
                "azdata_cell_guid": "fcbd097d-4e89-48dc-9463-e8e9d77d3461"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "mkdir C:/SQLData/Docker/SQLDev71"
            ],
            "metadata": {
                "azdata_cell_guid": "396f65ef-b04c-4f3c-8fe4-1a5c64ea829c"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\n\n    Directory: C:\\SQLData\\Docker\n\n\nMode                LastWriteTime         Length Name                                                                  \n----                -------------         ------ ----                                                                  \nd-----       11/20/2019   8:31 AM                SQLDev71                                                              \n\n\n"
                }
            ],
            "execution_count": 5
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Create your SQL-on-Linux Docker Container\r\n",
                "1) Create a SQL-on-Linux Docker Container with just the code below.\r\n",
                "    NOTE: You may want to change the password used for the SA account.\r\n",
                "    You may also want to change the Volumne path: \"C:\\SQLData\\Docker\\SQLDev71\""
            ],
            "metadata": {
                "azdata_cell_guid": "b5994870-dd5d-4e85-b266-607cbfb3f572"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "docker run -d -p 10071:1433 -v C:\\SQLData\\Docker\\SQLDev71:/sqlserver -e ACCEPT_EULA=Y -e SA_PASSWORD=Test1234 --name testcontainer71 mcr.microsoft.com/mssql/server:2019-latest"
            ],
            "metadata": {
                "azdata_cell_guid": "7637f355-04b2-4214-95e4-f0c089d6e890"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "Unable to find image 'mcr.microsoft.com/mssql/server:2019-latest' locally\n2019-latest: Pulling from mssql/server\n59ab41dd721a: Already exists\n57da90bec92c: Already exists\n06fe57530625: Already exists\n5a6315cba1ff: Already exists\n739f58768b3f: Already exists\nb77fc38faae9: Already exists\n11ca25075c70: Already exists\nc957a137b2c5: Already exists\n4794f7ad490c: Already exists\nDigest: sha256:0753f9e9c614c469e1e6b98ea60b21e20abd249323c9f6a9eab476c87a8cf826\nStatus: Downloaded newer image for mcr.microsoft.com/mssql/server:2019-latest\nc852c762ae3c9ceb98a7a55220191fe8c47c9d86f08b2bf9f9c0c5be398ddcc2\n"
                }
            ],
            "execution_count": 7
        },
        {
            "cell_type": "markdown",
            "source": [
                "Verify that the SQL instance in the container is up & running"
            ],
            "metadata": {
                "azdata_cell_guid": "859c0c6a-a935-42aa-889f-f1fed6815a27"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$SqlCred = (Get-Credential sa)\r\n",
                "Get-SqlDatabase -ServerInstance 'localhost,10071' -Credential $SqlCred"
            ],
            "metadata": {
                "azdata_cell_guid": "9ee1eb47-52cd-44c1-ad0d-41bc259cddad"
            },
            "outputs": [
                {
                    "name": "stdout",
                    "text": "\nName                 Status           Size     Space  Recovery Compat. Owner                     Collation             \n                                            Available  Model     Level                                                 \n----                 ------           ---- ---------- -------- ------- -----                     ---------             \nmaster               Normal        6.44 MB  976.00 KB Simple       150 sa                        SQL_Latin1_General_CP1\n                                                                                                 _CI_AS                \nmodel                Normal       16.00 MB    5.70 MB Full         150 sa                        SQL_Latin1_General_CP1\n                                                                                                 _CI_AS                \nmsdb                 Normal       15.50 MB    1.41 MB Simple       150 sa                        SQL_Latin1_General_CP1\n                                                                                                 _CI_AS                \ntempdb               Normal       24.00 MB   13.34 MB Simple       150 sa                        SQL_Latin1_General_CP1\n                                                                                                 _CI_AS                \n\n\n",
                    "output_type": "stream"
                }
            ],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": [
                "2) Copy the backup file to the directory your container volume is mapped to.\r\n",
                "    Make sure you use the location you stored the .bak file in."
            ],
            "metadata": {
                "azdata_cell_guid": "d6943801-6718-46c1-af96-e0001a246c13"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Copy-Item -Path \"$($Home)\\Downloads\\AdventureWorks2016.bak\" -Destination C:\\SQLData\\Docker\\SQLDev71 -Verbose"
            ],
            "metadata": {
                "azdata_cell_guid": "570be45b-08a6-4034-83f8-034c72b31cf6"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "VERBOSE: Performing the operation \"Copy File\" on target \"Item: C:\\Users\\aanelson\\Downloads\\AdventureWorks2016.bak \nDestination: C:\\SQLData\\Docker\\SQLDev71\\AdventureWorks2016.bak\".\n"
                }
            ],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "3) Restore the AdventureWorks2016 database"
            ],
            "metadata": {
                "azdata_cell_guid": "48afa7c6-f730-43fb-b298-4ff788d8ae9d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Restore-SqlDatabase -ServerInstance 'localhost,10071' -Credential $SqlCred -BackupFile '/sqlserver/AdventureWorks2016.bak' -Database 'AdventureWorks2016' -AutoRelocateFile -Script"
            ],
            "metadata": {
                "azdata_cell_guid": "7375976b-76fb-48f7-8906-9a04c9d00650"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "Restore-SqlDatabase : An exception occurred while executing a Transact-SQL statement or batch.\nAt line:1 char:5\n+ . { Restore-SqlDatabase -ServerInstance 'localhost,10071' -Credential ...\n+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n    + CategoryInfo          : NotSpecified: (:) [Restore-SqlDatabase], ExecutionFailureException\n    + FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ExecutionFailureException,Microsoft.SqlServer.Mana \n   gement.PowerShell.RestoreSqlDatabaseCommand\n \n"
                }
            ],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": [
                "Now verify the AdventureWorks 2016 database was successfully restored."
            ],
            "metadata": {
                "azdata_cell_guid": "f25043a9-b757-4b0b-a232-021883093af2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-SqlDatabase -ServerInstance 'localhost,10071' -Credential $SqlCred"
            ],
            "metadata": {
                "azdata_cell_guid": "1eb402dd-c9c8-4b0a-ab2c-0a210e73e016"
            },
            "outputs": [
                {
                    "name": "stdout",
                    "text": "\nName                 Status           Size     Space  Recovery Compat. Owner                     Collation             \n                                            Available  Model     Level                                                 \n----                 ------           ---- ---------- -------- ------- -----                     ---------             \nAdventureWorks2016   Normal      209.63 MB    1.19 MB Simple       130 sa                        SQL_Latin1_General_CP1\n                                                                                                 _CI_AS                \nmaster               Normal        6.44 MB  976.00 KB Simple       150 sa                        SQL_Latin1_General_CP1\n                                                                                                 _CI_AS                \nmodel                Normal       16.00 MB    5.70 MB Full         150 sa                        SQL_Latin1_General_CP1\n                                                                                                 _CI_AS                \nmsdb                 Normal       15.50 MB    1.14 MB Simple       150 sa                        SQL_Latin1_General_CP1\n                                                                                                 _CI_AS                \ntempdb               Normal       24.00 MB   13.42 MB Simple       150 sa                        SQL_Latin1_General_CP1\n                                                                                                 _CI_AS                \n\n\n",
                    "output_type": "stream"
                }
            ],
            "execution_count": 6
        }
    ]
}