Drag and Drop Question You are planning an SAP on Azure deployment that will contain instances of SQL Server on Azure Virtual Machines. Each virtual machine will contain a data file disk and a log…

Question

Exhibit

Answer Area

Explanation

SAP on Azure: SQL Server VM Disk Caching

The Two Disk Targets

1. Data File Disk → Read-only

Why: SQL Server data files have a read-heavy I/O pattern. Pages are frequently read into the buffer pool, but writes are less frequent and less latency-sensitive than log writes.

• The Azure host cache serves repeated reads directly from memory, reducing latency
• Writes still bypass the cache and go directly to the managed disk, preserving durability
• This is Microsoft's documented best practice for SQL Server data disks on Azure VMs

Common mistake: Choosing Read/Write seems intuitive because data files are both read and written. However, Read/Write caching is dangerous - if the host crashes before cached writes are flushed to disk, data corruption can occur. SQL Server's buffer pool already manages its own caching, so Read/Write host caching adds risk with minimal benefit.

2. Log File Disk → None

Why: Transaction log files (.ldf) are almost exclusively sequential writes. SQL Server's Write-Ahead Logging (WAL) protocol requires log writes to be immediately durable before a transaction can commit.

• Caching log writes introduces a durability gap - if the VM crashes, unflushed cached writes are lost, breaking ACID guarantees
• There is no meaningful read pattern to cache on a log disk
• Direct-to-disk writes (None) ensures SQL Server's durability guarantees are met

Common mistake: Choosing Write Accelerator for the log disk. Write Accelerator is valid only for Azure M-series VMs running SAP HANA workloads. For standard SQL Server on Azure VMs, the correct answer is None.

Why Not Read/Write for Either?

Never use Read/Write caching on database disks. If the host node loses power before the write-back cache flushes, the database can be left in an inconsistent state - a risk no production database should accept.

No community discussion yet for this question.