How to get the data in destination database immediately when source table updates?

  • We have 2 databases Source Database and Destination Database, We want to transfer data from source table records to destination tables immediately .

    Can anyone provide tips to implement this by using Integration services ?

    Regards,

    Ram

  • Try this tutorial

    http://msdn.microsoft.com/en-us/library/ms365330(v=sql.105).aspx

    Mack

  • Sorry - ignore my post I misread what you had written

  • sram24_mca (1/3/2013)


    We have 2 databases Source Database and Destination Database, We want to transfer data from source table records to destination tables immediately .

    Can anyone provide tips to implement this by using Integration services ?

    Regards,

    Ram

    Integration Services does not sound like the right tool for the job - it's not an always-on, data-drip-feed type solution. Have you considered CDC or replication instead?

    Rather than 'immediately', can you specify the maximum acceptable time between the data appearing in source and being copied to destination?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Ram

    Use the Import/Export wizard. You have the option to run it immediately or save it as an SSIS package.

    John

  • Phil Parkin (1/3/2013)


    Integration Services does not sound like the right tool for the job - it's not an always-on, data-drip-feed type solution. Have you considered CDC or replication instead?

    Rather than 'immediately', can you specify the maximum acceptable time between the data appearing in source and being copied to destination?

    Phil, I took "immediately" as meaning he wants to do it now rather than that he needs the source and destination to be always synchronised. I think we're going to need clarification.

    John

  • John - I think he wants the package to run each time data is added/updated to a table

    I think, as Phil has suggested, that replication might be his best option

  • Hi Parkin,

    Consider the source data has to copied to destination with in 3 Minutes.

    Regards,

    Ram

  • sram24_mca (1/3/2013)


    Hi Parkin,

    Consider the source data has to copied to destination with in 3 Minutes.

    Regards,

    Ram

    OK - and what about replication or CDC?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi Parkin,

    Thanks, Can you please explain the CDC Concepts? I am new to this CDC Concept. Thanks in advance

    Regards,

    Ram

  • What edition of SQL server are you running?

    2005/2008 Standard / Enterprise

    CDC is a 2008 onwards and Enterpise only feature

    http://msdn.microsoft.com/en-us/library/bb522489%28v=sql.100%29.aspx

    If using standard, you would need to use replication.

  • The other option to consider is Triggers on the source table that fire a replication script. How is the source table updated (e.g. Bulk load or data import from SSIS using some sort of Transform, or ad-hoc from an application/scheduled web-scrape)

    Another alternative would be to just have an SQL server Agent job that runs every 3 minutes and this will fire every time, whehter there is data to transfer or not.

    Does the data need to go through any transformation. If not, could you just make the destination server a linked server and read the data from the source table on demand.

    Each option has its own performance and maintenance issues.

  • aaron.reese (1/7/2013)


    The other option to consider is Triggers on the source table that fire a replication script.

    Probably best to stick to standard replication; this has better performance overall than using triggers. Triggers can also introduce some rather interesting logic errors if you're not careful.

    aaron.reese (1/7/2013)


    Another alternative would be to just have an SQL server Agent job that runs every 3 minutes and this will fire every time, whehter there is data to transfer or not.

    I've done this myself, but I would treat it as the last resort option, if you can't use replication.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply