Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am looking at snapshot replication between two servers. Here is what I have:

  • 500GB database
  • ~500MB nightly bcp loads
  • ~50MB daily transactions

I was asking other DBAs in the company about which replication type to use. I was told to use Snapshot Replication. However, from what I understand and have been reading, every night after the load the snapshot will completely copy the database over to the distributor and then completely overwrite the other server.

Do snapshots work in deltas or is it a complete copy every time?

share|improve this question
2  
It doesn't sound like snapshot replication is your best choice here. –  Thomas Stringer Oct 2 '12 at 20:06
add comment

1 Answer 1

up vote 5 down vote accepted

From Books On Line:

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Using snapshot replication by itself is most appropriate when one or more of the following is true:

  1. Data changes infrequently.

  2. It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.

  3. Replicating small volumes of data.

  4. A large volume of changes occurs over a short period of time.

Snapshot replication is most appropriate 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. Given certain types of data, more frequent snapshots may also be appropriate. For example, if a relatively small table is updated at the Publisher during the day, but some latency is acceptable, changes can be delivered nightly as a snapshot.

Snapshot replication has a lower continuous overhead on the Publisher than transactional replication, because incremental changes are not tracked. However, if the dataset set being replicated is very large, it will require substantial resources to generate and apply the snapshot. Consider the size of the entire data set and the frequency of changes to the data when evaluating whether to utilize snapshot replication.

Therefore, deltas are not generated, the entire database is snapshotted and replicated.

share|improve this answer
1  
Max, thanks for helping me so much! –  grid Oct 2 '12 at 20:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.