I have 2 SQL Server databases, one is Primary where all the write operations is done connected to Main app, and secondary database is connected to reporting app, data between 2 databases is synced via Always On group. The issue I am facing here is, all the data gets replicated soon, but when secondary database is having to many transaction (i.e if more number of users are accessing my report tool) then data replication gets delayed between primary and secondary database up to 3mins, 8mins and sometimes 20 mins as well. what is the for fix this issue?
Note: SQL Server is installed in a very good infrastructure with good RAM & HD.
I got to hear few solutions as, by applying locks in queries of reporting app, and query optimization and applying indexes might help, but my doubt is that, this will increase the query performance, but my issue is in data replication from primary to secondary database.
Can someone please help me.