1

I am stuck looking for a suitable database technology to suit our application. I am stuck between going cloud-based or keeping it local.

Scenario:

We have a service that receives a log stream for thousands of devices. We have to store every log in order to do queries and analytics on that data. We do about 4-6 GB a day.

We currently have a local machine running SQL Server Enterprise with daily partitions. But the database has reached 1TB now and queries have become very slow and when running big queries it starts affecting the service that is continuously inserting in to the DB.

Local machine specs:

  • 2 * 2TB 7200RPM Drive RAID 1
  • 16GB RAM
  • i7

So we are stuck between spending a fair amount of money on upgrading the local machine vs moving it to the cloud. However, when looking at cloud solutions, they seem to get very expensive when running at such levels of performance.

I am also open to moving away from SQL Server. I quite like the idea of Azure table storage and how it does easy partitioning. Is there possibly a similar technology that we can run on a local machine?

So my question is: what are my options here?

closed as off-topic by Shawn Melton, James Anderson, mustaccio, Tom V, Erik Nov 14 '16 at 14:33

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Shawn Melton, James Anderson, Tom V
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Define "fair amount of money" spent on local hardware. It's cheapest thing, compared to licencing cost - look here for older comparision. Does Your machine allow for more RAM? 16GB RAM for OS and 1TB DB screams for more (and You do queries on it from what I see). – Marcin S. Oct 19 '16 at 12:32
  • One valid option would be to use partitioning. – Jason B. Oct 19 '16 at 12:48
  • 1
    @JasonB. OP has stated: "We currently have a local machine running sql server enterprise with daily partitions." – Marcin S. Oct 19 '16 at 13:19
  • 1
    You can exclude Azure SQL Database, because it has a hard limit of 1TB per database. At 3-6GB a day, you'll hit that limit in six to nine months unless you go across databases, and that's ugly. – Randolph West Oct 19 '16 at 23:32
  • Do you need to retain the raw data? Or can it be archived/deleted once its been analysed? – Sir Swears-a-lot Oct 25 '16 at 9:41
1

Considering the volumes you are discussing moving it to the cloud isn't going to be magic fix.

"There is no such thing as a free lunch"

A computer still has to process that volume, and that comes at a cost. If you are asking a 3rd party vendor to provide that theyre not just covering costs, but trying to make a profit too.

Changing to a different db platform can also have a lot of hidden costs. Even though licensing might be "free", there can be additional costs in maintenance, integration and compatibility. Even learning a new system is an overhead.

The spec you mention is not a big machine by server standards. I would be confident saying thats undersized for the volumes you describe.

What performance tuning have you done already? What are the bottlenecks? RAM/CPU/IO?

How much faster does it need to be? How fast is fast enough? Do you need a 20% improvement? 50% 100% You might easily get that with more RAM and some SSD's.

If you cant add more resources, is there anyway you can make the server do less work?

Archiving: Could you move old data off to a separate db. Or even better could you aggregate the data and purge the raw records?

1

There are tools designed to do just that i.e. Splunk (I do not work for them).

If you really want to go ahead with RDBMS, 6GB a day is not a lot but I would suggest better storage, preferably RAID to provide the desired throughput and would probably want to look at partitioning data (SQL Enterprise only) and of course appropriate index strategy.

If you have complex queries or string comparison you will also need much more CPU power. You should probably start looking at dedicated server.

To stop blocking the incoming feeds when you run reports, you would have to look at either secondary reporting copy of the database i.e. DB1 replicated to DB2 on a different machine and only run queries against DB2 or change isolation level to "read committed snapshot isolation" or "snapshot" but please be aware of potential consequences.

  • What do you mean Splunk is designed to do just that? I looked into it, it looks like a Data Visualizer tool. Could you elaborate? What alternatives would you offer to SQL Server Enterprise? – Zapnologica Nov 7 '16 at 20:37
  • You said you wanted to report and analyse log data received from devices. Splunk is a log management solution. It can receive a stream and parse into fields based on regex which you can then use for reporting in a similar manner you use SQL. RDMBS Are not necessarily suitable for what you are trying to accomplish. Log Stash and GrayLog are similar to splunk but open source. Maybe you should ask in sysadmin? – Marcin Gminski Nov 7 '16 at 21:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.