Skip to content

Instantly share code, notes, and snippets.

@SQLvariant

SQLvariant/Build_SQLInstance_wStringCreds_andPortNumber.ps1

Last active January 28, 2023 22:28
Show Gist options
  • Save SQLvariant/472021981475d1cd917ac07205a2bcc3 to your computer and use it in GitHub Desktop.
Save SQLvariant/472021981475d1cd917ac07205a2bcc3 to your computer and use it in GitHub Desktop.
Download ZIP
Use Invoke-ExecuteNotebook to build a SQL-on-Linux instance in a Docker container, by calling Invoke-ExecuteNotebook to execute the Notebook, and passing in the sa_password & digits for the name/port number to the Notebook as a parameter.
Raw
_DoThisFirst.ps1
<# Download the PowerShellNotebook Module from the PowerShell Gallery #>
try {Import-Module PowerShellNotebook -ErrorAction Stop} catch {Install-Module -Name PowerShellNotebook -Scope CurrentUser} finally {Import-Module PowerShellNotebook}
Import-Module PowerShellNotebook;
<# Download the Jupyter Notebook in this Gist #>
Invoke-RestMethod https://gist.githubusercontent.com/SQLvariant/472021981475d1cd917ac07205a2bcc3/raw/24f7657d2d2ae7f904fe46a8604f8db1e844ebc0/SQL-on-Docker-with-PowerShell.ipynb -OutFile SQL-on-Docker-with-PowerShell.ipynb
<# OK, you should now be ready to move onto the steps in Build_SQLInstance_wStringCreds.ps1
Please note: you're going to need to have Docker Desktop installed & running for all this to work #>
Raw
Build_SQLInstance_wStringCreds_andPortNumber.ps1
Invoke-ExecuteNotebook -InputNotebook .\SQL-on-Docker-with-PowerShell.ipynb -Parameters @{sa_password = 'Test9999'; SQLNumber = 97}
Display the source blob
Display the rendered blob
Raw
SQL-on-Docker-with-PowerShell.ipynb
{
"metadata": {
"kernelspec": {
"name": ".net-powershell",
"display_name": ".NET (PowerShell)"
},
"language_info": {
"name": "PowerShell",
"version": "7.0",
"mimetype": "text/x-powershell",
"file_extension": ".ps1",
"pygments_lexer": "powershell"
},
"celltoolbar": "Tags"
},
"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%\"> ➕\n",
"<img src=\"https://github.com/Microsoft/azuredatastudio/blob/master/samples/notebookSamples/Graphics/AzureDataStudioLogo.png?raw=true\" width=\"6.5%\"> = ❤\n",
"\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:\n",
"\n",
"Note: You need to run that command above from an **elevated** PowerShell window (Run as administrator).\n",
"```powershell\n",
"choco install docker-desktop -y\n",
"```"
],
"metadata": {
"azdata_cell_guid": "41b4a48c-f224-42c1-a421-1e5ad5efd02b"
}
},
{
"cell_type": "markdown",
"source": [
"## Preparation Steps\n",
"\n",
"0A) Before any of this can work, you must have Docker Desktop running. You must also have the latest SqlServer module installed from the PowerShell Gallery. `Install-Module SqlServer`"
],
"metadata": {
"azdata_cell_guid": "e8c900fe-c443-4a1e-b86b-b59c17b642e6"
}
},
{
"cell_type": "code",
"source": [
"try {Import-Module SqlServer -ErrorAction Stop} catch {Install-Module -Name SqlServer -Scope CurrentUser} finally {Import-Module SqlServer}\r\n",
"Import-Module SqlServer;"
],
"metadata": {
"azdata_cell_guid": "12ca19b3-4b37-4409-a08a-8fc80c5bfd72"
},
"outputs": [],
"execution_count": 10
},
{
"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\";\n",
"$BakFile = \"$($Home)/Downloads/AdventureWorks2016.bak\";\n",
"Invoke-WebRequest -Uri $BakURL -OutFile $BakFile;"
],
"metadata": {
"azdata_cell_guid": "9970509e-857b-479e-9136-0859a5d2e74f"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"0C) Pick a two-digit number, to be used as the number for this instance, as well as part of the port number. "
],
"metadata": {
"azdata_cell_guid": "fcbd097d-4e89-48dc-9463-e8e9d77d3461"
}
},
{
"cell_type": "code",
"source": [
"if([string]::IsNullOrEmpty($SQLNumber)) {\r\n",
" $SQLNumber = Read-Host -Prompt \"Please enter two-digit sql number\"\r\n",
"}"
],
"metadata": {
"tags": [
"Parameters"
],
"azdata_cell_guid": "c60b6db7-1822-45e5-b68f-9cb5a06d3186"
},
"outputs": [],
"execution_count": 1
},
{
"cell_type": "markdown",
"source": [
"Create a local directory for the container to use to store database files, outside of the container. This step will use the number you supplied above"
],
"metadata": {
"azdata_cell_guid": "e6ea62dc-3f7f-4986-aae2-bc861a6860b9"
}
},
{
"cell_type": "code",
"source": [
"mkdir \"C:/SQLData/Docker/SQLDev$SQLNumber\""
],
"metadata": {
"azdata_cell_guid": "396f65ef-b04c-4f3c-8fe4-1a5c64ea829c"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": "\r\n"
},
{
"output_type": "stream",
"name": "stdout",
"text": "\r\n"
},
{
"output_type": "stream",
"name": "stdout",
"text": " Directory: C:\\SQLData\\Docker\r\n"
},
{
"output_type": "stream",
"name": "stdout",
"text": "\r\n"
},
{
"output_type": "stream",
"name": "stdout",
"text": "Mode LastWriteTime Length Name\r\n"
},
{
"output_type": "stream",
"name": "stdout",
"text": "---- ------------- ------ ----\r\n"
},
{
"output_type": "stream",
"name": "stdout",
"text": "d---- 9/29/2020 3:53 PM SQLDev19\r\n"
},
{
"output_type": "stream",
"name": "stdout",
"text": "\r\n"
}
],
"execution_count": 3
},
{
"cell_type": "markdown",
"source": [
"0D) Set the `sa` password. Then build your SQL Credential using the value of the `$sa_password` variable. If you are running this using `Invoke-ExecuteNotebook` you may pass the password in as a parameter.\n",
"\n",
"If you're running through this notebook interactively, make sure to remember the password you choose."
],
"metadata": {
"azdata_cell_guid": "ae185f64-4708-4851-bbd8-7e3571c0ae35"
}
},
{
"cell_type": "code",
"source": [
"if([string]::IsNullOrEmpty($sa_password)) {\r\n",
" $sa_password = Read-Host -Prompt \"Please enter sa password\"\r\n",
"}\r\n",
"\r\n",
"$password = ConvertTo-SecureString $sa_password -AsPlainText -Force\r\n",
"$SqlCred = New-Object System.Management.Automation.PSCredential ('sa', $password)"
],
"metadata": {
"azdata_cell_guid": "40dff255-8fa6-45a7-b049-2852fca01210"
},
"outputs": [],
"execution_count": 1
},
{
"cell_type": "markdown",
"source": [
"## Create your SQL-on-Linux Docker Container\n",
"1) Create a SQL-on-Linux Docker Container with just the code below.\n",
" NOTE: You may want to change the password used for the SA account.\n",
" You may also want to change the Volumne path: \"C:\\SQLData\\Docker\\SQLDev90\""
],
"metadata": {
"azdata_cell_guid": "b5994870-dd5d-4e85-b266-607cbfb3f572"
}
},
{
"cell_type": "code",
"source": [
"docker run -d -p \"100$($SQLNumber):1433\" -v \"C:\\SQLData\\Docker\\SQLDev$($SQLNumber):/sqlserver\" -e ACCEPT_EULA=Y -e SA_PASSWORD=$sa_password --name \"testcontainer$SQLNumber\" mcr.microsoft.com/mssql/server:2019-latest"
],
"metadata": {
"azdata_cell_guid": "7637f355-04b2-4214-95e4-f0c089d6e890",
"scrolled": true,
"tags": [
"Password"
]
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": "6d2bf629999c7909b9971d185ea3ff77ae77dad5cfb14e9d6acef96bfb533b6e\r\n"
}
],
"execution_count": 7
},
{
"cell_type": "code",
"source": [
"Start-Sleep -Seconds 15"
],
"metadata": {
"azdata_cell_guid": "c424030b-1914-451d-8da0-b68f67b496be"
},
"outputs": [],
"execution_count": 4
},
{
"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": [
"Get-SqlInstance -ServerInstance \"localhost,100$($SQLNumber)\" -Credential $SqlCred"
],
"metadata": {
"azdata_cell_guid": "d66f64f3-9681-4430-b900-af8c80634b3f"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": "\r\n"
},
{
"output_type": "stream",
"name": "stdout",
"text": "Instance Name Version ProductLevel UpdateLevel HostPlatform HostDistribution\r\n"
},
{
"output_type": "stream",
"name": "stdout",
"text": "------------- ------- ------------ ----------- ------------ ----------------\r\n"
},
{
"output_type": "stream",
"name": "stdout",
"text": "localhost,10019 15.0.4063 RTM CU7 Linux Ubuntu\r\n"
},
{
"output_type": "stream",
"name": "stdout",
"text": "\r\n"
}
],
"execution_count": 4
},
{
"cell_type": "markdown",
"source": [
"Now run the `Get-SqlDatabase` cmdlet against the container you created, and pass in the credential object you created in the previous step."
],
"metadata": {
"azdata_cell_guid": "a559679c-88fd-4a42-9dd1-48950560dcd9"
}
},
{
"cell_type": "code",
"source": [
"Get-SqlDatabase -ServerInstance \"localhost,100$($SQLNumber)\" -Credential $SqlCred"
],
"metadata": {
"azdata_cell_guid": "9ee1eb47-52cd-44c1-ad0d-41bc259cddad"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"2) Copy the backup file to the directory your container volume is mapped to.\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/SQLDev$SQLNumber\" -Verbose"
],
"metadata": {
"azdata_cell_guid": "570be45b-08a6-4034-83f8-034c72b31cf6"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": ""
}
],
"execution_count": 1
},
{
"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,100$($SQLNumber)\" -Credential $SqlCred -BackupFile '/sqlserver/AdventureWorks2016.bak' -Database 'AdventureWorks2016' -AutoRelocateFile"
],
"metadata": {
"azdata_cell_guid": "7375976b-76fb-48f7-8906-9a04c9d00650"
},
"outputs": [],
"execution_count": null
},
{
"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,100$($SQLNumber)\" -Credential $SqlCred"
],
"metadata": {
"azdata_cell_guid": "1eb402dd-c9c8-4b0a-ab2c-0a210e73e016"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": "\nName Status Size Space Recovery Compat. Owner Collation \n Available Model Level \n---- ------ ---- ---------- -------- ------- ----- --------- \nAdventureWorks2016 Normal 209.63 MB 1.31 MB Simple 130 sa SQL_Latin1_General_CP1\n _CI_AS \nmaster Normal 6.25 MB 584.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.56 MB 1.20 MB Simple 150 sa SQL_Latin1_General_CP1\n _CI_AS \ntempdb Normal 72.00 MB 61.33 MB Simple 150 sa SQL_Latin1_General_CP1\n _CI_AS \n\n\n"
}
],
"execution_count": 16
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment