Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    6

    Red face Unanswered: Snapshot replication

    Suppose i want to replicate data from server A to server B
    I am using snaphot replication.I did the snapshot replication for the first time
    and server B got a snapshot of server A.

    Next time i run snapshot i want the incremental data to be replicated and not all..Is this possible in snapshot replication? If not which type of replication should i use?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    From SQL BOL:
    Snapshot Replication
    Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

    Snapshot replication would be preferable over transactional replication when data changes are substantial but infrequent. For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended. Creating new snapshots nightly is also an option if you are publishing relatively small tables that are updated only at the Publisher.

    Snapshot replication is often used when needing to browse data such as price lists, online catalogs, or data for decision support, where the most current data is not essential and the data is used as read-only. These Subscribers can be disconnected if they are not updating the data.

    Snapshot replication is helpful when:

    • Data is mostly static and does not change often. When it does change, it makes more sense to publish an entirely new copy to Subscribers.

    • It is acceptable to have copies of data that are out of date for a period of time.

    • Replicating small volumes of data in which an entire refresh of the data is reasonable.


    Snapshot replication is mostly appropriate when you need to distribute a read-only copy of data, but it also provides the option to update data at the Subscriber. When Subscribers only read data, transactional consistency is maintained between the Publisher and Subscribers. When Subscribers to a snapshot publication must update data, transactional consistency can be maintained between the Publisher and Subscriber because the data is propagated using two-phase commit protocol (2PC),a feature of the immediate updating option. Snapshot replication requires less constant processor overhead than transactional replication because it does not require continuous monitoring of data changes on source servers. If the data set being replicated is very large, it can require substantial network resources to transmit. In deciding if snapshot replication is appropriate, you must consider the size of the entire data set and the frequency of changes to the data.

    You're two choices would be merge or transactional replication (for doing incremental updates). Transactional is dependent on good network connections. Merge Replication is designed for (theoretically anyway) disconnected users who periodically connect to get updates.

    Which you choose depends on what your needs are.

    You might also consider scheduled updates using DTS or some other tool. Merge and transactional replication impose restrictions on schema updates that can sometimes be a real PITA.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, snapshot replication does what the name implies... It takes a "snapshot" of the affected tables from the publisher, and pushes the whole snapshot to the subscribers.

    You might be able to coax transactional replication into doing what you want, but this wouldn't be simple.

    Backup might be an answer too, especially if you can set up your filegroups as you wish. I'm not sure, but you might be able to create differential backups of only the tables in a specific file group, which would make things relatively simple.

    The problem is that you are trying to get a very specific part of your database to replicate (only the changed rows, only in specific tables, only at specific times). Replication that is this specific you may have to code via DTS instead of using standard replication.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •