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!
Best Answer
I've encountered this issue before and in previous cases for us the solution was to develop a system of ETL processes which bring the information over into the warehouse or reporting database. What we did was design the process to run near continuously and at the head of each iteration we would grab the current maximum datetime stamp of each table. We would then process each record that was generated or updated between the last timestamp and the current one we just retrieved then repeat this process constantly. There is a chance with this for partial data, but with the process running continuously any partial data would be resolved within a few minutes.