We wish to upload products and associated images from a MS SQL Server 2008 Database on Windows Server (source) to our Magento website hosted on a separate remote Linux server (destination) automatically on a periodic basis using the fastest practicable means without risking the security of our data. The number of products involved is initially around 30,000 but will rise significantly to upwards of 250k once operational. Under normal operating circumstances, the system will be expected to transfer & upload all new, changed or deleted records once a day, with provision to increase the frequency as required. In the event of a recovery situation, all products would be uploaded in one go. Error handling will need to be built-in to ensure that read/write clashes and timing issues do not occur, failed transmissions are re-tried (eg 3 times) and user alerts/emails are sent out. The solution should include the minimum number of separate communications between the source and destination.
The interface should be configurable to extract data based on customisable rules. Initially we only want to extract products from one vendor where multiple vendors have the same product based on pricing and availability. As we evolve, we need to be able to adjust that to upload multiple vendors with the same product.
The interface should be built so that additional modules can be added with future projects. An example of an additional module would be importing customer data and sales from Magento back into the MS SQl database.