Product Description

SQL Server Management Studio popularly called SSMS in short is an integrated environment or an IDEto manage many kinds of SQL infrastructure including Microsoft SQL Server, SQL Data Warehouse, Azure Database, Azure Data Warehouseand also non-Microsoft technologies like Amazon RDS, Oracle Database and SQL Server deployments in Unix/Linux servers.However, it is important to note and keep in mind that SSMS can only be installed in the Windows operating system in spite of its support for non-Microsoft technologies. It is a client component which provides a graphical user interface (GUI) for administering local or one or more remote SQL server instances.

Origin and Evolution

SSMS is the successor of Enterprise Manager which was a similar product. SSMS was introduced with Microsoft SQL Server 2005 and has been a permanent feature since then. It is a free product which does not require any license unlike Microsoft SQL Server which is a commercial product and requires a valid license for commercial use. SSMS can be used to administer any licensed installation of SQL Server of any version. All SSMS versions are backward compatible for the SQL Server versions that preceded them.

Initially Microsoft used to update SSMS through monthly releases which was named according to the month. The last monthly branded release was the August 2016 release. Post that Microsoft started numeric versions starting from version 16.x. The current version of SSMS is version 18.6 released on July 22, 2020.

Distribution

SSMS is available as a standalone product from the Microsoft website. Earlier (i.e. till 2015) it also came bundled with Microsoft SQL Server as a Workstation Component. To install SSMS while installing SQL Server all you had to do was say Yes to Workstation Component installation while running the setup.

Installation

As mentioned above the installation of SSMS is a straightforward process. Download the SSMS installer (usually called SSMS-Setup-ENU.exe) from the Microsoft website (URL: https://docs.microsoft.com/en-us/sql/ssms/) and run the installer with an account having adequate privilege. The default installation path is Program Files but can be changed in the Location text box which presents itself during installation. It can be installed on most Windows systems and does not require the system to be a SQL Server.It also detects and prompts the user for update whenever a new version is available.

Launching SSMS

Once the installation is done, we can invoke SSMS from the Start Menu (it comes under Microsoft SQL Server Tools) or by typing SSMS in the Windows 10 search box. Please refer the screenshot below.

Connecting to Server

After it opens SSMS will prompt you to connect to Server. In the ‘Connect to Server’ box you have to select the Server Type, Server Name and Authentication. The default server type is Database Engine (which basically means connect to a Database Server), default Server Name might be localhost (if SSMS and SQL Server reside in the same host) or the SQL server which you usually connect or connected to the last time and default Authentication is Windows Authentication. Please refer the screenshots below. The first screenshot shows the ‘Connect to Server’ dialog with the different Server type options and the second screenshot shows the ‘Connect to Server’ dialog with the different Authentication types.

All the services showing in the figure can be configured and deployed using applications in the SQL Server suite like BIDS (Business Intelligence Services) and SSDT (SQL Server Data Tools). Analysis Services server is for online real time data analysis. Reporting Services server is for online real time reporting of business data. Integration Services server is for ETL and admin tasks across database engines. Azure SSIS Integration runtime is to integrate and work with Azure Data Factory.

Of all the options that are showing Windows Authentication is default and commonly used and is the Windows Server credentials or AD credentials. SQL Server Authentication refers to a SQL Server user authenticated by the SQL Server system. A SQL Server user can be created in SSMS from Security->Logins. The rest refer to different schemes of authentication hosted and made available by Azure Active Directory.

SSMS Layout

The heart of the SSMS is the Object Explorer which displays the database objects (which are commonly called dbo) as a tree in the left pane of the SSMS window. The objects as nodes or leaves are contained in high level containers. For example, the Databases containers contains the database instances in the server. There are 9 containers of which the most important are Databases, Security, Server Objects and Management. The right pane is the workspace where you perform tasks (like running a query) and get the result of your action(like the output of the query).

Functionality

SSMS is a one stop shop for all database activities. There is almost nothing pertaining to database that you cannot do in the SSMS in a Microsoft environment. From the simple tasks of creating a dbo, running a query on a database, creating a dbo from one of the SSMS inbuilt templates, creating a SQL Server user and giving only required access, exporting SSMS settings, backing up the database, creating a project, saving query output to file to the most complex tasks of migrating an entire database to Azure, fine tuning the database with the help of the Database Engine Tuning Advisor, troubleshooting SQL Server issues with the help of logs, configuring log schedule and rotation, creating and adding your own template, generating query execution plan, removing query execution plan from the plan cache,  making the most efficient query execution plan permanent, monitoring user and server activity using DMV scripts etc. All of want has been mentioned and a lot more is possible with the help of the neat and intuitive SSMS GUI.

Newbie helps and tips

Sincethe scope of SSMS is so broad in terms of functionality and so much can be done with it mastering the SSMS takes time and effort. It has a host of tools and features in it. Once you come to know of them and how to use them it is all very easy. But the fact of the matter is that even seasoned professionals know only parts of it and what and how much they know depend on their job role, task requirements and the breadth and complexity of the database environment of the firm where they work. As such numerous occasions arise when the DBA or developer is summoned and asked to do something immediately and he/she has no idea where to go or whether it is even possible in the SSMS.

So below are a few basic and interesting things which are fun to know and can save your day.

My object explorer is gone! What do I do now?

This can be a panic situation for a newbie. You open SSMS and connect to server but lo behold SSMS is empty and you do not know how to get the object explorer and workspace back. Simply go to View in the File menu and click on Object Explorer to bring it back. Please refer screenshot below.

My hands have gone sore typing

Having to type lines of code and query constantly can be tiring and taxing. Therefore, SSMS has inbuilt templates for creating dbo’s and performing many other tasks which require you to write long queries. Just invoke the Template explorer from the view menu (see screenshot above) or using the keyboard shortcut Ctrl+Alt+T. Please refer the screenshot below which shows the different categories of templates in the right pane and a simple Create database template where you just have to make the substitutions as per your requirement for the new database so as to create it.

Creating a SQL Server Login

A new employee has joined and you have been asked to create a SQL Server login for the user and give public access to a particular database. You have no idea how to provide SQL Server login access to the user’s domain account. Don’t worry. It is easy. In the Object Explorer expand Security and right click on Logins and you will get the New Login option to addthe user’s domain account for SQL Server access.  Please refer below screenshot.

Checking SQL server logs

Configuring, maintaining and checking logs is one of the primary responsibilities of any system administrator. Logs can be checked and analyzed in SSMS from Management->SQL Server Logs. You can see the current log and a set of old logs. You can view the content of the log by right clicking any log and selecting View SQL Server Log. You can configure log rotation and log size by right clicking and selecting Properties. Please refer screenshots below.

Registering a server

SSMS allows us to run queries and perform maintenance and administration on multiple SQL Servers at the same time. A large organization might have hundreds of servers. We can register them all in SSMS and group them logically (like Sales Servers or HR Servers) so that we do not have to struggle with excel sheets holding the server details to connect them when required. The same can be done by going to the View tab and clicking on Registered Servers. From the Registered Servers window, we can create a New Server Group and then add servers to the group (like create a MIS group and all the SQL Server of the MIS department to the group) or just add servers straightaway. Please refer the screenshots below. the first shows the option to add Registered Servers, the second shows the Registered Servers window and the last one shows organized Registered Servers.

With that we end for now. Happy exploring and keep learning.