Suggested setup for MS SQL storage

Status
Not open for further replies.

MilesB

Dabbler
Joined
Nov 26, 2014
Messages
16
Hi

I work at a very large financial institution and am assembling a proof of concept SQL environment. Our IT department has a history of building expensive low performance systems. I obtained some POC funding and have begged/borrowed some hardware and am now ready to build. I am intending to run MS SQL 2014 on a separate machine and use FreeNAS to manage the storage. My goals are max IOPS, min latency, and absolutely must have adequate redundancy and encrypted storage. Snapshots are not really a concern for me. SSDs will be used in the SQL machine for index storage but would not be suitable for cache in the FreeNAS table storage. My FreeNAS hardware:

ex-Dell T5500 motherboard with 2x Xeon X5650 (hex core 2.66-3.06GHz)
144GB DDR3 ECC RAM
LSI SAS 9201-16e connected to 3 Chenbro 23601 expanders
Each expander connected to 24 Toshiba 7200 rpm 4TB HDDs = total 72 x 4TB HDD
Chelsio S320e-SR 10gbe card to connect to identical card in SQL Server with twinax cable

I am looking for suggestions on pool/general configuration and whether to use iSCSI or NFS. I am leaning towards 36 mirrored pairs and iSCSI. If I understand correctly, this will give me read IOPS of 72 disks and write IOPS of 36 with capacity of 36 (adequate). Please let me know if I'm wrong and any other advice you may have. I'm very new to all of this but am trying to get this technology accepted in my workplace so that we can expand.
 

MilesB

Dabbler
Joined
Nov 26, 2014
Messages
16
As I said, I am running this as a proof of concept. I don't have money to spend unless this works.

Do you have any actual advice or information beyond "common sense"? Any technical reason why I can't run FreeNAS? Anything concrete to contribute instead of just being condescending to the new guy?
 

anodos

Sambassador
iXsystems
Joined
Mar 6, 2014
Messages
9,543
I think yours is probably a case where you should look into a truenas appliance. That's what I would do in your place. When your job is on the line, support contracts are worth their weight in gold. Especially if you're dealing with something as important as your storage. Saving $X,XXX sometimes just isn't worth the risk.

http://www.ixsystems.com/storage/truenas/
 

MilesB

Dabbler
Joined
Nov 26, 2014
Messages
16
Does anyone in this community actually want to talk to me about FreeNAS or encourage me to use FreeNAS?
I am trying to build a PROOF OF CONCEPT to get this technology accepted in my workplace.
Assume that I am able to worry about support myself and understand the benefits of support contracts.
You don't know what you would do in my place, because you are not in it. I do not have funding. I am building a POC. I don't know how many times I have to say that. Either I use FreeNAS or we go back to using EMC.

Would someone just talk to me about FreeNAS instead of why I should not use FreeNAS?
 

zambanini

Patron
Joined
Sep 11, 2013
Messages
479
freenas is a testing environment for truenas you are just on the wrong train. it is just for your kind of situation. you can spent a year with freenas and you will still not understand zfs fully. your complete project does not make any sense. you shoot yourself in the foot and then you will complain. that is far away from beeing smart.
 

MilesB

Dabbler
Joined
Nov 26, 2014
Messages
16
What do you not understand about me saying I cannot spend money on TrueNAS unless I run a successful proof of concept?

Are you telling me FreeNAS is not capable of storing files and will corrupt my data?
 

mjws00

Guru
Joined
Jul 25, 2014
Messages
798
No. They are telling you it will be difficult and expensive to get great performance. I really don't see what all the fuss is about a proof of concept server.

IT IS A LAB. There is no risk here folks.

I'll point you in the right direction. Give me a sec.
 

MilesB

Dabbler
Joined
Nov 26, 2014
Messages
16
Difficult I don't mind. I build very complex software for a living. I would just like someone to contribute some of their learnings or at least link me to some blogs etc of people who have done similar. That is what I figured I'd get with a "community". That's always been my experience using open source or community contributed projects in the past. Otherwise I just have to start at the beginning and figure it all out myself which costs me time.
 

