Azure provides multiple different options for choosing SQL Server, from having different PAAS offering to Azure SQL VM, which is basically a VM pre-installed with SQL Server (can also be installed manually).
When using SQL VM it is important to consider and know what are the best practices for choosing the disk and caching for the SQL Server data, log and tempdb files.
General considerations for choosing disk for Azure SQL VM
In Azure the IOPS changes as per the size of the disk (for premium SSD) and so does the costing.
As per above you can see that if you need anything more then 2 TiB you will be paying $240 extra every month having the same IOPS. So in such scenarios it could be more cost appropriate to have another disk of 1 TiB. At the same time also consider that the 1 TiB drive will be having lesser IOPS of 5000 only but if you environment doesn’t need IOPS more then 5000 it will be better to use P30 + P40 instead of using a P50 drive.
The decision is much easier in Standard SSD or HDD drive as the IOPS remain constant irrespective of the drive size up till 8 TiB. So if you are using lesser than 8 TiB better to distribute the files along a 1 TiB and 2 TiB drive for our above scenario.
Choosing data disk and caching for Azure SQL VM
Depending the type of load and size you can choose HDD, Standard SDD or Premium SSD.
Again if you are creating an Azure SQL VM for development better to choose a Standard SDD or even HDD as it will be able to suffice the general requirement for development.
For Testing you are better with using Standard SDD, and for Production use Premium SDD, still depends upon the load you are getting but having a better performing storage can save you headaches later on.
Inside the VM you can choose the type of caching you want your disk to have. There are three options available:
- Read-Only Caching: It improves latency and potentially gain higher IOPS per disk. Any data being read is stored in a local memory of the VM for faster retrievals on the second run and not on the Blob storage (disks).
- Read-Write Caching: Addition to what Read-Only cache is doing it also caches all the writes coming into the disk, by doing this it can acknowledge the application faster that the data is secured, but at the same time ensure that you have a proper way to write data from cache to persistent disks.
- None: As the name suggests it doesn’t cache anything.
Now coming back to our topic of choosing an appropriate data disk for Azure SQL VM, once you have made the choice of using the disk type based on your environment and utilization the next option to consider is the caching and for data disk you should choose the Read-only caching.
Choosing log files disk and caching for Azure SQL VM
Similar to choosing the data disk, you should make the choice based upon the load and environment.
Here as the Log files of all the data files are generally stored in the same location and depending upon how fast your environment is, generally in case of OLTP, you might need to choose a bigger disk disk for getting higher IOPS then you actually require based upon the size.
For caching, you should not choose any type of caching for your log files, as by using Read-Only caching it is not going to have any benefit from it as nothing is being read but sequentially written to the logs. And for Read-Write there is a big potential of data loss as if VM crashed the logs inside the cache, waiting to be written into the persistent storage, will be permanently lost.
Choosing TempDb disk and caching for Azure SQL VM
The first option for your Tempdb files should always be the D drive of your VM which is the disk local to your VM.
This local storage is very small and depends upon the size of VM chosen.
If your storage needs is more than the local storage better to use the Premium SSD for better throughput.
being a dba 