Storage pool configuration for SQL Server

Question

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

Answer 1

Is [using a single pool] applicable to Always-On or just applicable to shared storage FCI.

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.

Answer 2

Personal Experience, no links but I used to build these all the time for customers.

Your disk performance scales with the number of disks used in the pool (up to 4, then additional disks don't really increase performance). I wouldn't recommend using anything less than 4 disks in a pool and would lay them out 4xData, 4xLog and put the TempDB on the VM's local SSD (drive D:?) if it's available to you. Otherwise, put it on another set of 4 disks. TempDB gets rebuilt on server start so transient storage is ok for them.

You only pay for how much data you store (with standard disks) so get as many disks as your VM allows. This was often the driving factor for which VM to choose, getting enough disks to attach to it.

You need to have your VM's in an FCI so that the AG's will work, but since they won't be sharing disks, that rule doesn't apply to you. This was also a tricky moment during setup as you have to remember to UNCHECK the add shared storage button.

Final advice is to make sure that your witness is in a different resource group from your SQL servers. Ideally use the cloud witness option.

EDIT: It is far more likely that when there is a problem in Azure, it happens on an entire rack or subset of racks at a time. So it is very likely that your primary and the witness will go down at the same time. The secondary will not be able to be automatically failed over because it will never get quorum.