question

lakshmi.v avatar image
lakshmi.v 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?
datafiles
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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...........
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
tanglesoft avatar image
tanglesoft answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Krishjkc avatar image
Krishjkc answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.