Customizing Distribution Agent Stored Procedures

You may have the need to customize the stored procedures used by the Distribution Agent to apply changes to the Subscriber. For example, if in your particular environment replicated DELETEs often fails with “Row not found” error, by now you’ve most likely change the Distribution Agent to use the “Continue on Errors” Profile or added to SQL Distribution Agent job parameters “-SkipErrors 20598”.

Having the Distribution Agent, upon detection of the “row not found” error, suspends delivery of transactions to “handle” that error, even if the eventual Agent profile settings say go ahead and continue will result in significant performance overhead slowing down transaction delivery.

However, if you’re expecting rows to be missing, you can modify the DELETE stored procedure on the subscriber created by Replication to simply ignore the problem and move on to the next transaction. This is done by altering the stored procedure logic as shown below where the “row not found” error handing logic has been commented out.

Comment out RaiseError

First step is to script out the specific stored procedure.

Next comment out the RaiseError logic as shown below. In SQL Management Studio you can select large section of code then CTRL-K, CTRL-C to comment out all the code selected.

Challenges

Challenge occurs when you need to initialize the subscriber, pushing down a new snapshot. The default behavior is to drop and recreate the replication created stored procedures, not good if they’ve already been customized.

Reinitialize Solution

On the Publication properties, under the table properties, change the settings to not copy the stored procedures to the subscriber. Neat right? (my daughter is little embarrassed when I use “neat” and “cool”, so I had to find a way to work it in)

New Subscribers

What  if we’re setting up a new subscriber who does not have the custom procedures?

You can go to any subscriber to this same publication and script out all stored procedures through SQL Management Studio. You’ll need to go to “Advanced” settings to select just stored procedures to script out.

Once you have the script, simply connect to the new-to-be subscriber and execute the script. If the new subscriber is an empty database, not yet initialize with tables from publisher, you can wait until the initial snapshot is applied, then stop the Distribution Agent, run the custom script, then restart the Distribution Agent.

Okay, but now you’re setting up a Publication for the first time, there is no subscriber, no stored procedures on a subscriber to script out?  Here comes sp_scriptpublicationcustomprocs to the rescue! This procedure will as-needed, create a TSQL script that contains all of the stored procedures required by a Subscriber. Simply execute on the publisher passing in the publication name.  Comes in handy if you’ve had a schema change on the Publisher and you need to refresh your custom procedures. You can script out updated system generated procedures, then add your customization.

Our documentation says only the execute the parent procedure, but you’ll notice we also provide names of all child procedures. Running the parent procedure ensures all required Replication procedures are created. If you have specific needs, for example, only needing the DELETE procedures, it takes a little investigative work to find a solution as shown below. You decide and use as appropriately.

While this example will SKIP error when DELETE detects no rows to delete, the same approach is possible if you’re expecting an UPDATE to fail with Not Found as the Subscriber contained a subset of Publisher data. Or you’re adding special auditing or other logic to the subscriber stored procedures. Key is to understand your data, your environment and do what make sense for your business needs.

Chris Skorlinski
Microsoft SQL Server Escalation Services