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:

  1. Creating linked servers for every single client (up to 200+!)
  2. 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?

  • Performance over linked servers is likely to bad due their inability to use statistics. Security will also be a concern. It sounds like this data should be transferred at the app layer if possible. – James Anderson Jun 17 '15 at 17:29
  • @JamesAnderson could you expand a bit more on the better solution of transferring data at the app layer? I didn't quite understand what that meant. – volume one Jun 17 '15 at 17:31
  • I believe Linked Servers can use statistics if you have the correct permissions. I would be the db_owner – volume one Jun 18 '15 at 9:42
  • yes that is correct but then the security hole is about as bad as it can be – James Anderson Jun 18 '15 at 10:05
  • @JamesAnderson so what other option is there to getting at data which resides on a customer's server? I could copy across the data from their server and then perform queries, but I'd still need a linked server connection to get at it. Would this be safer/better/scalable option? – volume one Jun 18 '15 at 10:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.