How to Install SQL Server PowerShell 2017 Remotely to Your Servers

As part of upgrading our SQL Servers to 2017 CTP 2.1 we have a process that runs afterwards that uses Invoke-Sqlcmd cmdlet but with SQL Server 2017, SQL Server PowerShell 2017 is a separate install from the PowerShell Gallery.  So I wrote a process to that will install the SQL Server PowerShell remotely to the servers prior to the install.

NOTE: This process has several components and pieces but when you have to run it on over 400 servers it is definitely worth while.

First step is to make sure you have at least Windows Management Framework 5.0 on your server/workstation.  The current link to the downloads are here. Below is an example of copy the file from a network share to a list of servers a servers.txt files in the local temp folder.  The CopyFiles.ps1 file is in my Tips on Managing Lots of Instances and was written by my colleague Mark Wilkinson (Twitter | Blog).

.\CopyFiles.ps1 -servers 'c:\temp\servers.txt' -filePath '\\server\sharename\Win8.1AndW2K12R2-KB3191564-x64.msu' -destPath 'c$\temp' -ParallelJobs 20

Once the file is copied to the server locally you can use run the below script from your local machine to install SQL Server PowerShell 2017 for all user to use on the server.  The Get-CMSHosts function can be found on my blog here. You will need to download PsExec from here and extract to location on your local computer and provide the path.

NOTE: This is a hotfix that requires a RESTART. Be careful in PRODUCTION.

$servers= Get-CmsHosts -InstanceList 'C:\temp\servers.txt'
$hotfix = 'c:\temp\Win8.1AndW2K12R2-KB3191564-x64.msu'
$user = 'doamin\username'
$pass = 'password'
$psexec = 'c:\pstools\PsExec.exe'
foreach ($server in $servers)
{
    & $psexec /accepteula \\$server -u $user -p $pass -d wusa /install $hotfix /quiet /forcerestart
}

Once you have those components in place you finally can install SQL Server PowerShell 2017 using the following script:

Get-CmsHosts -InstanceList 'c:\temp\servers.txt' | % { New-PSSession -ComputerName $_ | out-null}
$sessions = Get-PSSession
$scriptblock = {Install-Module -Name sqlserver -Force -Scope allusers -Confirm}
Invoke-Command -Session $($sessions | ? { $_.State -eq 'Opened' }) -ScriptBlock $scriptblock$sessions | Remove-PSSession