Install SQL Server Management Studio Using PowerShell

Tagged: PowerShell

Problem#

In this blog post, we will show you how to install SQL Server Management Studio (SSMS) using PowerShell.

The installation process consists of three parts:

1. Define the download URL and the destination
2. Download SQL Server Management Studio installer
3. Install SQL Server Management Studio silently

You can directly apply the third step above if you already have the installer or download it manually. All solutions below share all the steps above. They only differ in how they download the installer.

Using Invoke-WebRequest and Start-Process#

First we need to define variables that hold the download URL and the destination of installer. Then, we use Invoke-WebRequest cmdlet to download the file and put it in destination folder.

Next, we perform silent installation using Start-Process cmdlet and /S argument that denotes the execution is performed in silent mode. We add -Wait parameter to wait until installation process completely finished.

# Define the download URL and the destination
$ssmsUrl = "https://aka.ms/ssmsfullsetup"
$destination = "$env:TEMP\ssms_installer.exe"

# Download SSMS installer
Invoke-WebRequest -Uri $ssmsUrl -OutFile $destination

# Install SSMS silently
Start-Process -FilePath $destination -ArgumentList "/S" -Wait

Using Invoke-RestMethod and Start-Process#

Similar to previous solution, but in this one we will use Invoke-RestMethod cmdlet to download the file. We can use this cmdlet because the communication is using HTTP(S) that is supported by RESTful services.

# Define the download URL and the destination
$ssmsUrl = "https://aka.ms/ssmsfullsetup"
$destination = "$env:TEMP\ssms_installer.exe"

# Download SSMS installer
Invoke-RestMethod -Uri $ssmsUrl -OutFile $destination

# Install SSMS silently
Start-Process -FilePath $destination -ArgumentList "/S" -Wait

Using WebClient and Start-Process#

Similar to previous solution, we can also use WebClient class from .NET Framework to download the file since PowerShell is deeply integrated with .NET Framework.

# Define the download URL and the destination
$ssmsUrl = "https://aka.ms/ssmsfullsetup"
$destination = "$env:TEMP\ssms_installer.exe"

# Download SSMS installer
$WebClient = New-Object System.Net.WebClient
$webclient.DownloadFile($ssmsUrl, $destination)

# Install SSMS silently
Start-Process -FilePath $destination -ArgumentList "/S" -Wait

Using HttpClient and Start-Process#

We can also use HttpClient from .NET Framework to download the installer, then install SSMS silently.

There are two flavors in using .NET Framework, i.e., C# code from PowerShell. We can create the object using PowerShell syntax as in previous solution or we can also embed C# code in our script then invoke it.

In this case, we present both of the approaches.

Create C# object using PowerShell syntax#

In this approach, we need to apply dispose pattern by enclosing each call to HttpClient constructor, GetStreamAsync and FileStream methods with try-finally block. It is optional to put catch block if we want to handle the exception specifically.

At finally block we close the connection after we have used disposable object. Otherwise, we won’t be able to perform silent installation because the process is still being used by another process.

A more concise way to apply dispose pattern in C# is to use using statement, but since PowerShell doesn’t have the syntax we can use try-finally block.

using namespace System.IO
using namespace System.Net.Http

# Define the download URL and the destination
$ssmsUrl = "https://aka.ms/ssmsfullsetup"
$destination = "$env:TEMP\ssms_installer.exe"

# Download SSMS installer
try {
    $client = [HttpClient]::new()    
    try {
        $stream = $client.GetStreamAsync($ssmsUrl)
        try {
            $fileStream = [FileStream]::new($destination, [FileMode]::Create)
            $stream.GetAwaiter().GetResult().CopyTo($fileStream)       
        }
        finally {
            <#Do this after the try block regardless of whether an exception occurred or not#>
            $fileStream.Dispose()
        }         
    }
    finally {
        <#Do this after the try block regardless of whether an exception occurred or not#>
        $stream.Dispose()
    }    
}
finally {
    <#Do this after the try block regardless of whether an exception occurred or not#>
    $client.Dispose()
}

# Install SSMS silently
Start-Process -FilePath $destination -ArgumentList "/S" -Wait

Embed C# code then invoke it from PowerShell#

In this approach, we copy and paste our C# code to PowerShell, then enclose it with Add-Type -TypeDefinition in order to add it to PowerShell session. Then, we will be able to call the method to download the file from PowerShell.

# Create C# Code to Download SSMS installer
Add-Type -TypeDefinition @"
    using System.IO;
    using System.Net.Http;

    public static class Downloader {
        public static void DownloadInstaller() {
            //Define the download URL and the destination
            string ssmsUrl  = "https://aka.ms/ssmsfullsetup";
            string destination = Path.Join(Path.GetTempPath(), "ssms_installer.exe");

            using (var client = new HttpClient())
            {
                using (var s = client.GetStreamAsync(ssmsUrl ))
                {
                    using (var fs = new FileStream(destination, FileMode.OpenOrCreate))
                    {
                        s.Result.CopyTo(fs);
                    }
                }
            }
        }
    }
"@
[Downloader]::DownloadInstaller()

# Install SSMS silently
Start-Process -FilePath "$env:TEMP\ssms_installer.exe" -ArgumentList "/S" -Wait

Conclusion#

To install SSMS using PowerShell, we need to download the file first and then perform silent installation. To download the file, there are some cmdlets we can use: using Invoke-WebRequest or Invoke-RestMethod. We can also use .NET Framework class like WebClient or HttpClient to download the installer.

If we already have the installer, we can perform silent installation using Start-Process cmdlet and /S argument.