-1

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

| improve this question | |
  • SQL Server FCIs do not work this way, they only run on a single server at a time. This is a locking and blocking issue, not a ihgh availability or disaster recovery issue. I'd suggest updating the post with what you've currently done to mitigate the issue so that people can make more informed answers. – Sean Gallardy - Retired User Jun 13 '19 at 23:30
1

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.

| improve this answer | |
  • Thanks for quick response. But my db size is quite high around 400 million records wih an option to extend further on regular basis. We trried query change with dirty reads. But that has not helped – Anand Gurjalwar Jun 13 '19 at 16:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.