Long shot - odd question re. very powerful server

[MeGaMaN]

A while back, for reasons I won't go into, a colleague at work convinced our boss to buy a very powerful server for our team with the expectation it would massively speed up a critical business process vs our old (but still powerful!) server. Needless to say it wasn't cheap!

New server - 88 core Intel Xeon E5-4699 v4 @ 2.2GHZ processor & 256 GB RAM (Windows Server 2012 Standard R2 OS).

Old server - 40 core Intel Xeon E7-4870 @ 2.4GHZ processor & 128 GB RAM (Windows Server 2008 Enterprise R2 OS).

The colleague has since left the business and it transpires the server has not delivered the value to the business he expected i.e. it did not significantly speed up the process it was purchased for vs our older server. Since then, both of the above servers are not being used at all, just sat gathering dust.

My team do a lot of data analytics, mostly using Microsoft SQL Server. We looked into the possibility of installing Microsoft SQL Server onto this server but the licensing costs were hundreds of thousands of pounds so decided against that!

Does anyone know of any (serious!) ideas we could use these servers for? Anyone have experience of any open source (i.e. free to license) alternatives to Microsoft SQL Server?

Long shot I know but figured can't hurt to ask...

 

[blueacid]

Well, there's a number of open source relational databases; MariaDB and Postgres spring to mind.

Having said that though, they aren't compatible with MS SQL Server, so an application compiled to speak to MSSQL won't connect to, say, Postgres.

Not knowing what your business needs, you might also want to investigate virtualisation software to allow you to run a number of virtual machines. But, really, the primary question is: What problem are you trying to solve?

 

[MeGaMaN]

Thanks for the reply, I manage a team of analysts in the research & development dept for a multinational retailer. Re. what problem are you trying to solve, we can get asked any question imaginable that could relate to the business and have to think of a sensible way of answering it, based on the data that is available.

We use MS SQL mainly, on a managed server, to pull vast amounts of data from all of the various systems into a central location where we develop our own pseudo systems to cleanse, aggregate and ultimately analyse to advise the senior business stakeholders on the best strategy to take the business forwards.

We are often crunching vast amounts of data and, whilst MS SQL is not neccessarily the best tool to do this, it is where our skillset sits, but more recently we are finding as the data we are analysing is growing exponentially, the queries are taking longer and longer to run.

Given Microsoft's SQL Server licensing model is based on the number of cores on the server, this new server we own but are not using would cost over half a million pounds to license which is not going to happen.

I was wondering if there are any other similar database / query languages very similar to MS SQL to learn but open source / very cheap to license that I could install on the new server with a view to, on an adhoc basis, manually load large datasets into then set the box off to crunch the data.

Or, if we are to truly embrace data science (rather than trying to find the patterns in the data manually), are there any data science "toolsets" out there we could install on the box for minimal outlay.

Appreciate these are rather vague questions I'm asking but geniunely don't know where to start as my skillset is not on the technical server side of things!

 

[Vince]

A while back, for reasons I won't go into, a colleague at work convinced our boss to buy a very powerful server for our team with the expectation it would massively speed up a critical business process vs our old (but still powerful!) server. Needless to say it wasn't cheap!

New server - 88 core Intel Xeon E5-4699 v4 @ 2.2GHZ processor & 256 GB RAM (Windows Server 2012 Standard R2 OS).

Old server - 40 core Intel Xeon E7-4870 @ 2.4GHZ processor & 128 GB RAM (Windows Server 2008 Enterprise R2 OS).

The colleague has since left the business and it transpires the server has not delivered the value to the business he expected i.e. it did not significantly speed up the process it was purchased for vs our older server. Since then, both of the above servers are not being used at all, just sat gathering dust.

My team do a lot of data analytics, mostly using Microsoft SQL Server. We looked into the possibility of installing Microsoft SQL Server onto this server but the licensing costs were hundreds of thousands of pounds so decided against that!

Does anyone know of any (serious!) ideas we could use these servers for? Anyone have experience of any open source (i.e. free to license) alternatives to Microsoft SQL Server?

Long shot I know but figured can't hurt to ask...

What kind of licensing? This although correct is open to various licensing models. You can license SQL a few ways:

SQL Server Enterprise Edition: $7,128 per core.
SQL Server Standard Edition: $1,859 per core.
SQL Server Standard Edition Server Licensing: $931 plus $209 per named user client access license (CAL)

