Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

It is a common practice to place the transaction logs (*.ldf) on a separate physical disk system than database files (*.mdf, *.ndf).

In a virtual machine cloud environment, is it okay to put the transaction logs on the same drive letter as the other data files? How is this usually handled in popular cloud virtual machine environments of today.

NOTE: Not using SQL Azure. Using a Windows virtual machine with full access.

share|improve this question
1  
If you're talking about Azure, I don't think you really have any control over the placement of files, or even visibility into the underlying disk system at all. That said, I don't think it would be very smart of Microsoft to violate best practices and place data and log files on the same disk, especially given the additional write overhead inherent in the platform. –  Aaron Bertrand Jan 18 '13 at 19:32
    
Clarified: Not using SQL Azure. Using a full windows virtual machine so the transaction log placement is up to me. –  Jason Kresowaty Jan 18 '13 at 21:13
1  
So if you have multiple independent volumes accessible to your VM, why do you think log placement considerations would be different for a virtual machine vs. a physical machine? –  Aaron Bertrand Jan 18 '13 at 21:16
    
I guess one needs to know the recommendations of the particular vendor to answer with certainty. I guess I would just like some general ideas about what people have actually been doing in this regard with popular cloud vendors. –  Jason Kresowaty Jan 18 '13 at 22:02
1  
So what are we talking about here? A VM hosted by you on hardware and storage you control or a VM hosted by a cloud provider like Azure or EC2? –  Mark Storey-Smith Jan 18 '13 at 23:45

2 Answers 2

Depends on the cloud environment you're using but typically it makes sense to put them on the same logical drive. If you need additional IOPS you can stripe across multiple volumes but still present a single drive.

In a cloud environment you are not the sole consumer of the storage you are allocated. You get a small slice of a very large pie and as such all IO is random in nature. There is nothing to be gained from trying to separate sequential access (log) from random (data).

Nothing to be gained from the recovery angle either as you can't insist that the two volumes will be allocated from different arrays. Also, there tends to be a different class of protection afforded to the availability of storage from the big players. Azure storage for example is triple replicated within the data centre, with an additional copy replicated to a failover data centre by default.

share|improve this answer

In my extensive testing of cloud SQL on cloud servers (rackspace cloud, to be specific), I found that splitting the ldf and mdf to distinct block storage volumes made a significant improvement in performance. I achieved really hot performance by putting the ldf on an SSD based block storage volume and the mdf on a standard block storage volume.

Ultimately, of course, a block storage volume is a shared SAN somewhere. And local "instance" storage is on the hypervisor itself. So the hardware topology of your cloud environment will dictate what config will work best.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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