Installing SQL Server and SSMS
SQL Server (the Database Engine) and SQL Server Management Studio (SSMS) are installed separately. The engine is the actual database service — you download an installer (Developer edition is a good free choice for learning), pick the features you want (Database Engine Services, and optionally Agent, Analysis Services, Reporting Services), and the setup wizard installs it as a Windows service that can run entirely without a GUI. SSMS is a separate, free download from Microsoft — since SQL Server 2016 it's no longer bundled with the engine installer — and it's simply a client that connects to any SQL Server instance, local or remote.
Cricket analogy: Installing SQL Server is like setting up the pitch and stumps at a ground before a match: the Database Engine (the pitch) must exist before SSMS (the umpire's kit used to manage the game) can even connect to it.
Choosing an Instance Configuration
During setup you choose whether the instance is a default instance (addressed simply by the server name, listening on TCP port 1433) or a named instance (addressed as SERVERNAME\INSTANCENAME, useful for running multiple isolated SQL Server installations side by side on one machine). You also choose the authentication mode: Windows Authentication only, or Mixed Mode, which additionally enables SQL Server logins like the built-in 'sa' account. Other setup-time choices include the server collation (affecting sort order and case sensitivity) and the service accounts each SQL Server service runs under.
Cricket analogy: Choosing between a default and named instance is like deciding whether a ground hosts just one match (default instance, MYSERVER) or multiple simultaneous games on adjoining pitches, each labeled separately (named instance, MYSERVER\FINANCE).
Installing and Connecting with SSMS
Once SSMS is installed, launch it and use the Connect to Server dialog to reach your instance: enter the server name (a dot '.' or 'localhost' for a local default instance, or SERVERNAME\INSTANCENAME for a named one), pick the authentication method, and supply credentials if using SQL Server Authentication. After connecting, Object Explorer on the left shows every database, security login, and server object you have permission to see, and you open a New Query window against a specific database to start writing T-SQL.
Cricket analogy: Connecting SSMS to your server is like a commentator plugging their headset into the stadium's broadcast feed: you supply the server name (ground) and credentials (accreditation) before you can watch the live data.
-- Verify the SQL Server service and connect via sqlcmd
sqlcmd -S localhost\SQLEXPRESS -U sa -P YourStrong!Passw0rd -Q "SELECT @@VERSION;"
-- List all databases on the instance
sqlcmd -S localhost -E -Q "SELECT name, state_desc FROM sys.databases;"Verifying Your Installation
After installation, don't just assume everything works — verify it. Open SQL Server Configuration Manager to confirm the SQL Server service is running (not just installed) and that the TCP/IP protocol is enabled under SQL Server Network Configuration; by default TCP/IP is disabled for local installs and must be turned on for remote connections. If you need to connect from another machine, also confirm the Windows Firewall allows inbound traffic on port 1433 (or the dynamic port a named instance uses, resolved via the SQL Server Browser service).
Cricket analogy: Verifying your installation is like a groundskeeper checking that the sight screens and floodlights actually work before match day: you check SQL Server Configuration Manager to confirm the engine service is running and TCP/IP is enabled, not just installed.
Enabling SQL Server Authentication (Mixed Mode) creates a powerful 'sa' account. Always set a strong password during setup and disable or rename the sa login in production environments to reduce attack surface.
- SQL Server (the engine) and SSMS (the management tool) are installed separately; the engine can run headless.
- During setup you choose between Windows Authentication and Mixed Mode (SQL Server + Windows Authentication).
- Instances can be default (MYSERVER) or named (MYSERVER\INSTANCENAME), allowing multiple isolated engines on one machine.
- SSMS uses Object Explorer to browse databases, security logins, and server objects after connecting.
- SQL Server Configuration Manager lets you verify the service is running and enable protocols like TCP/IP.
- Port 1433 is the default TCP port and must be open in the firewall for remote connections.
- Always secure the 'sa' account with a strong password if Mixed Mode authentication is enabled.
Practice what you learned
1. What is SSMS?
2. Which authentication mode allows login via a SQL Server-specific account like 'sa'?
3. What is the default TCP port SQL Server listens on?
4. Which tool lets you verify SQL Server services are running and enable TCP/IP?
5. What naming pattern identifies a named instance?
Was this page helpful?
You May Also Like
What Is SQL Server?
An introduction to Microsoft SQL Server, its architecture, editions, and where it fits among relational database systems.
T-SQL Syntax Basics
Core T-SQL syntax rules — statements, batches, comments, identifiers, and control-of-flow — you need before writing real queries.
Your First T-SQL Query
Write, run, and understand your first SELECT statement in SQL Server, from filtering rows to sorting results.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics