I’m working on a project where it’s useful to automate environment setup and teardown for testing some devops deployment scenarios for databases using transactional replication.

Tools I’m Using

To make this easier, I’m using:

• Octopus Deploy Runbooks to organize a series of commands (they have a free tier, FYI)
• Chocolatey to install multiple SQL Server 2019 Developer Edition instances (I’ve written about choco before, it’s also free)

The Challenge

While I’m not at all a fan of “stacking” multiple SQL Server Instances into one Windows Installation in production, it’s fine for this testing scenario.

But I came across a little puzzle when trying to get this to work: when I tried to use choco install or choco upgrade for my second instance, it saw that SQL Server 2019 Developer Edition was already installed and did nothing. There is a –force option which can be used to install additional instances, but the SQL Server Installer will throw an error if you use –force and pass it information for an instance which is already installed. I needed to add a little extra PowerShell to make my script re-runnable and simple move on gracefully if an instance is already installed.

The Solution

Here is the code pattern I finally landed on for each instance installation in my runbook:

$inst=(get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

if ($inst.Contains('SQL1')) {'SQL1 installed, no action taken'} 
else {
choco install sql-server-2019 -Y --force --params="'/SQLSYSADMINACCOUNTS:domainname\username /SECURITYMODE:SQL /SAPWD:MyNotVerySecurePassword /IgnorePendingReboot /INSTANCENAME:SQL1 /INSTANCEDIR:c:\MSSQL\SQL1'"
}

Thanks to this StackOverflow answer for helping me along.

I’m looking forward to sharing more of my progress on this project as I go!