Replicating Code Modules in SQL Server

Date: Oct 15, 2004

Return to the article

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).

Setup

Setting up replication of views, UDFs, and stored procedures is very similar to setting up transactional replication for table articles. Please refer to my earlier articles for a detailed overview of transactional replication setup.

On the Specify Articles tab of the Create Publication Wizard you can select views, stored procedures, or UDFs instead of tables, as shown in the following figure.

Figure 1

Note that I added a stored procedure populate_discounts and a UDF called udf_check_business_day to the pubs database specifically for this article. If you want to follow along with the examples shown here, run the following script in your pubs sample database:

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'p' AND name = 'populate_discounts')
BEGIN 
    DROP PROCEDURE populate_discounts
END
GO
CREATE PROC populate_discounts ( 
    @discounttype VARCHAR(45), 
    @stor_id INT, 
    @lowqty INT, 
    @highqty INT, 
    @discount INT) 
AS 
SET NOCOUNT ON 
BEGIN TRAN 
INSERT discounts ( 
    discounttype, 
    stor_id, 
    lowqty, 
    highqty, 
    discount) 
SELECT 
    @discounttype, 
    @stor_id , 
    @lowqty , 
    @highqty , 
    @discount 
IF @@ERROR <> 0 
    BEGIN 
        RAISERROR('did not work, please try again', 16, 1) 
        ROLLBACK 
        RETURN 
    END 
UPDATE sales 
SET payterms = 'Net 120' 
WHERE stor_id = @stor_id 
IF @@ERROR <> 0 
    BEGIN 
        RAISERROR('did not work, please try again', 16, 1) 
        ROLLBACK 
        RETURN 
    END 
COMMIT TRAN 
GO


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'fn'
AND name = 'udf_check_business_day')
BEGIN
    DROP FUNCTION udf_check_business_day
END
GO

CREATE FUNCTION udf_check_business_day (@ord_num VARCHAR(15)) 
RETURNS BIT 
AS 
BEGIN 
DECLARE @business_day INT 
SELECT @business_day = CASE WHEN DATEPART(WEEKDAY, ord_date) NOT IN (1, 7) THEN 1 ELSE 0 END 
FROM sales WHERE ord_num = @ord_num 
RETURN @business_day 
END

Each type of code module has different replication options that you can choose during setup. All objects allow you to include extended properties along with the article's schema. If you're not familiar with extended properties, they're a neat way to track application-specific metadata within the database. When replicating views, you can also replicate triggers defined on the views. Note that although triggers are essentially a special case of stored procedures, you cannot explicitly replicate the execution or schema of the triggers. Stored procedures allow you to replicate each execution of the procedure—or only those executions included within a serializable transaction. All these replication options are configured using the Other tab of the articles. The following figure shows the Other tab of Stored Procedure Article Properties.

Figure 2

When you publish code modules, the Create Publication Wizard warns you that your application might require changes in order to work as expected. To create views, UDFs, and stored procedures on the subscriber, you must first have all tables, other views, or UDFs referenced by these code modules. After you ensure that all necessary objects exist on the subscriber(s), you can complete the wizard.

Setting up the subscription for publications, including nontable articles, is identical to that for subscribing to table publications.

Although you could replicate the execution of procedures that only read data, you should replicate only the execution of your stored procedures that modify data. If a procedure doesn't change any data, it really makes no sense to replicate its execution unless you want to duplicate the load of the production server on a non-production computer to undertake some sort of performance testing. If so, you should consider using the replay functionality available with SQL Profiler instead of replicating stored procedures.

Synchronizing Replicated Code Modules

Non-table articles are synchronized only when the Snapshot agent runs and updates subscriber databases. For this article, I published the stored procedure and UDF that you saw earlier, as well as titleview view included with pubs database.

Immediately after creating the subscription, Enterprise Manager reports that subscription's status is Pending (as shown in the following figure) because the Snapshot agent hasn't taken the snapshot of the articles yet.

Figure 3

After you run the Snapshot agent, it creates the scripts for generating the replicated articles on the subscriber. These scripts reside in the snapshot directory you specified during publication setup. (By default, this directory is Program Files\Microsoft SQL Server\MSSQL\REPLDATA\UNC\server_name_publication_name_subscription_name.) After the log reader and distribution agent run, SQL Server will delete the snapshot scripts. After the initial snapshot has been delivered, the Status column will say Active.

Although you can run the Snapshot agent as often as you like, it generates the snapshot of the published articles only if the subscriptions are marked for re-initialization. If the Snapshot agent doesn't find any subscriptions that need to be re-initialized, it simply returns the following message within the replication monitor (as shown in the following figure): "A snapshot was not generated because no subscriptions needed initialization."

Figure 4

You might think that if you change the replicated code module's definition that SQL Server would automatically reinitialize the subscriptions, but if you modify the replicated stored procedure and run the Snapshot agent manually, it still reports the same message. Furthermore, if you check the replicated procedure's code on the subscriber, you'll notice that changes haven't been applied.

To reinitialize the subscription, you need to navigate to the publication within Enterprise Manger, right-click the desired subscription, and choose Re-initialize. SQL Server then executes a system stored procedure called sp_reinitsubscription, which in turn calls sp_changesubstatus—this procedure modifies the Status column of the syssubscriptions system table found in the published database. It is the Status column that determines whether the subscription is re-initialized. When you re-initialize the subscription, Enterprise Manager changes the value of the Status column to Pending, just as it appeared during the initial synchronization. Next time the Snapshot agent runs, it will report a message stating that the snapshot of replicated articles has been generated, as shown in the following figure.

Figure 5

If you examine the replicated articles, you'll notice that changes have made their way to the subscriber.

Stored Procedure Execution Options