So realistically looking at those why not just license per server rather than per core? So if the machine was department specific and you needed a server plus say 5 users licensed you could pay just $913 + ($209x5) So around 2k to license it. The problem with that is that SQL Standard is limited to 128gb / 24 physical cores per instance (so a single one of those 4699's fit in this perfectly) but that doesn't stop you at all spinning up a couple of instances and cleverly distributing resource and cores over those instances. Also note the word physical you could still run 48 threads.

On the bigger server I assume it has 2 sockets, 2x 22 core CPU's, so if you virtualised it, span up 2 VM's each with an SQL instances and then linked the servers you could potentially license all the cores and distribute the load for (depending on users) very little cash.

It's always handy to know your licensing position pretty well because other licensing such as datacenter licensing, core cal etc can help bring down costs of associated licensing. Really you should speak to somebody in the know that can satisfy microsoft SAM (should you get one) while still keeping licensing costs sensible.

I have 3x 32c/64t epyc chips and dont pay "hundreds of thousands" in licensing for the whole estate, in fact its around 75k a year and we run around 5 SQL servers, 3 datacenter licenses, core cal on 100 users, sql cal on 100 users.. etc etc.

Before anybody jumps in yes we did get singled out for a microsoft SAM (software asset management) visit last year and the only thing they found a true up on was a rogue copy of Visio.

Out of interest have you tried to tune the current queries to improve performance? Also how big a dataset are we talking?

 

[MeGaMaN]

Hi Vince, thank you for taking the time to reply.

Re. the licensing questions, this is out of my dept's jurisdiction - we have a Licensing department and their advice was we couldn't install MS SQL as it would cost over half a million pounds (albeit for a few years). Based on the numbers you have just quoted, I'm guessing it was Enterprise edition as $7128 * 88 cores = $627k which is in the same ballpark as half a million pounds.

But I'm definitely no expert - the licensing department have said if we want it, we need to be willing to pay half a million pounds.

Re. the tuning question yes, we are doing what we can to make queries as efficient as possible. We are self taught so I'm not for one second suggesting our databases are set up optimally but we are sensible with our approach and understand the concepts of good indexing practices, server statistics etc. that impact the way the SQL query optimiser works. Also appreciate you can spend a lifetime optimizing & tuning databases (which is absolutely not our skillset) - we have a department of technical specialist database administrators who do this.

I'm guessing a large part of the issue is we have maybe 15 - 30 analysts working concurrently, running complex queries (as well as the batches that are running in the background).

Just to get back on point though with my OP, I'm really looking to see if there is anything we can take an inexpensive punt on with this server to see if we can get any value out of it. Failing that, I'll just recommend we sell it to recoup what little we can (it cost over £30k in parts alone when new maybe 2 or 3 years ago).

 

[BigT]

It's way beyond my knowledge level but our analysts have moved to Apache Superset https://superset.apache.org/ and underlying open source database technology. All I know is it produces some cool insights from data for me and it requires a beefy server to run (albeit we do it on Cloud infrastructure) so may be of use for you.

 

[BongoHunter]

I would recommend Elasticsearch - it's great for doing the work you describe.

You use logstash to query stuff like your source db's and other data sources (it can read pretty much anything), you can then either insert it straight into Elastic - or transform and enrich it first before pushing it into Elastic.

Once it's in there you can use Kibana (the UI for Elastic) to analyse and visualise the data, or you can interact with your data using APIs.

It's quite a simple tool to stand up and play with, but it can be quite difficult to master if you get into clustering and using it at scale as its quite different from traditional SQL DBs.

 

[LuckyWig]

You don't have to license every single core on the server, the physical server could be used to run a VM with a small number of cores assigned to it. Have you checked the feature list between standard and enterprise? If you are not using the features then getting Standard licenses would also save money. Your half million for the full server could drop to under 20k if scaled to your needs correctly.

 

[ecksmen]

MS SQL offers 180 trial licenses; personally if I had that much hardware and there were issues with performance then I would set up a POC using trail software and make the business make an informed discussion as to how much of that hardware is actually needed, or release any capital and sell it on. Depending on what its used for, a million spend may not be all that crazy (although I would argue there are better ways of spending it, but you get the idea).

 

[Trig]

What storage are you using, is the lack of performance somewhere else on the system?