SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Expectations w/ Transactional Replication


Performance Expectations w/ Transactional Replication

Author
Message
C2S
C2S
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 200
Can anyone offer some information about performance issues, latency...etc when using transactional replication? I realize there are many factors which affect performance and latency, but in general, what would one expect to see on the primary database by enabling transactional replication? I understand the concepts and requirements for replication however, just wanted to see if anyone had some real-world experience they could share..

Our goal is to setup a secondary server to use for real time reporting.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117251 Visits: 41445
Latency is controlled by how often you run the replication. How much is replicated will be the only cause of any performance "problems" on the subscriber. Most of it is all very, very fast because it uses BCP behind the scenes.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
happycat59
happycat59
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5532 Visits: 3256
Assuming that the servers are connected by a high speed network, you would normally expect latency to be pretty low (maybe a few seconds). If the network speed is low (e.g. WAN connection between the servers), the latency would increase but you would still expect everything to have replicated soon after it has been commited.



C2S
C2S
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 200
Since transaction replication is written to the secondary database, does that mean that the entire secondary database is read-only? For example, if we only have 5 tables being published from the primary, but we have created 10 custom tables on the secondary independently, can we modify the 10 custom tables as needed or does replication place a restriction on the 10 custom tables as well even though they are not in the publication?
Ian Scarlett
Ian Scarlett
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2819 Visits: 7055
Jeff Moden (11/5/2009)
Most of it is all very, very fast because it uses BCP behind the scenes.

Are you sure, Jeff?
I know the initial snapshot uses BCP, but (unless it's changed radically since SQL 2000), I thought the transactions were then reconstructed as individual insert/update/delete statements for application to the subscriber. It replicates "transactions" (usually involving > 1 table), and therefore has to apply the changes in the same sequence they were done on the publisher.

Code2Solutions (11/6/2009)
Since transaction replication is written to the secondary database, does that mean that the entire secondary database is read-only?

No restrictions are placed on the secondary (subscriber) database... even for the tables being replicated.
The activity on the subscriber database can have a major impact on the latency.
If you run any heavy duty reports on the subscriber, you can easily lock rows/tables, delaying the distributor's attempts to update the subscriber.

Having said that, if you don't do much on the subscriber, then it's usually a pretty slick process.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117251 Visits: 41445
Ian Scarlett (11/6/2009)
Jeff Moden (11/5/2009)
Most of it is all very, very fast because it uses BCP behind the scenes.

Are you sure, Jeff?
I know the initial snapshot uses BCP, but (unless it's changed radically since SQL 2000), I thought the transactions were then reconstructed as individual insert/update/delete statements for application to the subscriber. It replicates "transactions" (usually involving > 1 table), and therefore has to apply the changes in the same sequence they were done on the publisher.


Heh... I was until you said that... I've got to double check but I believe you're probably right... thanks for the catch.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
happycat59
happycat59
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5532 Visits: 3256
bcp is only used for the delivery of snapshots. For the on-going transactional feed, standard t-sql statements are used (either stored procs or INSERT/UPDATE/DELETE statements depending on how you configure the publication).

If you choose to update the tables being replicated, replication will not bother checking for your changes...i.e. if you update a record on the subscriber and the publisher, you may lose the changes made at the subscriber. If you delete a record on the subscriber and then make a change/deletion to the same record on the published, this may cause a problem that requires intevention since replication will expect to find the record.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117251 Visits: 41445
Thanks for the info and the corrections. Although replication certainly has some good uses, I'll always think of it as a bit of a pain because of problems as you describe and others.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search