Problem
You’re need to access a Microsoft SQL Server instance. If you’re going to manage it or develop on it, you will need install a tool like Microsoft SQL Server Management Studio (SSMS) to do so. In this tutorial, we look at how to install SSMS via the GUI and command line.
Solution
We’ll look at a brief history of SSMS, what it is, and walk through the process of downloading, installing, connecting to a SQL Server, some basic functionality, and how to check for new versions.
For the purposes of this tutorial, I am installing SSMS 18.10 on a Windows Server 2019 operating system Virtual Machine and will connect to a SQL Server 2019 SQL Server which are all the latest versions as of the time of writing.
What is SSMS?
Earlier versions of SQL Server came with a graphical tool (IDE) and SQL script editor called Enterprise Manager. It was upgraded along with each version and used up through SQL Server 2000. It was updated and repackaged based on Visual Studio toolset as SQL Server Management Studio in SQL Server 2005 and the name continues.
SSMS is a powerful integrated environment that lets you:
- Configure, monitor, and manage On-Premises and Azure SQL Servers and databases
- Design, develop and execute T-SQL queries, SQL scripts, stored procedures and functions against SQL Server databases with a query editor
- Generate scripts from database objects or GUI based processes
- Also connects to:
- SQL Server Reporting Services (SSRS)
- SQL Server Reporting Analysis Services (SSAS)
- SQL Server Integration Services (SSIS)
Prior to SQL Server 2016, SSMS was installed from the same SQL Server media used to install the SQL Server database engine. As of SQL Server 2016, it’s a separate downloadable install and is released independently of the SQL Server database engine releases. This allows:
- Anyone to install it without needing access to the install media
- Easy updates as they are now frequently released
Other features are:
- Works with all supported versions of SQL Server
- Cost is $0.00 for DBAs, Developers and Business Intelligence Professionals
Download SSMS
Let’s get started.
Obtaining SSMS is much easier now as there is no need to find your install media. Simply go to: Download SQL Server Management Studio (SSMS) and you will be redirected to the download page of the latest version.
Here, you can:
- Find a link to the release notes
- See what’s been fixed
- Make sure there is no functionality you need that was broken (it happens)
- Find links to older SSMS versions
- Downgrade to an earlier version if there is broken functionality you weren’t aware of if you didn’t read the release notes (we all forget to do that sometimes)
- Download the installer executable
To start the download, click the Free Download for SQL Server Management Studio (SSMS) x.x link on this page Download SQL Server Management Studio (SSMS).
Install SSMS
Now that we have the installer, SSMS-Setup-ENU.exe, in our download directory we can choose one of the two install options. The first is the traditional way through the GUI by responding to prompts, in an attended install. The second is running it from a command prompt with some options for an unattended or silent install. As GUI method is the most likely method, we’ll look at that first.
Install SQL Server Management Studio with GUI
Open your download directory in Windows Explorer.
- Right click on the SSMS-Setup-ENU.exe
- ‘Run as administrator’
- If you’re prompted with ‘Do you want to allow this app to make changes to your device?’, click ‘Yes’
- Leave the default install directory or change it if you want, but note that if you change the install directory and want to start SSMS from Start > Run you’ll need to add the directory to your %PATH% environment variable
- Click ‘Install’
The ‘Setup Completed’ without any errors indicates the install is done.
- Click ‘Close’
- Check the install log file under
%TEMP%\SSMS
SSMS Unattended Install from Command Line
In the event you want to install from a script you can do an unattended install from the command line.
Open Search and enter cmd.exe.
- Right click cmd.exe
- ‘Run as administrator’ (install will fail with access denied if you don’t do it this way)
- If you’re prompted with ‘Do you want to allow this app to make changes to your device?’, click ‘Yes’
Use the ‘cd’ command to change to the directory where you downloaded SSMS-Setup-ENU.exe. For this example, I ‘m opening the installer from my user’s Downloads directory and installing it in the default install directory with the following commands.
cd \users\username\Downloads start "" /w SSMS-Setup-ENU.exe /Quiet
When the command prompt returns, make sure there are no errors displayed on the screen and check the install log file under %TEMP%\SSMS
Create a SSMS Desktop Shortcut
The install is done, and we can create a desktop shortcut to SSMS.
- Open Search
- Enter ssms
- ‘Open file location’
- Right click Ssms.exe
- ‘Send to’
- ‘Desktop (create shortcut)’
We know it’s a shortcut so let’s rename it.
- Right click ‘Ssms.exe - Shortcut’
- Rename to something like ‘SSMS’ and hit Enter
Start SSMS and Connect to Server
- Double click the shortcut
- Server name and authentication – enter server name and authentication information
- Click Connect
And you’re brought to the main SSMS screen. If the Object Explorer windows doesn’t open automatically simply go to View > Object Explorer or hit F8.
In the Object Explorer you can connect to another server, and expand a server dropdown where you can access Databases, Security Objects, SQL Agent, and more.
Upgrading SSMS
By default, SSMS will automatically check for updates when you start the application.
To manually check, or disable automatic checking:
- Tools
- Check for Updates…
If there is an update available, a link to the download site will be displayed.
It’s unlikely you would want to disable automatic checking, but if you do simply uncheck ‘Automatically check for updates for SQL Server Management Studio’.
One thing to note, when you install later versions of SSMS 18, you also install Azure Data Studio (ADS). While SSMS is geared toward database administration and development work, ADS is still suitable for administration, especially with some of the extensions for it, but is more geared toward development work.
Next Steps
- We’ve covered the installation and basic functionality of SSMS.
- You can find links to a wealth of SSMS tips here: SQL Server Management Studio Tips.
- And you can also find links to a wealth of ADS tips here: SQL Server Azure Data Studio Tips.
- Learn about the following:
