Question :
Problem statement -I have a situation, while the daily data is being loaded in the sql server DB with Insert, update DML operations, the user reports should continue viewing previous day data. Once DB operations is over, additional that day data will be available for reports. At present while this DML operation is in progress, the table is locked and reports are displayed blank till data load is complete. This is an issue.
Expectations – while the daily data is being loaded in db with DML operations, user should be allowed to continue viewing previous day data which is already available in db. To achieve this can we use 2 node failover cluster active/passive option? While daily data is processed into passive node, active node will continue serving the request. Once the passive node is processed with daily data, then passive node can be made active and active node passive. This new active node with latest data, can serve the request while the new passive node can now undergo data load and later become active node
Anand Gurjalwar
Answer :
Rather than 2 node cluster option you mentioned above problem can be addressed if you implement availability groups. You can have the dml operation going on the primary replica and create a readable secondary replica for reporting which would not have a disconnect for the dml in primary.
Other option includes database mirroring snaphsot for reporting depending upon if mirroring is already configured.
Above are all infra related changes but what you might be witnessing seems related to blocking scenario. See if the queries can be written in better way to avoid the blocking or reports can check for an interval outside the locking scenario. But that locking scenario is how it works.