June 1, 2017 at 8:41 pm
Can anyone suggest a technology that will allow distributed queries across multiple servers where the data is vertically partitioned (different tables on different servers)?
We have organisations that share their (different) data model with other organisations. When 2 organisations want to share their data they each have conformed tables in their local database (schema the same, data may be different). When Org A want data from Org B they can submit a query against Org B's server using 'IN ( <list of common keys> )' to get a result set back. The '<list of common keys>' is built from a local query being run against Org A's server to reduce the set of data being queried again Org B's server.
Ideally a MSoft technology - or something Free/PD - would be preferred, but NOT cloud orientated - so anything Azure based is out. Assume that the databases are either 'OnPrem' or 'Azure' (cloud, datawarehouse). Support for non MSoft databases would be a nice bonus. Structured data is the focus, non structured is not required.
I have stumbled across Presto, which is a potential candidate, but would prefer something closer to 'home' (MSoft) if possible. Linked servers and / or the use of synonyms has been considered, but I'm not sure if the performance would be acceptable.
pcd
June 1, 2017 at 10:36 pm
I would use a linked server from each server to the shared server, and create a view that references the remote table across the linked server.
Now use the view for your data.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply