We have a big vendor application and oracle is the database server. We are not allowed to write direct view, functions on the oralce database, so we planned to use SQL server to connect to Oracle.
We know there is an option of linked server, but we are concerned if it will affect performance of oracle server and will it create concurrency or lock, or bandiwith issues. We will create views and function on the linked server, and most of time it is used for nightly data extracts. so maybe it won't cause issues, but without experiencing of using it, we are not sure. Or any other method, for example using replication or SSIS to pull data to SQL?
Any recommendations? Thanks