How to install SQL Server Instances on remote computer with powershell?

Question

Hi

As a part of a larger automation with PowerShell, I would like to install a SQL Express 2022 instance on a remote server.

I have this vars:

#variables for sql instance  
$SQLServer = "SQL01"  
$SQLInstanceName = -join ("T_","$TenantID")  
$SQLInstanceFolder = -join ("D:\Instances\T_","$TenantID")  
  

I have this function:

function New-SQLServerInstance {  
    param ($TenantID, $Server, $SQLInstanceFolder, $SQLInstanceName)  
    Write-Host ""  
    Write-Host "Die Installation der SQL Server 2022 Instanz für den Tenant $TenantID wird auf dem Server $Server gestartet. Dies dauert ein paar Minuten."  
  
    Invoke-Command -ComputerName $Server{  
      
        # Install SQL Instance  
        D:\SQL2022\Express_ENU\SETUP.EXE `  
        /SKIPRULES=RebootRequiredCheck `  
        /ACTION=Install `  
        /ERRORREPORTING=False `  
        /FEATURES="SQLEngine" `  
        /IACCEPTSQLSERVERLICENSETERMS=True `  
        /SUPPRESSPRIVACYSTATEMENTNOTICE=True `  
        /INSTANCEDIR=$SQLInstanceFolder `  
        /INSTANCEID=$SQLInstanceName `  
        /INSTANCENAME=$SQLInstanceName `  
        /SQLSYSADMINACCOUNTS="$GRP_DB" `  
        /QUIET `  
        /TCPENABLED=1 `  
        /UpdateEnabled=0 `  
        /UpdateSource=MU        
        
       }  
    }  
  

The SQL Server installation starts but aborts a few seconds later with en error. If I have a look at the summary.txt (sql server install log) I get the error: "Exit message: The object state property 'this.InstanceId' is null/empty."

The corresponding fields are empty:
INSTANCEDIR:
INSTANCEID:
INSTANCENAME:

So basically I have a problem with handing over those arguments. I tried different things with " and ' or without. Doesn't work. The arguments without variables are passed to the sql installer.

Anybody knows how I need to do this?

Thanks and greetings
Matt

Answer 1

Hello there,

Have you tried Desired State Configuration ?
By using DSC, you build one configuration template that you can reuse over hundreds and thousands of servers. Depending on the build, you might have to tweak a few of the setup parameters. But that's not a significant issue because you can keep all of the standard settings in place.

https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-with-powershell-desired-state-configuration?view=sql-server-ver16

---

--If the reply is helpful, please Upvote and Accept it as an answer--

Answer 2

Hello Matt,

INSTANCEID is an optional parameter and should be a ID, not a name as you used; remove the parameter, the installer will generate one automatically. For parameter details see
https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver16

Answer 3

Hi Olaf
Thanks for your answer. We need to deploy multiple intances per server. The hole solution consists of different services per tenant. The identification over all services depends on the tenant ID. So the SQL instance Name/ID/Installpath should be the tenant id. I already know, that the instancename can not start with a number, so it should be T_12345.

So because of this, I need to set the instance name.

But thanks anyway.
Greetings,
Matt

Answer 4

by the way: I have a script local on the sql server which works. So I the problem is not to generate the right arguments for the setup.exe, but to transfer them over to the sql server.

The central point for the automation is a management server and when I want to start the script on the sql server, I face more or less the same problems... So, i step back and implement everything in to the main script on the automation server from which you can see the function in my first post.