Distribution Agent unable to STREAM when replicating as TSQL

Share this Post

New for SQL 2008 is the ability for the Distribution Agent to push updates to the Subscriber using multiple connections.  This feature, called Subscription Streams, only works when replicating transactions as stored procedures and not TSQL statements.

See: Enhancing Transactional Replication Performance (Use the –SubscriptionStreams parameter for the Distribution Agent.)

Background

The published articles can be configured to replicate INS/UPD/DEL to the Subscribers using standard TSQL statements like “UPDATE SalesOrderDetail SET Price = 2 WHERE OrderId = 43659” or using the default method which is to call a Replication created stored procedures on the subscriber, passing as parameters the column to update and the table’s PrimaryKey.

To confirm which method is selected you can execute the Replication stored procedure sp_browsereplcmds show data queued in the Distribution database.  You can review the SQL BOL for optional parameters.

{CALL [dbo].[sp_MSupd_SalesSalesOrderDetail] (,,,2,,,,,,,43659,1,0×0800)}

You can also script out the publication and review the sp_addarticle statement.  The ‘SQL’ parameters as shown below indicate replication updates as TSQL commands to the subscriber and not the default replication created stored procedures.

@ins_cmd = N’SQL’, @del_cmd = N’SQL’, @upd_cmd = N’SQL’

SubscriptionStreams (hashkey)SubscriptionStreams

The SubscriptionStreams feature only supports replicating using the default replication stored procedures.  If this setting is customized to replicate using TSQL commands, Subscription Streams parameter will be ignored.  To confirm if replication configuration selected supports Streams, you can also query the distribution database MSrepl_commands and check hashkey column.  Value =0 indicates single stream, Values 0-63 indicates Streams are allowed.


Share this Post

About: ReplTalk


One thought on “Distribution Agent unable to STREAM when replicating as TSQL”

  1. Two things.  

    1) I remember streams being available in SQL 2005.

    2) There seems to be a conflict in your interpretation of the 0 value for the hashkey.  Specifically, if "Value =0 indicates single stream, Values 0-63 indicates Streams are allowed." is true, 0 indicates both a single stream and multiple streams.  That doesn't seem quite right.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.