Design Strategy: Query and Update data across 2 different databases
Is it not possible to use SSIS package to do the data transformation between 2 servers and invoke it either via ASP.Net & c# project or via schedule job invoked on demand?
Will the results from one of the databases be small enough to efficiently pass around?
If so, I would suggest treating the databases as two independent datasources.
If the datasets are large, then you may have to consider some form of ETL into a staging area on one of the database. You may have issues if you need the queries to return up-to-date data from both databases. Because you will need to do a real-time ETL.
There is an article here about performing distributed transactions between Microsoft SQL server and Oracle:
I don't know how well this works, however if it does work, this will probably be the best solution for you:
- It will almost certainly be the fastest method of querying across multiple database servers.
- It should also allow for true transactional support even when writing to both databases.