Setup:
I have a centralized oracle database server. I also have approximately 105 oracle public database links setup to point to 105 different oracle servers to connect to each oracle database.
I am in the process of migrating the centralized oracle database server to sql server but keeping the 105 remote oracle database servers the same for right now. My question is does sql server have something similar (database links)? I see that you can link servers but wasn't sure if there would be an easier way to set this up instead of having 105 linked servers.
Thanks
Deanna
not sure I'm answering the right question 
but here goes:
A LINKED SERVER in SQL Server will connect to a physical Oracle server. If that Oracle server has multiple databases then it should be possible to use that one Linked Server to access all of the DBs.
If the 105 Oracle Databases are on DIFFERENT servers then I reckon you'll need 105 Linked server in SQL Server to connect to them.
You can set up a linked server with SQL commands ... and you can generate SQL commands mechanically (either in SQL itself, or with some macros in an editor, or whatever), so if you can generate a list of the servers it should be straightforward to "convert" that list into the SQL commands needed to create the Linked Servers.
1 Like
ok, thank you! That's what I thought but wasn't sure if there might have been an easier way.
You might be able to link to "various machines" dynamically, rather than having to set up Linked Servers
OPENDATASOURCE or OPENROWSET could "dynamically" set up the connection string (instead using a linked server), e.g. by querying it from a "table of offices", and then use that connection to query some data from the remote database. There are probably other Pros / Cons, such as performance, compared to using a Linked Server.
1 Like