I have a SQL server DB, a Oracle DB, a MySQL DB. I have a way to join the tables from each DB. How would be the best way to create this view? I have just started SSIS project that pulls the tables from the DBs and creates the view I want, but is there a way to grab the data from each engine in the view in real-time\dynamic?
Asked
Active
Viewed 271 times
3
-
I'd guess step one would be to put them all on a single database server. – Ed Grimm Feb 08 '19 at 01:55
-
Load the data from other Db's to one db and create the view. If you use linked server it has other issues until unless data is very small. – Roshan Nuvvula Feb 08 '19 at 02:00
-
@Ed that's possible but that would be a massive project. – JBrown Feb 08 '19 at 02:00
-
@Roshan I have the Oracle linked to the MSSQL already. I'll look at linking the MySQL tomorrow. Thanks! – JBrown Feb 08 '19 at 02:05
-
Having thought about it a bit more, you could probably do it with an SQL proxy server. I've not looked into the capabilities of SQL proxy servers; you would need one that could connect to all of the servers that couldn't already talk with each other. – Ed Grimm Feb 08 '19 at 02:07
-
@Roshan I tried OpenQuery 2 years ago maybe this time I'll have better luck with it... maybe. – JBrown Feb 08 '19 at 02:24
-
You asked "best" - the best way is not to cross instance boundaries in a select query, especially in a heterogeneous environment. Linked servers can be very fragile - especially when dealing with many rows. But your description is confusing. A SSIS project would typically be used to move/copy data between databases, so it isn't clear what your SSIS project does nor what your real goal is. – SMor Feb 08 '19 at 04:30
1 Answers
2
Thr best way to do that is to create a linked server for each instance (Oracle, MySQL) on sql server without the need to copy data.
And then you can create a view that join the tables between instances.
For more information, you can follow these articles:
Hadi
- 36,233
- 13
- 65
- 124