Hi
I have read a lot of posts re: advice on disk configurations for SQL Server Implementations on SANs. They seems to be quite out dated and the technologies behind SANs have improved a lot over the years, so I would like to start this new thread which will hopefully be helpful and benefit others as well.
The system we are trying to configure is as follows;
NAV : 2009 R2
SQL : SQL Server 2008R2 Standard Edition
Server : Win 2008 Enterprise
Users : 110
DB Size : Approx. 450GB
we are planning to migrate our NAV database onto a new SAN. Configuration of the SAN is as follows;
Drive Array 2: RAID6 - 20 600GB 15K SAS Drives with hot spare drives
Drive Array 3: RAID6 - 2 100GB SSDs with hot spare drive
Drive Array 4: 4 600GB VAULT 15K Drives used as a failover mechanism
Configuration for the SQL Box is as follows;
Drive C:\ - RAID 1+0 Local Drives (2 physical drives) for OS/Installation Files etc
Drive D:\ - RAID 1+0 Local Drives (4 physical drives) for SQL Tempdb
Drive E:\ - RAID 6 on SAN for SQL Data Files
Drive F:\ - RAID 6 on SAN for SQL Log Files
Drive G:\ - RAID 6 on SAN for Backups
Drive H:\ - RAID 6 on SAN for SQL Transaction log backups/log shipping etc
The issue I have is I’m not getting separate physical drives allocated on the SAN for our SQL Data and SQL Log files. They will be split between all the drives on the SAN. SAN is not dedicated to SQL/NAV and our IT is planning to use this for Exchange/ Datawarehosues etc.
I explained the recommended disk configurations for NAV (separate physical drives for tempdb/SQL data/SQL Log) for EMC guys but they are advising us that the above RAID 6 configuration is the best configuration for SQL and they never had any issues with other clients etc.
When I implemented SQL and NAV before I’ve always used local disk arrays with RAID 1+0 configurations separate disks for tempdb, SQL data and logs.
Please could you shed some light and share your experience on pros and cons of a configuration like this please as I have not had much experience working with SAN configurations like this.
Would you think I should press on and get separate spindles allocated to SQL tembdb, data and log files ?
I hear that “…configuration is the best configuration for SQL…” statement all the time. My response is “best for SQL doing what”. SQL is not a “one size fits all” solution. How you configure servers has a lot to do with how you intend to use it. The requirements such as “RAID 10” and separate log/data drives are not special NAV requirements. This is how you configure transaction processing systems with SQL. Any system of the same nature has these same requirements.
I work with a client that runs a similar size NAV database on a EMC CX4. They’ve been on this system for about 3 years. The SAN supports their entire network, but we insisted on having dedicated disk for NAV. They listened and have been very happy with the performance. EMC is hard at work trying to sell them a VNX.
When it comes to configuration, details matter. Do you need dedicated data drives? Well that depends on what else the drive would be doing if not dedicated. You tend to dedicate disk so you have a dedicated resource and are not inpacted by other processes.
In terms of the transaction log, that’s really a different animal. You want a dedicated spindle set here. At least for the live database IMHO.
RAID 10 is recommended for data as it is the best at supporting the small random I/O nature of transaction processing databases. RAID 6 is similar to RAID 5 but with an extra parity bit. RAID 5/6 is better suited to large sequential I/O.
Remember that ultimately the client is going to make their own decision. After all it is their money. You can only provide advice and guidance. But you can’t make the decisions for them.
You are in a difficult position, but one that is solvable. The database and server you have put you at that cross road between big and very big, where the decision of what RAID to use becomes complex in terms of value for money.
There are a lot of questions to ask, and the answers to those questions will require a lot of real time analysis. Since you already have a working system, you should do as much analysis of what you have before you start designing the new configuration.
Starting with the SAN you need a lot more information that what you have given here. The RAID6 configurations don’t make a lot of sense as written, but maybe with more information they will. For example how do you get RAID 6 from 2 SSDs? RAID 6 needs a minimum of 3 drives. Also how is the break down of the 14x2T and 20x600G drives, and why not just have SAS drives? Is the cost benefit of NLSAS really worth it in the long run? Or is it a space issue? Its not ideal to mix different performing RAIDs in the same box like this. Maybe think of a separate low end box for the Large slower storage?
Also although its not clear I assume the NAV SQL server is a physical box rather than a VM. BUIt what about the other servers? Are they also physical or do you also have some virtual machines? If so then you need to address how those are accessing the SAN, and how the LUNs are created (by the SAN or by VM).
Then there is the question of the virtual RAIDS and volumes created for the SQL server, Which part of the SAN are they connected to and why? Is there an advantage to creating multiple LUNs or should they be just one?
In the end though, your biggest issue is going to be that which BBrown pointed out. “…configuration is the best configuration for SQL…” It is really hard to convince the hardware providers that SQL is not a one size fits all product. You are going to need to show them real world examples and not links on the internet. But once you can show them what is needed, provided they are open minded and intelligent, then you will be able to work with them to get a solid and fast server.
And on the “Performance per $” discussion, you are at that point where RAID 5/6 can be faster than RAID10, so don’t be deterred. It is obviously a lot more work up front to make it work, but it can work if done properly. And the long term benefits are numerous.
Chaminda, also don’t think of any of this as right or wrong or opinions. IN the end as with everything in business it comes down to Risk vs reward vs cost. It is important to balance all these to come up with the best solution.
There is never a right or wrong, and I am glad to see that you are investigating all possibilities with an open mind [Y]
PS in my earlier post I assumed you were the end user, but I think actually you are the partner. Of course this is a very different situation, because for you it is very difficult to recommend something to the customer that is different to what they learn from Mr Google. Also it will be difficult for you to tell the customer that they will need to pay money for all the analysis and testing needed. It puts you in a difficult position between simply saying “this is the recommendation” and taking the bold step to get the client on board and work with them to find the best solution. Not an easy task, but keep at it you will get there and find the best solution.
Thanks a lot for your advice. We are actually an end user [:)]. As you’ve said I have a mountain to climb to get IT and business provide me with whats best for NAV . It seems that business is not going to change the current configuration and we’ll be going ahead with hardware manufactures advice. It’s all to do with space issues and the costs/resource involved in configuring things again.
I have however provided my recommendations a) at least 20 local disks array b) separate disks on SAN
The SQL box is a physical box and and it’s not a VM. The LUNs are created by the SAN. The point you made about the number of disks and the RAID configuration - this is what I was provided by IT and I’ll have to check with them again [:D]
I would however like to monitor the performance with this configuration and like to ask how do I approach monitoring performance ? Could you recommend any tools which I can use?
In addition, what would be the best way to compare the performance against ? Is there any standards available ?
I’m thinking of generating performance reports based on our current configuration (we will be using this as a test system) and comparing them against the new configuration.
SQL Perform Analysis tool is generally the best option. It is possible to analyze your current configuration in a live environment and see where the performance bottle necks are and then get a better picture of how the system is going to perform. The tools are not free, but as you probably know in this industry, you get what you pay for [:D]