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?

more ▼

asked Mar 19, 2010 at 01:47 AM in Default

venkatreddy gravatar image

562 28 29 32

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Mar 19, 2010 at 04:45 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Thanks Pavel,Can you please answer which environment supposed to use snapshot replication?
Mar 19, 2010 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, 2010 at 02:21 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Mar 19, 2010 at 03:58 AM

mreed gravatar image


can you give that long answer.,first be clear to understand my question.
Mar 19, 2010 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, 2010 at 04:16 AM venkatreddy
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 19, 2010 at 01:47 AM

Seen: 3492 times

Last Updated: Mar 19, 2010 at 01:03 PM