Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Posts
    23

    Unanswered: Multiple server select query

    hi,
    i have three database servers with heterogeneous databases, and i have a sql server 2005 that should has a table that will be filled with records from the three servers every time period, so what would be the best technique to create this table with the scripts ????

    i used to use linked server + sql server agent jobs but usually for one linked server only, but this time i am afraid of the performance as there will be three linked servers, so i need ur suggestions.
    Last edited by akkad; 03-01-08 at 11:08.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The performance ought to be about the same no matter how you organize the data.

    Are the table schemas static, or do they change over time? If one schema changes, do all of them?

    Are all of the tables in a single site, or are some of them communicating via a tin-can-and-string (slow data link) arrangement?

    How much data are you moving, and how often?

    -PatP

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Rule 1 - Minimize data in transit (columns and rows)

    If you'r getting the entire table each time from the remote servers is there a way you can only get records that have changed eg. get rows from remote table where date updated > last time sync'd OR use incremental PK > highest PK from last time I sync'd (if you'r not dealing with remote edits)

    if you can't do the above your really trying to do replication.

    If you can't do proper sql replication (possibly to local staging tables with a view on top) then your question is what is the best way for me to shunt the entire remote table to my local db - which may be either remote dump to txt file n BCP into local or continue with SELECT col1,col2,.... from linked server.

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •