SQLServerCentral.com recently published my case study on how my company implemented Windows Containers running SQL Server to streamline our QA setup process. If you haven’t seen it, it’s here.

One of the problems that we ran into when moving to using containers was how to get the applications to connect. Let me explain the situation.

The applications in our production environment use DNS CNAME aliases that reference the production SQL instance’s IP address. In our old QA environment, the applications and SQL instance lived on the same virtual server so the DNS aliases were overwritten by host file entries that would point to 127.0.0.1.

This caused us a problem when moving to containers as the containers were on a separate server listening on a custom tcp port. Port numbers cannot be specified in DNS aliases or host file entries and we couldn’t update the application string (one of the pre-requisites of the project) so we were pretty stuck until we realised that we could use SQL client aliases.

The client aliases would allow us to replicate the DNS aliases that we had in production and we wouldn’t have to change the app connection string.

Setting up aliases is simple enough if you have the SQL configuration manager installed. Simply navigate down to the SQL Native Client section within the config manager, right click and fill out the name, server IP and port number: –

N.B. – I always do both 64 and 32-bit sections to be safe

But we didn’t want to install the config manager on the QA servers and also, that’s a very manual process. So we scripted it out.

First thing to do was get the SQL Native Client installed on all the VMs. The way we did this was to install it on one and then create a VM template that all the other servers were built from. The SQL Native Client installer can be downloaded from here. (that’s for SQL 2012 btw as that’s the version of SQL that we worked with).

Once that was done we then ran a powershell script to create the aliases directly in the registry of the server. Here’s a sample of the script: –

# First of all create the registry keys to hold the aliases
New-Item -Path "HKLM:\Software\Microsoft\MSSQLServer\Client\" -Name ConnectTo -Force -ErrorAction Stop | Out-Null
New-Item -Path "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\" -Name ConnectTo -Force -ErrorAction Stop  | Out-Null

# Then create the aliases
$ServerName = "192.168.1.7";
$dbPort     = "10010";
$TCPAlias   = "DBMSSOCN," + $ServerName + "," + $dbPort

$Aliases = @("Alias1","Alias2")

foreach($Alias in $Aliases)
{
    New-ItemProperty -Path "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo\" -Name $Alias -PropertyType String -Value $TCPAlias -ErrorAction Stop | Out-Null
    New-ItemProperty -Path "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo\" -Name $Alias -PropertyType String -Value $TCPAlias -ErrorAction Stop | Out-Null
}

Obviously we wrapped the script up in a load of try…catch blocks to make sure that if the keys were already there then it would drop & re-create but the core of what it does is above. What the script above will do is create two aliases that map to server 192.168.1.7 on port 10010: –

This allowed us to move the apps from using local instances of SQL to using containers listening on a custom port on a remote server without having to change anything in the app configuration. This reduced our server setup time significantly and freed up resources on the app server. Also, we could control what container the apps were pointing to via the registry entries! Very handy.

Thanks for reading, let me know if you have any questions.