Unanswered: Replicating to a MySql 5.0 reporting/archive database.
Hey folks, we're considering using MySql 5.0 as a reporting/archive database. The idea is that we'd replicate from our MS SQL OLTP database and occasionally prune old data to keep the OLTP database responsive. The reporting/archive database, however, would contain all data.
I'd be interested in hearing suggestions on the following:
1. Scalability. What's the best approach for handling the transformation of the data to a denormalized structure. It appears that replicating to non-MSSQL databases complicates things.
2. Retaining the archived data. I'm concerned that the snapshot process that kicks off all transactional replications would wipe the archived data. Is there a way to tell SQL Server to start replicating transactions from a particular point in the transaction logs without pushing the entire current state of the database?