Installation, Tools and Environment Setup

2.1 Installing SQL Server (Windows)

Download the Developer Edition installer.

Choose Custom install → New SQL Server standalone installation.

Select features: Database Engine Services, Full-Text Search, PolyBase (optional).

Instance configuration: Default instance (MSSQLSERVER) or a named instance (SERVER\INSTANCE).

Service accounts: use virtual accounts or gMSAs (managed service accounts).

Collation (e.g., SQL_Latin1_General_CP1_CI_AS or Latin1_General_100_CI_AS_SC_UTF8).

Authentication mode: Windows or Mixed mode (set an sa password).

Data directories: keep data, log, tempdb, and backups on separate drives.

TempDB: multiple data files (1 per core, up to 8).

MaxDOP and memory settings (configurable during setup since 2019).

2.2 Tools

SSMS (SQL Server Management Studio) The primary IDE for administration and development VS Code + MSSQL extension

Lightweight cross-platform editor (Azure Data Studio was retired in 2026) sqlcmd / go-sqlcmd Command-line querying and scripting SQL Server Configuration Manager Services, network protocols, ports Bulk copy utility SQL Server Profiler

Tracing (deprecated; use Extended Events) Database Engine Tuning Advisor DACPAC/BACPAC deployment dbatools (PowerShell) Community DBA automation

2.3 Connecting

sqlcmd -S localhost -E                 # Windows authentication
sqlcmd -S localhost -U sa -P "Pwd" -Q "SELECT @@VERSION"

SELECT @@VERSION;
SELECT @@SERVERNAME, SERVERPROPERTY('Edition'), SERVERPROPERTY('ProductVersion');

2.4 Network Configuration

Protocols: Shared Memory, TCP/IP (default port 1433), Named Pipes.

SQL Server Browser service (UDP 1434), used for named instances.

Firewall rules and dynamic vs static ports.

2.5 Restoring Sample Databases

RESTORE DATABASE AdventureWorks2022
FROM DISK = 'C:\Backups\AdventureWorks2022.bak'
WITH MOVE 'AdventureWorks2022'     TO 'D:\Data\AdventureWorks2022.mdf',
     MOVE 'AdventureWorks2022_log' TO 'L:\Log\AdventureWorks2022_log.ldf',
     STATS = 10;

2.6 SSMS Productivity

Object Explorer, Query Editor, Registered Servers, Template Explorer

IntelliSense (Ctrl+Shift+R to refresh), Include Actual Execution Plan (Ctrl+M)

Results to grid/text/file (Ctrl+D / Ctrl+T / Ctrl+Shift+F)

Snippets (Ctrl+K, Ctrl+X), Comment (Ctrl+K, Ctrl+C) Generate scripts, Activity Monitor, Copilot in SSMS 21+