When replicating data over a WAN using a Push Subscription in Transactional Replication there is one parameter related to the article’s properties that has a significant impact on the transactions delivery rate, this is the @status parameter defined with sp_addarticle.
The tests I have done (see bellow table) show that if your replication is configured with the @status parameter set to 8 you should gain a noticeable improvement in performance by setting a value of 16 or 24 instead.
To modify the article’s property use sp_changearticle and to to view the article’s properties use sp_helparticle
This is a copy of the @status in sp_addarticle from BOL (Books on Line)
[ @status=] status
Specifies if the article is active and additional options for how changes are propagated. status is tinyint, and can be the | (Bitwise OR) product of one or more of these values.
Value Description
1 Article is active.
8 Includes the column name in INSERT statements.
16 (default) Uses parameterized statements.
24 Includes the column name in INSERT statements and uses parameterized statements.
64 Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
For example, an active article using parameterized statements would have a value of 17 in this column. A value of 0 means that the article is inactive and no additional properties are defined.
The bellow table shows the results of a test I have done over a 160 ms WAN with one server located at NY and the other at TLV.
Both servers are running SQL Server 2005 x64 Enterprise Edition sp2 build 3282 (CU9) on top of VMware virtual machines running Windows Server 2008 Build 6001 Service Pack 1.
| Publisher | Subscriber | Type | Status | Transactions | Commands | Sec/Delivered | Trans/Sec |
| 2005 | 2005 | Push | 8 | 20000 | 20000 | Too slow - stopped after 20 minutes | NA |
| 2005 | 2005 | Push | 16 | 20000 | 20000 | 215 | 93 |
| 2005 | 2005 | Push | 25 | 20000 | 20000 | 210 | 95 |
To leave a comment: http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/03/30/replication-performance-and-the-status-parameter-in-sp-addarticle.aspx
-Yaniv
===================================
