on 2017 Jun 27 8:05 PM
Hello,
Are there any "generally accepted" best practices for the location of the database Transaction Log file? I've seen recommendations that it should be placed on different media from the database.
1) If this is recommended, are there any "cons" to this? 2) Also, how do I indicate to the server that the Transaction Log file is "somewhere else"?
Thank you.
Request clarification before answering.
IHMO, one disadvantage is the different location itself, i.e. you do not have the database files in the same directory anymore, which is inherently more complex and makes it possibly more difficult to copy them to another location (say, for testing).
When using RAID systems, SANs or virtualized storage (where you possibly do not know about the physical location at all), I don't think it's that important to use a different location for db and log: When configured accordingly, the files may be mirrored by default, thus protecting against failure of one physical media. Additionally, when db and log are stored on different logical volumes, they still may be placed on the same physical storage in the end. - So the challenge is to find out where and how the files are "really" stored and whether the storage system itself already makes use of redundancy (different controllers, links, disks, ...).
So I think that general recommendation is primarily meant for databases with storage on classical, non-mirrored, physical storage devices. For other configuration, I think "it depends".
More in this similar old discussion:
Is "separate physical drive" no longer important on RAID, SAN and/or NAS?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I recently had a discussion about this with an system engineer who's opinion I respect. We are virtualized on VMWare, connected to the SAN via iSCSI. The gist of it is that for each iSCSI LUN connection that a host has, it has a limited number of command queues. In theory, putting the data file and transaction file on different LUNs would yield a performance benefit, but neither of was convinced it would be significant.
It would be really good to get some expert updated advice on this whole subject from SAP. SAN configuration guidelines in particular would be very helpful. We see wildly varying performance on apparently similar set-ups.
It's also not at all clear whether the calibrate IO features understand modern disk systems or not.
Yes, more advice would be helpful. In our case it seems to stem specifically from VM configuration. We install the software in the environment we are provided and as you stated, we see wildly varying performance on apparently similar setups.
I am often asked what the system requirements for our software are and expected to rattle off cpus, cores, RAM, and disk numbers. The reality is that while our software's needs are quite minimal, there are definitely some VM configurations that slow it down more than others without any apparent common factor.
User | Count |
---|---|
60 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.