What is Mixed Mode Authentication in SQL Server?

Mixed Mode allows both Windows Authentication (uses your Windows login) and SQL Server Authentication (separate SQL username/password like 'sa'). Windows Authentication is more secure; SQL Authentication is needed when connecting from non-Windows apps or containers.

What is a named instance vs default instance?

A default instance is accessed as 'localhost' or the machine name. A named instance is accessed as 'localhost\InstanceName' (e.g. localhost\SQLEXPRESS). You can run multiple named instances on one machine.

How do I enable TCP/IP for remote connections?

Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for [instance] → right-click TCP/IP → Enable. Then restart the SQL Server service. Also open port 1433 in Windows Firewall for remote access.

How to Download & Install SQL Server + SSMS + Azure Data Studio

June 2026 | 12 min read | SQL Server, Install, Setup | — views

A complete beginner-friendly setup guide — SQL Server Developer Edition is free and fully featured

Before you can run any T-SQL, you need a running SQL Server instance and a tool to talk to it. This guide walks you through every step: picking the right edition, installing SQL Server, configuring the server, installing SSMS (the classic Windows GUI) and Azure Data Studio (the modern cross-platform IDE), and verifying that everything works with a quick test query. Total time: ~20–30 minutes.

Already installed? Jump straight to the SQL Server 2017 Complete Playground — 14 script files covering every T-SQL topic from CTEs to In-Memory OLTP.

1. Choose Your SQL Server Edition

Microsoft offers several editions. For learning and development, Developer Edition is the obvious choice — it is 100% free and identical to Enterprise Edition in every feature.

Edition	Cost	Features	Use case
Developer ⭐	Free	All Enterprise features	Development & testing only (not production)
Express	Free	10 GB max DB size, limited CPU/RAM	Small apps, embedded, learning
Standard	Paid	Core RDBMS features, limited HA	Departmental / mid-size workloads
Enterprise	Paid	Full feature set — Always On, partitioning, In-Memory OLTP	Mission-critical production

Recommendation

Download SQL Server Developer Edition (any year — 2017, 2019 or 2022). All three are free, fully featured, and work perfectly with this guide. SQL Server 2022 is the latest stable release as of 2026.

2. Download SQL Server

Official Download Links

SQL Server 2022 Developer (recommended):
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
Click "Download now" under the Developer column
SQL Server 2019 / 2017 (if you need older versions):
https://www.microsoft.com/en-us/sql-server/sql-server-previous-versions

System Requirements

OS: Windows 10 / 11 (64-bit) or Windows Server 2016+
RAM: 1 GB minimum, 8 GB recommended
Disk: 6 GB free for core installation

CPU: x64 processor, 1.4 GHz minimum
.NET: .NET Framework 4.6+ (installer checks)
Internet: Needed for online installer mode

3. Install SQL Server Step by Step

Step 3.1 — Launch the installer

Run SQL2022-SSEI-Dev.exe (or equivalent). You will see three installation modes:

Basic — one-click, default settings. Best for beginners.
Custom — choose features, paths, collation. Recommended for production or learning all options.
Download Media — downloads a full ISO for offline installs.

Choose Basic to get running in 5 minutes, then follow the configuration steps below.

Step 3.2 — Custom installation: key settings to know

If you choose Custom, the installer wizard asks:

Features: At minimum select Database Engine Services. Optionally add Full-Text Search, Integration Services (SSIS), Analysis Services (SSAS).
Instance name: MSSQLSERVER = default instance (connect as localhost). Any other name = named instance (connect as localhost\YourName).
Data directories: Point data files (.mdf) and log files (.ldf) to separate drives in production. For development, defaults are fine.
Collation: SQL_Latin1_General_CP1_CI_AS is the most common for English. Choose before installing — changing it later is painful.
Authentication mode: Choose Mixed Mode (see step 3.3).
Service accounts: Default NT Service\MSSQLSERVER is appropriate for development.

Step 3.3 — Set Mixed Mode Authentication & sa password

During setup (or after via SSMS), configure authentication:

Authentication Mode: Mixed Mode  ← choose this
sa Password: set a STRONG password (e.g. Admin@1234!)
             Must meet complexity: upper + lower + digit + symbol

Windows Authentication = uses your Windows login — more secure, no separate password.
Mixed Mode = also allows SQL Server logins (like sa) — needed for apps, containers, JDBC/ODBC connections from non-Windows hosts.

Step 3.4 — Installation complete ✔

The installer creates and starts the SQL Server (MSSQLSERVER) Windows service automatically. You can verify in services.msc or Task Manager → Services.

4. Install SQL Server Management Studio (SSMS)

SSMS is the primary Windows GUI for SQL Server — object explorer, query editor, backup/restore wizards, job agent, security management. It is free and separate from the server itself.

Download SSMS

