How to automate install of multiple SQL Server instances

Question

I need to do unattended install of 10 instances of SQL Server 2019 -- 1 default instance + 9 named instances -- on each of 3 nodes of a new production Always On cluster. And same with 3-node test cluster. That's 60 instances in all.

For a single stand-alone instance, I use SETUP.EXE with a config file which allows me to customize file locations, size tempdb, etc. Then I would follow up with any additional SQL Server property customization manually; for example memory config, database mail config, etc. No big deal for a single instance. But 60 instances setup across 2 x 3-node Always On clusters will be quite time consuming and potentially error-prone, especially if multiple DBAs doing the instance installs.

Is there a way to automate install of 10 instances on each Always On cluster node? By the way, another server team in my organization will install the failover cluster cluster instances ahead of giving me the VMs. Also, not sure it matters but the 3-node test cluster is all Azure and the 3-node prod cluster is hybrid with DR node in Azure.

I'm wondering if this can be automated using PowerShell in combination with putting all installation variables -- instance names, listener IPs, etc. -- in a text file or SQL table.

Answer 1

Hi David,

there is an article about Powershell DSC:
[https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-with-powershell-desired-state-configuration?view=sql-server-ver16

All command line params from the classic command line installation will work.
There is also an article about the whole process: [https://techcommunity.microsoft.com/t5/sql-server-blog/configuring-alwayson-with-sql-server-powershell/ba-p/384022

Using a table or file as input for params should work.

Thomas