SQL Server on Windows, Linux, and Containers: A practical guide for DBAs, developers, and data warehouse teams

Leave a Comment / Articles, Resilience / By

If you work with SQL Server today, you’ve got three main hosting choices: classic Windows, modern Linux, and containers. Each path can deliver excellent performance and reliability. The trick is knowing when to pick which, how to set it up right, and how to leverage SQL Server features to get real gains.

Below is a field-tested walkthrough from beginner to advanced, with example commands, tuning tips, and “why this vs. that” callouts. Sources are included so you can dig deeper.

The quick takeaway

  • Windows is a great fit when you need full Active Directory integration, mature WSFC-based clustering, SSMS-centric workflows, and broad ecosystem support. Always On availability groups run on top of WSFC. Microsoft Learn+1
  • Linux is first-class for SQL Server 2017+ and shines for cost, automation, and container-friendly ops. HA uses Pacemaker instead of WSFC. You manage with systemctl and mssql-conf. Microsoft Learn+2Microsoft Learn+2
  • Containers are fantastic for dev/test, CI/CD, demos, and ephemeral environments. For production, run Linux-based images on Linux hosts with persistent volumes. Microsoft’s official guidance emphasizes Linux images and notes production support is on Linux, not Windows or macOS Docker hosts. Microsoft Learn+2Microsoft Learn+2

Installation and first-run

Windows (beginner)

  1. Download media, run setup, choose Database Engine Services, then follow the wizard for instance config and authentication. Microsoft Learn
  2. To prep for high availability later, make sure the OS is supported for your SQL version and that Windows Server Failover Clustering is available. Microsoft Learn

Why pick Windows?
You already standardize on AD and WSFC, your team lives in SSMS, and you need the broadest third-party integration. Availability groups on Windows depend on WSFC. Microsoft Learn

Linux (beginner)

On Ubuntu as an example:

# Install the engine (high-level quickstart flow)
# See Microsoft docs for exact repo & version commands
sudo /opt/mssql/bin/mssql-conf setup   # edition, SA password, language, more
sudo systemctl status mssql-server     # verify

Adjust settings with mssql-conf (port, TLS, Agent on/off, memory limits). Restart the service to apply changes. Microsoft Learn+1

Enable SQL Server Agent on Linux

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server

Agent has been included since SQL Server 2017 CU4; it’s disabled by default. Microsoft Learn

Why pick Linux?
You want smaller OS footprint, a consistent automation story with systemd and shell, and you plan to containerize. Pacemaker replaces WSFC for availability groups. Microsoft Learn

Containers (beginner)

Start a SQL Server 2022 container for dev:

docker run -e "ACCEPT_EULA=Y" \
  -e "MSSQL_SA_PASSWORD=YourStr0ngP@ss" \
  -e "MSSQL_PID=Developer" \
  -p 1433:1433 \
  -v sqlvolume:/var/opt/mssql \
  -d mcr.microsoft.com/mssql/server:2022-latest

Use MSSQL_SA_PASSWORD (the older SA_PASSWORD env var is deprecated). Persist /var/opt/mssql via a named volume or host bind mount. Microsoft Learn

Production note
Microsoft’s official docs call out that running SQL Server containers for production is supported on Linux. Windows or macOS Docker hosts are not supported for production SQL Server containers. Microsoft Learn

Why pick containers?
Spin up throwaway instances for tests, version your infra with compose, and ship the same bits from laptop to CI to staging. Use them in prod when you can give them Linux hosts, persistent storage, and proper orchestration. Microsoft Learn

High availability and disaster recovery (HADR)

Availability Groups on Windows

Availability groups require WSFC. Read the prerequisites, then create the AG and listener in SSMS or T-SQL. Microsoft Learn+1

Example T-SQL stub






