1

I have run into a scenario for a production server where I was recommended to create a separate drive for each one of the bigger clients (based on database sizes) whereas all other smaller clients can be placed on the same drive in Azure VM for better performance. Eg:

  • Client 1 has 800GB size (data file)
  • Client 2 has 900GB size (data file)
  • Client 3 has 50GB size (data file)
  • Client 4 has 40GB size (data file)

We already have

  • 1 separate drive D: for temp db (both .ldf and .mdf are stored on drive D)
  • and 1 separate drive E: for log files (all .ldf file for all clients is stored here on E:)
  • and I was recommended to create a Drive F: for Client 3 and Client 4 (to store data files whereas their log files are already stored in E:),
  • Drive G: for Client 1 (to store data files whereas their log files are already stored in E:)
  • and Drive H: for Client 2 (to store data files whereas their log files are already stored in E:).

Can this increase performance? Also, if I need to add a disk (Premium SSD) for 1 TB, would it be good if we use 2564 or 5122 or a single 1 TB disk?

Please let me know if anyone has any experience with this.

Rohit Gupta
  • 2,116
  • 8
  • 19
  • 25
Bishal Basyal
  • 7
  • 1

1 Answers1

1

My recommendations for storage for Azure VM's running SQL Server:

Use the TempDB disk that comes with the VM unless it's not large enough for your needs. You may need to script permissions on system startup, but that is your fastest disk and you should make full use of it for TempDB (ONLY TempDB). TEST that on reboot of the VM that it comes back up without issue.

For other needs, I would suggest creating a volume for all MDF, another for all LDF and if you need it, additional pairs of volumes for other databases if you need further segregation of storage performance.

You should also consider throughput of your storage. Standard (spinning) disks have about 500 iops each in Azure and you only pay for what you actually store. For these I would suggest attaching at least 4 (benefits cap out at 4, but more if you actually need the storage instead of throughput). But attach at least 4 and then stripe them so you can get closer to 2,000 iops. Do this for your MDF and another set of four for your LDF files.

Solid State disks have a faster base throughput, but also benefit from striping them. Although you pay for the max storage rather than just what you use. So I wouldn't use them unless you really need the speed boost. But then you probably want to stripe multiple smaller disks rather than the biggest one. Although with SSD's you get more performance on bigger disks, so maybe a trade-off?

So, for each volume that you need, attach at least 4 disks and stripe them. Most of the time I was selecting VM's based on how many disks they supported; after a certain point (at least with the SQL Servers I worked with) I was disk bound rather than cpu or memory bound.

Jonathan Fite
  • 9,414
  • 1
  • 25
  • 30