SQL Server Management Studio or SSMS is, perhaps, the most famous product for managing Microsoft SQL Servers and any SQL infrastructure. Many experts consider it the most useful management tool of all.
SSMS is the default choice for all tasks related to Microsoft SQL Server. It allows users to not only create databases and their elements but also write SQL queries of any complexity faster and easier. A set of powerful multi-featured graphical tools and script editors show high performance in managing all database-related routines. Besides, all these tools and features are available out of the box.
Given the fact that this solution is free of charge and easy-to-use, it became indispensable for specialists dealing with SQL databases, such as developers, administrators, and analysts, as well as database DevOps engineers and security specialists. Despite other professional tools, MS SQL Management Studio remains a leading tool on the market.
Download and Install SSMS
To install SSMS and work with it properly, you need a 1.8 GHz or faster x86 (Intel, AMD) processor, dual-core, or better. The minimum RAM is 2 GB (2.5 GB if you run it on a virtual machine), but the optimal would be 4 GB. For the hard disk space, you need at least 2 GB of available space (more is better: up to 10 GB).
The supported operating systems are the Windows Server line from 2008 R2 to 2019 (all 64-bit), OS Windows 8.1 (64-bit), and Windows 10 (64-bit, version 1607 or higher).
SSMS is compatible with Windows only. If you need to run MS SQL Studio on other OS, such as macOS or Linux, you will have to utilize another Microsoft product – Azure Data Studio.
The latest SSMS version together with release notes is always available on the official Microsoft download page.
Download the setup file and execute it to start the installation. Note that it will display the default location to install the software. Here, you can either accept it or click Change and specify the new path.
By clicking Install, you accept the License Agreement terms.
After that, the installation process begins.
When the installation has completed successfully, you will receive a notification.
There is one more way to install SQL Server Studio, which is via the command-line interface. The process is running in the background, and the user does not receive any GUI prompts.
If this method suits you better, launch the command prompt with the elevated permissions and type the following command:
start "" /w <path where SSMS-ENU.exe file is located> /Quiet SSMSInstallRoot=<path where you want to install SSMS>
If any failure or error occurs during the installation process, the data will be stored in a log file in %TEMP%\SSMSSetup.
After successful installation, launch SQL Server Management Studio from the Start menu:
With the first launch of the product, we get the screen prompting to connect to SQL Server. It is the first step we need to perform to configure our MS SQL Management Studio:
- Define the Server type
- Set the Server name
- Specify the Authentication type
- Provide the Username and Password
- Click Connect
You can save the username and password. So next time you connect to the same SQL Server instance, you won’t need to enter them again.
Note: SQL Server Management Studio can connect to other SQL Server components, such as Analysis Services (SSAS), Integration Services (SSIS), and Reporting Services (SSRS). This functionality comes in handy as it allows users to apply SSMS in the following cases:
- Manage the Analysis Services objects
- Write and save Analysis Services Scripts in MDX, DMX, and XMLA
- Manage packages – import, export, monitor, upgrade, and organize them into folders
- Manage roles and jobs
- Administer server and databases, etc.
SQL Server Management Studio features
In our overview, we’ll stick to the most prominent SSMS tools and options for SQL Server management:
- Object Explorer
- Query Editor
- Template Explorer
- Query Execution Plan
- Table Designer
- Database Designer
- Query and View Designer
- Generate and Publish Scripts Wizard
- Backup and Restore
- SQL Server Security Management
- Activity Monitor
- XEvent Profiler
Object Explorer
Object Explorer is the most frequently used feature in SQL Server Management Studio. Object Explorer has a user interface that offers a convenient hierarchical view of server objects, similar to Windows Explorer with its system of folders, subfolders, files, etc.
Among the many helpful options, it allows you to do the following:
- Search a specific object
- View and edit object properties
- Manage objects represented as nodes
- Run custom reports
Query Editor
The primary tasks of SQL Server Management Studio are writing, executing, and debugging T-SQL code. To implement these functions, SSMS provides an advanced SQL Query Editor with IntelliSense support. Equipped with this technology, the tool is able to auto-complete code. This means, as soon as you start writing code, the software suggests variants to finish the row. This helps accelerate the code writing process and make it more accurate.
There are many more helpful options in SSMS Query Editor:
- Building scripts with T-SQL and XQuery statements
- SQLCMD Scripts editor
- MDX, DMX, and XML/A editors
- Code formatting, including syntax highlighting
- Code debugging
- Commenting and uncommenting selected lines
- Code lines numeration
- Drag-and-drop text
- Selected code executing
- Getting query results as text
- Saving results to file
- Bookmarks
- Integration with Query Execution Plan
Template Explorer
Templates are files with ready-made SQL scripts that create standard objects (databases, tables, views, indexes, stored procedures, triggers, statistics, and functions). Besides, there are templates for SQL Server Management tasks. You can use them to create extended properties, linked servers, logins, users, roles, and Analysis Services.
There is a large collection of predefined templates available in Template Explorer. Refer to any such file and use the code in the Editor with many customization options. Additionally, you can make custom code templates. All such scripts are organized in folders, so you will have to either take the existing folders or develop a new folder structure.
Query Execution Plan
A query execution plan is the sequence of operations on SQL Server that you perform to obtain a SQL query result. Before the server executes a SQL query, it must analyze the instructions and define the most efficient way of execution. For that, it uses the Query Optimizer component. Its input data is the query execution plan itself.
In SQL Server Management Studio, you can view a query execution plan and identify the most resource-intensive operations. Then, you can adjust the query to achieve optimal results.
Table Designer
This visual tool lets users design and visualize tables in a database. Table Designer makes it possible to create, edit, and delete both tables and their components (columns, indexes, keys, relationships, and constraints). The main advantage is that you can perform all these tasks in a visual mode, without manual typing of SQL code.
Database Designer
Database Designer is another visual tool that assists in designing databases and their components. It visualizes each database with tables, columns, constraints, and dependencies. Similarly, you can create and edit databases using diagrams. With SQL Server Studio, it is possible to work on one or several diagrams – there can be as many of them as necessary. Also, each database can be a part of multiple diagrams. This way, you can focus on different database aspects.
Query and View Designer
Query and View Designer allows users to develop queries and views. A significant plus is that you can create database queries using the mouse only – there is no need to write code manually. The Designer also allows you to select a particular SQL query and create a similar one in the Editor.
Generate and Publish Scripts Wizard
This feature generates specific scripts that can run on other SQL Server instances. In essence, you can generate a database object creation script and then use the script on another instance to create the same object.
The Wizard also handles the task of publishing database contents to the Web service. The scripts may be intended for the entire database or specific objects only. Thus, you can restore objects or share the script with your colleagues for object unification.
Backup and Restore
The graphical interface of SQL Server Management Studio simplifies the tasks of backing up and restoring databases. The necessary options are provided for each database in the context menu of Object Explorer.
SQL Server Security Management
Apart from T-SQL code development and execution, SSMS can manage SQL Server itself. In particular, it deals with SQL Server security.
SSMS Security Manager can create usernames and database users and configure their access rights. The necessary options for both the entire SQL Server and separate databases are available in the “Security” section.
Activity Monitor
The tool displays the current data and SQL Server processes. With Activity Monitor, users can track server activities, such as the execution of SQL queries and statements, check the connected users, view data input and output, and examine the most recent and current resource-consuming queries, etc.
XEvent Profiler
XEvent Profiler is an SSMS component that provides quick access to a live streaming view of diagnostics events on SQL Server. One of its advantages is that the database session is less intrusive, so you can debug SQL Server events without performance degradation. Also, it provides a possibility to customize the event view adjusting it to your needs.
Conclusion
SQL Server Management Studio is an undeniable leader that stands out from similar products. Its multiple features allow performing a range of database-related jobs, and, what’s more, they are available for free out of the box. They facilitate the tasks and help specialists save their time and work more efficiently.
However, it does not mean there is no room for improvement. There are lots of additional tools, add-ins, and extensions helping to enhance the functionality and fill the existing gaps in performance. It’s worth mentioning the SSMS tools and add-ins provided by Devart. The variety of means within this solution is excellent for resolving different tasks, from enhancing the IntelliSense options to schema and data comparison and index management.
