which gives good performance..snapshot replication or transactional replication?

for the same workload and same environment we are implimenting snapshot replication and transactional replication at diffrent locations over produdction servers. Both standby servers we are using for adhoc report generation and for end user interface. Which performs better?

asked Mar 19 '10 at 01:47 AM in Default

venkatreddy
562 ● 26 ● 28 ● 32

(comments are locked)

2 answers:

Depends how often the replication will occur and often the data in the production database are modified and also the amount of data being replicated.

Generally the transaction replication will give you a better performance as only the changes are propagated to the subscribers periodically. During the snapshot replication the whole snapshots needs to be redistributed. Also transactional replication will put less locks on the source system.

See: http://msdn.microsoft.com/en-us/library/ms151734.aspx and http://msdn.microsoft.com/en-us/library/aa179423%28SQL.80%29.aspx

answered Mar 19 '10 at 04:45 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

Thanks Pavel,Can you please answer which environment supposed to use snapshot replication?

Mar 19 '10 at 06:55 AM venkatreddy

Snapshot replication can be good if you have data that very rarely changes, then you can set it up, do a new snapshot when it does rarely change and use 0 resources for maintenance in between. On the flip side, it can be good for a database that goes through enormous changes (especially were the same rows are updated over and over) but where the subscriber is allowed to lag substantially behind the publisher. In that case it can be more bandwidth efficient to just replace everything occassionally than try to push every one of the vast number of changes.

Mar 19 '10 at 02:21 PM TimothyAWiseman

(comments are locked)

I started to write a long answer but realized you need to define 'performs' before anyone can give an answer shorter than a book. What, and be very specific, do you expect this replica to do for you?

answered Mar 19 '10 at 03:58 AM

mreed
19

can you give that long answer.,first be clear to understand my question.

Mar 19 '10 at 04:04 AM venkatreddy

better you understand what i did asked.please be clear about the question, at least in understanding not in answering.I have clearly asked the things..

Mar 19 '10 at 04:16 AM venkatreddy

(comments are locked)