Skip to content

Instantly share code, notes, and snippets.

@SQLvariant

SQLvariant/Build_SQLInstance_wStringCreds.ps1

Created September 29, 2020 17:06
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save SQLvariant/576ac911f8558b9c4e83bb4e58590e08 to your computer and use it in GitHub Desktop.
Save SQLvariant/576ac911f8558b9c4e83bb4e58590e08 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 a Notebook to execute, and passing in the sa_password to the Notebook as a parameter.
Raw
Build_SQLInstance_wStringCreds.ps1
Invoke-ExecuteNotebook -InputNotebook .\DockerDesktop-with-SQL-PowerShell-90.ipynb -Parameters @{sa_password = 'Test9999'}
Display the source blob
Display the rendered blob
Raw
DockerDesktop-with-SQL-PowerShell-90.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": "code",
"source": [
"\"$sa_password\""
],
"metadata": {
"tags": [
"Parameters"
],
"azdata_cell_guid": "c60b6db7-1822-45e5-b68f-9cb5a06d3186"
},
"outputs": [],
"execution_count": null
},
{
"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",
"0A) Before any of this can work, you must have Docker Destop running. \n",
" You must also have the latest SqlServer module installed from the PowerShell Gallery.\n",
"Install-Module SqlServe"
],
"metadata": {
"azdata_cell_guid": "e8c900fe-c443-4a1e-b86b-b59c17b642e6"
}
},
{
"cell_type": "code",
"source": [
"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\";\n",
"$BakFile = \"$($Home)/Downloads/AdventureWorks2016.bak\";\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/SQLDev90"
],
"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 9:44 AM SQLDev90 \n\n\n"
}
],
"execution_count": 1
},
{
"cell_type": "markdown",
"source": [
"0D) Build your SQL Credential using the value of the $sa\\_password variable, which may have been passed in as a parameter."
],
"metadata": {
"azdata_cell_guid": "ae185f64-4708-4851-bbd8-7e3571c0ae35"
}
},
{
"cell_type": "code",
"source": [
"\"$sa_password\"\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": null
},
{
"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 10090:1433 -v C:\\SQLData\\Docker\\SQLDev90:/sqlserver -e ACCEPT_EULA=Y -e SA_PASSWORD=$sa_password --name testcontainer90 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": "212bced0f344ccaab1de86ae616b0d4bb300a3e70932190713218eafcd5b6e88\n"
}
],
"execution_count": 17
},
{
"cell_type": "markdown",
"source": [
"### Verify that the SQL instance in the container is up & running\n",
"First, set a credential with the sa password you used. If you're running throught this notebook verbatim, that might be `Test1234`."
],
"metadata": {
"azdata_cell_guid": "859c0c6a-a935-42aa-889f-f1fed6815a27"
}
},
{
"cell_type": "code",
"source": [
"Start-Sleep -Seconds 10\r\n",
"$SqlCred"
],
"metadata": {
"azdata_cell_guid": "c424030b-1914-451d-8da0-b68f67b496be"
},
"outputs": [],
"execution_count": null
},
{
"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,10090' -Credential $SqlCred"
],
"metadata": {
"azdata_cell_guid": "9ee1eb47-52cd-44c1-ad0d-41bc259cddad"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": "\nName Status Size Space Recovery Compat. Owner Collation \n Available Model Level \n---- ------ ---- ---------- -------- ------- ----- --------- \nmaster Normal 6.25 MB 608.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 13.94 MB 96.00 KB Simple 150 sa SQL_Latin1_General_CP1\n _CI_AS \ntempdb Normal 72.00 MB 61.40 MB Simple 150 sa SQL_Latin1_General_CP1\n _CI_AS \n\n\n. {\n>> $SqlCred = (Get-Credential sa)\n>> Get-SqlDatabase -ServerInstance 'localhost,10090' -Credential $SqlCred\n>> }\n>> \n"
}
],
"execution_count": 4
},
{
"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\\SQLDev90 -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,10090' -Credential $SqlCred -BackupFile '/sqlserver/AdventureWorks2016.bak' -Database 'AdventureWorks2016' -AutoRelocateFile"
],
"metadata": {
"azdata_cell_guid": "7375976b-76fb-48f7-8906-9a04c9d00650"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": ""
}
],
"execution_count": 15
},
{
"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,10090' -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