I have an application which needs to grab data from different clients' databases that reside at the client's location. So alongside their normal details such as company name, address etc, I also store the name of their DB server and the name of the database I need to interrogate.
The number of clients is currently zero. However, I expect this to grow to to around 200+ in a year's time.
I am a bit confused with which option to go with to run distributed queries:
- Creating linked servers for every single client (up to 200+!)
- Using an OpenDataSource() or OpenRowset() ad-hoc query within an SP that feeds in the DB server and DB name dynamically from the client account table
Option 2 sounds the easiest to manage because if a client was to move their server or whatever, they just need to update their details in their account once and everything should work correctly.
But the reason I'm confused is because of this statement on Microsoft's site:
OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently. For any data sources that will be accessed more than several times, define a linked server.
The external DBs will get accessed quite frequently, with the majority of transactions being SELECT statements.
I'm also unsure to the security implications and which option is more tight for security. Does anyone have experience in this area and could give me some tips please?
I have used both. Linked servers are typically used if a database has lookup data and is connected to often. There is nothing wrong with using dynamic opendatasource to connect to your clients machines. Be very security aware as to where, and how, you store your clients credentials. You probably should read up on encrypting passwords and usernames.