I'm running a BI solution currently reading data from an OLTP SQL Server 2008 to a SQL Server 2012 data warehouse.
The ETL phase is implemented with SSIS and runs twice a day. This phase relies on a database shapshot of the source database, which is created on the fly as the first step of the ETL procedure.
Now, my client plans to move his OLTP database to a MySQL 5.7 on Amazon RDS solution, while keeping the data warehouse on SQL Server on premise.
I'm no MySQL expert, but did some searches and it doesn't seem to me that MySQL has a feature similar to "database snapshot" as implemented in SQL Server (which is not replcation nor a backup/restore, but something much more lightweight and easier to setup).
Is that true? If that's true, are there best practices / patterns about pulling data from MySQL via SSIS?
My requirement is to leave the online database as alone as possible, reading from a somewhat "offline" version of the database, just like we do now using SQL Server snapshots.
Should I use replication, are there AWS infrastructure features that could help?