I am stuck looking for a suitable database technology to suit our application. I am stuck between going cloud-based or keeping it local.
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
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?