When installing SQL Server in an Azure VM FCI with Storage Spaces Direct, does it matter what host caching I select for each of the disks?

Adam 20 Reputation points
2024-07-11T02:59:29.62+00:00

Hi Azure Community

I have been given the task of installing a two node SQL Server 2022 failover cluster instance on Windows Server 2022. I'm going to use Storage Spaces Direct to create one storage pool, out of which I'll create volumes for data and transaction log files. However, I am wondering if it matters what host caching I select for each of the physical disks in the Azure Portal. Usually I would select 'Read-only' for disks hosting data files and 'None' for disks hosting transaction log files. But when they all get thrown in a big storage pool, and I have no idea what disk is being used for what, does it matter?

Thanks

Adam

Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
9,057 questions
SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Amira Bedhiafi 34,491 Reputation points Volunteer Moderator
    2025-01-19T11:23:35.6066667+00:00

    In an S2D configuration, the individual disks are pooled together, and the operating system interacts with the aggregated storage pool rather than individual disks.

    This abstraction reduces the direct control or visibility over how specific disks are utilized for data, logs, or other purposes.

    For caching :

    • Read-only caching is beneficial for workloads that are read-heavy and do not frequently write to the disk like SQL data files for reads
    • None (No caching) is typically preferred for write-heavy workloads (transaction log files) to avoid double writes and latency.
    • Read/Write caching is generally not recommended for SQL Server workloads because it may introduce inconsistencies or performance degradation in write-heavy operations.

    For disks being added to the S2D pool, None (No caching) is the recommended setting. This is because S2D handles caching and data management internally, and Azure host caching can interfere with or duplicate this functionality.

    SQL Server data and log files will reside on the volumes created from the S2D pool. Since these volumes abstract away the underlying disks, the host caching settings will have minimal direct impact on SQL Server performance if configured uniformly as None for all disks in the pool.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.