I have a reporting database (in SQL Server) that takes nightly snapshots of the production data (Oracle) for users to query against for the next day, but now the requirement is getting to be that they want near realtime data (about every 15-30 mins). What is the best way to do this in production without downtime or any partial data getting returned?
I've looked into realtime data mirroring using triggers, but because of licensing we can't alter anything in the Oracle database.
I've seen some information on partition switching, where I do a full refresh into a set of alternate tables then switch the partitions. Is it good practice to be doing that on 20 tables every 15 minutes? A lot of these tables have one-to-many relationships, but I figure if I replace the "many" tables before the "one" tables, reports won't return partial data if they run during this process.
I also want to avoid locking the whole database, because I want the system to be able to scale to many concurrent users, and it seems like if I do that, even if it takes just a second, it might take some time to acquire the lock.
Any strategies or insights are appreciated! Thanks!