'Duplicate Record Fetching from Load Balancer System

One windows service is installed on two servers for load balancing. Both server nodes pointed to a single SQL Server database. How can we avoid duplicate records fetching from two server nodes? Following the solution is provided. But sometimes it fails.

  1. Instance ID and timestamp column added in the table.
  2. Instance ID and time stamp will update during service are trying to fetch the data.
  3. Update the time stamp only if the timestamp column is null. Otherwise, it's not getting an update.
  4. The instance ID is always updated during fetching the records. As per our system architecture, we cannot restrict the record to a single server instance ID.
    UPDATE  MyTable 
    SET     TimeStamp= (case when TimeStamp is null then @TimeStamp else TimeStamp end),
            instanceID = @p_InstanceID

Any solution to avoid duplicate fetching during two servers trying to access the same records.

sql-serverwindows-servicesload-balancing


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions