SQL Server Management Studio (SSMS)

SQL Server Management Studio is a free multipurpose integrated tool to access, develop, administer, and manage SQL Server databases, Azure SQL Databases, and Azure Synapse Analytics. SSMS allows you to manage SQL Server using a graphical interface.

SSMS can also be used to access, configure, manage & administer Analysis services, Reporting services, & Integration services.

SSMS runs only on Windows. Use Azure Data Studio to run on your macOS or Linux (and also Windows). Install SQL Server Management Studio if you have not installed it yet.

To launch SSMS from the Start menu, click Start and scroll down and expand Microsoft SQL Server Tools 18. Click on Microsoft SQL Server Management Studio.

Clicking on the "Microsoft SQL Server Management Studio" will open the following window to connect with the server.

Select Server Type, Server Name, and Authentication mode to connect with your server. SSMS can be used to connect with Database Engine, Analysis Services, Reporting Services, Integration Services, etc. Here, we will connect with our local SQL Server database, so select Database Engine as a server type.

Next, select the server name to which you want to connect. It can be a local or remote DB server. Select an appropriate server name or <Browse for more..> to browse server name.

Next, select the authentication mode. The latest version of SSMS offers five modes of authentication to choose from, as shown below. The most common is Windows Authentication and SQL Server Authentication.

After selecting the authentication type and entering the user name and password (SQL authentication) and click on the Connect button.

On successful login, the SSMS opens the following window.

SSMS Components

SQL Server Management Studio has the following components:

• Object Explorer
• Security
• Server Objects
• Query and Text Editor
• Template Explorer
• Solution Explorer
• Visual Database Tools

Object Explorer

Object Explorer contains different components of one or more instances of SQL Server in a hierarchical manner. You can view and manage components such as Databases, Security, Server Objects, Replication, Polybase, Management, etc. Expand the component node to see further objects.

For example, expand the Databases folder to see all the databases available in the server instance. Any new database you create will be available here. There is a default System databases folder, which hosts four default databases: master, model, msdb and tempdb.

Security

Managing security for your database server is extremely important. The Security node is below the Databases node in the Object Explorer. You can create Logins and assign Server roles for any database instance. In addition, you can assign role-based security to logins and users. The Server roles you create here have server-wide scope.

Server Objects

The Server Objects node in SSMS has four sub-nodes: Backup devices, EndPoints, Linked Servers, and Triggers. A linked server is a method by which a SQL Server can talk to another ODBC database with a T-SQL statement. SQL Server EndPoints are a point of entry into SQL Server. It is a database object that defines a way in which the SQL Server can communicate over the network. All objects under Server Objects have server-wide scope.

Replication

Replication is a set of technologies for copying and distributing data and database objects between databases and synchronizing databases. This is mainly used for maintaining consistency between databases.

Polybase

Polybase allows your SQL Server to query directly from other SQL Server, Oracle, MongoDB, Hadoop clusters, Teradata, Cosmos DB by installing client connection software using T-SQL separately. Polybase is used for data virtualization.

Query and Text Editor

Open a query editor by clicking on the New Query on the tool bar. Query editor lets you create, edit & execute Transact SQL (T-SQL) statements. It is equipped with IntelliSense support by auto-completing the script by suggesting variants. This makes writing & debugging code easier and faster.

Template Explorer

Template explorer provides templates for creating various database objects. You can browse the available templates in Template Explorer and open it into a code editor window. You can also create your own custom templates.

Open Template Explorer from View menu -> Template Explorer. The following displays Create Database template.

Solution Explorer

Solution explorer is used to manage administration items such as scripts and queries. Open it from View -> Solution Explorer menu.