March 15, 2011 at 3:21 pm
We have Customer server tables which are loaded from multiple Processing server tables. The data in tables might or might not be the same across the processing servers. In case of the data being same across processing servers we need to avoid duplicate inserts for which we are making use of look-up on the target table just before the insert. There won’t be any problem when data from a single processing server table is loading into the customer server table at a given instance. But when there are two servers trying to load the same table simultaneously we get a primary key violation error saying ‘Cannot insert duplicate key’. Ideally this shouldn’t happen because we are checking the existing data in the target table and inserting only those records that are not present. But since there are chances that records can be inserted into the target table from one of the processing server (say in Package 1) during whatever little time lag that is there between look-up and insert in other package (say Package 2) we might be getting this primary key violation error.
Note: Customer Server - Target Server and Multiple Processing Servers are source
March 15, 2011 at 7:25 pm
Why don't you control the process up front and assign records to servers which would guarantee that you don't have wasted effort. You have cases where each server is doing the same thing for the same record.. Each server would take a group and you would never have the case where they do duplicate work.
Now, with that said, do you need two servers, can you simply run two processes on the same server.. In most cases parallel threads on the same server will be suitable..
You might look at:
http://www.sqlservercentral.com/Authors/Articles/Elliott__Whitlow_/483442/[/url]
CEWII
March 16, 2011 at 7:15 am
Thanks for youe quick response.
The requirement to run the process from two servers and each server has multiple tables with different sets of data. But only one table may or may not have same set of data. When both processes run at different time our logic work as excepted. The condition will be checked before inserting the data and it wont insert if the data is already inserted by other server. Here we get this problem only when we are executing at the same time where both condition passed and it's failed while inserting the data. In the lookup, we have handled the condition to check whether the data is inserted or not. When two processes running at the same time, both are passed while checking the condition and it's getting failed while inserting the data.
Do you suggest any other solution?
March 16, 2011 at 8:56 am
My first thought was to prevent that one table from having duplicate data, or at least some process that controls which side is allowed to process its records.. I would think that it would be advantageous to limit duplication of effort. While I understand that you have a requirement, I have on occasion challenged requirements such as this, as a rule, business requirements don't get to define how many servers do the processing, this is defined by processing loads and times. It could be that you can easily load your data within the required window and continue to with anticipated growth. In this case having multiple servers introduces overhead without much value. As for your case I can't say. Your process needs to be structured to handle concurrent processing if it makes sense.
I worked in an organization that for a long time allowed business requirements to specify technology and software and such. This gave software salesmen a great in, since the business requirements were written for their software. There was a pushback from the dev and admin groups about the process. In the end the business requirements rules were changed to not allow them to specify tech, software, or exactly how or how many servers were used in processing. They could define processes and if there were time requirements they could specify them. With all that said the business requirements were not "set" until they had been reviewed by the tech folks. Often the requirements were not practical or simply didn't make sense. When explained the business folks were generally open to the change simply because they had a much better chance of getting what they wanted. Not sure if this helped, hope so..
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply