November 8, 2018 at 11:00 pm
Hi Guys,
I am confused with placing data file , log file and temp file
When I did browsing , I found some articles saying it is best to place those files in 3 different drives as it will improve performance , but other article told me to put them together in 1 drive as it will reduce the failure rate of each volume ( less drive to be maintained )
- If I put all DATA files in 1 single drive independently , what will happen if it blows up ? Database is Read only and no insert ,update , and delete will be allowed ? BUT I am still able to do point in time recovery as LOG file is on other drive ?
- If I put all LOG files in 1 single drive independently , what will happen if it blows up ? Database is read only ?
- If I put all Temp files in 1 single drive independently , what will happen if it blows up ? We are not able to perform any join , sorting ?
Please advice
Much appreciated
November 9, 2018 at 12:54 am
Putting all the files together on one drive invites a single point of failure disaster, so that's just plain wrong. Say they're all on the same drive, and the drive blows out. What do you do? If you had a backup and the log files, you'd just restore the old backup and then restore the transaction log, and the database would be brought current. If you have everything on the same drive, you can't do that.
November 9, 2018 at 2:21 am
If your database is read-only then your log file is not going to be used much and you're not going to need point-in-time recovery, so there's no harm in having data and log on the same drive. If you lose the drive, you just restore from backup. It's still a good idea to have tempdb on a separate drive, though. If your server is a VM, speak to your SAN administrator about whether there's any performance benefit of having separate drives.
John
November 9, 2018 at 10:31 am
The data and log files for any given db must always be on separate drives. That is, that a single physical drive failure cannot destroy both data and log files for the same db. Otherwise a single failure will destroy the whole db.
However, there is really no need to put all logs on a single drive. For example, for Db1, you could have data on driveA and log on driveB, and for Db2, you could have log on driveA and data on driveB. But no db should have its data file(s) and log both on driveA or driveB only.
Performance-wise, it can be best to spread tempdb across all drives, but if you have a dedicated drive available for tempdb, that can also work out well.
If a log file runs out of space, the db becomes in effect read-only. But if the log file is otherwise unavailable/damaged, the db will be unavailable also. A db must have a log file.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 9, 2018 at 11:48 am
Is your SQL Server using directly attached storage or is it using a SAN? If you use a SAN then any separate "drives" your server sees may just be logical drives carved out of the same physical array of disks, so it doesn't really matter then. I suppose to answer your other questions, it depends on the RAID level the disk array is setup with, and what you mean by "it blows up" as to what the effects will be.
November 11, 2018 at 4:57 pm
WhiteLotus - Thursday, November 8, 2018 11:00 PMHi Guys,
I am confused with placing data file , log file and temp file
When I did browsing , I found some articles saying it is best to place those files in 3 different drives as it will improve performance , but other article told me to put them together in 1 drive as it will reduce the failure rate of each volume ( less drive to be maintained )
Is your SQL Server using directly attached storage or is it using a SAN? If you use a SAN then any separate "drives" your server sees may just be logical drives carved out of the same physical array of disks, so it doesn't really matter then. I suppose to answer your other questions, it depends on the RAID level the disk array is setup with, and what you mean by "it blows up" as to what the effects will be.
Thanks for the reply
I just found out from my sys admin , it is using SAN ,and if the drive is down ,SAN will automatic point other drive to get up and perform
Related Temp files , will performance gets better if i put it on dedicated drive ?
November 11, 2018 at 4:58 pm
a
Chris Harshman - Friday, November 9, 2018 11:48 AMWhiteLotus - Thursday, November 8, 2018 11:00 PMHi Guys,
I am confused with placing data file , log file and temp file
When I did browsing , I found some articles saying it is best to place those files in 3 different drives as it will improve performance , but other article told me to put them together in 1 drive as it will reduce the failure rate of each volume ( less drive to be maintained )Is your SQL Server using directly attached storage or is it using a SAN? If you use a SAN then any separate "drives" your server sees may just be logical drives carved out of the same physical array of disks, so it doesn't really matter then. I suppose to answer your other questions, it depends on the RAID level the disk array is setup with, and what you mean by "it blows up" as to what the effects will be.
Thanks for the reply
I just found out from my sys admin , it is using SAN ,and if the drive is down ,SAN will automatic point other drive to get up and perform
Related Temp files , will performance gets better if i put it on dedicated drive ?
What I mean by Blow up is it filling up the disk space ( no space left )
November 11, 2018 at 5:21 pm
Everyone says that they're supposed to be on "separate drives". If these things are on a SAN, you'll have a bunch of "logical drives" and there's absolutely no guarantee that the files will actually be on separate physical drives unless the SAN guy sets it up that way. And I can tell you for sure that the SAN guy isn't going to dedicate a 650 GB drive to your paltry TempDB or other small drive requirement.
Also, if the head-end of the SAN goes south for the winter, all the drives are dead so it wouldn't matter if they were on real live separate "spindles" or not.
Backups are whole 'nuther story. If you're storing backups on the same SAN as the actual data, you're making a HUGE mistake!
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2018 at 5:46 pm
Jeff Moden - Sunday, November 11, 2018 5:21 PMEveryone says that they supposed to be on "separate drives". If these things are on a SAN, you'll have a bunch of "logical drives" and there's absolutely no guarantee that the files will actually be on separate physical drives unless the SAN guy sets it up that way. And I can tell you for sure that the SAN guy isn't going to dedicate a 650 GB drive to your paltry TempDB or other small drive requirement.Also, if the head-end of the SAN goes south for the winter, all the drives are dead so it wouldn't matter if they were on real live separate "spindles" or not.
Backups are whole 'nuther story. If you're storing backups on the same SAN as the actual data, you're making a HUGE mistake!
Hi Jeff,
Thanks for the response
Do you mean it is ok to put all data file , log file and temp file in 1 drive ? as these things are on a SAN
We put backup on different san
November 11, 2018 at 6:59 pm
For the sake of "management purposes", people will have the SAN guy set up different "drive letters" so they can more easily keep track of their data files, log files, and do some cool things with TempDB. On a SAN, that's normal "just" all logical junk. It's a very rare thing where the logical "drive letters" actually match physical drives.
So, yes... go ahead and do the separation of different files/uses by drive letter. Just understand that there's not going to be a performance improvement because you probably won't be talking separate drives just for your drive letters. And, other systems are also sharing those same disks so the chances of you have 1 or more physical drives dedicated to your drive letters falls somewhere between slim and none and slim just left the building. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply