SQL Server Performance

What is the best way to copy only UPDATED data between two exact tables on two diff servers?

Discussion in 'SQL Server 2005 General DBA Questions' started by playafree, Nov 27, 2007.

  1. playafree New Member

    HI, i'm new to SQL so please bare with me. Here's the situation. I have two SQL db's on two different servers with the same tables. I have already setup a SSIS job to use the copy column to have the tables the same. My problem is that i don't know the most efficient way to create a SSIS job to only copy over UPDATED data that needs to run every 10mins. Can anyone help me out?
    Thanks,
  2. satya.sqldba New Member

    If I were you, I would try setting up replication between the two databases across those instances.
    Satya
  3. Greg Larsen New Member

    To use SSIS your table would need to have a column that contains the datetime when the data was updated, and you would select based on that datetime column
  4. playafree New Member

    So if the source db is on SQL2000, i would create the publication there and have that publish to the SQL2005 instance? How well does this work?
  5. satya.sqldba New Member

    I have setup replication across different versions of SQL Server in the past and didn't have any problems.
  6. FrankKalis Moderator

    [quote user="playafree"]
    HI, i'm new to SQL so please bare with me. Here's the situation. I have two SQL db's on two different servers with the same tables. I have already setup a SSIS job to use the copy column to have the tables the same. My problem is that i don't know the most efficient way to create a SSIS job to only copy over UPDATED data that needs to run every 10mins. Can anyone help me out?
    [/quote]
    What I don't understand is, what happens with the data copied over?
    Do you want to changes made to server1 be reflected 1:1 on server2, or do you want to keep some sort of modification history? If the latter is the case I would have a look at the OUTPUT clause in SQL Server 2005. You could use it to write updated rows into a staging table from which you then copy the rows to server2 asynchronously.
  7. playafree New Member

    The reason why is becuase the source db that i'm pulling from only has read-only access. that's all i can do with that db. since i'm not suppose to be having any type of access to that db, i just created a read-only account on that db so i can eventually pull data from it. I copied over some tables from that db to another SQL db that i do have full access to. From the destination db, i can manipulate the data how i want since i have full access to it.
    I was thinking of just adding the linked server and then using some kind of UPDATE statement in a T-SQL statement to pull the updated data. I'm not familiar with UPDATE so i'm not sure how to do it. I've been reading some examples but i'm still not sure. Lets say for example i'm working with the following: Can anyone please write up the query for me?
    dbo.source with tbl1, tbl2, tbl3
    dbo.destination with tbl1, tbl2, tbl3.
    Knowing this, i neeed to make sure that dbo.destination tbls(s) is updated every 10mins with dbo.source tbl(s).

Share This Page