August 26, 2011 at 8:40 am
Hi Guys/Gals,
We're migrating to new servers shortly and I'm in the process of setting up and finalizing a new SQL Server 2008 instance. While I've never done any SQL Server migrations before, I'm no stranger to moving databases around to other SQL Server instances.
I have all of my migration steps planned and tested and everything looks to check out OK, but I'm seeing on our old production server that the LDF is located on the C:\ drive where the data is on the D:\. I've configured our new server to have both the MDF and LDF in the same locations.
For the sake of industry standards, does it matter if the LDF and MDF reside in the same folder? I can see the benefit in the case of a failover, but we already have a failover in place (VM Snapshots and Clustering). Or maybe someone can show me the path to the "perfect" SQL Server configuration 🙂
August 26, 2011 at 8:53 am
Performance and recoverability.
With the log file on a separate drive, if the mdf drive fails you can still back up the tail of the log so that you lose no data. With both on the same drive, if the drive fails you're restoring to last backup + log backup and losing anything since.
Clustering is of no help against drive failures (shared storage) and last time I checked, restoring a VM from a snapshot with SQL in the VM wasn't supported by MS. Could be wrong on that point.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 9:02 am
Only thing I would point out in this case is if you have log file in the drive of the OS and for some reason the log file grows too large and fill up the C-Drive, you could end up in a very bad situation as well.
There fore in your case, you have a small problem. If you are doing log back ups regularly, you should not get into the situation I described above. But if you put the log file in the same drive as the data file, you will face the situation what Gail pointed out.
-Roy
August 26, 2011 at 10:01 am
depending upon your hardware and the application ( database ) requirements sharing could be very bad as the disk operations require sequential writes for the transaction log, this is why it's best practice to have the t-log on its own drive.
If you slow the t-log writes then your whole application will slow during data inserts/updates.
However depends upon what your server hardware platform actually is.
As already mentioned clustering only protects the physical server hardware not your databases(s)
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 26, 2011 at 10:40 am
I appreciate all of the feedback guys.
I haven't begun to think of performance since we're a small user base (25-30 users). I figured if we're going to have a clean slate to start from we're going to do it to industry and performance standards right off of the bat.
Thanks again for the feedback. If you have any other suggestions performance wise, I'd be more than greatful.
August 28, 2011 at 6:36 pm
Hello,
You can use the built-in Policy Based Management and import the Best Practice Policies from Microsoft and run that against your instance to se where you are failing against industry stand best practices.
The policies are XML files that are located on the disk of the same instance you installed SQL Server... on a x64 machine they would reside here...
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033
August 29, 2011 at 2:42 pm
If the drives are logical and on the same physical set of disk then makes no real difference.
August 30, 2011 at 4:26 am
I often hear that the transaction log should be placed on different drives for performance reasons, and I tend to say that it's wrong. Typically, the SAN limitation is the number of Iops it can deliver, and thus splitting log and data on two different luns does not give you any performance benefits. If you could manage to split them on two different buses (HBAs), you could see a performance benefit though, depending on other bottlenecks in your SAN.
I also hear that the transaction log should be placed on a different LUN than the database files, but that does not necessarily improve reliability either, as they could possibly share the same set of disks. The Transaction log and the database files should be placed on different physical disks, or in a SAN that would be a storage group. Or, even better, they should be placed on different storage arrays, preferably on different fabrics.
Long story short, the transaction log and database files should be placed on two IO subsystems sharing as few components as possible.
August 30, 2011 at 6:09 am
with respect - the original post mentioned C: and D: drives, doesn't sound like a typical SAN.
Apart from which it all really depends upon your make of SAN, the segregation is still valid in certain senarios.
Before anyone posts that the SAN is not the storage, yes I know, there's just not the terminology to differentiate and most people don't actually have a SAN ( storage Area Network ) they just have shared storage which isn't the same thing at all.
For instance I have a dedicated 3PAR T series for one of my database servers ( one application ), now a 3PAR T Series is really known as a SAN but I'm actually using it as DAS, but even if I attached a couple of other servers to it I'd still not really have Storage Area Network.
Anyway regardless of anything the head for a given disk can only be in one place at a time so sharing can cause contention, I suspect, and I apologise if I'm wrong, this is likely to be a server with internal disks, it didn't sound to me if it even had a DAS let along a SAN
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 30, 2011 at 6:59 am
Guess I should have caught it since the C and D drive was mentioned, what caught my attention was the mentioning of clustering, which clearly does need shared storage in terms of a SAN (as long as you're running on Windows Server 2008 or later). Most likely it is the virtualization that is clustered then. Still, what he asked for was the "perfect" configuration, where I sincerely believe that I made some valid points. What I should have mentioned since I wrote about SANs, was multipathing for redundancy and/or performance. If I'm right about this, it may even be an option to present LUNs directly to the VMs.
Still, the best practice to reduce likeliness of losing both the transaction log and the data files at the same time, is to have them share as little hardware components as possible. Different HBAs/Controllers/Busses/Cabinets/Disks. But of course, often there will be a trade off, but you really cannot make a good trade off if you don't know your options, and the consequences of your choice.
August 30, 2011 at 7:07 am
Just to clarify, we are running a SAN. I forgot to mention that
It's my first realm into complete virtualization with SAN storage. We're a small company that looks to be expanding sooner than expected, so I will take all of these suggestions into consideration for the long term.
Once again, thanks for the input everyone. I'm still a fresh DBA trying to learn the ropes 🙂
August 31, 2011 at 4:09 am
well you've introduced the two biggest performance bottlenecks known to man for SQL Server 😀
I hate virtualised SQL Server and in tests I was able to outperform a very expensive SAN with an array of sata disks on a home server.
In terms of "real servers" our dev box, a dl580 with one DAS array can still outperform a dl580 attached to a san with 4 times the spindles and 16Gb bandwidth compared to the 3GB sas on the dev box. ( all with 15k sas disks in raid 10 )
so in answer to your orginal question: Shared resources can degrade performance badly. Without mentioning manufacturers, in one instance defining luns of dedicated spindles for mdf/ldf/tempdb/backups made vast performance differences, for another manufacturer the difference was marginal, however sharing the disk pool with others gave inconstent performance. Sorry I can't really be more specific than that - both these sans were fibre attached - 1gb isci is really a bad idea for sql server btw. - you need at leat 4gb hba bandwidth to your storage.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply