November 2, 2006 at 8:18 am
I am trying to explain to our SAN administrator that I need two areas reserved for my SQL server installations, one for the MDF files and one for the LDF files. He insists that it it is not nessecary as the SAN stripes file data across its disks and providing two small areas is the same as providing one big one. I have explained that the SQL server OS needs to see two different disks to prevent any possible problems fwith logfiles filling up the data area and then possibly corrupting the data. I have aslo used the 'Industry Standard installation argument' to no avail.Does any one have any documentaion I can point him at that will help explain why he needs to give me what I want
November 2, 2006 at 8:30 am
Optimizing Transaction Log Performance
General recommendations for creating transaction log files include:
Vasc
November 2, 2006 at 8:57 am
thanks for the reply
I'm not sure he will buy that, as we have loads of apps and db's on the SAN I dont think the disk heads remain stationary due to the way it stripes data. My main thrust in the argument has always been about data integrity and data recoverability if the log files fill the available space on the drive.
November 2, 2006 at 9:10 am
"if the log files fill the available space on the drive. " ??? you do log backups right?
Vasc
November 2, 2006 at 9:28 am
Yes we back up nightly and I do monitor space and truncate logs when they start getting overly large ,
but we also run an extraction from OLTP to OLAP warehouse which can create 20GB growth in the transaction log. On a database with other things going on at same time I dont want possibility of data corruption.
November 3, 2006 at 4:33 am
Hi,
SQL Server requires at least one data file and one log file (.MDF and .LDF)
There is no advantage creating multiple log files as the SAN is managing which physical disk the log file is mounted on, you have no control which it uses, so therefore even if you do create two .LDF files they may still be mounted on the same physical disk.
Peter Gadsby
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 3, 2006 at 7:38 am
AS far as the SAN goes it is in your SAN administrators best interest as well as DB admins to manage SQL on the SAN correctly.
Don't think of individual hardrives. Everything in the SAN world is virtual.
Most SANs are configured with FIBER drives as well as ATA drives.
ATA is a bit slower and is ideal for archival items such as log files.
Fiber is faster and great for live database files.
Even more important is the type of RAID configured for each.
A mirror set will have greater access speed than will a RAID 5 set.
Basically, whatever method you use, you want the SQL server to be the only application on that drive. There are as many opinions on this subject as there are IT people.
Your goal is to get the fastest disk access speed for SQL.
November 3, 2006 at 8:53 am
I am intrigued now.... Why will multiple files be better than a single file? If the SAN controller can put the different bits of data on multiple drives where is the benefit of manually configuring multiple files?
I thought the objective of creating multiple files, is to allow the files to be on different physical disks, and as you have no control of the physical disks on a SAN, how does multiple files help?
I'm probably completely wrong, I would just like to understand the reasoning....
Cheers
Peter Gadsby
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 3, 2006 at 9:02 am
I don't want multiple files. I want a seperate drive or volume for OS, datafiles and log files. So for a server with multiple databases the os drive would hold windows and sql software, data drive holds all of the mdf files and log drive holds the ldf files. A fairly standard solution recommended by Microsoft
November 3, 2006 at 9:44 am
Ah ok, sorry I'm having a blonde day 🙂
I'm still interested though, what would be the advantage of having multiple drive/volumes? There would be no performance difference between having one or many drives?
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 3, 2006 at 10:11 am
Keeping data and logs on different disks/volumes has a performance advantage when using local disks on your server as data read and write operations and log read and write operations are kept seperate and so the amount of time seeking the right clusters on the disk is minimised, less disk thrashing and less fragmentation.
What I am more concerned about is preventing data corruption by filling the available space on a disk with log info during an intensive process where the data and log files reside on the same disk. I know its fairly unlikely when using the SAN, but I've seen it happen on an old DB using local disks where we needed to rebuild the server and then restore the DBs from back up and I dont want to risk it on my new data warehouse as the restore process would be horrendous .
November 7, 2006 at 5:32 pm
The historical reason, as far as I recall, is ...
If disk fails on which data resides, DB can be rebuilt to point in time just before failure.
If disk on which log resides fails, then simply run full backup ASAP after disk is fixed.
This reduces chances of single point of failure - if data and logs on same drive when it fails, then cannot restore except to last full backup.
Similar logic for putting backups on a 'third' drive (especially as tapes may not always be 100% reliable).
When you use a SAN, then the SAN provides redundancy to varying degrees depending on striping, mirroring, RAID and it is a good argument as to whether they need to be on separate logical drives. The argument for drive space filling is a good one. Logs may cause drive space to fill and vice data file growth too, so I think it adds safegaurd to separate them onto different drives for this reason. (Some SANs may provide auto-grow to resolve this issue.)
Depending on how critical the DB is, a SAN with RAID 10 (RAID 5 mirrored) and with hot spares will provide a good safeguard against loss of data AND downtime. If the mirroring is onto separate disk sets on SAN (usual best practice) then this is equivalent to putting data and logs on physically separate drives. (there would have to be 4 disk failures before any downtime or data loss). If only using RAID 5 then using separate disks (luns) on separate drive sets (if available) is best you can do. (Again 4 disk failures would cause data loss - VERY unlikely).
🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply