sql server replication: Snapshot or Transaction which would be faster? -- sql server replication

Snapshot or Transaction which would be faster?

Greg Larsen
4/22/2007 9:42:02 AM

sql server replication: I am wondering how to set up replication. Say you have a very slow
connection to a subscriber, so you want to minimize the amount of data piped
across the network. The subscribers only need the data updated once a day.
The publisher and distributor are on the same server, but the subscriber is
at a remote location. Now if the articles being published where small it
seems that snapshot replication that runs once a night would be a good
option. But what if the amount of data associated with my article was quite
large. Would transaction replication where the subscriptions were scheduled
to run once a night be better then snapshot replication? I assume this would
be quicker and require less network traffic if the number of updates was
small. Would this assumption be correct? Now what if the number of updates
was significant, at what point do you think snapshot replication would be
better? Let me know you thoughts and advise here. Thanks.

Re: Snapshot or Transaction which would be faster?

Greg Larsen
4/22/2007 7:38:00 PM

Thanks for the advice. Then when would snapshot be of some value over
transaction replication?

[quoted text, click to view]

Re: Snapshot or Transaction which would be faster?

Paul Ibison
4/22/2007 11:15:28 PM

Greg,
in my experience I've never had a system where snapshot would be faster than
transactional. Clearly if rows were updated multiple times then there could
be such a scenario, but most systems aren't so update-intensive. The other
advantage of transactional is that it allows concurrent access on the
subscriber and we don't need to drop all connections to the subscriber while
dropping all the tables. The associated objects - FKs indexes all don't need
to be recreated either.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Re: Snapshot or Transaction which would be faster?

Paul Ibison
4/23/2007 1:36:03 AM

Also some databases contain tables without PKs which are precluded from being
transactionally replicated, and the overhead of merge might be considered too
large.
Cheers,
Paul Ibison

Re: Snapshot or Transaction which would be faster?

bb_43 NO[at]SPAM hotmail.com
4/23/2007 6:07:12 AM

[quoted text, click to view]
Say you have a table with 10 rows.
If you had an app that was constantly updating those ten rows all day, you'd
get thousands of transactions.

Re: Snapshot or Transaction which would be faster?

Joseph
4/24/2007 5:58:44 AM

[quoted text, click to view]

Another scenario would be if you had the Desktop edition (sorry, MS-
SQL7 lingo) installed on a non-server OS, like I deal with.
Transactional isn't available to you in that case, and you have to use
merge or snapshop.

-Joseph

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.