SQL Server

[SuperSaussage]

Hello i am building a monster SQL server and this will be my first computer build ever so im a little worried if these parts will work together so i was just wondering if somebody could help me make sure that they are all compatible together here are the parts 

http://ark.intel.com/products/82766/Intel-Xeon-Processor-E5-1660-v3-20M-Cache-3_00-GHz

http://www.newegg.ca/Product/Product.aspx?Item=N82E16813128755 

http://www.newegg.ca/Product/Product.aspx?Item=N82E16820242019&cm_re=ECC_DDR4-_-20-242-019-_-Product 

http://www.newegg.ca/Product/Product.aspx?Item=N82E16814127763 

http://www.newegg.ca/Product/Product.aspx?Item=9SIA8X036X6284 

http://www.newegg.ca/Product/Product.aspx?Item=N82E16817139082

http://www.newegg.ca/Product/Product.aspx?Item=N82E16835181094&cm_re=cpu_cooler-_-35-181-090-_-Product 

 

Thanks any guidance would be greatly appreciated (I know its alot)

[cgtechuk]

Hi there welcome,

What do you need this SQL Server to be doing? 

 

Is it Microsoft SQL, Oracle, Ingres, Sybase, MysqL?

 

99% of SQL Servers end up over specced, with that hardware you could build a VMWare machine host a SQL Server on it and also have a DEV / Test SQL Server on there too, 

Is it for web, Application / .NET etc?

[SuperSaussage]

Its is a Microsoft SQL Its is for several hundered users and running crazy amounts of data as well as accounts and what not.

[cgtechuk]

Haha ok in that case.

 

Some observations: That CPU even under full load would run using a stock cooler, I wouldnt bother with the GPU either as the motherboard has built in VGA which is plenty sufficient for what is needed. The PSU could even be downgraded to a lower wattage just as a cost factor. 

 

The SSD, Is the Encryption required I am guessing because of the business spec of  this machine?

 

If you used the above as an example that would save you a few hundred dollars that you could use to add a second disk for RAID 1 for data redundancy and also get you some more RAM, MSSQL Loves RAM lol like a kid and candy haha.

[manikyath]

some stuff about servers:

- servers are supposed to be reliable, watercoolers dont fit in with this picture.

- same story on the gpu, if its just for a display, theres MUCH better solutions than an R7 250.

- i'd recommend looking into a raid of high end hard drives, instead of relying on a single SSD. in the server world you REALLY cannot have your data depend on a single piece of electronics.

[SuperSaussage]

I was thinking about lowering the power supply as well but i am also thinking about possibly putting in a intel add in card (PCIE SSD) card for insane read/write speeds also some other stuff down the road so that way i also dont need to add another PSU. How much ram would you recommend for MSSQL 128 gigs or is 64 sufficient

[SuperSaussage]

3 minutes ago, manikyath said:

some stuff about servers:

- servers are supposed to be reliable, watercoolers dont fit in with this picture.

- same story on the gpu, if its just for a display, theres MUCH better solutions than an R7 250.

- i'd recommend looking into a raid of high end hard drives, instead of relying on a single SSD. in the server world you REALLY cannot have your data depend on a single piece of electronics.

Now that you say that Since i need to build 2 duplicate servers that will have main then a fall back server incase one fails cause it needs to run 24/7/365 so what if i built a raid storage server then built the 2 servers with small ssds then they just grab data off the raid server?

[manikyath]

Just now, SuperSaussage said:

Now that you say that Since i need to build 2 duplicate servers that will have main then a fall back server incase one fails cause it needs to run 24/7/365 so what if i built a raid storage server then built the 2 servers with small ssds then they just grab data off the raid server?

well, in terms of data security, this is what people usually recommend:

 

- your server has at least two copies, in your method of choice

- you have a weekly backup that gets locked into a safe to recover from theft or critical system failure. ("the server room is on fire")

- you have an offsite backup solution that syncs overnight in case of disaster. ("the entire company is on fire")

 

should add you should look into a netfiltering UPS, and probably also some protections against energy surges from lightning strikes etc, and offcourse the "crap, bennie flipped the wrong breaker" related problems.

[cgtechuk]

whilst that would be a good solution the problem you then have is that if the RAID becomes unavailable you lose both the Main and the backup SQL Server as its a bit eggs in one basket.

 

I agree with manikyath the server should be using more than one disk. Rotational disks in a raid format work just as well as a single SSD would. Raid 50 on an add in card would work here but would mean you needed 6 disks per server which may not be cost effective but would at least give you speed and some redundancy.

 

RAM wise depends on what sort of application it is, Where I work we have a SQL server that is used for real time data access from the web and also from users in the company and it runs really well and it 128GB DDR3 but never goes above about 70% used so even 96 would be enough.

 

 

[SuperSaussage]

Yes even as we speak with our old MSSQL servers we make nightly backups have a off site backup as well as a secondary backup 

[SuperSaussage]

2 minutes ago, cgtechuk said:

whilst that would be a good solution the problem you then have is that if the RAID becomes unavailable you lose both the Main and the backup SQL Server as its a bit eggs in one basket.

 

I agree with manikyath the server should be using more than one disk. Rotational disks in a raid format work just as well as a single SSD would. Raid 50 on an add in card would work here but would mean you needed 6 disks per server which may not be cost effective but would at least give you speed and some redundancy.

 

RAM wise depends on what sort of application it is, Where I work we have a SQL server that is used for real time data access from the web and also from users in the company and it runs really well and it 128GB DDR3 but never goes above about 70% used so even 96 would be enough.

 

 

Okay great and i do also think that more than one drive should be needed 

[cgtechuk]

Just now, SuperSaussage said:

Okay great and i do also think that more than one drive should be needed 

Let me know how you get on. good luck!

[SuperSaussage]

Possible new setup http://ca.pcpartpicker.com/p/VNgg8d 

Now i have added 2 ssds

[leadeater]

2 hours ago, SuperSaussage said:

Possible new setup http://ca.pcpartpicker.com/p/VNgg8d 

Now i have added 2 ssds

 

Don't use a consumer brand motherboard, you will really want something properly server rated. Consider a Supermicro instead. Also at a minimum upgrade to a Samsung Pro for this type of workload, and the EVO's aren't warrantied for server usage. That power supply is nice but way more than you need, wayyy more.

 

I also find it hard to beleave you actually need that much ram for your workload. We have at work a dual socket SQL server with 64GB ram running 7 SQL instances with 6TB of database serving multiple applications for 6000 staff and 35000 students. This isn't our only SQL server cluster, we have 4 but the one I mentioned is the most widely used for the most applications.

 

Just remember that SQL will reserve the majority of the ram in the server by default regardless if it needs all of it or not. You should do some proper analysis on the current SQL server to figure out what you actually use, but then again ram is so cheap now days over buying really doesn't cost that much and there is no down side other than cost.

[pat-e]

I know everyone (especially Linus) likes custom made servers ...

 

but why not a real server (like HP ProLiant or Cisco UCS or Dell PowerEdge or .... you get the idea)

[SuperSaussage]

3 hours ago, pat-e said:

I know everyone (especially Linus) likes custom made servers ...

 

but why not a real server (like HP ProLiant or Cisco UCS or Dell PowerEdge or .... you get the idea)

Simply because of compatibility issues between software and components as well as future upgrades and cost are the main factors 

[stevieb]

In my opinion you should use a off the shelf Server.

 

you just say loads of data? how much is loads?

what are the IOPS

Is there an existing SQL database if so can you use the Microsoft tool to obtain the IOPS and other data. you can use this to specify your server.

what kind of SQL server is it a transaction database or just a large database that's got to big for access

 

For MS SQL what version are you using? some version of SQL have CPU Limits

 

my idea approach would be to span the SQL database across 2-3 machines. one as a master DB server and 1 as a slave these 2 would be configured to do the load balancing of the users so to provide a stable platform and prevents against single point of failure.

 

