|So, you have data coming in from 200 devices, 5 times a second. Let's think about the problem areas here:
- When you add data, at that volume, you're adding 1000 items a second. Not a problem in SQL Server.
- I'm assuming it's one aggregation per device every minute, so you're looking at a read of 60K rows every minute, with a write of 200 rows.
I would be tempted to invert the problem slightly. Rather than write to one source, write to two. So, write the incoming messages to SQL Server and, at the same time, write them to something like Redis Cache. Then, every minute, get your data from Redis and use that to calculate the values for the other table and write to that. This approach follows (somewhat), the CQRS so you're separating the reads and writes from each other.
The beauty is, this scales well.