Replicating Code Modules in SQL Server
- Replicating Non-Table Articles
- Setup
- Synchronizing Replicated Code Modules
- Stored Procedure Execution Options
- Executing Procedures Inside Serializable Transactions
- Summary
Like this article? We recommend
Like this article? We recommend
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).