December 3, 2007 at 7:40 am
I'm a DBA in a large organization. Recently I was working on requirements for a new SQL Server with various groups in IT. I experienced a problem with the storage group that manages the enterprise SAN environment that prompted this post.
They provided the required amount of disk space I needed, but when I asked "How many disks?" and "How many different RAID groups?" made up the allocated space, they said that wasn't my concern. The storage group believes those details and others should be transparent to their customers and didn't want to answer the questions.
As a DBA I want to know details of the physical disk so I can configure my data files the best way possible.
When SQL Servers are attached to enterprise SAN environments, are the traditional concerns about physical disk and IO not an issue? Have we reached a point with SAN technology that DBA's don't need to know the underlying physical implementation of the logical drives that appear on the SQL Server?
I'm interested in hearing from some DBA's on this topic. Since I haven't had much experience with SAN environments I'm wondering if my insistence on knowing some of these details is unfounded.
Thanks in advance.
December 3, 2007 at 7:55 am
Not being that familiar with SAN's myself, my answer is an unqualified yes. We are getting a new SAN here, and I expressly asked that the disks used by our SQL Server systems be setup as raid 10 arrays based on several threads and articles I have read on SSC.
😎
December 3, 2007 at 8:13 am
tmc (12/3/2007)
I'm a DBA in a large organization. Recently I was working on requirements for a new SQL Server with various groups in IT. I experienced a problem with the storage group that manages the enterprise SAN environment that prompted this post.They provided the required amount of disk space I needed, but when I asked "How many disks?" and "How many different RAID groups?" made up the allocated space, they said that wasn't my concern. The storage group believes those details and others should be transparent to their customers and didn't want to answer the questions.
As a DBA I want to know details of the physical disk so I can configure my data files the best way possible.
When SQL Servers are attached to enterprise SAN environments, are the traditional concerns about physical disk and IO not an issue? Have we reached a point with SAN technology that DBA's don't need to know the underlying physical implementation of the logical drives that appear on the SQL Server?
I'm interested in hearing from some DBA's on this topic. Since I haven't had much experience with SAN environments I'm wondering if my insistence on knowing some of these details is unfounded.
Thanks in advance.
Ahh...infighting...turf wars...the old story again.
It's a shame to see that kind of attitude. Storage IS one of the main areas a lot of orgs can improve (for relatively cheap) on the performance of their SQL Servers.
SAN technology is not so far along that you will be immune to storage-related issues. As a matter of fact - because of the centralizations, SAN's arguably need to be very carefully set up, continuously monitored, and diligently optimized, or they can easily turn on you and give you WORSE performance than dedicated local disks. If you don't manage to keep the effort spread fairly evenly within your SAN, then the storage processor cache will back up, and start paging requests on the local server.
That being said - I've also been in an organization during the "mindset conversion" process of trying to get your departmental clients to stop thinking in terms of "my physical hardware" and start thinking in terms of the logical volumes/enclosures. That might be what your storage group is trying to deal with, but still - as part of the IT team, and in terms of your job being on the line, you ARE entitled to said information.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 3, 2007 at 8:16 am
I would agree with the previous message as well, the answer has to be a "YES".
I have been involved with the database storage design at our place, and it because of my knowledge of the hardware, which helps me to decide what goes where.
It also means that priorities can be easily determined; i.e critical applications can go on the 15k rpm disks, while non critical ones can stay on the 10k spec.
One thing I have noticed though, it can be difficult sometimes working with System Admins when it comes to this issue, and they always think they know what the best is for the situation.
December 3, 2007 at 8:23 am
YES!
You should know, but that doesn't mean you get to decide. There's a difference and a storage group needs to have control over their storage. Just as you want control over your database. However you should share information with others as should they.
I've argued this before and you have to go to managers with the argument that you need to be able to troubleshoot performance, and know what potential issues you face. They have zero argument for not giving you the information about how it's configured. You might not like the answer and have to argue for changes, but just knowing how it's set up is information that should be available.
December 3, 2007 at 9:44 am
Personally I am convinced that most people that admin SAN's do not understand the technology they are working with and that is why they refuse to answer....
December 3, 2007 at 12:37 pm
It's a yes from me as well. Particularly if the SAN is to be used as a shared repository for other stuff as well - you don't want everything using the same set of disks (for example, imagine something like MS Exchange sharing the same disks as SQL Server - ick).
At the very least, you need to make your concerns heard to the person configuring the SAN. Hell, get it in writing and keep it (email will suffice). If disk I/O ever becomes an issue for your SQL instance(s) because of a bad SAN configuration, you can wave it in front of people and tell them you raised it as an issue right from the start.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 3, 2007 at 3:47 pm
I have been in situations in which if I would have not known about some inner workings of the SAN serplication the company was using I would not have been able to explain some SAN issues that Affected SQL Server indirectly ...
So although should not be your most knowlegeable point you should have access to all the info of what is actually going on...
* Noel
December 3, 2007 at 4:06 pm
Absolutely!! The fact that they refuse to disclose this to you is absurd and counterproductive. Whenever I work with a client that is having performance problems and I find out they are using SAN storage.....instant red flag. Most SAN storage 'experts' do not understand the importance of isolating resources at the PHYSICAL level for database storage. Most believe that they should follow their standard file system storage allocation methods and it bites them in the rear. Go to your manager and get your point accross that you need to know so that you can place your files in the most optimal way. The storage that has been allocated to you should have been done according to your requirements, not the SAN guys preferences.
December 4, 2007 at 2:39 am
tmc (12/3/2007)
They provided the required amount of disk space I needed, but when I asked "How many disks?" and "How many different RAID groups?" made up the allocated space, they said that wasn't my concern. The storage group believes those details and others should be transparent to their customers and didn't want to answer the questions.
...Sounds like an answer by a BOFH 😉 As a DBA you should know all details about the SAN.
December 5, 2007 at 12:08 am
Though I believe its' best to know the details of your SAN an alternative would be to ask the storage folks to provide SLA(s) for storage performance, uptime and whatever else you can think of...
You might have to think about exactly what performance you need from each component of the infrastructure and then figure out a way to consistently measure performance of that particular component but it would be an interesting exercise...
Joe
December 5, 2007 at 2:44 am
my 2pence worth........
of course you need to know - how can you interprete results from perfmon counters like %disk time or avg q. length if you do not know the physical set up of each drive.
I have had storage guys try to tell me I cannot have raid 10 for logs as raid 5 is 'good enough' so I think it is true to say some storage guys do not necessarily understand that database requirements are not the same as flat file requirements. And different raid configurations in a SAN complicates things for them.
---------------------------------------------------------------------
December 6, 2007 at 12:04 am
I've always felt that the DBA and the storage team should be partners. Alternatively, SQL needs to have it's own SAN. Giving a DBA SAN storage on an Enterprise level database without any knowledge of the underlying disk configuration is tantamount to placing a noose around his/her neck. As previously mentioned, you will be unable to troubleshoot performance problems when they occur. More importantly you will be completely unable to plan future growth to explicitly avoid performance problems.
Example: you have a large table (1 billion records) - not much write, but given the reporting requirements there are many indexes on it. How can you plan to isolate indexes and filegroups to different physical disks if you don't know the configuration?
How can you isolate the IO on a table that has a heavy OLTP load from the transaction log, or backup, or other tables?
Do you have isolated storage for tempdb? How many processors do you have, and how many files in tempdb would you like? Would you like to isolate the IO for each one to a sperate physical disk?
In short, without the ability to see what you have been allocated, see how it has been configured, and the ability to reconfigure it going forward according to your needs, you are in trouble.
December 6, 2007 at 2:48 am
welcome to the wonderful world of mystery storage!! Ever since the first SAN arrived I've found performance problems with SQL Server. I got SNIA certified so that I could ask questions from a position of reference ( http://www.snia.org )
In general terms you'll likely have a problem although I have to put in a plug for Hitachi ( HDS ) in the UK who have some very sharp people and in the conversations I've had with the various major SAN vendors only HDS had someone who understood databases and storage with regard to a SAN.
I must make a point here in as much as the storage is not the SAN, the N stands for network and it's wrong to point at your storage array and say SAN !!
So some points:-
Most SAN installs come without any diagnostics or peformance tools so you'll struggle to get performance data if things don't go well.
You'll often struggle to get raid 10 as most SAN storage is sold on the basis of maximising the usage, typically 85% or better which means large raid 5 arrays ( typically 13 or 14 spindles ) which are split down into up to 256 LUN's or more.
High usage means high ntfs fragmentation which helps screw your performance.
Quite often the admins of the SAN know very little about it but are highly protective.
Ever tried telling the IT director their new £500k SAN is rubbish?
Some sans virtualise their arrays so you have little idea of what you're connected to.
shared LUNs will give unpredictable performance
The fabric is also important and although the actual storage may be sold as 4gb fibre you'll likely find that only a few of the available ports are at that speed and most may only be at 1gb
The setup of the HBA's and switches is critical, ideally you'd want a redundant core edge in a reasonable sice environment.
watch how your backups work - streaming all the data out to a media server can sometimes swamp the bandwidth - you'd be amazed how it works if you do the math.
so yes as DBA you need to be able to have a view of the SAN - you performance tool is perfmon counters for io completion times, most other counters don't work, but this one does.
I've a few posts about san storage on my blog http://sqlblogcasts.com/blogs/grumpyolddba/
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 6, 2007 at 8:04 am
You definately need to know all the details. Even on a SAN you need to worry about putting data files, log files, and tempdb on separate physical disks. You want to format the volumes with a different stripe size for SQL Server than you would for a file share. And you definately want to know what other applications will be using LUNS on the same physical disks as the database LUNS.
Every SAN vendor I've seen claims that their gee-whiz technology makes all these concerns obsolete and you can put database LUNS right next to anything else, but they're all lying. Or to be charitable, they think they know what they're talking about but they are wrong. If your SAN admins know the SQL Server recommendations for stripe size and why log I/O is different than data I/O they might be managing your storage correctly, but they have an obligation to tell you the details so you can verify them.
Viewing 15 posts - 1 through 15 (of 55 total)
You must be logged in to reply to this topic. Login to reply