We have a custom Customer Relationship Management (CRM) tool being built based on LAMP infrastructure.
We receive new customer orders via the web and by cron jobs that FTP and import order data from other sources.
Right now, we have a single web server that also runs MySQL - a dedicated server at a large provider.
Customer Service agents are all located in an office not near the datacenter where the current web/db server is.
Agents make changes to customer orders.
Management (located in the same office as the agents) need to run reports. These reports are very comprehensive and involve millions of orders (rows).
Customers have the ability to check order status online.
I am thinking it is necessary to have a robust server brought in to the office that will have the order data replicated for customer service and management to use and manipulate.
How would you set this up to maximize agent performance, management reporting tools, and customer ability to get the latest information about their orders.
How/when/which direction would you replicate - which server would be slave/master?