I have previously asked the below question about LogShipping frequencies, but I think I might be approaching this problem incorrectly (LogShipping frequency)
I have a high volume site that I would like to divert some of the load off the primary server. The main area I wish to divert is the admin reporting queries which do not require the data to be real time (a lag of 5-10 mins is ok).
This appeared to be ok, but I have an issue with the two ways that it handles connected clients.
- Disconnect on restore. If I have a long running report from the web app, it will kill the report in the middle of processing.
- Dont disconnect user. This is also not very good because if the admin db is getting a high amount of traffic it cannot get an exclusive lock on the db to do the restore and the lag of the data difference could push out to be more than 10+ mins.
- This looked very promising, but seems more focused around data redundancy and the ability to have a failover db, not my actual aim of reducing overhead on the primary server.
Is the something I am missing here, should I be trying something else (readonly database, better hardware, clustering), or am I completly missing the point of the above technologies?