https://aka.ms/ssmsfullsetup (always the latest stable release)

File size: ~600 MB. Requires Windows 10 / 11 or Windows Server 2016+. Comes with Azure Data Studio bundled since SSMS 18.7.

Install steps

Run SSMS-Setup-ENU.exe as Administrator
Accept defaults — installation path and features are pre-selected
Click Install and wait (~5–10 min)
Restart your PC if prompted (SSMS installs Visual Studio components)

Connecting to SQL Server for the first time

Server type:   Database Engine
Server name:   localhost         ← default instance
               localhost\SQLEXPRESS  ← named Express instance
               localhost\YourName   ← any named instance

Authentication: Windows Authentication   ← uses your Windows login
                                          (recommended for local dev)
               OR
               SQL Server Authentication  ← use sa + the password you set

Click Connect. The Object Explorer on the left shows your server, databases, security objects and more.

5. Install Azure Data Studio (Optional but Recommended)

Azure Data Studio (ADS) is a lightweight, modern, cross-platform IDE (Windows, macOS, Linux) built on VS Code. It adds Jupyter-style SQL notebooks, an integrated terminal, and a rich extension marketplace. Great companion to SSMS.

Download Azure Data Studio

https://aka.ms/azuredatastudio

Windows: .exe installer or .zip portable
macOS: .dmg
Linux: .deb / .rpm / .tar.gz

SSMS vs Azure Data Studio — which to use?

Feature	SSMS	Azure Data Studio
Platform	Windows only	Windows, macOS, Linux
Object Explorer	✔ Full	✔ Good
Backup / Restore GUI	✔ Full wizards	Extension needed
SQL Agent GUI	✔	Extension needed
SQL Notebooks	✗	✔ Built-in
Extensions / plugins	Limited	✔ Rich marketplace
IntelliCode AI	Basic	✔ Better
File size	~600 MB	~100 MB
Best for	DBA tasks, full GUI management	Development, notebooks, cross-platform

6. Install sqlcmd (Command-Line Tool)

sqlcmd lets you run T-SQL from any terminal, shell script, or CI pipeline — no GUI needed. Essential for DevOps and automation.

Install sqlcmd on Windows

-- Option A: via winget (Windows Package Manager) — fastest
winget install Microsoft.SQLCMD

-- Option B: download the MSI installer
-- https://aka.ms/sqlcmdmsi

-- Option C: if you installed SSMS, sqlcmd is already installed at:
-- C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\...\Tools\Binn\sqlcmd.exe

Test sqlcmd from Command Prompt

-- Connect to local default instance with Windows Auth
sqlcmd -S localhost -E

-- Connect with SQL Auth (sa user)
sqlcmd -S localhost -U sa -P YourPassword!

-- Once connected, run a query:
1> SELECT @@VERSION;
2> GO

-- Should print something like:
-- Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 ...

7. Post-Installation Configuration

A few configuration steps make your development environment significantly better. Run these in SSMS or sqlcmd right after installation.

Enable TCP/IP for remote connections

-- 1. Open: SQL Server Configuration Manager
--    (search "SQL Server Configuration Manager" in Start)
-- 2. Expand: SQL Server Network Configuration
--            → Protocols for MSSQLSERVER
-- 3. Right-click TCP/IP → Enable
-- 4. Right-click TCP/IP → Properties → IP Addresses tab
--    Scroll to IPALL → TCP Port = 1433
-- 5. Restart: SQL Server (MSSQLSERVER) service

-- Open port in Windows Firewall (for remote access):
netsh advfirewall firewall add rule name="SQL Server 1433" ^
      protocol=TCP dir=in localport=1433 action=allow

Essential server-level settings (run in SSMS)

-- Enable advanced options and useful settings
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;

-- Max server memory (leave 2–4 GB for OS — e.g. on 16 GB machine set 12000)
EXEC sp_configure 'max server memory (MB)', 12000; RECONFIGURE;

-- Cost threshold for parallelism (raise from 5 to 50 on dev machines)
EXEC sp_configure 'cost threshold for parallelism', 50; RECONFIGURE;

-- Max degree of parallelism (MAXDOP = number of physical cores, max 8)
EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE;

-- Enable contained databases (useful for portability)
EXEC sp_configure 'contained database authentication', 1; RECONFIGURE;

-- Check current settings
EXEC sp_configure;

Enable SQL Server Agent (for scheduled jobs)

-- SQL Server Agent is stopped by default on Developer/Express Edition.
-- In SSMS: Object Explorer → SQL Server Agent → right-click → Start
-- Or via services.msc: set SQL Server Agent to Automatic startup

-- Verify Agent is running:
SELECT name, status_desc
FROM sys.dm_server_services
WHERE servicename LIKE '%Agent%';

Enable sa login (if you need SQL Auth but forgot to set Mixed Mode)

