Sitecore And Docker In Love – Using SQL Express Containers

I’ve recently been playing around with the Docker SQL Express image to figure out how I can use it in my Sitecore development and testing. Installation was a breeze, and once I got it playing nicely with my local Sitecore website, provisioning Sitecore DBs was a walk in the park.

How it works

A Docker SQL Express container is like a named instance. Creating multiple named instance means installing multiple SQL Server Express, but with Docker you just fire up a new container and, voila! You have a new named instance in a matter of seconds.

If you’re trying this out locally you’ll notice that the container’s IP address is dynamic. I had challenges with this at first since I had to update my local Sitecore website’s connection strings every time I had to fire up a new container for my Sitecore DBs. Knowing that Docker containers are meant to be used remotely I shouldn’t bother at all… only that I was too hell-bent on obliterating this problem at all costs.

Solving the Dynamic IP problem locally

The Docker RUN command accepts –hostname as argument which I was hoping would modify the HOSTS file so I could use the hostname in the connection strings instead of the dynamic IP. But I couldn’t make it work so I wrote a PowerShell script, specifically for the Docker SQL Express image, that adds the hostname and IP address of the container into the HOSTS file, and as well as generates the RUN command arguments to simplify the SQL Express docker run call to something like this:

./run-dockersqlexpress -password mysecurepassword -hostname sqlexpress 
     -port 123 -dbpath C:\db\sitecore-8.2-update-4

The PowerShell script accepts a $hostname param which will also be used as the container’s name. After the container has been created a combination of Docker commands will retrieve its IP address which will be added to the HOSTS file along with the hostname or will just update an existing entry.

Param($password,$hostname, $port=1433, $dbpath)
...
$container = docker ps -q --filter ("name=" + $hostname)
$ip = docker inspect --format "{{ .NetworkSettings.Networks.nat.IPAddress }}"
     $container

#UPDATE HOSTS FILE
$hostsFile = $env:windir + "\system32\drivers\etc\hosts"
$content = (Get-Content $hostsFile)
$regex = "(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})(\s+|t+)(" + $hostname
     + "(\s+|\t+)|" + $hostname + "$)"
$value = $ip + "`t`t" + $hostname + "`t`t"

if($content -imatch $regex) 
{
    $content = $content -ireplace $regex, $value
}
else 
{
    $content = $content + ($value + "# docker sqlexpress host")
}
    
Out-File -FilePath $hostsFile -InputObject $content

Once executed the HOSTS file should have an entry like this:

[dynamic IP address]          sqlexpress          # docker sqlexpress host

To verify that the connection works, of course, you can use the SQL Server Management Studio or SQLCMD.exe and connect using the hostname and the ‘sa’ credentials.

Connecting to SQL Express containers remotely

Normally, docker containers are accessed remotely. To connect to your SQL Express containers hosted in another server all you need is the server’s IP address (hostname if available) and the host’s port mapped to 1433 in the docker run call (e.g docker run -p 123:1433) . Once you have those then your connection string should look something like this:

<add name=”core” connectionString=”user id=sa;password=p@ssw0rd1;Data Source=[IP or hostname],[host port number];Database=Sitecore.Core” />

Note: If you’re using the host’s port 1433 then there’s no need to add the port number to the connection string.

I wanted to simulate this scenario by connecting to the SQL Express containers in my Windows Server 2016 VM from my host computer, and for that I needed to find my VM’s IP address.

On VMware follow these steps to find the VM’s IP address:

1. Go to Virtual Machine Settings
2. In the Hardware tab select Network Adapter and click Advanced
3. Take note of the MAC address
4. In the host computer execute arp -a in the command prompt
5. Look for the IP address with physical address equivalent to the MAC address

Going back to Sitecore

In an enterprise Sitecore solution the architeture requires multiple non-production environments for development and testing, and each environment may have its dedicated DB server. If this is your setup you can significantly cut the maintenance cost and startup time by just using 1 high capacity server and fire up as many SQL Express containers as needed. Provisioning Docker SQL Express containers is absurdly fast and way cheaper than having to install a proper SQL Server Express.

Click on the image below to see how it only takes less than 20 seconds to provision 3 SQL Express containers in a Windows Server 2016 VM with 4 cores and 8GB of RAM.

Though it’s tempting to use SQL Express containers all the way to PROD I would recommend otherwise because it will never be able to replace the stability and scalability a normal SQL Server provides. Use it only for your development and testing environments where it requires fast and easy provisioning of SQL servers, like in the following use-cases:

• Propagate TEST content to DEV
• Run coded UI tests against fixed predefined content
• Dry run Sitecore update installation
• Switch to fresh set of Sitecore DBs for demo purposes

I’m planning to continue this journey on Azure so there’s more to come on that. Hit me up in the comments section if you have suggestions or simply to show some love.