Updating SSMS v17.x Remotely On Server/s

Problem

My previous post talked about how to check the version of SSMS that is installed on different machines remotely.

Now we need to find a way to update/upgrade older versions of SSMS remotely based on the list that was generated by our software version checker.

Solution

I created a GUI powershell script and made it executable.

Below is my thought process for making this script:

Step 1

How do I install the SSMS upgrade silently? I ran the executable with /? for help it showed this screen:

I tried the following command to test the SSMS Silent Install:

C:\Temp\SSMS-Setup-ENU-Upgrade.exe /install /quiet /passive /norestart

But there is no option to run it remotely. This needs to run on the machine that I need to upgrade.

Step 2

Copy the installer to the machine that you need to install. The only common place where I can copy the file on the machine is c:\temp directory.

Using powershell, I created a simple script to copy a file from a source path to the server's c:\temp folder:
PS> Copy-Item "<source path>\SSMS-Setup-ENU-Upgrade.exe" -Destination "\\<servername>\C$\temp\SSMS-Setup-ENU-Upgrade.exe" -Force

Step 3

Run the executable remotely. This is where I had spent most of my time troubleshooting. I tried running an Invoke-Command to run SSMS-Setup-ENU-Upgrade.exe with silent install parameters and I got security/permissions error.

As a work around, you can execute a powershell script remotely, and that powershell script can run/call the executable on the server.

Since I know that the executable file will always be in the server's c:\temp folder, I created an update_ssms.ps1 powershell script with the following command:

$ssms_path = "c:\temp\SSMS-Setup-ENU-Upgrade.exe" 

$ssms_arguments = "/install /quiet /passive /norestart"
Start-Process -FilePath $ssms_path -ArgumentList $ssms_arguments -NoNewWindow

I used the process of copying the update_ssms.ps1 from a source path to the server's c:\temp folder:

PS> Copy-Item "<source path>\update_ssms.ps1" -Destination "\\<servername>\C$\temp\update_ssms.ps1" -Force

The following files are now on the server's c:\temp folder:

• SSMS-Setup-ENU-Upgrade.exe
• update_ssms.ps1 

Now we can run the update_ssms.ps1 and bypass security/permissions by executing the following command:
PS> Invoke-Command -ComputerName <servername> -ScriptBlock {powershell.exe -ExecutionPolicy ByPass -File c:\temp\update_ssms.ps1}

Step 4

Now we can put them all together and be able to upgrade a machine remotely.

But, I still have a few more requirements:

• I want to have the ability to upgrade more than one server in a single run execution.
• I also don't like running the powershell script in a command line and provide several parameters for the script to work.
• I wanted the script to be executable
• The update_ssms.ps1 should be in the same folder as the executable powershell script.

I create a GUI screen for wherein the user will provide the following:

• Name of server or servers. separated by comma (,) if more than one
• Or the path of the server list in .txt file.
• The path of SSMS-Setup-ENU-Upgrade.exe

I used PS2EXE-GUI to compile my powershell script into executable.

The executable file can be downloaded from this URL:
https://gallery.technet.microsoft.com/scriptcenter/Updating-SSMS-v17x-62e5af20

UPDATE: Technet Gallery has been closed by Microsoft, you can view the archived version on Technet Gallery Archive

Previous post: Installed SSMS Version Checker...

Installed SSMS Version Checker

Problem

SQL Server Management Studio 17.0 came out in April 25, 2017. Since then, several version and iterations have been released by Microsoft. Now the latest version of SSMS is 17.8.1.

We have also built several database servers for the past year. Every time we build a server we also install the latest copy of the SQL Server Management Studio on the server.

After a year, our servers have different versions of SQL Server Management Studio on them. We did not do a good job of keeping and upgrading our SSMS on the servers.

Since we have several environments (Development, Test, Stage and Production) it was hard to keep up with the version.

Solution

The good thing is that we can easily generate the list of our non-production and production database servers from our monitoring tools.

Now that I have the list of servers, all I need to do is figure out how to find out the version of SQL Server Management Studio that is installed on the servers.

So, I searched Google on how to get the software that are installed on a computer. I found the blog by Techibee, titled Powershell: Script to query softwares installed on remote computer. This made my life easier since he already have a script that can be wrapped as a module or function.

Now all I need to do is to loop though the list of servers and pass the names into my newly found script. But I also wanted my powershell script to look nice, so I used WPF to build a GUI powershell script.

Then I compiled my powershell script so that it will be executable using PS2EXE-GUI.

You can download the executable file here: https://gallery.technet.microsoft.com/scriptcenter/Installed-Software-Checker-48d967eb
or in github

UPDATE: Technet Gallery has been closed by Microsoft, you can view the archived version on Technet Gallery Archive.

Next post: Updating SSMS v17.x Remotely On Server/s...