Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance Expectations w/ Transactional Replication Expand / Collapse
Author
Message
Posted Thursday, November 5, 2009 2:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, December 22, 2012 8:14 PM
Points: 39, 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.
Post #814577
Posted Thursday, November 5, 2009 8:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 36,955, Visits: 31,463
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #814670
Posted Thursday, November 5, 2009 10:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:24 PM
Points: 2,954, Visits: 2,557
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.


Post #814703
Posted Friday, November 6, 2009 7:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, December 22, 2012 8:14 PM
Points: 39, 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?
Post #814906
Posted Friday, November 6, 2009 7:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:22 AM
Points: 1,327, Visits: 4,506
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.



Post #814924
Posted Friday, November 6, 2009 9:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 36,955, Visits: 31,463
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #815303
Posted Saturday, November 7, 2009 6:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:24 PM
Points: 2,954, Visits: 2,557
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.



Post #815395
Posted Saturday, November 7, 2009 8:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 36,955, Visits: 31,463
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #815434
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse