2

I'm setting up an Azure D11 SQL Server to be used for a data warehouse. I can only attach 4 data disks and have done so, now I need to determine how to configure them and split the DB files. I'm trying to adapt MS guidance to my specific situation.

Traditional Design:

OS Disk/Azure Temp Disk
1 data disk - TempDB
1 data disk - Log
2 data disk - Data

I'm expecting their guidelines are for general/potentially OLTP servers. I have a data warehouse in simple recovery mode with a single nightly ETL process. I'm not particularly fluent in log file management, but my inclination is the log and TempDB can go on a single data disk, leaving three disks available to stripe and maximize IO for the nightly ETL and, more importantly, day time reporting.

Is it reasonable to put the log and tempdb on a single disk and stripe three for the data or does the log file really need to be on a separate disk?

Improve this question
0
2

Azure IaaS is a different animal than traditional on-premise servers (virtual with a SAN or purely physical). You will get at best 500 IOPS per drive attached.

General recommendation is to use storage pools to stripe multiple drives to increase throughput.

With only four drives available on a D series, I would do the following.

  • Put TempDB on the D:\, it's an SSD. Don't put anything else there, make sure that a startup script will check that drive for any folders that are necessary and fix permissions if required.

  • Stripe the four disks you have into a single driver and put both your data and log files on there.

If you had 8 disks available to attach then I would recommend doing two drives of four disks each and putting log on one and data on the other.

Improve this answer
4
  • I like this idea but Microsoft specifically says not to put anything on D:\ as it is a throw away disk.... I assume you're accounting for that by suggesting a script to ensure its integrity on restart. What would a script like that look like? Thanks! – Dave Oct 28 '15 at 15:23
  • Not to put anything permanent on the drive certainly. SQL Server will rebuild the tempdb files if they are not present when it starts up. The script I reference to run on startup would create any folder structures you need and assign the SQL Service account permissions to the folder so that it can create the files it needs. Of course, if the SQL Service account is a local administrator and you just put the tempdb at the root of the drive then you won't have any problems. – Jonathan Fite Oct 28 '15 at 15:25
  • Actually, in the article linked above, they do mention it's okay to put the TempDB and Buffer Pool Extensions on the temp disk and even link to an article explaining how to do it properly. – Namrehs Oct 28 '15 at 15:26
  • Perfect, thanks! I missed that aspect of the article obviously. It doesn't help that we keep changing our service tier selection. I asked this on server fault but do you know how to achieve the column and 256k recommended striping? serverfault.com/questions/732078/… – Dave Oct 28 '15 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.