Installing MSSQL (Microsoft SQL Server)

This guide covers installing SQL Server for development and preparing a foundation for production deployment. Use Developer Edition locally unless you specifically need Express limits.

System Requirements link

Hardware (Minimum vs Recommended) link

Supported Platforms link

• Windows — Windows Server 2016+ or Windows 10/11 (dev)
• Linux — RHEL 8+, Ubuntu 20.04+, SLES 15+
• Containers — Official Microsoft SQL Server Docker images

Download and Choose Edition link

1. Visit SQL Server downloads
2. Select Developer (full features, dev/test license) or Express (10 GB limit)
3. Download the installer or Docker pull:

  docker pull mcr.microsoft.com/mssql/server:2022-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourStrong!Passw0rd" \
  -p 1433:1433 --name sql2022 -d mcr.microsoft.com/mssql/server:2022-latest
  

Windows Installation (Custom) link

Use Custom installation when you need control over features, directories, and service accounts.

Feature Selection link

Instance Configuration link

Data Directory Layout (Production) link

Separate volumes improve I/O and recovery:

  E:\Data\     — .mdf data files
F:\Log\      — .ldf transaction logs
G:\TempDB\   — tempdb (multiple equal-sized files)
H:\Backup\   — local backup staging (copy off-site)
  

During setup, set default data/log paths on the Database Engine Configuration page.

Linux Installation (Ubuntu Example) link

  # Import Microsoft repo key and package
curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
curl https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list | \
  sudo tee /etc/apt/sources.list.d/mssql-server-2022.list

sudo apt-get update
sudo apt-get install -y mssql-server

# Configure edition, SA password, accept EULA
sudo /opt/mssql/bin/mssql-conf setup
  

Install command-line tools:

  curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | \
  sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18 unixodbc-dev

echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
  

Connect and verify:

  sqlcmd -S localhost -U sa -P 'YourStrong!Passw0rd' -C -Q "SELECT @@VERSION"
  

Post-Install Configuration link

Enable TCP/IP (Windows) link

1. Open SQL Server Configuration Manager
2. SQL Server Network Configuration → Protocols for INSTANCE → TCP/IP → Enabled
3. Set static port 1433 under IP Addresses (or document dynamic port)
4. Restart SQL Server service

Firewall Rules link

  New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound `
  -Protocol TCP -LocalPort 1433 -Action Allow
  

Linux (ufw):

  sudo ufw allow 1433/tcp
  

Create Application Login link

  CREATE LOGIN app_api WITH PASSWORD = 'ComplexP@ssw0rd!2024';
GO

CREATE DATABASE MyApp;
GO

USE MyApp;
CREATE USER app_api FOR LOGIN app_api;
ALTER ROLE db_datareader ADD MEMBER app_api;
ALTER ROLE db_datawriter ADD MEMBER app_api;
-- Grant execute on procedures as needed
GO
  

Never embed the sa account in application connection strings.

Set Recovery Model link

  ALTER DATABASE MyApp SET RECOVERY FULL;
ALTER DATABASE MyApp SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE MyApp SET AUTO_UPDATE_STATISTICS ON;
  

Install SSMS and Azure Data Studio link

• SSMS — Download SSMS — primary Windows admin GUI
• Azure Data Studio — cross-platform; Git integration, notebooks, lightweight queries

SSMS is not required on the server — install on admin workstations only.

Validate Installation link

  -- Version and edition
SELECT @@VERSION, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductVersion') AS Version;

-- Instance and service account
SELECT servicename, service_account, status_desc FROM sys.dm_server_services;

-- Database files
SELECT name, physical_name, size * 8 / 1024 AS size_mb
FROM sys.master_files
WHERE database_id = DB_ID('MyApp');
  

Common Installation Mistakes link

Performance Tips at Install Time link

• Pre-size data and log files — avoid autogrowth storms during load tests
• Create tempdb with one file per CPU core (up to 8), equal size
• Enable instant file initialization for the SQL Server service account (Windows Perform volume maintenance tasks)
• Set max server memory leaving headroom for OS:

  EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 12288;  -- 12 GB on 16 GB server
RECONFIGURE;
  

Production Scenario: Multi-Instance Server link

Large hosts sometimes host multiple named instances (e.g., PROD_OLTP, PROD_REPORT). Each instance:

• Has separate memory cap via max server memory
• Uses distinct ports (document in connection strings)
• Requires independent backup and patch schedules

Troubleshooting link

  # Linux — view error log tail
sudo tail -100 /var/opt/mssql/log/errorlog
  

Next Steps link

Install SSMS, connect to your instance, and walk through basic administration before writing application schema. Document instance name, port, collation, and service accounts in your team wiki.