Proper Disk drive configuration when you using Azure SQL Server VMs

Question

Proper Disk drive configuration when you using Azure SQL Server VMs

WebTechie

SSChampion

Points: 11405
More actions
May 24, 2017 at 9:39 am

#343013

Hello,
We are thinking about using Azure SQL Server VMs to create our next project. However, the VM only comes with a c drive. You have to purchase additional disk drives.
When we are looking at cloud computing / administration, should we still have the same type of data drive configuration as we do with on-premise servers.
C drive for OS
D drive for Apps (probably not needed since the SQL Server software comes with the template).
L drive for User database log files
T drive for TempDB database and log files
S drive for System databases and logs
U drive for User database data files
Is this still best practice for Azure? Buy additional drives with this configuration seems somewhat costly.
Any input is appreciated.
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 1

Best practices from what I can remember previously are:
Minimum of 2 disks - ideally Premium - 1 for logs and 1 for data and temp db.
Enable read caching on data / temp db disks but not the log disk
Ensure VM and storage are in the same region (obviously)

I believe when you create the VM from the SQL server image in the gallery then it will bring up a wizard for you to enter IPOS / Throughput / Size and usage (OLTP, DHW) and it will advise on the best disk setup.

WebTechie SSChampion Points: 11405 More actions · Answer 2

The configuration does advise but only at the 1 TB level. It gives you up a TB but this is allocated to c drive. If you want additional drives, you have to add drives. If you go above 1 TB, it will suggest additional drives.

If not, everything ends up on the C drive. So according to size, that may be ok. But according to good DBA practices, it may not. Just trying to see if best practices for databases (traditional on premise) apply to database servers in the cloud.

Thanks.

Things will work out. Get back up, change some parameters and recode.

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 3

WebTechie - Wednesday, May 24, 2017 11:12 AM
The configuration does advise but only at the 1 TB level. It gives you up a TB but this is allocated to c drive. If you want additional drives, you have to add drives. If you go above 1 TB, it will suggest additional drives.
If not, everything ends up on the C drive. So according to size, that may be ok. But according to good DBA practices, it may not. Just trying to see if best practices for databases (traditional on premise) apply to database servers in the cloud.
Thanks.

Are you sure it is adding it to C? It should add a data disk - the screen below is the one i was referring to.
You should not hold Applications or data on the OS drive - you need to add at least 2 data drives - 1 for logs and 1 for data/tempdb.
its been a while since i set up a vm in azure - I use mostly the Azure SQL database these days

WebTechie SSChampion Points: 11405 More actions · Answer 4

Yes, this is what I was referring to also.

I assumed it was adding it as a C drive. I am not sure now. However, if thought it suggests another data drive, as you mentioned I need at least two more.

I would need to add at least one more. Right?

Things will work out. Get back up, change some parameters and recode.

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 5

Check out this article, it should be able to help you.
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performance

Yes I have always used two data disks on as well as the OS drive.
I've got away with using Standard disks in the past due to cost but you need to test. If you start off on standard and find you need to go to premium for performance reasons then it is a headache.