Post reply

transfere data between branches

  • March 31, 2012 at 12:21 pm

    #255863

    I work in company have more branches main branch and other branches work with sql server 2005

    all databases in main branch and other branches has same structure and schema and tables(every thing) but different only in data.

    what i need is to take last update data(only data) from every branch (daily) then put in main branch are there are any way easy to make this

    please help me

    thanks

  • March 31, 2012 at 1:26 pm

    #1467329

    You can replicate the data back to the main branch.

    You need to make sure that the schemas truly are the same. You also need to ensure the data is unique. Do you need to know what the source of the data was (e.g. what branch did the data come from)?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 31, 2012 at 1:55 pm

    #1467338

    I do not think this is as straigh forward as posted.

    If - as it appears to be - branches are running structural identical databases on their own then not all the tables have the same volatility e.g. each branch is generating new invoices every day into the INVOICE table but branches are most probably not adding new products to the PRODUCT table. This means that some data should be sent to the main corporate database (new invoices, etc.) but some data should not be sent (old invoices, price list, etc.)

    Not knowing the business requirements and the system's architecture it is hard to say anything else than this may look like a case of two ways replication when some stuff gets replicated from Corporate to Branches and some stuff gets replicated from Branches to Corporate.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • April 1, 2012 at 4:56 am

    #1467387

    A fairly complicated request. Does all of this infrastructure already exist?

    If it doesnt I'd probably suggest merge replication. It'd be a star like topology with the main branch hosting all data.

    If it does exist this is a little more complicated. I'm still thinking merge replication is best for the data transportation but how you do that is still open. Possibly triggers/views point at the replicated database at the subscriber?

    You could do it the other way and make all of your branches replicate to the central repository. It would mean any ddl changes would need apply carefully at each publisher.

    There are many other designs you could follow such as SSIS but each will mean more development than replication.

  • April 1, 2012 at 5:18 am

    #1467389

    If the data is same and the structure is same why don’t use the date modified to copy only the necessary data to the branches? Rather copying all the data? Not sure this will have any selecting criteria involved?

Viewing 5 posts - 1 through 4 (of 4 total)

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