Distribution Agent unable to STREAM when replicating as TSQL

Chris Skorlinski
Microsoft SQL Server Escalation Services

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,0x0800)}

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.

SELECT TOP 100 [publisher_database_id]
      ,[type]
      ,[article_id]
      ,[hashkey]  -- > 0 value indicates single stream
FROM [distribution].[dbo].[MSrepl_commands]
ORDER BY [xact_seqno] DESC