# Install Chocolatey
[Net.ServicePointManager]::SecurityProtocol = [Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls12
Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))

# Install Prerequisites
choco install kubernetes-cli sqlserver-cmdlineutils azure-cli -y
$env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine") + ";" + [System.Environment]::GetEnvironmentVariable("Path","User") 

# Set some variables
$Region="eastus"
$Password="S@LServerGeeks2021!"
$RG_Name="MySQLonAKS"
$Cluster_Name="MyFirstAKS"
$Subscription=""

# Login to Azure Account and set Subscription
az login 
az account set -s $Subscription

# Create Resource Group
az group create --name $RG_Name --location $Region

# Create AKS Cluster with default settings
az aks create -n $Cluster_Name -g $RG_Name --generate-ssh-keys

# Retrieve credentials and verify connectivity 
az aks get-credentials -n $Cluster_Name -g $RG_Name --overwrite-existing
kubectl get nodes

# Create secret on Kubernetes Cluster for SA Password
kubectl create secret generic mssql --from-literal=SA_PASSWORD=$Password

# Deploy SQL Server 2019
kubectl apply -f https://bookmark.ws/SQLonAKSManifest

# Check result
kubectl get pvc,pods,service

# Retrieve the (dynamically created) endpoint
$ServerIP=(kubectl get svc mssql-deployment --output jsonpath='{.status.loadBalancer.ingress[0].ip}')

# Check Version of SQL Server
sqlcmd -S $ServerIP,31433 -U sa -P $Password -Q "SELECT @@VERSION"

# Download Adventureworks2017
curl -o AdventureWorks2017.bak 'https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak'

# Copy Backup to the SQL Pod
$Target=(kubectl get pod -l app=mssql -o jsonpath="{.items[0].metadata.name}")+ ":var/opt/mssql/data/AdventureWorks2017.bak"
kubectl cp AdventureWorks2017.bak $Target -c mssql

# Restore AdventureWorks2017
sqlcmd -S $ServerIP,31433 -U sa -P $Password `
    -Q "RESTORE DATABASE AdventureWorks2017 FROM DISK = '/var/opt/mssql/data/AdventureWorks2017.bak' WITH `
        MOVE 'AdventureWorks2017' to '/var/opt/mssql/data/AdventureWorks2017.mdf', `
        MOVE 'AdventureWorks2017_Log' to '/var/opt/mssql/data/AdventureWorks2017_log.ldf', NOUNLOAD, STATS = 5"

# Check that the AdventureWorks2017 database has been created
sqlcmd -S $ServerIP,31433 -U sa -P $Password -Q "SELECT NAME FROM SYS.DATABASES"

# Delete everything
az group delete --name $RG_Name