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.
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)
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.
Here is the code pattern I finally landed on for each instance installation in my runbook:
$inst=(get-itemproperty 'HKLM:SOFTWAREMicrosoftMicrosoft SQL Server').InstalledInstances
if ($inst.Contains('SQL1')) {'SQL1 installed, no action taken'}
else {
choco install sql-server-2019 -Y --force --params="'/SQLSYSADMINACCOUNTS:domainnameusername /SECURITYMODE:SQL /SAPWD:MyNotVerySecurePassword /IgnorePendingReboot /INSTANCENAME:SQL1 /INSTANCEDIR:c:MSSQLSQL1'"
}
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!