MDF, LDF and Tempdb files placement drive?

Question

MDF, LDF and Tempdb files placement drive?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
May 10, 2014 at 12:39 am

#286599

Hi,
Physical server H/W have single array disk controller.
But logically dived two difftent array controller
"c" and "D" drives - Logical array with RAID 1 (mirroring)
"E" drive - Local array with RAID 5 (stripping)
Pls. suggest me,
1. Can we place user database MDF file at "E" drive (RAID5)?
2. can we place Tempdb files at "C" drive (RAID 1)?
3. can we place user database LDF at "D (RAID1)?
still I have doubt H/W side, all database transactions through single array only, does performance will
really improve if setup those file different logical array?
rgds
ananda

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can put TempDB there, but it's an extremely bad idea. If TempDB grows unexpectedly, you could have the server crash because of lack of free space on C.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Ok.. I will changed to another Separate partition “F” - Tempdb Data and log files.

Pls. suggest. what about other files placement? is it really get performance on different logical array in single Physical arrary controller?

Thanks.

ananda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

ananda.murugesan (5/10/2014)
is it really get performance on different logical array in single Physical arrary controller?

Probably not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Thank you for reply..

I will convinced to vendor about put on MDF, LDF file on different logical partition in single Physical array controller. Also not getting IO performance for the same and as per current server model HP DL380 G7.

Pls. guide and help me. For leaning purpose:

What is the best practice’s these MDF, LDF and TEMPDB files configuration and placement? With standalone server, HD configured on RAID 5 or RAID 10. What kind of HP server model h/w to be choose and purchase?

What is the best practice’s these MDF, LDF and TEMPDB files configuration and placement? With Nodes connected to Physical storage SAN and NAS.

Thanks

ananda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I suggest you spend some time on google, there's lots and lots written on the subject. Also, probably Glenn Berry's hardware book.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

Ok..I have downloded that H/W books. Chapter 2: The Storage Subsystem. Now i am reading that books..

Thanks Mr. Glenn Berry for free pdf edition.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

nm

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

ananda.murugesan (5/10/2014)
is it really get performance on different logical array in single Physical arrary controller?

If you have 4 physical disks attached to the controller and you provision 2 disks into 1 logical array and the remaining 2 into another logical array then yes expect good performance from each array.

What won't provide good performance is creating 1 array from the 4 drives and within Windows putting 2 logical drives (C and D for instance) across this array. In this situation you may just as well create one logical drive.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 9

Thank you Mr. Perry..

As per current configuration in physical server as below

Logical view at HP array configuration tools

1.SAS Array A – 1 Logical drive(s) - RAID 1 (Mirroring)

Total HDD 300 GB * 2 = 600 GB, Here there are two partition created

a. “C” drive = 100 GB

b. “D” drive = 176 GB ( after format)

2. SAS Array B – 1 Logical drive(s) – RAID 5 (stripping with parity)

Total HDD 600 GB * 5 = 3000 GB, Here only one partition created

a. “E” drive = 2.18 TB ( after format)

I will decided to install MSSQL S/W at “D” drive

User database MDF, NDF and LDF and TEMPDB will configured on “E” drive, pls. suggest.

Thanks

ananda

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

ananda.murugesan (5/12/2014)
Total HDD 300 GB * 2 = 600 GB

No, in a RAID 1 configuration 300GB * 2 = 300GB

ananda.murugesan (5/12/2014)
Total HDD 600 GB * 5 = 3000 GB

No, in a RAID 5 configuration 600GB * 5 = 2400GB

ananda.murugesan (5/12/2014)
Here only one partition created
a. “E” drive = 2.18 TB ( after format)

Thats roughly the formatted size one would expect.

With the number of disks at your disposal you are going to be limited on your configuration this is a fact you will need to bear in mind.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 11

Thanks Mr. Perrry.

In My case, I have to configure all user database MDF, LDF, NDF file and tempdb will placed to RAID 5 in single logical drive in "E" drive.

Thanks

ananda.