Remote Install SQL Server

Question

I am trying to install SQL server 2016 on remote machine using PowerShell. below is my script. Can someone please help in identifying the issue.

# Variables
$INSTANCENAME = 'DB1234INST01'
$VOLNAME = '1234INST01'
$SAPASS = "abc@12345"
$InstMem = '4096'

Invoke-Command  -ComputerName DB12345 -ScriptBlock{

# Install SQL Instance
D:\Setup.exe `
/SkipRules=RebootRequiredCheck `
/ACTION=Install `
/AGTSVCSTARTUPTYPE=Automatic `
/BROWSERSVCSTARTUPTYPE=Automatic `
/ERRORREPORTING=False `
/FEATURES="SQLEngine,DQ" `
/IACCEPTSQLSERVERLICENSETERMS `
/INSTANCEDIR="E:\$VOLNAME" `
/INSTANCEID=$INSTANCENAME `
/INSTANCENAME=$INSTANCENAME `
/ISSVCSTARTUPTYPE=Automatic `
/QUIETSIMPLE `
/SAPWD=$SAPASS `
/SECURITYMODE=SQL `
/SQLSVCSTARTUPTYPE=Automatic `
/SQLSYSADMINACCOUNTS="bac\Domain Admins" "bac\DB Admins" `
/SQMREPORTING=False `
/TCPENABLED=1 `
/UpdateEnabled=1 `
/UpdateSource=MU

#Set Memory on Instance
import-module SQLPS -DisableNameChecking
Invoke-Sqlcmd -ServerInstance .\$INSTANCENAME -Username sa -Password $SAPASS -Query "EXEC sys.sp_configure 'show advanced options', 1"
Invoke-Sqlcmd -ServerInstance .\$INSTANCENAME -Username sa -Password $SAPASS -Query "RECONFIGURE"
Invoke-Sqlcmd -ServerInstance .\$INSTANCENAME -Username sa -Password $SAPASS -Query "EXEC sys.sp_configure 'max server memory (MB)', $InstMem"
Invoke-Sqlcmd -ServerInstance .\$INSTANCENAME -Username sa -Password $SAPASS -Query "EXEC sys.sp_configure 'show advanced options', 0"
Invoke-Sqlcmd -ServerInstance .\$INSTANCENAME -Username sa -Password $SAPASS -Query "RECONFIGURE"

}

While running from remote machine like jump host i am getting below error:

SQL Server 2016 transmits information about your installation experience, as well as other usage and performance data, to Microsoft to help improve 
the product. To learn more about SQL Server 2016 data processing and privacy controls, please see the Privacy Statement.
The following error occurred:
The specified value for setting 'SAPWD' is invalid. The expected value type is SqlSecureString.

Error result: -2068578303
Result facility code: 1204
Result error code: 1

Please review the summary.txt log for further details
Microsoft (R) SQL Server 2016 13.00.1601.05

Copyright (c) 2016 Microsoft Corporation.  All rights reserved.


Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of 
its dependencies. The system cannot find the file specified.
    + CategoryInfo          : NotSpecified: (:) [Import-Module], FileNotFoundException
    + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.ImportModuleCommand
    + PSComputerName        : DB12345

Alexander Volok · Answer 1 · 2019-02-28T12:32:55.747

1

There are few errors in your question post. One of them appears because of missing SMO (SQL Shared Management Objects):

Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=13.0.0.0

It can be downloaded from the SQL Server Feature Packages page

If the installation didn't help, take a look also on this thread: Could not load file or assembly microsoft.sqlserver.sqlclrprovider 13.100.0.0

There is a reference to a related ms connect item...

Update 2019-02-09:

/QUIETSIMPLE

Specifies that Setup runs and shows progress through the UI, but does not accept any input or show any error messages.

/QUIET

Specifies that Setup runs in a quiet mode without any user interface. This is used for unattended installations. The /Q parameter overrides the input of the /QS parameter.

Setup.exe arguments to be changed from /QUIETSIMPLE to /QUIET since remote execution via Invoke-Command is not happy with the interactivity that QUIETSIMPLE brings

edited Feb 28 '19 at 12:32

answered Feb 27 '19 at 11:02

Alexander Volok

5,630
3
17
33

When i run my script without invoke command on DB server there is no issue. Only problem i am facing while running script from remote server. Is that something SSMS needs to be installed on jumb box as well.. – Abhishek Feb 27 '19 at 17:59
@Abhishek, your variables. Can you place them inside of the ScriptBlock? – Alexander Volok Feb 27 '19 at 18:18
Error:-Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotifica tion or DefaultDesktopOnly style to display a notification from a service application. – Abhishek Feb 28 '19 at 03:22
@Abhishek, QUIETSIMPLE still generated some interactions, I made an adjustment – Alexander Volok Feb 28 '19 at 08:51
same issue.. Just wanted to check i am on server A and trying to install on server B so is it something exe present on server B will create an issue and i have to use config file to do silent installation. – Abhishek Feb 28 '19 at 11:33
I think that that set of arguments you provide is sufficient alternative to installation based on a config file, there is only one thing that was striking is `/QUIETSIMPLE`, but you confirmed that even replacing it with `/QUIET` didn't help to resolve that issue – Alexander Volok Feb 28 '19 at 12:35

score 0 · Accepted Answer · answered Mar 15 '19 at 22:00

I have resolved the issue of installing SQL remotely and its working fine. Only problem is now i am not able to modify the memory value to created instance. Somehow Invoke-Sqlcmd -ServerInstance $Servers\$INSTANCENAME command is stating that its not able to connect to server or instance not found. Any idea on this context would help.

Variables

$Servers = Get-Content C:\server.txt

Invoke-Command -ComputerName $Servers{

$INSTANCENAME = 'DB1243INST01'
$VOLNAME = '1243INST01'
$SAPASS = 'test123'
$InstMem = '4096'
# Install SQL Instance
C:\Temp\Setup.exe `
/SkipRules=RebootRequiredCheck `
/ACTION=Install `
/AGTSVCSTARTUPTYPE=Automatic `
/BROWSERSVCSTARTUPTYPE=Automatic `
/ERRORREPORTING=False `
/FEATURES="SQLEngine,DQ" `
/IACCEPTSQLSERVERLICENSETERMS `
/INSTANCEDIR="E:\$VOLNAME" `
/INSTANCEID=$INSTANCENAME `
/INSTANCENAME=$INSTANCENAME `
/ISSVCSTARTUPTYPE=Automatic `
/QUIET `
/SAPWD=$SAPASS `
/SECURITYMODE=SQL `
/SQLSVCSTARTUPTYPE=Automatic `
/SQLSYSADMINACCOUNTS="abc\Admins" "abc\DB Admins" `
/SQMREPORTING=False `
/TCPENABLED=1 `
/UpdateEnabled=FALSE `
/UpdateSource=MU `

#Set Memory on Instance
import-module SQLPS -DisableNameChecking
Invoke-Sqlcmd -ServerInstance $Servers\$INSTANCENAME -Username sa -Password $SAPASS -Query "EXEC sys.sp_configure 'show advanced options', 1"
Invoke-Sqlcmd -ServerInstance $Servers\$INSTANCENAMEE -Username sa -Password $SAPASS -Query "RECONFIGURE"
Invoke-Sqlcmd -ServerInstance $Servers\$INSTANCENAMEE -Username sa -Password $SAPASS -Query "EXEC sys.sp_configure 'max server memory (MB)', $InstMem"
Invoke-Sqlcmd -ServerInstance $Servers\$INSTANCENAME -Username sa -Password $SAPASS -Query "EXEC sys.sp_configure 'show advanced options', 0"
Invoke-Sqlcmd -ServerInstance $Servers\$INSTANCENAME -Username sa -Password $SAPASS -Query "RECONFIGURE"

}

score -1 · Answer 3 · answered Oct 28 '22 at 10:47

-1

#Invoke-Command -ComputerName MSEDGEWIN10.*****.corp { #CD wsman: #Set-Item localhost\shell\MaxmemoryPerShellMB 2048 #CD c: #}

answered Oct 28 '22 at 10:47

Сайпутдин Омаров

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 31 '22 at 18:28

Remote Install SQL Server

3 Answers3

Variables