Storage for data and logs
I am planning on doing database mirroring using two (2) servers for each instance and a SAN to store the data and log files for both the primary server and mirrored server. How do I arrange the SAN 4 Physical Drives?
My options are:
·2 Raid 1 Mirrors giving 250 GB to each SQL engine – This though has both the transaction logs and data on the same physical drive even if we split it up further into logical drives
·A Raid 10 - The transaction logs and data can be on separate drives
·A Raid 5 using the 4 Drives. (How SQL will see these drives I’m not sure when it’s 2 SQL engines)
· Or I could get a 5th drive and have a mirror set for transaction logs and a RAID 5 configured for the data.
[1397 byte] By [
Nigel] at [2008-1-1]
Why use mirroring when both the primary and mirror DBs are just going to be on the same hardware device? Doesn't that defeat the purpose of mirroring? We use mirroring to avoid a single point of failure, but if all your data is on the same SAN device you have a single point of failure again...
As for the setup of HDDs, this depends on your application. If you have a low transaction log creation rate and not a lot of IOs then it doesn't really matter. If you generate a lot of IO intensive queries, you should make sure your DB drive is as fast as possible, usually with a big RAID5 drive over lots of disks. If you have a lot of writes (and thus a fast log creation rate) you should also make sure your transaction log files are on a seperate drive (usually RAID1) since log writes are largely sequential while query reads on the DB file is mostly random.
Top end systems will usually have the DB on a large RAID5 set (often with dozens of disks to improve the Random IOPS, even though most of the drive space is unused) and with a seperate RAID1 set for the transaction logs. The transaction log drive set doesn't have anything else on it to keep the sequential write rate high.
