Best Practices for Configuring Windows Server for SQL Server (On-Prem, Azure, AWS, GCP)

Introduction

SQL Server is one of the most widely used database systems in the world. Whether it runs on a physical server (on-premises) or in the cloud like Azure, AWS, or Google Cloud, its performance, reliability, and security depend heavily on how Windows Server is configured.

This essay explains those topics in a simple way using:

👉 WHAT it is
👉 WHY it matters
👉 HOW to fix or configure it

---

1. CPU Configuration (Most Important)

WHAT

CPU configuration means how many processors and cores are assigned to SQL Server and how they are used.

WHY

SQL Server is CPU-intensive. Poor CPU setup causes:

• Slow queries

• High latency

• Application delays

In cloud environments, CPU misconfiguration leads to wasted money.

HOW TO RESOLVE

On-Prem

• Use high-frequency CPUs

• Prefer fewer cores with higher speed

• Disable power saving (set to High Performance)

Azure / AWS / GCP

• Choose compute-optimized instances:

  • Azure: D-series, E-series

  • AWS: C5, M5, R5

  • GCP: N2, C2

SQL Server Settings

• Set MAXDOP (Max Degree of Parallelism):

  • Recommended: 4–8 depending on workload

• Set Cost Threshold for Parallelism:

  • Default (5) is too low → increase to 50+

---

2. Memory Configuration

WHAT

Memory determines how much RAM SQL Server can use.

WHY

SQL Server stores data in memory. If memory is not configured:

• System becomes slow

• OS may crash

• Disk I/O increases

HOW TO RESOLVE

Best Practice

• Always set Max Server Memory

Formula

• Leave 10–20% RAM for OS

Example:

• 64GB RAM → SQL Server uses 50–55GB

On-Prem & Cloud

• Same rule applies

Additional Settings

• Enable Lock Pages in Memory

• Use Large Pages (if supported)

---

3. Storage Configuration (Disk Performance)

WHAT

Storage includes disks used for:

• Data files (.mdf)

• Log files (.ldf)

• TempDB

WHY

Disk is the biggest performance bottleneck.

Bad disk setup causes:

• Slow queries

• Blocking

• Timeouts

HOW TO RESOLVE

Separation Rule (Very Important)

Always separate:

• OS drive

• Data files

• Log files

• TempDB

On-Prem

• Use SSD or NVMe

• Use RAID:

  • RAID 10 for data

  • RAID 1 for logs

Azure

• Use Premium SSD or Ultra Disk

• Enable Write Accelerator (for logs)

AWS

• Use io1 / io2 EBS volumes

• Provision IOPS

GCP

• Use SSD Persistent Disk

• Use Local SSD for TempDB

---

4. TempDB Optimization

WHAT

TempDB is a system database used for temporary operations.

WHY

It is heavily used and often becomes a bottleneck.

HOW TO RESOLVE

Best Practices

• Create multiple TempDB files:

  • 1 file per CPU core (up to 8)

• Make all files:

  • Same size

  • Same growth rate

Place TempDB on:

• Fastest storage (SSD)

---

5. Network Configuration

WHAT

Network settings affect communication between SQL Server and applications.

WHY

Poor network setup causes:

• Slow connections

• Timeouts

• Packet loss

HOW TO RESOLVE

Settings

• Enable TCP/IP protocol

• Disable unused protocols (Named Pipes)

Increase:

• Network packet size (if needed)

Cloud Best Practice

• Use private IPs

• Avoid public exposure

---

6. Power Plan Settings

WHAT

Windows power plan controls CPU performance.

WHY

Default settings slow down CPU to save energy.

HOW TO RESOLVE

• Set Power Plan to:
  👉 High Performance

Applies to:

• On-Prem

• Azure VM

• AWS EC2

• GCP VM

---

7. Windows Updates and Patching

WHAT

Keeping OS and SQL Server updated.

WHY

• Security protection

• Performance fixes

• Stability improvements

HOW TO RESOLVE

• Enable regular patching

• Use maintenance windows

