We are in the middle of building a new SQL Server 2017 always-on cluster in Azure VM. The data disk layout of VM are as follows
TempDb : 2 Azure Disk Read Cache
TempDBLog : 1 Azure Disk No Cache
UserDB : 3 Azure Disk Read Cache
UserDBLog : 2 Azure Disk No Cache
My plan is to create a multiple storage pool, i.e TempDB storage pool with 2 TempDB disk, UserDB storage pool with 3 User db Azure Disk.
However, according to the Microsoft's documentation on SQL Server in Azure VMs:
If you are using a disk striping technique, such as Storage Spaces,
you achieve optimal performance by having two pools, one for the log
file(s) and the other for the data files. However, if you plan to use
SQL Server Failover Cluster Instances (FCI), you must configure one
pool.
Is this applicable to Always-On or just applicable to shared storage FCI. Please guide me, Please share good articles/docs for reference.
Thanks
Best Answer
FCI's in Azure use storage spaces direct for disk replication, which I imagine is the source of this requirement. For AGs you can use multiple separate disks or pools. You should ensure that the same logical volumes and paths are available on each node, otherwise AG replication will stop if you perform file and filegroup operations on the primary. EG if you ad a file 'g:\sql\data\foo.ndf' to a database, that path needs to exist on all the other cluster nodes for that change to be replicated. See Troubleshoot a Failed Add-File Operation (Always On Availability Groups).
Also, wherever you put Tempdb data files, put Tempdb log there too. Splitting out tempdb's log is overkill as it's likely to be underutilized, and its IO access patterns are more like tembdb data files than user database log files. In particular transaction commit doesn't flush the tempdb logs.