We're setting up SQL servers in the Azure cloud using VMs. When we were determining the best setup for our data/logs/tempdb we ran into many blog posts that recommend placing the tempdb on the Temporary Storage drive provided by Azure. However deeper research revealed this information from Microsoft where it's said that this shouldn't be done.
So we're left with following questions:
There's some confusion about this since the original recommendation was to place tempdb on the D: drive. This is no longer true. For the latest info, I recommend that you read the "Performance Guidance for SQL Server in Windows Azure Virtual Machine" whitepaper located here: http://msdn.microsoft.com/en-us/library/windowsazure/dn248436.aspx
Here's an extract with the TempDB section:
As mentioned in section Windows Azure virtual machine disks and cache settings, we recommend that you place tempDB on the operating system disk or the data disk instead of the temporary disk (D:). Following are the three primary reasons for this recommendation based on our internal testing with SQL Server test workloads.
• Performance variance: In our testing, we noticed that you can get the same level of performance you get on D:, if not more IOPS from the operating system or a single data disk. However, the performance of D: drive is not guaranteed to be as predictable as the operating system or data disk. This is because the size of the D: drive and the performance you get from it depends on the size of the virtual machine you use.
• Configuration upon VM downtime situation: If the virtual machine gets shutdown down (due to planned or unplanned reasons), in order for SQL Server to recreate the tempDB under the D: drive, the service account under which SQL Server service is started needs to have local administrator privileges. In addition, the common practice with on-premises SQL deployments is to keep database and log files (including tempDB) in a separate folder, in which case the folder needs to be created before SQL Server starts. For most customers, this extra re-configuration overhead is not worth the return.
• Performance bottleneck: If you place tempdb on D: drive and your application workloads use tempDB heavily, this can cause performance bottleneck because the D: drive can introduce constraints in terms of IOPS throughput. Instead, place tempDB on the operating system or data disks to gain more flexibility. For more information on configuration best practices for optimizing tempdb, see Compilation of SQL Server TempDB IO Best Practices.