August 12, 2009 at 10:26 pm
Hi
can any body please suggest to choose between snapshot replication and Transactional replication? as I am some what confused with this statements
"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."
my database is of 20GB
August 14, 2009 at 7:42 am
It depends on how much data you need to replicate and how often the subscribers need updating. If all 20GB of data needs to be replicated, I would go with transactional replication as it creates a snapshot once to initialize the subscription, and subsequent data changes are applied on a row-by-row basis. In my opinion this overhead is much easier to predict and manage.
If you do decide to try the transactional option, when you create the subscription you can watch the snapshot generation and application. This is what happens each time a subscriber to a snapshot publication is updated.
Scott
August 14, 2009 at 8:04 am
It depends on frequency and size of updates. For most cases transactional replication is ok. Snapshot replication is usually used for initial data. If transactions are big and very frequent, it causes fast transaction log growth in dest database. You can (probably) even combine them: you put in one database the part with high load and snapshot replication, in the other database the rest with transactional replication.
August 14, 2009 at 8:22 am
They are used for different purposes. both copy data at periodic intervals, but they work differently.
snapshot - copy all the data at the interval as one batch. It's essentially an automatic BCP out/in. This somewhat limits the amount of data that you can send often. Typically this is a once a day event. all data is moved, so all data is removed from the subscriber and then reinserted.
transaction - sends transactions from pub to sub. This can be bundled/scheduled or it can be transaction by transaction. So the bandwidth to move data is low. The initialization is typically through a snapshot replication.
August 14, 2009 at 10:38 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply