Post reply

updating tables between 2 servers

  • October 12, 2011 at 11:05 am

    #258765

    I need to update two tables from Server ABC to Server XYZ (after every 5 minutes only new transactions ideally)

    what will be the best way to do this ?

    Please let me know ASAP

  • October 12, 2011 at 11:41 am

    #1393627

    I think I'm able to use Import/Export and save as SSIS package and then run it after every 5 minutes

    Please let me know, what I'm thinking is correct or not

  • October 12, 2011 at 12:26 pm

    #1393645

    You can't really schedule the wizard so that probably is a no-go.

    You have several options.

    You could build an SSIS package that is scheduled to run every 5 minutes and copies the new data.

    You could build an SSIS package that is kicked off by a sproc when new data is added through a sproc, depending on security.

    You could build an SSIS package that is kicked off using a trigger on the table, depending on security.

    You could build an SSIS package that is kicked off using a trigger to write to a Service Broker Queue, this would circumvent security issues.

    You could use a linked server using a trigger.

    Lots of options, a lot depends on what you are comfortable with and your skills. How much data you need to transfer.. I am not a fan of linked servers.

    CEWII

  • October 12, 2011 at 1:26 pm

    #1393677

    Elliott Whitlow (10/12/2011)

    You can't really schedule the wizard so that probably is a no-go.

    You have several options.

    ...

    You could use a linked server using a trigger.

    Lots of options, a lot depends on what you are comfortable with and your skills. How much data you need to transfer.. I am not a fan of linked servers.

    CEWII

    ... Especially if it's called by a trigger. That might seriously affect performance if the linked server is not available...

    Another option might be replication. As Elliott already stated: "It depends" 😀


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • October 12, 2011 at 1:57 pm

    #1393693

    Ok, thanks

    I think I'm able to use Export wizard and save as SSIS package, correct ??

    But going back to your suggestions I think the quickest thing is SSIS package

    Thanks for the quick reply

    Dave

  • October 13, 2011 at 9:12 am

    #1394125

    That could build you a package that can be the basis for the rest of your work yes. But the package from it will probably not be sufficient for a repeatable process.

    CEWII

  • October 13, 2011 at 10:41 am

    #1394209

    Yes, thanks for your help

    I build the package using Import Wizard and then modified it

Viewing 7 posts - 1 through 6 (of 6 total)

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