SQL Server on Mac via virtualization

gistfile1.txt

SQL Server on Mac via virtualization
Goal: I want to be able to spin up generic instances of sql server on-demand

Assumptions:
* [Windows Server 2016 Technical Preview (Core) is installed on virtualbox](http://ezeeetm.github.io/Docker-Engine-for-Windows-Server-Walkthrough/)
  * Typical defaults: 4096MB RAM, 32 GB fixed space
  * The image name is "sql server"
  * The network is "bridged adapter"
  * Guest additions are installed via `D:\VBoxWindowsAdditions.exe`


#### 1. Install Container Feature

The container feature needs to be enabled before working with Windows containers. To do so run the following command in an elevated PowerShell session.

```
Install-WindowsFeature containers
```
When the feature installation has completed, reboot the computer.

```
Restart-Computer -Force
```
#### 2. Install Docker

Docker is required in order to work with Windows containers. Docker consists of the Docker Engine, and the Docker client. For this exercise, both will be installed.

Create a folder for the Docker executables.

```
New-Item -Type Directory -Path 'C:\Program Files\docker\'
```
Download the Docker daemon.

```
Invoke-WebRequest https://aka.ms/tp5/b/dockerd -OutFile $env:ProgramFiles\docker\dockerd.exe -UseBasicParsing
```
Download the Docker client.

```
Invoke-WebRequest https://aka.ms/tp5/b/docker -OutFile $env:ProgramFiles\docker\docker.exe -UseBasicParsing
```
Add the Docker directory to the system path. When complete, restart the PowerShell session so that the modified path is recognized.

```
[Environment]::SetEnvironmentVariable("Path", $env:Path + ";C:\Program Files\docker", [EnvironmentVariableTarget]::Machine)
Restart-Computer -Force
```
To install Docker as a Windows service, run the following.

```
dockerd --register-service
```
Once installed, the service can be started.

```
Start-Service docker
```

#### 3. Install Base Container Images

Windows containers are deployed from templates or images. Before a container can be deployed, a base OS image needs to be downloaded. The following commands will download the Windows Server Core base image.

First, install the container image package provider.

```
Install-PackageProvider ContainerImage -Force
```
Next, install the Windows Server Core image. This process can take some time, so take a break and pick back up once the download has completed.

```
Install-ContainerImage -Name WindowsServerCore
```
After the base image has been installed, the Docker service needs to be restarted.

```
Restart-Service docker
```
At this stage, running docker images will return a list of installed images, in this case the Windows Server Core image.

docker images

REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
windowsservercore   10.0.14300.1000     dbfee88ee9fd        7 weeks ago         9.344 GB
Before proceeding, this image needs to be tagged with a version of ‘latest’. To do so, run the following command.

```
docker tag windowsservercore:10.0.14300.1000 windowsservercore:latest
```

#### 4. Install git
```
New-Item -Type Directory -Path 'C:\Program Files\git\'
Invoke-WebRequest https://github.com/git-for-windows/git/releases/download/<some-version>/<some-binary>.exe -OutFile 'C:\Program Files\git\git.exe' -UseBasicParsing
[Environment]::SetEnvironmentVariable("Path", $env:Path + ";C:\Program Files\git", [EnvironmentVariableTarget]::Machine)
Restart-Computer -Force
Install-Module posh-git
Set-ExecutionPolicy RemoteSigned -Scope CurrentUser -Confirm
cd C:\ProgramFiles\WindowsPowerShell\Modules\posh-git\<some-version>
.\install.ps1
. $PROFILE
rm C:\Program Files\git\git.exe
```

#### 4. Build SQL Server 2014 express
```
git clone https://github.com/brogersyh/Dockerfiles-for-windows.git
cd Dockerfiles-for-windows/sqlexpress/
docker build -t sqlexpress .
```

#### 5. Expose SQL Server
```
if (!(Get-NetFirewallRule | where {$_.Name -eq "SQL Server 1433"})) { New-NetFirewallRule -Name "SQL Server 1433" -DisplayName "SQL Server 1433" -Protocol tcp -LocalPort 1433 -Action Allow -Enabled True}
docker run -d -p 1433:1433 sqlexpress
```

If you see `Error response from daemon: failed to create endpoint <image_name> on network nat: HNS failed with error : Failed to create endpoint.`, then do
```
Stop-Service docker
Get-ContainerNetwork | Remove-ContainerNetwork
Start-Service docker
```

If you want to connect to the sql server instance, do the following:
```
docker run -ti sqlexpress "powershell.exe"
sqlcmd -S .\SQL
```

#### 6. Connect to SQL Server from local machine
Set a static IP for the VM.
Add the following file via `vim Set-StaticIP.ps1` and then execute it:
```
$IP = "192.168.99.51"
$MaskBits = 24 # This means subnet mask = 255.255.255.0
$Gateway = "192.168.99.1"
$DNS = "192.168.99.50"
$IPType = "IPv4"

# Retrieve the network adapter that you want to configure
$adapter = Get-NetAdapter | ? {$_.Status -eq "up"}

# Remove any existing IP, gateway from our ipv4 adapter
If (($adapter | Get-NetIPConfiguration).IPv4Address.IPAddress) {
    $adapter | Remove-NetIPAddress -AddressFamily $IPType -Confirm:$false
}

If (($adapter | Get-NetIPConfiguration).Ipv4DefaultGateway) {
    $adapter | Remove-NetRoute -AddressFamily $IPType -Confirm:$false
}

 # Configure the IP address and default gateway
$adapter | New-NetIPAddress `
    -AddressFamily $IPType `
    -IPAddress $IP `
    -PrefixLength $MaskBits `
    -DefaultGateway $Gateway

# Configure the DNS client server IP addresses
$adapter | Set-DnsClientServerAddress -ServerAddresses $DNS
```
Test this on your local machine. View IPs:
```
VBoxManage guestproperty enumerate "sql server" | grep -i ip
```
```
nc -zv 192.168.99.51 1433
found 0 associations
found 1 connections:
     1: flags=82<CONNECTED,PREFERRED>
        outif en0
        src 192.168.1.6 port 59395
        dst 192.168.99.51 port 1433
        rank info not available
        TCP aux info available

Connection to 192.168.99.51 port 1433 [tcp/ms-sql-s] succeeded!
```
Assuming `docker-machine ip dev` is `192.168.99.100`, edit the following line of the docker-compose.yml like so:
```
extra_hosts:
  - "192.168.99.51:192.168.99.100"
```
Test that this mapping works like so:
```
docker-compose run app bash -c "apt-get install netcat -y && nc -zv 192.168.99.51 1433"
```
The last line of output should say something like `blah blah [192.168.99.51] 1433 (ms-sql-s) open`