Replicating table articles can help you maintain multiple databases in sync. But what about stored procedures, views, and user defined functions? Do you have to apply the same code changes on a multitude of servers you manage? Fortunately, there is a way to synchronize the schema of non-table articles; and replicating the execution of stored procedures can provide better performance than replicating individual commands when adding, modifying, or removing rows from tables. Read on to find out how!

Like this article? We recommend

My previous articles introduced you to replication with Microsoft SQL Server
2000. After reading them, you should have a good grasp of replication concepts
and be ready to learn some helpful details. This article will show you how to
replicate code modules: stored procedures, user-defined functions (UDFs), and
views.

Replicating Non-Table Articles

Each SQL Server publication consists of articles. An article is a database
object: table, view, stored procedure, or UDF. Replicating code modules is based
on the same concept as replicating tables; however, there is a significant
difference.

When replicating table articles, data changes are immediately read by the log
reader agent and subsequently delivered to the subscribers by the distribution
agent. You can configure replication agents to either run continuously (thereby
assuring quicker delivery of changes) or periodically. By default, SQL Server
uses stored procedures to apply replicated transactions to the subscribers.

When you replicate views, UDFs and stored procedures changes to these objects
are not continuously sent to subscribers; indeed, it's difficult to imagine
an environment with a need to continuously update database code modules.
Instead, replicating code modules provides a database administrator (DBA) with a
convenient way to maintain an up-to-date copy of all code modules on a multitude
of servers. For example, suppose that you are a DBA managing 30 servers that
make up the backbone of the same application, each serving a different user
base. When you're deploying changes to your application you have a choice:
You can run the same scripts on 30 different servers, or deploy the scripts once
and replicate the schema of your code modules to the rest of the servers. If
you're like me and like to sleep at night, you'd choose the latter
option.

Yet another difference is that changes to the code modules are not applied
through stored procedures. Instead, such changes are delivered by taking a
snapshot of their content on the publisher and then applying that snapshot to
the subscriber(s).

Replicating stored procedures offers a special benefit; you can replicate not
only the schema of the procedures, but also their execution. In fact,
replicating the execution of stored procedures can be used as an alternative for
replicating table data. If you replicate tables, the log reader agent has to
record each data modification and translate it into a replication command. For
example, if you execute a stored procedure on the publishing server to update
sales, titles, and discount tables in a pubs database, replication translates it
into three transactionsone for updating each table.

A single stored procedure can modify numerous tables, so replicating its
execution can be considerably more efficient than splitting each command into
INSERT, UPDATE or DELETE of individual rows and replicating data changes that
way.

The next section teaches you how to set up replication of stored procedures,
views, and UDFs (and discusses the pros and cons of each).