December 14, 2011 at 11:29 am
I want to make sure I don't cause my data files and transaction logs to compete for resources. As such on my current setup I have the data files on one hard drive and the transaction logs on another.
Soon we'll have a SAN available for the data for this server. I want to move the data files to the SAN and keep the transaction logs on the largest drive left on the server. The other proposal being made is to move both sets of data to the SAN. I believe they're suggesting that each will get its own LUN.
I don't know enough about SANs to know if this is a reasonable suggestion for SQL Server. Could there be throughput problems? Would there still be resource allocation problems? Is this best practicce or should I stick with the data on the SAN and the transaction logs on the local hard drive?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 14, 2011 at 11:39 am
Stefan Krzywicki (12/14/2011)
I don't know enough about SANs to know if this is a reasonable suggestion for SQL Server. Could there be throughput problems? Would there still be resource allocation problems? Is this best practicce or should I stick with the data on the SAN and the transaction logs on the local hard drive?
Depends on if the LUNs are on the same physical spindles, but SANs cache and help a lot in dealing with that. If this Iron is brand new, you'll probably still be better off then a couple of local spindles. Best practice is hard to determine still, unless you're really familiar with your SAN equipment. Under most circumstances, your local RAID in the U-Rack is for the OS and swapfiles, and all your datafiles are up on the SAN somewhere.
Just make sure they configure your LUNs for 64k read/write transfers, you'll thank me later. 🙂
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
December 14, 2011 at 11:48 am
I agree to what was already said by the Craig.
About leaving the Log files on the Largest drive on the Server (keeping it local), is that drive fault tolerant?
The reason being, with most SANs (or at least with those that I have worked on) have redundancy to ensure we are good in case of disk failures.
Unless the SQL Server is a non critical or test box, I have always had both Data and Log files on SAN storage and had no issues..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 14, 2011 at 11:48 am
Evil Kraig F (12/14/2011)
Stefan Krzywicki (12/14/2011)
I don't know enough about SANs to know if this is a reasonable suggestion for SQL Server. Could there be throughput problems? Would there still be resource allocation problems? Is this best practicce or should I stick with the data on the SAN and the transaction logs on the local hard drive?Depends on if the LUNs are on the same physical spindles, but SANs cache and help a lot in dealing with that. If this Iron is brand new, you'll probably still be better off then a couple of local spindles. Best practice is hard to determine still, unless you're really familiar with your SAN equipment. Under most circumstances, your local RAID in the U-Rack is for the OS and swapfiles, and all your datafiles are up on the SAN somewhere.
Just make sure they configure your LUNs for 64k read/write transfers, you'll thank me later. 🙂
The're not new, but there'll be a new one coming up and we'll transfer to that.
So separate physical spindles for the data and log files? And those should be better than log files on a local drive? What if we only have one fiber cable connecting the server to the SAN?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 14, 2011 at 11:52 am
Bru Medishetty (12/14/2011)
I agree to what was already said by the Craig.About leaving the Log files on the Largest drive on the Server (keeping it local), is that drive fault tolerant?
The reason being, with most SANs (or at least with those that I have worked on) have redundancy to ensure we are good in case of disk failures.
Unless the SQL Server is a non critical or test box, I have always had both Data and Log files on SAN storage and had no issues..
Fault tolerance is a good point, thanks.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 14, 2011 at 11:52 am
I can't tell you anything that I don't think you will already know, but here are some points that you should be sure to consider.
It depends on how the SAN is configured. If the two LUNs inquestion don't share any drive(s) there shouldn't be any conflict. If they do there will be and it's a bad configuration for SQL Server performance.
As well as conflict for discs, think about total throughput. Whatever your SAN channel is, it will have a limited throughput. The limit may be quite high, or it may be quite low. You need to check the numbers against your throughput requirement.
Then worry also about the fact that now the storage is out on a SAN than SAN may be being used by many other things. Do any of those other things store data on drives used by any of the LUNs assigned to SQL Server? If they do, that's a conflict which you may not have had with your local discs.
When you take account of the throughput required by those other things, is there enough capacity left for you? (Some SANs share a single access channel between all their users, which might become a bottle-neck; but some provide separate paths - if you have the latter type you probably don't need to worry about this point.)
Tom
December 14, 2011 at 11:58 am
I'm accustomed to SANs with multiple fiber channels, so can't speak to that aspect of it.
I've been using servers where both data and logs are on the SAN for years, never had a problem with it, so long as the SAN is configured correctly. But I'm also not dealing with high-throughput systems. Dozens of transactions per second, not thousands of transactions per second. Magnitude might change that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2011 at 12:09 pm
Stefan Krzywicki (12/14/2011)
The're not new, but there'll be a new one coming up and we'll transfer to that.So separate physical spindles for the data and log files? And those should be better than log files on a local drive? What if we only have one fiber cable connecting the server to the SAN?
I have found under most circumstances Sharing fiber and spindles on a SAN that is intelligently set up to outperform local RAID 95% of the time. The times its not is when it WAS intelligently setup and then someone made a mistake down the road forgetting WHY it was setup that way. If the Fiber throughput is actually a concern, you've got more problems than just a little SAN optimization, and you've got a dozen U of drives directly wired to the SQL Server in question.
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
December 14, 2011 at 12:16 pm
Thanks everyone, these responses plus things I'm finding on-line, like articles by Brent Ozar are getting me to the point where I can at least ask intelligent questions and make some suggestions.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 14, 2011 at 12:17 pm
Stefan Krzywicki (12/14/2011)
Thanks everyone, these responses plus things I'm finding on-line, like articles by Brent Ozar are getting me to the point where I can at least ask intelligent questions and make some suggestions.
You are welcome and good luck in setting up your environment..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 14, 2011 at 12:33 pm
You might want to have a look at my question on a similar thread.
It's not as much focused on the location of the file itself but more on the SAN config in general. The links provided were really helpful.
December 14, 2011 at 12:36 pm
LutzM (12/14/2011)
You might want to have a look at my question on a similar thread.It's not as much focused on the location of the file itself but more on the SAN config in general. The links provided were really helpful.
Great, the more reading I can do the better. I missed this thread when I searched.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 16, 2011 at 3:28 pm
Whatever configuration you decide on...test the crap out of it if possible. From my experience, once your hardware is setup and running it's next to impossible to get someone to change it. I have a new box that our systems group recently setup that I'm going to beat on next week. They have a tendency of setting up 2 disks on one lun and washing their hands of it...:crazy:
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply