I have a system database in Oracle & SQL Server. Both systems are very busy and some important tables are being written or read atleast 1 time per second.
We would like to stream this data to other system to do processing and analyzing. I thought to write a cyclic query that checks if there is something new in the tables and push/write it to other system (like SQL, other DB) to do there the analyzing of data.
The question is: the tables are very busy. Could I get problem when cyclic push query is executed (+- every 5 à 10 seconds). I'm thinking about locking of tables etc..
Has anyone an idea about this issue? Also, what kind of protocols is preferred to transfer data to other systems database?
Thanks!
So, as I understand it, you have a 1) and Oracle database and 2) a SQL Server database. And you want replicate data from both to some unspecified third system for reporting? Because some source tables are very busy and you think (have not demonstrated) that the reporting may have a negative impact on OLTP operations? Well, the LAST thing you want to do is manually lock any tables. On the Oracle side, I would simply create a materialized view(s) and pull the reports from there. No third database needed. I'm not a MSSQL guy, but I'd be surprised if they didn't have the same functionality, though it might be called something else.