-- Connect via Windows Authentication, then:

-- 1. Switch to Mixed Mode (if not done during setup)
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'LoginMode', REG_DWORD, 2;

-- 2. Enable and reset sa password
ALTER LOGIN sa ENABLE;
ALTER LOGIN sa WITH PASSWORD = 'YourStr0ng!Pass#';

-- 3. Restart SQL Server service for auth mode change to take effect

8. Verify the Installation

Run these queries in SSMS or sqlcmd to confirm everything is working correctly.

✔ Verification checklist — run in SSMS

-- 1. Version check
SELECT @@VERSION AS SQLVersion;
-- Expected: Microsoft SQL Server 20xx ...

-- 2. Server name and instance
SELECT @@SERVERNAME AS ServerName,
       SERVERPROPERTY('InstanceName') AS InstanceName,
       SERVERPROPERTY('Edition')      AS Edition,
       SERVERPROPERTY('ProductVersion') AS Version;

-- 3. Current date/time (server timezone)
SELECT GETDATE() AS ServerTime, SYSDATETIME() AS HighPrecisionTime;

-- 4. List all system databases
SELECT name, state_desc, recovery_model_desc
FROM sys.databases ORDER BY name;
-- Should show: master, model, msdb, tempdb

-- 5. Confirm TCP is listening
EXEC xp_readerrorlog 0, 1, N'Server is listening on';

-- 6. Check memory config
SELECT physical_memory_in_use_kb / 1024 AS MemoryUsedMB
FROM sys.dm_os_process_memory;

-- 7. SQL Server Agent status
EXEC master.dbo.xp_servicecontrol 'QUERYSTATE','SQLSERVERAGENT';

9. Recommended Extensions & Tools

Azure Data Studio — must-have extensions

SQL Server Schema Compare — diff and sync database schemas
SQL Server Import — import CSV/Excel into tables with wizard
SQL Server Dacpac — deploy/publish .dacpac packages
Query History — browse and replay past queries
GitLens — version-control your SQL scripts
Markdown Books / SQL Notebooks — document and share runnable SQL

Other free developer tools

Brent Ozar's sp_Blitz® suite — free health-check stored procedures (brentozar.com/blitz)
Ola Hallengren's Maintenance Solution — best-practice backup, integrity check and index maintenance jobs (ola.hallengren.com)
DBAtools (PowerShell) — 500+ cmdlets for DBA automation (dbatools.io)
SQL Server Data Tools (SSDT) — Visual Studio add-in for database projects and schema comparison
RedGate SQL Search (free) — search across all objects in a SQL Server instance from SSMS

10. Common Problems & Fixes

Cannot connect to localhost — troubleshooting checklist

Error	Likely cause	Fix
Error 53: Network path not found	SQL Server service not running	Open services.msc → start SQL Server (MSSQLSERVER)
Error 18456: Login failed for user 'sa'	sa disabled or wrong password	Connect via Windows Auth → `ALTER LOGIN sa ENABLE`
Error 233: No process at the other end of the pipe	Named Pipes not enabled	SQL Server Config Manager → enable Named Pipes, restart service
Can't find instance (named instance)	SQL Browser service stopped	services.msc → start SQL Server Browser
Error 10061: Connection refused (remote)	TCP/IP disabled or firewall blocking 1433	Enable TCP/IP in Config Manager + open port 1433 in Firewall
SSMS hangs on "Connecting..."	Wrong server name or authentication	Check server name: `SELECT @@SERVERNAME`. Try `.\MSSQLSERVER` or `(local)`

What Next?

Your SQL Server is running — now learn T-SQL!

Head over to the SQL Server 2017 Complete Playground article. It has 14 ready-to-run script files covering every topic from database setup, all JOIN types, window functions, stored procedures, triggers, security, backup/restore, table partitioning and In-Memory OLTP.

Start the SQL Server Playground →

Frequently Asked Questions

Is SQL Server Developer Edition really free?

What is the difference between SSMS and Azure Data Studio?

What is Mixed Mode Authentication?

Default instance vs named instance — which should I use?

How do I enable remote connections / TCP port 1433?

Was this guide helpful?

Continue Learning

Next Step ⭐

SQL Server 2017 Complete Playground

14 script files — JOINs, CTEs, security, In-Memory OLTP & more

Java / ORM

Hibernate Tutorial for Architects

Connect your Java app to SQL Server via Hibernate JPA

Database

MongoDB vs PostgreSQL

When to choose NoSQL vs relational

Performance

Hibernate Performance Optimization

N+1 fixes, pooling, batch processing

Quick Download Links

Next Step

Server installed? Start the complete T-SQL playground with 14 script files.

SQL Server Playground →

Article Info

Level: Beginner
Time: 20–30 minutes
Cost: Free (Developer Edition)
OS: Windows 10 / 11