SQL server - realtime and historical dashboard reading form tables

Question

I have APPS Service hosted on azure using Azure SQL Database with around 15 tables: - assistances - users - eventLogs etc.

Currently I have around 150k records, and on daily based my DB is receiving around 2000 new assistances with new users related. On my app I have a cron, which is making a lot operations every 1 mins to all tables (updating, inserting etc).

Right now my aim is to create some nice dashboard, which will display data for admins (like states of assistances, number of assistances delay etc) - basicly reading data from those tables. It should give as well possibility to filter by dates (from - to date) - so in worst case scenario few users can generate report for month (aprox. 60k records) in the same time. I'm afraid doing it directly on my prod database, due to fact, that I've already cron going on with a lot operations, so I'm worry about locking.

My ideas are: - sql database warehouse -> the biggest problem is the cost of it. - replication to second DB, which will be used for querying data for dashboard. - I'm not convince about this solution. - replication to noSQL database (pushing only important information) and use it for source of dashboard. - I don't have experience with such solution so far.

Do you have maybe some suggestion what will be the best?

Answer 1

In the end, I've used Geo-replication option from Azure, which is using snapshot isolation, so it's great! Even MS Azure recommend to use this geo-replication database as second DB used for read-only operations! I've tested and working great :)

Answer 2

You can use Azure automation to schedule those tasks that run every minute, instead of doing that from the application. You can know more about Azure automation here.

Instead of using Geo-replication consider using SQL Azure Data Sync. Make your primary database a “hub” database and use a replica for reporting. You can learn more about SQL Data Sync here.

You can also use Power BI to create your dashboards as explained here.

Hope this helps.