Hello All!,
I have a situation which I am a little confused as to the best way to go about solving the problem. I'm sure that there is no easy solution. It's probably a brain teaser so worth reading further just for fun...
Situation & Back Ground Information
I am designing a manufacturing system for a product which shall become a high volume consumer item expected to sell in the millions per annum from around 2010 onwards.
The manufacturing database shall contain information related to the calibration & test process. The data flow between the manufacturing sites and our head quarters shall be bi-directional. The bulk of the data flowing from HQ to the Contact Manufacturer shall be test limits, software upgrades and Work Orders. Conversly data flowing back shall be test results.
The product shall be manufactured across several sites which are geographically dispersed. As the product is high volume and manufactured in a low cost region; we are unable to rely on data links between sites for live data (as a leased line or VPN failure would result in a back log of thousands of product per day!), therefore each site must be able to operate as an island. Any data generated whilst a site has been islanded must be syncronised when the link is re-established.
A simplfied network diagram is given below.
As you can see the network forms a tree heirachy. At the top is the central server at our HQ. Below this are servers located in each manufacturing site (or repair centres, or customer sites...), and below these are the end users in that site (typically automatic test machines connected on a LAN). Data flow is always in the up/down direction. There is no direct peer to peer communications, however data generated in one location (manufacture) shall need to be accessed in another (repair). This is done via syncronising to the central server.
Questions
1.
From what I understand about replication, it would typically have a master - slave relationship in which all writes must occur on the master only. Unfortunatly as data shall be generated by each site seperatly (and perhaps in isolation) replication does not appear to be of use. Are there any features of MySQL (or other databases) which support multi-master syncronisation without the need for reliable live data feeds?
2.
Assuming that there is no built in method for syncronising multi-master databases without live links; I guess that I shall need to write some outside intellegent software to handle the merging of data.
Are there any tips when designing the database (in MySQL Workbench) which shall help me in this situation? Obviously it is possible to tell from which site the record origionated by a field associated with a set of entries, but how can I merge data between two databases which may have allocated identical keys?
3.
As manufacturing volumes increase it is likely that we shall wish to have redundency in some sites. I would wish to mirror the servers (at any given level, on an ad-hoc basis) such that they have automatic failover capabilities. Does anyone have any ideas on how this could be acheived whilst fitting in with the other requirements? Would this be a suitable situation for replication in a (local) master - slave relationship?
Thank you all for reading this far!
Regards,
Tim Warren
I have a situation which I am a little confused as to the best way to go about solving the problem. I'm sure that there is no easy solution. It's probably a brain teaser so worth reading further just for fun...
Situation & Back Ground Information
I am designing a manufacturing system for a product which shall become a high volume consumer item expected to sell in the millions per annum from around 2010 onwards.
The manufacturing database shall contain information related to the calibration & test process. The data flow between the manufacturing sites and our head quarters shall be bi-directional. The bulk of the data flowing from HQ to the Contact Manufacturer shall be test limits, software upgrades and Work Orders. Conversly data flowing back shall be test results.
The product shall be manufactured across several sites which are geographically dispersed. As the product is high volume and manufactured in a low cost region; we are unable to rely on data links between sites for live data (as a leased line or VPN failure would result in a back log of thousands of product per day!), therefore each site must be able to operate as an island. Any data generated whilst a site has been islanded must be syncronised when the link is re-established.
A simplfied network diagram is given below.
As you can see the network forms a tree heirachy. At the top is the central server at our HQ. Below this are servers located in each manufacturing site (or repair centres, or customer sites...), and below these are the end users in that site (typically automatic test machines connected on a LAN). Data flow is always in the up/down direction. There is no direct peer to peer communications, however data generated in one location (manufacture) shall need to be accessed in another (repair). This is done via syncronising to the central server.
Questions
1.
From what I understand about replication, it would typically have a master - slave relationship in which all writes must occur on the master only. Unfortunatly as data shall be generated by each site seperatly (and perhaps in isolation) replication does not appear to be of use. Are there any features of MySQL (or other databases) which support multi-master syncronisation without the need for reliable live data feeds?
2.
Assuming that there is no built in method for syncronising multi-master databases without live links; I guess that I shall need to write some outside intellegent software to handle the merging of data.
Are there any tips when designing the database (in MySQL Workbench) which shall help me in this situation? Obviously it is possible to tell from which site the record origionated by a field associated with a set of entries, but how can I merge data between two databases which may have allocated identical keys?
3.
As manufacturing volumes increase it is likely that we shall wish to have redundency in some sites. I would wish to mirror the servers (at any given level, on an ad-hoc basis) such that they have automatic failover capabilities. Does anyone have any ideas on how this could be acheived whilst fitting in with the other requirements? Would this be a suitable situation for replication in a (local) master - slave relationship?
Thank you all for reading this far!
Regards,
Tim Warren
Subject
Views
Written By
Posted
Questions about:- Multi Servers. Geographically Dispersed. Fault Tolerant Databases
3155
January 23, 2009 05:59AM
2315
January 27, 2009 04:03AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.