June 21, 2013 at 12:10 pm
Hi,
At work today I noticed that most of our SQL Server environments have the same setup:
2 disks in RAID 1
4 disks in RAID 5
The database config also follows this format:
OS s/w & backups on the RAID 1
db s/w, mdf & ldf all on the RAID 5
Wouldn't it be better for performance if the OS & logs were on the RAID 1 instead of the logs being on the same disks as the datafiles? I guess the split was decided to ensure the backup were on a separate RAID collection but was just wondering~ it's not as if these servers are ever pushed much
June 21, 2013 at 12:42 pm
It is best for the OS, Data, Log, and TempDB files all to be on separate drives, but your config does not allow for this. As far as where each would perform best, you are probably right but you need to get some metrics on the performance of each set of drives to be sure, I don't like to make assumptions.
Although it isn't universal I don't backup to local drives at all, I use a fileserver for all backups, this takes local storage completely out of the equation. If the local storage dies I STILL have the backups. The whole drive array could die and I would still be able to rebuild..
CEWII
June 21, 2013 at 12:57 pm
Elliot,
The other week I found a database with no backups at all and the 1.7gb database had a 120gb log file because it was in full mode...remote storage is asking too much 😛 afaik we only do networked backups for 1 server and thats only because it's out of room 😡
Not sure what RAID setup the main 2 sql server environments are using but judging from the others it seems they use 2 RAID collections then do virtual partitions to make sure backups are on a "separate drive" 😉
June 21, 2013 at 1:06 pm
Dird (6/21/2013)
Elliot,The other week I found a database with no backups at all and the 1.7gb database had a 120gb log file because it was in full mode...remote storage is asking too much 😛 afaik we only do networked backups for 1 server and thats only because it's out of room 😡
Not sure what RAID setup the main 2 sql server environments are using but judging from the others it seems they use 2 RAID collections then do virtual partitions to make sure backups are on a "separate drive" 😉
I have checks on all my servers to verify that all databases have a full backup at least in the last 25 hours. My maintenance plans do log backups on all (full recovery mode) databases every 20 minutes, if the job fails, I know to check.. Remote storage was easy to push for me, you can lose a server completely but not lose its data. As a further justification the question becomes how long can you be without this data before it becomes business critical? If it is unavailable for 6 hours, 12 hours, 24 hours, 2 days, 3 days, a week? Some systems that wouldn't make a huge impact but my critical systems I can't have down for even 6 hours. YMMV..
As far as partitioning the raid collection it isn't really on different drives, there is even an argument that head movement to the separate areas of the drive to read and write from the different partitions could have a negative effect on performance.
CEWII
June 21, 2013 at 1:21 pm
Yeah the ";-)" signalled a joke 😛
No reason to cause waves about it although they're throwing up like 50 of these servers that dont seem to do much & all having SE licenses :s I guess it's the one department just using up their budget though. The priority here is Oracle (everything on SAN with netbackups).
We do get alerts for failed jobs though it just turned out that the one I mentioned will be commissioned now (who knows how long it was sitting idle before).
Also: there had been no jobs set up on that server to raise alerts with anyways 🙂 there had been an alert months back about the disk being 95% full then someone removed an 800mb file and nothing came up again (until an issue on the C: made someone mention the other drive to us)
June 21, 2013 at 1:27 pm
Dird (6/21/2013)
Hi,At work today I noticed that most of our SQL Server environments have the same setup:
2 disks in RAID 1
4 disks in RAID 5
The database config also follows this format:
OS s/w & backups on the RAID 1
db s/w, mdf & ldf all on the RAID 5
Wouldn't it be better for performance if the OS & logs were on the RAID 1 instead of the logs being on the same disks as the datafiles? I guess the split was decided to ensure the backup were on a separate RAID collection but was just wondering~ it's not as if these servers are ever pushed much
I personally would not push your OS and logs onto the same disk. The OS is usually where the swapfile is, and that can get pretty messy. I also personally try to keep the OS drives as clean as possible so if/when there's a problem I can just ghost the last backup into place without affecting the data. I would, however, install SQL Server software into the OS drive and put all the system databases onto the RAID 5.
Backups to the OS drive probably aren't a huge deal (just keep aware for swapfile interference) but that's probably not the best place to keep them. You've got a relatively tight system drivewise, though, so really it's a matter of 'making do' then best practices. If you notice nearly no activity on the OS drives and you're running into significant I/O pressures, then moving the logs might make sense. I wouldn't advocate it personally though unless I could literally come over, hop on the box, and start doing benchmarks. It's typically a bad practice except in very unusual circumstances.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 21, 2013 at 3:07 pm
While performance could be adversely affected, possibly severely, the really big problem, at least for a DBA, with data and logs for the same db on the same drive is recoverability. If that drive fails, you've lost everything, and the db must fall back to the last full backup only.
If the data and log/log backups are on separate drives, you can recover your db to point-in-time, or at least very close to it.
Note that db1 data and db2 logs could be on the same drive, with db1 log and db2 data on a separate drive, and not impair recoverability. I don't object to that as much as some DBAs, given the proper RAID on each drive; with the size of drives and number of files on them nowadays, I think some of the older objections to data and logs on the same drive are no longer really valid.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 21, 2013 at 3:13 pm
Seems there's no real issues then Scott since the majority of the databases are in SIMPLE anyways :hehe:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply