copy tables from local machine to remote machine

  • I have a central server and 3 local servers for 3-offices.

    The database structure is same in 1-server and 3-clients(offices).

    Clients do transactions.

    Every week they the data of client machines should be uploaded to server.

    Question:

    How can i upload 1-more table from local machine to remote machine as a unit.

    I had tried Linkedserver but did not solve my issue because, it does not support transactions.

    So as a unit can not upload many tables.

  • There are a lot of options:

    * replication

    * mirroring

    * log shipping

    * SSIS

    At first glance, I would say replication is your best option.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • can you explain how data of few tables can be uploaded to a remote server automatically using replication.

  • Hi,

    Yes, if you replicate table's that particular tables will update.

    In replication :- If you insert the data in particular replicated table automatically change in subscriber also....

    If you required deep explanation search in msdn.Microsoft.com\replication....

    Thanks & Regards

    Satish

  • Log shipping/Replication are two of the most widely used high availability solutions available in SQL Server. Implementing either one depends completely on your requirement.

    Usually Replication or Mirroring are used for critical real time applications where data loss needs to be kept to a minimum. Since log shipping involves some time lag, you can expect certain amount of data loss.

    I suggest you read BOL to find out which one fits your needs the best.

  • You'll want to implement transactional replication if you need the data to be more real-time. If you simply need to systematically upload the data to a client at a scheduled time than you can implement snapshot replication. When you setup replication you'll be able to specify only the objects you want to replicate. I've worked with call center applications that use replication for the same purpose you describe.

    Keep in mind though that whichever path you choose you will want to avoid any of the clients updating data on the subscribers (their local databases). If they do then you can run into some nasty constraint violations that will break replication. Data should only be updated on the publisher.

  • From the OP's message, what I see is the data from the client machines needs to be updated on the server, which would be possible using Merge Replication (necessary care needs to be taken to solve the conflicts).

    If this not a near real time requirement (I see the OP mentioned every week), then I would incline to do this using SSIS packages, which gives a lot of control and customization to the user..


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

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

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