Hey guys, So I've been contracted to provide the hardware for someone so that they can use it as a SQL database. This system will be used in a grocery store type business where a maximum of 14 POS systems (Point of Sales Systems (the cash registers)) . The "Server" will be exchanging/syncing/backing up data regarding transactions/inventory/prices of items etc.
Now they have simply asked me to provide the Hardware either by purchasing or building a system and they have someone else that will handle the setup of the SQL server on their system.
My question then is: What components do I need?
I begin with the processor as my limited research has shown people with 2 opinions. Either people are recommending $1000+ Xeons or saying that I will get by just fine with something like a dual core I3 as SQl isn't CPU intensive (for the scenario I need it for). So which one is Correct? What would you recommend? If I do really need something as powerful as a Xeon (I don't think i do) than am I not better off picking up a non - k I7? Much "Lower Price" with often times better performance (I don't really think I need ECC here).
I know they want a 1Tb drive in RAID 1 (So 2 1TB drives) but will any run of the mill WD or Seagate do or do I need to pickup a "NAS" HDD? They have also requested that I provide a RAID card either LSI or Adaptec (IDK how educated they are on these thing but they specifically requested hardware raid) any suggestions on something decent (I don't personally use any kind of RAID but I do understand how it works as well as software vs hardware RAID)?
They also specifically requested 16GB of RAM .... Do they need that much? Again I haven't ever built/configured a SQL database machine.
OS isn't a problem and neither is mouse, keyboard, or monitor.
Keep in mind I've built a fair amount of PC's for a variety of reasons and I am quick to learn many things but I do not have experience with a machine specifically geared for this purpose. So, I need advice on what YOU would do if you were building a client a machine and not yourself (maybe your personal machine would be better IDK). Client wants little to no downtime once the store is up and running
Any tips/suggestions in addition to what I have would be helpful?
An SQL Server, as with any relational database, is actually going to be pretty resource intensive, and as always the rule is "One is none, two is one" (redundancy is king). It sounds like the server is going to be processing both transactions and inventory, which means both a lot of write activity and high rate of Input and Output per Second.
One key thing is that the processor have the largest L2 and L3 chache as possible. You want a high quality processor, a middle of the road Xeon sounds like it would be perfect. You can always buy more RAM later, but it can be a pain in the ass to upgrade the CPU down the road. Speaking of RAM, you can never have too much RAM for a SQL server. If they say you need 16, get 32. Most server chipsets require ECC, anyway, and I think it's a good idea for the use-case to have ECC. For the storage, you're probably looking at something like Two WD Red Pro's in RAID 1, as you mentioned.
Also, you're probably going to want an APC (UPS) and a complete plan, including documentation for every component and it's warranty, and what to do if any particular component fails. I think you should probably have an external backup drive as well, that periodically takes a snapshot of everything, just in case.
I could write a really long reply on this; but I'll try not to :-)
Firstly, this is a business critical machine, ECC memory and RAID'ed drives are an absolute must.
Secondly, most SQL Server bottlenecks are caused by lack of memory or slow storage. CPU problems are often caused by poor queries or lack of table indexes. I expect 16GB was recommended by the application/database vendor to try and guarantee that the database tables effectively reside in the buffer pool.
Thirdly, if they require almost zero downtime make sure that server and database patching and maintenance policies and times are understood. If they really do require zero downtime e.g. the store operates 24/7 then you are effectively into clustering territory and your hardware requirements double.
Believe me, some of things I have listed here may cost a lot of money but understanding how much money the store will loose if the server is off-line for 4 hours or so will help quantify if they are worth having (they usually are).
I've just checked and you can use i3 processors with unbuffered ECC memory in motherboards intended for E3 Xeons. i7's won't work however. I would generally recommend a single E3 Xeon e.g. a E3-1231 v3 as a minimum for a small SQL Server deployment that will never need more than 32GB Ram.
Will the server you provide need to be rack mounted or free standing? If free standing a workstation with an E3 or E5 Xeon wouldf probably be a good start, but remember that most only have a single PSU where most rack mounted servers have two.
In terms of guaranteeing storage performance you could look at using a pair of quality SSD's in a RAID 1 mirror. That way you can store Data, Transaction Log and TempDB together quite happily.
You also didn't mention which version of Windows and SQL Server will be used. I recommend going with Windows Server 2012 R2 and SQL Server 2014; they will likely be budgeting for Standard Edition but be aware that it requires a minimum of 4 CPU cores to be licensed, and if you deploy a server with eight CPU cores that cost will double. Alternatively it can be licensed under a Server and CAL model which can be considerably cheaper but a little more difficult to manage.
When you come to hand over the build to the guy deploying the database ask if he knows how to configure SQL Server settings for TempDB, Min and Max Buffer Pools and Transaction Log and Data-File growth. There are a lot of database developers who don't know these things and expect the infrastructure guys to sort them out. If he doesn't google is your friend and RedGate have some great free books and people on SQLServerCentral.com are mostly all friendly.
Yeah I didn't mention the OS but I figured I could throw a bare bones versions of Windows 7 on there and it would work just fine. Any particular reason you recommend Server 2012 instead?
When I said zero down time I didn't mean a 24 hour store but that does still implythat ideally it isn't in constant need/if ever in need of maintance/repair.
So I need a high end CPU but would you recommend a high end I7 to a Xeon? I know I've seen plenty of benchmarks (Synthetic and Real) showing the price to performance is a lot better with an I7. I only ask because (Again I've never had this type of client) I'm not quite familiar with the various skews of a xeons and the performance difference or even which sockets accept which xeons (I know some fit in the mansteam Intel sockets while others have their own). I also have a general understanding of the way ECC works but can you explain the real world benefits in this specific use case scenario as ECC is quite a bit more expensive so I'm going to have to justify the cost of all of this to the client. Again small huissness and I know I said 14 POS systems off of this single machine but thats the future goal they hope to achieve and not what they initially plan to start start with (I'm basically saying the cost per advantage has to make sense to them).
Defiantly a standalone case and not a rackmount as I mentioned this isn't a "huge" buissness. Which I also needed recommendations on as they would like to have a case than can be locked with a physical key. Any particular suggestions?
Finally how about the raid card? Any suggestions? Is it worth it to get a separate raid card vs using onboard raid (provided I get a decent motherboard).
That's what I have so far as I spoke to the person installing the SQL server and by his own admission a high end I7 will work just fine as ECC is not needed for this project.
BGL and blaxa, you seem to be quite familiar with this kind of stuff, so I'd really appreciate it if you could take a look at the build I'm currently planning for my own business. It'll be running my ERP/CRM system with an SQL database plus something like owncloud or alike. Really nothing big since we are a start-up company in the wine business, but I want it to be solid and reliable.
It looks solid enough. I generally recommend just buying off the shelf servers which are covered by a 3 year parts and labour warranty; but where's the fun in that :-)
ECC Memory is always good for systems that will host databases and will rarely be switched off. It just gives piece of mind that no bits are getting flipped as often data corruption problems are discovered when its too late and many days worth of backups contain the error. I recommend daily or weekly DBCC CheckDB runs on SQL Server databases for this reason.
If SQL Server is your RDBMS of choice just use Ola Hallengrens jobs for your database backups and maintenance, they are free and work really well: https://ola.hallengren.com/sql-server-backup.html