>You don't mention the bandwidth between the web server and the store based SQL server but I assume it's slow enough to rule out accessing it directly from the web server. If that's the case then one copy of the DB in one location is always going to be a problem for one group of clients (IAC, as Al mentions I would be surprised if the company would accept a 'one copy on the web server' option).
The client site doesn't have any server related infrastructure. So, it is a one way only.
The server database is part of a proprietary design and is within a vertical market application. So, the client has to submit only a minimum number of fields with the image. So, if the client has 100 fields in their table, they need to only send about 17 fields. It could be that the 17 fields are spreaded across multiple tables at the client site.
>In my experience (which may well not be typical) some things change more often than others. For example the actual product list may not change very often - but prices will.
Yes, they received occasional updates from their suppliers. And, the price list may change.
>Also the main database will contain a large amount of data that is irrelevant to (or very rarely accessed by) the web site.
>So how selective are you currently in the synchronization process? Obviously, for example, re-uploading an image when only a price has changed is not very efficient.
Exactly, those are all related points I need to address. This is why I wanted to start a thread to collect feedback.
>One approach is to not bother updating a web-site based DB unless the data is actually requested. Using your pricing example:
>Record the DateTime the price of an item was last updated on the web server DB. When that item is next accessed check whether the update is recent enough and, if not, refresh from the main server.
Yes, this is the one I thought about. We need to have an application at the client site which will upload to a Web Service all the updated records. It is sure that we cannot change the data model locally. So, we can only act upon. So, everytime a synchronization will be needed, I see that the local application will get all the primary keys from the Web Service with the last updated time stamps. Then, the local application can verify that with each record locally and synchronize new ones. This will also include another method to add new ones and another method to delete old ones.
