Performance Expectations w/ Transactional Replication

Question

Performance Expectations w/ Transactional Replication

C2S

SSC-Addicted

Points: 499
More actions
November 5, 2009 at 2:32 pm

#213542

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

happycat59 One Orange Chip Points: 29389 More actions · Answer 2

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 SSC-Addicted Points: 499 More actions · Answer 3

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 SSC-Insane Points: 23197 More actions · Answer 4

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 SSC Guru Points: 1004318 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

happycat59 One Orange Chip Points: 29389 More actions · Answer 6

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 SSC Guru Points: 1004318 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)