asked
Architectural diagram for mdf and ldf in a separate drive
Is there any block diagram to explain the internal working of mdf and ldf placed in a separate drives?
MBPS and IOPS. The more spindles you have the more throughput and IO you have. Also MDF is random IO and transaction logs are sequential writes. Isolating these read and write patterns enables the heads on the disk to work more efficiently. I have a blog post about IOPS and throughput [here](
http://www.timradney.com/iops). Disadvantage would be ummm...........
Depending on your IO requirements, if the IO is low enough then a single set of disk could provide all the IO you need for the work request. I have several small servers where all I have is a set of local disk that we created a single large array so log and data is on the same set of spindles with zero issues. When work load increases and disk is the issue then splitting log, data and tempdb becomes a priority.
In all down to contention. A single drive cannot write to two location at the same time while two or more can. There will still be contention in the drive controller so you could have two of these also and so on. Less contention means higher read and write speeds and hence better performance. Can't immediately think of a disadvantage apart from you only need to buy one drive.
We can store both ldf and Mdf files in different drives but majore problem is complex to restore it back. You need to remember path and you need to specify path MDF as well as LDF also.
When you restore a database it will default to the path of the MDF and LDF from the backup. So either you replicate the backup directory structure or alter them. However if you change the location between backup and restore locations you can script this so the complexity goes away once it has been written.
This isn't an issue at all if you are restoring to a server with the same drive configuration so restores on the same server you simply state 'restore database db_name from disk = 'path:\filename' and presto it restores. If restoring to a server with a different drive configuration you can chose with move using TSQL or use the GUI and click options to chose where to place the MDF and LOG file. I don't see that as a complex issue.
15 People are following this question.