-- Sketch only: creating an AG differs by environment
CREATE AVAILABILITY GROUP SalesAG
  WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
  FOR DATABASE Sales
  REPLICA ON
    'NODE1' WITH (ENDPOINT_URL='TCP://node1:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC),
    'NODE2' WITH (ENDPOINT_URL='TCP://node2:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC);
GO





Availability Groups on Linux





Use the same SQL Server AG concepts, but cluster management is Pacemaker rather than WSFC. In T-SQL, CLUSTER_TYPE = EXTERNAL indicates a non-WSFC manager like Pacemaker. Microsoft Learn





When would I prefer Linux AGs?
Linux-first shops, standardized tooling, and when you want to avoid Windows clustering components. You still get synchronous commit, automatic failover, and readable secondaries.










Performance features that matter (and how to use them)





Query Store + Intelligent Query Processing (IQP)





Turn on Query Store to capture plans and runtime stats. This makes plan regression fixes and IQP features far easier. IQP adds benefits like DOP feedback, memory grant feedback, and CE feedback, with gains often achieved without code changes when you run at the right compatibility level. Microsoft Learn+2Microsoft Learn+2





Checklist





ALTER DATABASE YourDB SET QUERY_STORE = ON;
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON; -- 2022+
ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 160;       -- SQL 2022 level





DOP feedback requires compat level 160 and Query Store in read-write. Microsoft Learn





Why this helps
As a DBA, you can stabilize performance after patching or schema changes. As a developer, you spot regressions quickly and pin a good plan. As a DW engineer, you can run at higher throughput with fewer surprises. Microsoft Learn










Columnstore indexes for analytics





Columnstore gives big scan and compression wins for star schemas and large fact tables. Typical guidance calls out up to 10x faster queries and up to 10x compression vs. rowstore, with even larger gains in some DW patterns. Plan and load carefully, and consider ordered columnstore for specific predicates. Microsoft Learn+2Microsoft Learn+2





Example





-- Convert a large fact table to clustered columnstore
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON dbo.FactSales;





When to use





    

  • Heavy aggregations and scans in DW or reporting.
    • 




  • Real-time analytics over operational data by pairing rowstore OLTP with a nonclustered columnstore on hot tables. Microsoft Learn
    • 











In-Memory OLTP for hot transactional paths





Memory-optimized tables and natively compiled procedures can reduce latch/latch waits and speed up critical OLTP paths. Requires planning for memory and logging. Microsoft Learn+1





Minimal example





-- Enable a memory-optimized filegroup
ALTER DATABASE YourDB ADD FILEGROUP YourDB_mod CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE YourDB ADD FILE (NAME='YourDB_mod1', FILENAME='D:\Data\YourDB_mod1') TO FILEGROUP YourDB_mod;

-- Create a memory-optimized table and index
CREATE TABLE dbo.OrdersHot
(
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
  CustomerID int NOT NULL,
  CreatedAt datetime2 NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);





Great for high-contention temp structures and short-lived staging too. Microsoft Learn










Faster recovery and safer maintenance





    

  • Accelerated Database Recovery (ADR) gives fast, consistent recovery and instant rollbacks even with long transactions. Enable it per-database. Microsoft Learn+1
    • 




  • Resumable index operations let you pause and resume index rebuilds or creates. Perfect for tight maintenance windows. Microsoft Learn+1
    • 






Examples





-- Turn on ADR for a database
ALTER DATABASE YourDB SET ACCELERATED_DATABASE_RECOVERY = ON;

-- Resumable index rebuild
ALTER INDEX IX_FactSales_Date ON dbo.FactSales
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAXDOP = 4);

-- Later...
ALTER INDEX IX_FactSales_Date ON dbo.FactSales RESUME;





Platform tuning: what moves the needle





Windows basics





    

  • Size TempDB appropriately and place it on fast storage.
    • 




  • Set MAXDOP and memory caps based on CPU topology and workload.
    • 




  • Use Extended Events for lightweight tracing.
    • 






(Microsoft’s MAXDOP guidance and DOP feedback docs provide helpful context when you tune parallelism.) Microsoft Learn+1





Linux specifics





    

  • Use XFS or ext4 for data and log. XFS is a strong default and has kernel features that help write-heavy workloads. Microsoft LearnRed Hat Docs
    • 




  • Manage the service with systemctl and use mssql-conf for memory caps, ports, TLS, Agent, and trace flags. Microsoft Learn
    • 




  • Default installs create multiple TempDB data files up to eight, aligned with best practices. Verify and size for your workload. Microsoft Learn
    • 











Security essentials





    

  • TDE encrypts data at rest. Plan key backups and restores if you move databases. Microsoft Learn+1
    • 




  • Always Encrypted protects sensitive columns so keys never reach the engine. Secure enclaves expand scenarios on Windows for in-place encryption and richer queries. Microsoft Learn+1
    • 




  • Encrypt connections with certificates and force TLS where possible. On Linux, set TLS paths and network.forceencryption via mssql-conf. Microsoft Learn+1
    • 











Backups that are fast and resilient





    

  • Backup to URL targets Azure Blob Storage. In SQL Server 2022 you can also target S3-compatible object stores with the new connector. Use compression and, when needed, encryption. Microsoft Learn+1
    • 






Examples





-- Create a credential for Azure Blob (SAS token recommended)
CREATE CREDENTIAL [https://mystorage.blob.core.windows.net/sqlbackups]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET='?sv=...';

-- Backup to URL
BACKUP DATABASE YourDB
TO URL='https://mystorage.blob.core.windows.net/sqlbackups/YourDB_full.bak'
WITH COMPRESSION, STATS=5, CHECKSUM;





Best practices and troubleshooting are documented if you’re new to URL backups. Microsoft Learn










Dev and CI/CD workflow tips with containers





    

  • Use image tags like 2022-latest for stability.
    • 




  • Health-check with sqlcmd in your compose to wait for readiness.
    • 




  • Persist /var/opt/mssql, and keep secrets out of images.
    • 




  • Remember: production container support targets Linux. Microsoft Learn+1MSSQLTips.com
    • 











Role-based examples: how each persona benefits





Database Administrator





    

  • Reliability: AGs on Windows with WSFC, or on Linux with Pacemaker, get you automatic failover and readable secondaries. ADR shortens recovery times. Resumable index ops make maintenance predictable. Microsoft Learn+2Microsoft Learn+2
    • 




  • Performance: Use Query Store and IQP features like DOP feedback to fix regressions and balance parallelism without hand-tuning every query. Size TempDB, apply MAXDOP guidance, and monitor with Extended Events. Microsoft Learn+2Microsoft Learn+2
    • 






Application Developer





    

  • Velocity: Containers give you local DBs that match prod versions. Spin up integration DBs per branch and tear them down after tests. Microsoft Learn
    • 




  • Performance: Use columnstore for heavy analytics inside OLTP when appropriate, or targeted nonclustered columnstore for hybrid workloads. Lean on Query Store during rollouts. Microsoft Learn+1
    • 






Data Warehouse / Analytics





    

  • Throughput: Columnstore indexes and partitioning unlock big scans and compress data significantly. Query Store helps you track plan changes across ETL releases. Microsoft Learn+2Microsoft Learn+2
    • 




  • Resilience: S3-compatible or Azure Blob backups let you keep DW backups offsite, cheaply and durably. Microsoft Learn
    • 











Common “why this vs. that?” decisions





    

  • Windows vs. Linux for AGs
    Pick Windows if you want WSFC and have deep AD integration. Pick Linux if your ops model is Linux-first and you prefer Pacemaker. Both deliver synchronous commit and automatic failover. Microsoft Learn+1
    • 




  • Virtual machine vs. container
    VMs when you need long-lived servers and tight integration with OS tooling. Containers for ephemeral workloads, CI, and developer productivity. Production containers should run on Linux with persistent volumes. Microsoft Learn+2Microsoft Learn+2
    • 




  • Rowstore vs. columnstore
    Rowstore for OLTP point lookups and small updates. Columnstore for DW scans, aggregations, and compression. Consider nonclustered columnstore on hot OLTP tables for hybrid scenarios. Microsoft Learn+1
    • 




  • Traditional tuning vs. IQP
    Keep doing smart indexing and query tuning, but turn on Query Store and correct compat levels so IQP can help with feedback loops like DOP and memory grants. Microsoft Learn+1
    • 











Summary





SQL Server runs brilliantly on all three platforms. On Windows, you get WSFC-based HA and deep AD integration. On Linux, you get a lean footprint, strong automation, and first-class features with Pacemaker for HA. In containers, you get speed and consistency for dev and CI/CD, and with the right foundation, production on Linux as well.





Performance wins often come from platform-agnostic features: Query Store + IQP to stabilize and speed queries, columnstore for big analytics, In-Memory OLTP for hot paths, ADR for recovery, and resumable index operations for safer maintenance. For reliability, pair availability groups with solid backup strategies like Backup to URL.





Pick the deployment model that fits your org’s skills and constraints, then layer on the features above. You’ll get fast, reliable SQL Server regardless of OS or packaging.










Final thoughts





If you’re starting fresh, I’d suggest:





    

  1. Stand up a Linux test VM and a SQL Server 2022 container on a Linux host.
    2. 




  2. Load a real workload, enable Query Store, set compat level 160, and watch IQP do its thing.
    3. 




  3. Add columnstore to one large table and measure.
    4. 




  4. Practice ADR and resumable index rebuilds in a maintenance window dry run.
    5. 




  5. For HA, prototype an availability group on your chosen platform (WSFC on Windows or Pacemaker on Linux) and verify failover and read-only routing.
    6. 






Small, repeatable wins compound fast.










References
























	
	

	

	
	
Discover more from SQLYARD

	

	
	
Subscribe to get the latest posts sent to your email.

	

	
	

			

		

							

					

												

							
													

												

							
							
							
							
							
							
							
														
						

					

				

								

	

	
	

	



		
		
			




	




	        
 
 Related Posts 
 
						
											
					
 
		

	
	
	
	
	
		

			
Leave a Reply
			

				
									
					
					
							

		


		
		

		
	



			

			
		
	



	
 
	

		

			

			

			

					

						
					

					

								

			
		

				

			
Contact us
Contact us
		

							

					

											

				

		

		


		
	





					

						

								
	


		
		
Discover more from SQLYARD

		

		
		
Subscribe now to keep reading and get access to the full archive.

		

			

		

							

					

												

							
													

												

							
							
							
							
							
							
							
														
						

					

				

								

	

	

		
		
Continue reading