anodos

Sambassador
iXsystems
Joined
Mar 6, 2014
Messages
9,543
No. They are telling you it will be difficult and expensive to get great performance. I really don't see what all the fuss is about a proof of concept server.

IT IS A LAB. There is no risk here folks.
My reading comprehension was failing. :D

Probably a side-effect of staying up past my bedtime. I'd recommend looking through some of @jgreco stickies regarding NFS, iscsi, etc. There are some caveats in using zfs as an iscsi target, but he explains it much better than I ever could.

I try to avoid windows + NFS.
 
Last edited:

MilesB

Dabbler
Joined
Nov 26, 2014
Messages
16
Thanks, that gives me a great place to start.

Is there anywhere I can see some quick info on pool design, IOPS calculations, block sizes explained etc?
I also don't favor Windows and NFS but have never had a problem with iscsi. The Chelsio cards say they are "optimised" for iscsi but I have no real world opinion on what that may be worth.
 

mjws00

Guru
Joined
Jul 25, 2014
Messages
798
Here is the the quick and dirty for Proof of Concept. For production, seriously consider talking to iX... that advice is sound and well intentioned.

You want to set up your pool as striped mirrors. With 72 spindles it should be performant. Depending on protocol nfs or iscsi with sync you may need a very fast slog. This is a COW system so you will have to keep your block device very under utilized, think 50% max use, or suffer an extreme performance hit due to fragmentation. The magic is in maxing out RAM so you hit your ARC as much as possible. SQL 2014 also has some goodies under the hood that you are going to want to take advantage of.

I don't know that card well, but I am pretty sure someone used one here. Chelsio is pretty much the top 10GBe vendor so likely a great choice.

Unfortunately it's a pretty tall order for a walkthrough and WAY outside the realm of general user experience.
 

MilesB

Dabbler
Joined
Nov 26, 2014
Messages
16
I'm aware of 2014's new features but we are yet to experiment much. I am concerned with both machines caching the same data. That's one of the things we'll be investigating.
I plan to investigate the consequences of filling a block device and make recommendations on how data should be moved from a zfs volume once it moves from a high read/update scenario (this fiscal year) to low update high read (past years).
 

MilesB

Dabbler
Joined
Nov 26, 2014
Messages
16
I should note that I am worried about using an ssd for the slog. I'm aware that the ZIL is only read for playback after a crash (like a log file for a DB), so it's not really immediate life and death if we burn the ssd out, but it will go fast. Our databases pump a lot of disk writes. Guys experimenting here with ssds to hold table data have burned them up in months. I'm trying to weasel a nvram device for my slog, otherwise we'll probably start with the zil on the hdds.
 

MilesB

Dabbler
Joined
Nov 26, 2014
Messages
16
I had read this, indicating that ZIL is like a rdbms log:
https://pthree.org/2013/04/19/zfs-administration-appendix-a-visualizing-the-zfs-intent-log/


edit: I read the doc you linked to and it said nothing to contradict my understanding of the ZIL. It did highlight that the best use of ssd for me will likely be l2arc. I am likely better off dedicating my limited ssd dollars to index storage though, as I know it will provide the greatest performance boost.
 
Last edited:

anodos

Sambassador
iXsystems
Joined
Mar 6, 2014
Messages
9,543

I perhaps misunderstood the direction you were going with the last post. It seemed at the time that you were thinking a SLOG may be something optional, which really depends on your workload and whether you'll be performing synchronous writes. If you're using ISCI with the parameter "sync=always" or NFS, then you'll want a SLOG.

See information here about types of devices to look into as well as an explanation of zil/SLOG (it's helpful to have more sources of info).

https://forums.freenas.org/index.php?threads/some-insights-into-slog-zil-with-zfs-on-freenas.13633/

Well, I've put my foot in my mouth enough for one night. :D
 
Last edited:
Status
Not open for further replies.
Top