Sync. table in different database & location?

[inetd]

Hi,
How to sync. the tables in the databases with different location? For example, there are 1 head office and 2 branch offices and each has the same database structure in its local server. However the data in these databases should be sync. after office hour. i.e. A sync. operation/program is needed. I have no idea on how to design the table and sync. operation since all the offices can modify the data (add, change, delete).

My project is something like a multi-location point of sales system but they are not using a centalized database.

do Anyone give me some advice?

Thanks.

 

[willir]

inetd

What type of database is this - Access? Are you using replication? Sounds like it would here.

Hint: You may have to convert any autonumber fields to ReplicationID datatype. This may require some work.

 

[johnwm]

If all offices can modify and delete each others transactions then you may have a bigger problem tha replication can deal with automatically! Youn may need to clarify the spec

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[SQLSister]

You may have a huge problem as the separate sites probably are using the same ID s for different records. (Example at site one, customer ID ABC stands for the ABC plumbing company, at site two the customer ID ABC stands for the Abacus Development company) Try to combine all both together and it will probanbly refuse to load the second but may happily load any related records which now as far as the combined database is concerned belong to the ABC Plumbing company) Even worse, if no primary key or unique index was set up onthe ID field, then it may load both and then queries to get the order records will include all the orders for both companies when you query one.

Setting up replication after the fact can be dfficult to maintain the previous data integrity. You may need to choose the largest of the databases as the base database, then add the records tothis as a central database from the other sites, converting any ID numbers inthe process before you set up replication.

REplication is a complex procedure and requires a good set of requirements and a well-thought out plan. adding it after the fact when three disparte databases already exist can create problems. YOu need to think about these problems and how to address them and test your process with three development databases before rolling it out to the real databases. YOu will probably spend a weekend at work doiung the final production rolloout becasue there is no way that you should attempt this while any users are trying to add or update the data. That's why you need three development databases to work all the kinks out with first. Also, are you positive all three databases have the same structure? Often when databases are not connected with replication individuals at one site may make a change that is not given to the other siters. YOu will need to check this first, but the data integrity problem is usually the worst problem.

For advice onteh specifics of setting up replication, you need to go to the forum for the particular database you are running as each database would do this differently.

 

[johnherman]

You have a big problem. You need to assign a data steward (or data champion) for each data element in the database. That data steward and/or his/her department are the only ones who can update that data element, and in addition, the data steward is responsible for the quality of that data. Once you have agreement on who is responsible for the data elements, you can write scripts to "publish" from the data steward's location to the subscribing locations.

Available for short and long term data warehousing consulting and contracts.