Five Steps of Remote SQL Server installation using PowerShell

Finally I've nailed down that topic and hopefully that will be my last post dedicated to SQL Server installations on Windows Core.

In this post I will show how it is easy to install SQL Server from a remote computer without remoting to a server, without any GUI, just by using simple command line.


Previous topics:
Seven obstacles of SQL Server Core Installation
SQL Server Windows Core installation does not support Reporting Service
How to Install and prepare Windows Core for SQL Server installation

Attention: Exercise in that blog-post REQUIRES Environment preparations described in my earlier post: How to Install and prepare Windows Core for SQL Server installation

Here is the step-by-step guidance:

Step #1. Start PowerShell in Administrator's mode.

Just do a Right Click on PowerShell Icon an choose "Run as Administrator" command:

You have to get something like this:

Step #2. Restart remote server.

We are under assumption that your server is in "Ready for SQL Server" stage, but just in case will restart it again.
At first we have to connect to the server:
> Enter-PSSession -ComputerName 192.168.1.101 -Credential CoreAdmin
(In your case Server IP/Name and Administrator's name could be different)

After providing Administrator's password you have to get in:

Now you can initiate Restarting command to the remote server:
> shutdown /r /t 0

Then immediately type and run:
> exit

And on your local machine start pinging the remote server:
> ping 192.168.1.101 -t

It will give you an idea what is going on with the reboot process and when server is back again.
In My case it looked like this:
- At first it took a while for the server to get down;
- Then it stopped responding on pinging;
- Then it disappeared from the network;
- Then it reappeared;
- And finally it became pingable again.

As soon server started responding you can press Ctrl-C to stop pinging.

Step #3. Installing the SQL Server.

Assumptions:
1. Your SQL Installation image is on "D:\" drive.
2. You have a local admin named "CoreAdmin" with password "LocalAdmin2016".

In that example, to save the time, I will run installation of ONLY of SQL Server Engine.

At first, clean the PowerShell window by following command:
> cls

Then connect to the remote server:
> Enter-PSSession -ComputerName 192.168.1.101 -Credential CoreAdmin

Enter the password and Now you are ready to run SQL Installation.

Just want to explain how that will be performed:
1. We create scheduled task to install SQL Server.
2. Schedule it one minute after "Now"
3. Waiting for the installation to be performed.

Little disclosure: Script is small and not perfect. If you run it at 23:59 it will fail. :-)

$action = New-ScheduledTaskAction -Execute 'd:\setup.exe' -Argument '/ACTION=Install /Q /IAcceptSQLServerLicenseTerms /INSTANCENAME=MSSQLSERVER /SAPWD=MyStrongSAPassword2016 /SECURITYMODE=SQL /FEATURES=SQLENGINE /SQLSVCACCOUNT="WinCore\CoreAdmin" /SQLSVCPASSWORD="LocalAdmin2016" /SQLSYSADMINACCOUNTS="WinCore\CoreAdmin" /AGTSVCACCOUNT="WinCore\CoreAdmin" /AGTSVCPASSWORD="LocalAdmin2016" /ASSYSADMINACCOUNTS="WinCore\CoreAdmin"'

$CurHour = Get-Date -Format HH; $CurMinute = Get-Date -Format mm
if ($CurMinute -eq 59) {$CurHour = [int] $CurHour + 1; $CurMinute = [int] 0} else {$CurMinute = [int] $CurMinute + 1}
 
$NewTime = Get-Date -Format HH:mm -Minute $CurMinute -Hour $CurHour
 
$trigger =  New-ScheduledTaskTrigger -Once -At $NewTime
 
Register-ScheduledTask -Action $action -Trigger $trigger -TaskName SQLInstall -Description "Install SQL" -User WINCORE\CoreAdmin -Password LocalAdmin2016

If you run following script right after, you will get some details on what you just did:

> Schtasks /Query /FO LIST /V /TN SQLInstall; Get-Date -Format HH:mm:ss

Here is what I've got:

As you can see, my current time was "21:42:17" and SQL Installation task was scheduled for the very next minute: "9:43:00 PM"

I run that script again to see the progress:

As you can see, task started at "9:43:01 PM" and current status has changed to running.

Actually, I was lucky enough to capture that, because a moment after that the situation has changed to the following:

Status has changed from "Running" to "Ready".

At this point we do not know if our installation finished successfully, but anyway, we can perform the cleanup and delete the task we used:

> schtasks /Delete /TN SQLInstall /F

Step #4. Verification.

At first, run following two commands:

$file = Get-Item "c:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\Summary.txt"

$file.LastWriteTime

Note: The number "130" is Compatibility Level assigned to SQL Server 2016.
Correspondingly other versions have following numbers:
2014 - 120
2012 - 110
2008 - 100


Here is my result:

Only at that point I've realized how lucky I was by capturing that task in still running state. Just one second before it finished.

So, from that point we can see that SQL Server Engine installation took on regular workstation 6 minutes and 4 seconds.

Now we have to verify how successful it was using following simple command:

> type "c:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\Summary.txt"

Here is the last portion of the "Summary.txt" file:

From that perspective everything is OK.

And here is the last checkup:
Will verify if SQL Server is actually working:

cd "c:\Program Files\Microsoft SQL Server\130\Tools\Binn".\OSQL -E -Q "SELECT @@VERSION;"

I've got following result for that script:

It looks like SQL Server alive and doing well. It returned it's version and version of the operating system.

After successful verification we have to restart our Windows Server again. Run following script to make it happen:

cls
shutdown /r /t 0
exit
ping 192.168.1.101 -t

After successful pinging reappears you can close PowerShell window. We won't need it anymore.

Step #5. Usage.

That step is the simplest one.
At first just start SQL Server Management Studio on your PC.
Then select "SQL Server Authentication" mode.
Use login "SA" with password "MyStrongSAPassword2016", which I used in the script during the SQL Server installation:

If network is still OK, you should see something like this in your SSMS:

That's it. If anybody have questions to cover please let me know.