How to Install and download Sql Server Management Studio
Sql Server database divides in two parts.
- A physical part composed of database and log files
- A logical part divided in Database Structures, tables, etc.
The physical database files is located into a directory in the computer where you installed Sql Server..
On the other hand if you want to have access to the logical part of your database you need some tools called Database Management Systems (DBMS); One of those tools that belong to Microsoft is Sql Server Management Studio (SSMS).
Sql Server Management Studio gives you the possibility to access your database, it gives you the ability to administer and execute queries also a great variety of tools to improve your data analytics and management.
Some benefits of using SSMS are:
- You can create, delete or modify a database on a graphical interface,
- You can modify, add or delete rows
- Configuring some parameter of your database without writing a single query. For example you can Import databases from Csv, txt or Excel files into Tables.
1. Sql Server Management Studio Download
SSMS has a lot of functionalities that you will discover according to your needs. By using this tool, you can connect to different servers with different versions of SQL SERVER (2008, 2012, 2016, 2019, etc.) and execute queries on different Databases/Tables. This tool is mandatory when developing and optimizing queries.
In the last versions of Sql Server, Sql Server Management Studio is not installed by default.
You can download Sql Server Management Studio from the following link.
2. Sql Server Management Studio Install
After you download SSMS from official Microsoft Website, install is easy all you have to do is to run installer and then click on “Install”.
On Location part try not to change it, default location is always the best option.
Proceed to click on “Install”, then wait a few minutes until installation process ends.
And then wait a few minutes to complete installation process.
After Sql Server installation ends, you can proceed to use the application.
3. Access Sql Server Management Studio
When we start SSMS, Connect to Server windows open and request credentials. We need to provide the following information in order to logging into Sql Server Database.
Server Type: This option enables you to select four different Server Types.
- Database Engine: enables us to store and process data, it provides to us controlled access to data.
- Analysis Services : This server is used for decision support and business analytics. It enables us to create data models for business reports that can belong to different applications as Excel.
- Reporting Services : It provides a set of tools and services to create and deploy reports.
- Integration Services : This server enables us to do data integration and transformation.
Server Name: Depending the location of the server, we can connect by referencing the server name or using the ip address of a local or remote server.
The Server name in Sql Server Management Studio consist of two parts.
[Ip Address]\[Mssql Instance]
For example we can use 192.168.1.1\MSSQLSERVER
Authentication: We can choose which authentication mode fits best our use during Sql Server installation. In SSMS there are two common authentication modes.
- Windows Authentication: This one depends on Windows authentication and it verifies it users by having contact with the local directory or by having access to an active directory server.
- Sql Server Authentication: This one depends on Sql Server for validate and verify the user/password.
Username/Password: They are defined during user’s creation. As you can see this space is going to be empty if our user have Windows authentication, otherwise we will have to fill this one with the user credentials.
A) Sql Server local connection
If your Sql Server Management studio is in the same computer where your server is, login is easy. All you have to do is to choose the authentication mode that you configure during Sql Server installation.
You can logging multiple services using SSMS, if you want to logging into your database engine pick “Database Engine” on “Server type”.
If you Sql server is in your local computer, your Server name is your IP Address (This can be obtained by typing ipconfig on Command Prompt console), or “localhost” to establish your local connection, followed by instance name (If you installed Sql server).
Your Sql Server Instance name would depend on your SQL installation.
- Default Instance. You connect to this instance by using only server name (ex. localhost or Sql server IP )
- Named Instance. You can connect to this instance by using [ServerName]\[Instance Name] (ex localhost\SQLEXPRESS)
If your server is on your personal computer, you can use any of the following options for server name
If you use “Sql Server Authentication” you will have to introduce your logging Credentials (Superadmin user is “sa” and use the password established during installation).
Some names for default instances are
Instance is defined during server installation, also you can leave it as default.
In case you are using Windows Authentication, Sql Server validates the user and password using Windows credentials of the current Windows user using the computer to connect using SSMS. This means that Windows confirms the user and not Sql Server.
B) Remote Sql Server connection
Use the following methods to check if remote Sql Server connection is possible:
- Try to login with SSMS and see if it fails.
- Check if port 1433 (Default Sql Server port) from Sql Server Host is reachable from your computer.
You can check if port is available for your computer by using telnet on windows “command prompt”
- First check if your telnet is working on cmd by typing “telnet” on cmd
- After checking that telnet is available type on your Command Prompt. Telnet “SqlServerHostIP” 1433
- In this example “192.168.11.129” is our host IP, 1433 is Sql default port (If you change it during installation change this number to the one you set). If telnet connection success, you are going to see an empty cmd windows executing. if not you are going to receive the following message.
If telnet is working for port 1433(Sql Server default connection port) that means that remote Sql Server is reachable, it is 100% sure that you will be able to connect
In case IP port 1433 is not reachable you will have to check your network connections and check if there is no firewall or other security configuration blocking your connection. If you receive Connect Failed message you should check your net connection with sql server host, check your firewall or check if Sql server is enabling remote connections.
If your telnet is not available in your computer follow the next steps: Enable Telnet
If you already installed SSMS and you are interested in enabling SSMS Dark Mode click on the following link: SSMS Dark Theme