When most of build transactional publications we think of articles as tables,
but articles can actually be other objects including stored procedures, the
focus of this article. To get started I've created a database with a single
table called Employees and two stored procedures, one to add an employee and the
other to increase the pay of all employees. In the screenshot below you can set
the publication configuration.
You can see that I've elected to publish both stored procedures as articles.
If we highlight a stored proc and click on the article properties button, we can
see that we have three choices for how the stored procedure gets replicated. I'm
going to leave it set at the default 'Stored Procedure Definition Only' for now.
Once we add a subscriber and let the snapshot run, this is what we have on
Now let's do a couple quick tests. The first is to run the Add Employee proc
and see how the change is transmitted to the subscriber:
exec [sp_MSins_dboEmployees] 21,'2007-08-19 11:18:16:077','John','Public',NULL
This shows that we're calling the replication generated procedure on the
subscriber. Our Add Employee proc will never get called by the publisher, but it
might be called by some process on the subscriber. Now let's see what happens if
we modify the proc on the publisher - I'll add a comment to it. The following
was executed on the subscriber automatically:
ALTER PROCEDURE [dbo].[usp_Employee_Add] @FirstName varchar(50), @LastName varchar(50) as set nocount on --TEST COMMENT insert into dbo.Employees ( FirstName, LastName) values ( @FirstName, @LastName)
Changes to stored procedures are propagated to subscribers automatically if
you make them part of the publication. Remember, the stored procedure won't get
called as part of standard replication. It makes sense to replicate the
definition if you plan to use the subscriber as a standby server (having all
your proc changes kept in sync is pretty important) or if you've configured
replication for updating subscribers (would need the same stored procedures that
would normally get executed on the publisher).
Now let's revisit our settings and change it to replicate stored procedure
I'll execute the same IncreasePay proc on the publisher:
And then we see the same call executed on the subscriber:
This can be a huge performance advantage. I called a single proc that updated
all the rows on the publisher and then instead of transmitting a replication
proc call on the subscriber for each row changed, we just executed the same proc
on the subscriber(s). Given that the proc definition and data hasn't been manually
adjusted on the subscriber you wind up with exactly the same results for a lot
less work - one row logged and processed in the distribution database and one
row instead of x rows sent across the network We still have to do the actual
work on the subscriber of course; if the proc modifies a million rows there is a
certain amount of overhead that we pay regardless of how the update was issued.
And even though we've elected to replication stored procedure execution in this
case we still have the ability to directly modify the table and have those
changes replicated as usual.
The last option is 'Execution in a serialized transaction of the SP' and has
the same advantages of replicating procedure execution but it does so within a
serialized transaction, that is, one with the isolation level set to
SERIALIZABLE. Most of us don't use that high a setting often because it
typically has a negative impact on concurrency. If you need absolute perfection,
this is the way to go, because it eliminates the chance that the stored
procedure can affect a different number of rows because of other transactions
that are occurring at the same time. But in practice do you really need this?
The easy answer is to say yes and the possibly risky answer is to say no. As
with most things that have to do with SQL performance the answer is 'it depends'
and in this case, it depends on you looking at how and when data access occurs,
and your tolerance for having data wind up slightly different. It's probably not
a fair line to draw but I'm usually comfortable skipping serialization if the
changes are just being executed on a reporting server. No, it's not good to have
incorrect data, but if something happens I definitely do still have the
original/correct data. Even if you do select the serialize option you will
probably wind up a net gain compared to processing the individual row changes
that would normally be done.
As a follow up to that point, nothing says that you have to allow replication
of all stored procedures, or that they all have to be serialized or not. It's
entirely worthwhile to just create one stored procedure called something like 'usp_DBA_AdminProc'
and add it to the publication when you create it and set it to replicate stored
procedure execution. It's just for you, will never get called by an application.
But the next time there are a large number of rows to be modified you can alter
the proc to have the needed definition and then run it on the publisher,
probably at night or other lull. You get all the benefits of replicating
procedure execution and almost none of the possible downside because it's being
executed off peak. You can modify the proc again and again, changing it however
is needed, knowing that the right definition will be on each subscriber at the
The scenario where replicating execution makes the most sense would be where
you have a slow network connection, followed by when you are frequently updating
or deleting large numbers of rows, and the last would be when you just need to
reduce the latency between execution on the publisher and having the changes
fully applied on the subscriber. There can also be some reduction of load on the
distributor if the procs are updating large numbers of rows, but probably much
less savings if you are just replicating OLTP type procs that only affect a
small number of rows with each call.