• Avoid automatic restarts in production

---

8. SQL Server Configuration Settings

WHAT

Internal SQL Server settings.

WHY

Defaults are not optimized for production.

HOW TO RESOLVE

Key Settings

• Max Server Memory ✔

• MaxDOP ✔

• Cost Threshold ✔

• Optimize for Ad Hoc Workloads ✔

---

9. Security Configuration

WHAT

Protecting SQL Server from unauthorized access.

WHY

Data breaches are costly and dangerous.

HOW TO RESOLVE

Best Practices

• Disable sa login

• Use Windows Authentication

• Enable Firewall rules

• Encrypt connections (TLS)

Cloud Security

Azure

• Use NSG (Network Security Group)

AWS

• Use Security Groups

GCP

• Use Firewall Rules

---

10. Backup Configuration

WHAT

Backing up databases.

WHY

Prevents data loss.

HOW TO RESOLVE

Backup Types

• Full backup

• Differential backup

• Transaction log backup

Storage

• On-Prem → external disk / NAS

• Cloud → Blob / S3 / GCS

---

11. High Availability (HA)

WHAT

Keeping SQL Server running during failures.

WHY

Downtime = loss of business.

HOW TO RESOLVE

Options

• Always On Availability Groups

• Failover Cluster

Cloud

• Azure → Availability Sets / Zones

• AWS → Multi-AZ

• GCP → Regional deployment

---

12. Monitoring and Performance Tuning

WHAT

Tracking SQL Server health.

WHY

Detect problems early.

HOW TO RESOLVE

Tools

• SQL Server Management Studio (SSMS)

• Performance Monitor

• Query Store

Monitor:

• CPU usage

• Memory usage

• Disk latency

---

13. Virtual Machine Sizing (Cloud-Specific)

WHAT

Choosing the right VM size.

WHY

Wrong size = poor performance or wasted cost.

HOW TO RESOLVE

Azure

• Use memory-optimized VMs for databases

AWS

• Use R-series instances

GCP

• Use high-memory machines

---

14. Disk Caching Settings

WHAT

Caching improves read/write speed.

WHY

Improper caching reduces performance.

HOW TO RESOLVE

Azure

• Read caching for data disks

• No caching for log disks

AWS / GCP

• Use optimized disk settings

---

15. Antivirus Configuration

WHAT

Antivirus scanning on SQL files.

WHY

Scanning can block SQL operations.

HOW TO RESOLVE

Exclude:

• Data files (.mdf)

• Log files (.ldf)

• Backup files

---

16. Page File Configuration

WHAT

Windows virtual memory.

WHY

Needed for stability.

HOW TO RESOLVE

• Set fixed size

• Avoid automatic sizing

---

17. Time Synchronization

WHAT

System clock accuracy.

WHY

Important for logs, transactions, and security.

HOW TO RESOLVE

• Sync with NTP server

• Cloud VMs usually auto-sync

---

18. File Growth Settings

WHAT

Auto-growth of database files.

WHY

Bad settings cause fragmentation.

HOW TO RESOLVE

• Use fixed growth (MB, not %)

• Pre-size databases

---

19. Index Optimization

WHAT

Indexes improve query performance.

WHY

Missing or fragmented indexes slow queries.

HOW TO RESOLVE

• Rebuild indexes regularly

• Remove unused indexes

---

20. Disaster Recovery Planning

WHAT

Plan for worst-case failure.

WHY

Ensures business continuity.

HOW TO RESOLVE

• Test backups

• Use geo-replication (cloud)

---

Conclusion

Configuring Windows Server for SQL Server is critical for performance, security, and reliability. Whether running on:

• On-premises servers

• Azure Virtual Machines

• AWS EC2

• Google Cloud

the same core principles apply:

✔ Optimize CPU
✔ Configure memory correctly
✔ Use fast and separated storage
✔ Secure the system
✔ Monitor continuously

By following these best practices using the WHAT → WHY → HOW method, organizations can ensure:

• Faster performance

• Better stability

• Lower costs

• Stronger security