I am working on a php / mysql application and it will be working on intranet and internet.
The intranet application is hosted on a windows server 2008 and the online application will be hosted on a shared linux hosting.
The process is that the requests will be initiated online by the customers and then the request goes through a complete work flow system on the intranet application and the status has to be updated online for the customers to see.
What would be the best mechanism to Synchronize the online and offline database??
Some services / utilties should continously run on the intranet application which keeps updating the online db from offline db and vice versa or something else should be done?? If this is the best technique then there is an issue here for remote mysql access - no shared hosting keeps the access open , it has to be opened for specific IP and due to shared internet connection the internet IP would keep on changing and someone has to keep checking the recent IP and enable it on the remote server which is not practical.
So what is the solution to above.
Is there a major, mission critical reason that the two halves of the application are on different hosting environments? Maybe each application is doing tons of other stuff and this is only one insignificant tiny part, but my first reaction is that these two apps are way too interdependent on each other to logically set up in this architecture. If you host the intranet on the same online hosting environment, essentially you’d turn it into an extranet (which it may be already), and then both sites would run from the online instance of the database. That would be much more stable.
The alternative is probably to set up a type of web service situation. It sounds like the customer initiates a process which has to be processed through the intranet and then they complete the process on the website. Logically, when they hit the intranet you should simply pass along whatever data is needed to synch the two DBs, then the intranet can grab the synchronization info and run a completely separate process that the customer won’t even know about to update the offline DB. In this situation, you should still designate one DB as the master DB. I recommend whichever one will have the most reliable information. Occasionally, you’ll want to backup the master DB and use that copy to “restore” over the backup. That ensures that every so often the DBs are definitely running the same info. The simple fact is that unless you’re running some kind of cluster scenario (in which case you’ve got multiple DBs acting like one BIG one), two DBs is one too many. You should only have one DB plus assorted backup and development DBs which are treated as entirely inferior and are never used for production. Your data should only be in one place.
If your intranet public IP address is changes constantly then I suggest you use a domain name service like DynDNS.com. That way you could make your intranet app executable and accessible from the online app. Then continue with the procedure as Chroniclemaster1’s post above.