Stored procedure articles can be replicated each time they're invoked on the publisher or only when executed within a serializable transaction. If you go back to the publication properties screen and attempt to modify the article properties, you'll see that you can't change the stored procedure execution options—they're grayed-out (see the following figure).

Figure 6

To make stored procedure execution options available, you must drop any subscriptions from this publication and drop the article from the publication. After you add the article back to the publication, all options will be available to you again.

Why you would want to replicate the execution of a stored procedure? The sample procedure I supplied with this article populates the discounts table and then updates any related rows in the sales table. Suppose that I had replicated discounts and sales tables instead of replicating the execution of the populate_discounts procedure. And suppose that I executed the procedure as follows:

EXEC populate_discounts 'great discount', 6380, 10, 90, 20

This would result in the following three commands sent to the distribution database and then further down to the subscriber server:

• {CALL sp_MSupd_sales (NULL,NULL,NULL,NULL,'Net 120',NULL,'6380','6871','BU1032',0x10)}

• {CALL sp_MSupd_sales (NULL,NULL,NULL,NULL,'Net 120',NULL,'6380','722a','PS2091',0x10)}

• {CALL sp_MSins_discounts ('great discount', '6380', 10, 90, 20.00)}

Note that for every row updated in the sales table, replication will issue a separate call to the sp_MSupd_sales procedure; similarly, every row inserted into the discounts table will translate into a call of sp_MSins_discounts. Now let's see what happens if we replicate only the execution of populate_discounts. First, as soon as we choose to replicate the execution of a stored procedure SQL Server will issue the following judicious warning.

Figure 7

For now, choose Yes and continue. After we subscribe to the publication containing the populate_discounts procedure, let's execute the procedure on the publisher as follows:

EXEC populate_discounts 'fabulous discount', 7067, 100, 1000, 25

If we check the distribution database, this execution translates into a single command on the subscriber:

{call "dbo"."populate_discounts" ('fabulous discount',7067,100,1000,25)}

Because we have a fewer commands to move from the publisher to the distributor and then to the subscriber, the replicated transactions will be delivered more efficiently. This will result in a more scalable application that can support more transactions than if we were to replicate changes to individual tables.

However, SQL Server did not raise the warning we saw earlier by mistake; to ensure that all data changes get replicated to the subscribers, we would have to replicate the execution of all stored procedures that modify data. Furthermore, we would have to ensure that publisher and subscriber have the same data in all tables affected by the replicated stored procedures at all times. Otherwise, the stored procedure call that succeeds on the publisher might fail on the subscriber.

Executing Procedures Inside Serializable Transactions

Recall that you can replicate each execution of the stored procedure or only those executions that occur within a serializable transaction. Serializable transactions require (not surprisingly) that the transaction isolation level be set to SERIALIZABLE.

If we replicate each execution of the stored procedure, SQL Server will attempt replicating even those executions that error out on the publisher. For example, suppose that I execute the populate_discounts procedure with an incorrect store id:

EXEC populate_discounts 'special special discount', 134567, 100, 1000, 25

Results:

Server: Msg 547, Level 16, State 1, Procedure populate_discounts, Line 10
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__discounts__stor___0F975522'. The conflict occurred in database 'pubs', table 'stores', column 'stor_id'.
Server: Msg 50000, Level 16, State 1, Procedure populate_discounts, Line 24
did not work, please try again
The statement has been terminated.

Even if the execution of the procedure has failed on the publisher, SQL Server still attempts to execute it on the subscriber. The sp_browsereplcmds procedure executed against the distribution database reports the following:

{call "dbo"."populate_discounts" ('special special discount',134567,100,1000,25)}

Incidentally, this stored procedure call also fails on the subscriber server because store id = 134567 does not exist on the subscribed database, either.

Now let's see what happens if we choose to replicate only those executions of the procedure that are enclosed within a SERIALIZABLE transaction. Keep in mind that you'll have to drop the subscription as well as the populate_discounts article from the publication before you can configure this procedure to replicate only when executed within a serializable transaction. After you have configured replication, let's attempt to execute the following:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
GO
BEGIN TRAN 
EXEC populate_discounts 'fabulous customer special', 114251, 50, 1000, 25 

IF @@ERROR <>0 
    BEGIN 
        ROLLBACK 
    END 
ELSE
COMMIT

This execution fails because store id = 114251 does not exist. If you check the distribution database, this command is never considered for replication. The log reader agent simply reports that no replicated transactions are available. Now let's execute the same procedure with a proper store id:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
GO 
BEGIN TRAN
EXEC populate_discounts 'customer appreciation special', 7066, 50, 1000, 25 


IF @@ERROR <>0 
    BEGIN 
        ROLLBACK 
    END 
ELSE
COMMIT

This execution works on the publisher and is replicated to the subscriber. So replicating the execution of procedures only within a serializable transaction guarantees that they will be executed on the subscriber only if they succeed on the publisher. Keep in mind, however, that the SERIALIZABLE isolation level is the most restrictive locking mode and can have a negative performance impact on your application.

Summary

This article showed you how to use transactional replication to replicate the schema and execution of stored procedures as well as the schema of views and UDFs. Although similar to replicating table data, replicating code modules is quite different because it requires re-initializing subscriptions. Unlike replicating table data, the schema of code modules is replicated only when the Snapshot agent runs and generates the snapshot for initialized subscriptions.

SQL Server supports replicating the execution of stored procedures. With careful planning, you can take advantage of this functionality to replicate data changes more efficiently than with table replication.

The next article in this series will continue exploring the details of transactional replication. Stay tuned!

© 2024 Pearson Education, Informit. All rights reserved.

800 East 96th Street, Indianapolis, Indiana 46240