I would have a 3rd Backup SQL server ready to go online but of a significant lower spec. but enabling you to but this online if the first 2 fail.

 

Regarding the IOPS this will let you specify the number of spindles you need in your raid to provide enough headroom over the current committed peak IOPS

[leadeater]

5 hours ago, SuperSaussage said:

Simply because of compatibility issues between software and components as well as future upgrades and cost are the main factors 

Compatibility of an HP server will be far and away better than anything anyone could build using parts. That is the point of buying from these hardware vendors, they do all the testing and validation of parts and produce a qualified parts list that is guaranteed to work or money back, you don't get this building yourself.

 

Also the firmware/bios is far superior to Supermicro and anything lesser to them, another reason for why compatibility is better than self built.

 

You can upgrade HP servers yourself, you can even use non qualified parts or 3rd party cheaper equivalents. The only caveat to this is you must be sure it will work but this is no different to a self built server.

 

I can keep listing reasons as to why self built cannot match proper hardware vendors but since there is so many more and some require a good deal of explaining the page scrolling would be annoying and it would never be read. At work we are an HP shop, we tried Supermicro and that was a cold hard lesson to never do that again. It's not that Supermicro make bad servers or are generally less reliable but the support framework around their products is not sufficient for our business requirements and the amount of servers we have.

 

TL:DR You can't build something better than HP, IBM, Dell only cheaper. Often you'll end up building something less reliable while trying to do so, you get what you pay for.

[pat-e]

@leadeater Good point, but try putting your TL;DR on top 

 

It really depends on what you want: Cheap but with many risks (like no support from OS- and application-vendor) or quality hardware (but with higher price).

 

[alex_read]

On 20 February 2016 at 9:43 PM, cgtechuk said:

What do you need this SQL Server to be doing?

Is it Microsoft SQL, Oracle, Ingres, Sybase, MysqL?

...
Is it for web, Application / .NET etc?

SQL Server is a Microsoft product - it's for housing databases (at a basic level, or the more complete answer is that it's an RDBMS). The info which hasn't been mentioned here is the flavour or tier, the version being installed... each version has different hardware limits (as well as costs) associated and could impact the plans & any recommendations we all make here.

 

For the mentioning of backups, I fully agree with all points manikyath made above. For the server itself, I completely agree with most above - it sounds like you've got the enthusiasm & passion of a hobbiest but are a little out of your depth in the corporate world in this scenario. Building your own server would be cool, but (i) not cost efficient and (ii) would likely (reading from above) lead to overspecced hardware which would be wasted (iii) wouldn't be as tested as anything an off-the-shelf server vendor would produce. Also (iv) might not be as mainable or future proof either (with hot swap drives, space for multiple NICs or CPUs etc.). I am impressed you looked at Xeon's and ECC memory, but the idea of having any not onboard GPU and watercooling should be left to the gamer/PC upgrade fanatic area away from servers.

[Smite]

So to add to the conversation. What are the Database sizes? Also as mentioned you say sever hundred users? Does this mean you have them all doing transactions at the same time or close to it? is your application going to be on the same server? Only thing server here is xeon and memory. I will tell you first hand significant databases will eat your 64gb of memory for lunch. Also windows 7? 

 

Consumer parts in servers is a black eye and know that if this is production server and it goes down you will be down for a while. 

I agree with others leave enthusiast parts at home when it comes to work/production. 

[Mikensan]

Those SSDs will die significantly faster than an enterprise slc ssd. This is mainly because of the constant writes/transactions that the SQL server is going to be doing. I've killed an OCZ Vertex 4 in 6 months with a database that only averaged 90gb worth of changes per day. It might be a tough pill to swallow now, but if you pony up the cash today and get one that'll last longer you'll save yourself in labor and parts later down the road. 

 

The rest of the parts look like they're for a gaming computer. Sorry if I missed a post in this thread already saying that's your intention - but the 750ti and x99 chipset motherboard is something I'd expect for a gamer or budget engineer workstation.