Maintaining Transactional Replication

• By Baya Dewald
• Apr 30, 2004

Like this article? We recommend
Modernizing Legacy Systems: Software Technologies, Engineering Processes, and Business Practices

In previous articles of this series, I showed you how to plan for transactional replication, how to set it up, how to monitor it, and how to troubleshoot it. Contrary to what some may think, replication isn't maintenance-free. As your application changes, the set of data and transactions you need to replicate is likely to change, too. For example, when new tables are added to your database and new columns are added to the existing tables, you need to ensure that replication accounts for such changes. Furthermore, at some point after the initial replication setup, you might want to modify publications by adding filters to articles and thereby limiting the number of replicated transactions.

This article discusses the essentials of maintaining previously configured publications. In addition, I'll also tell you about factors affecting replication performance and how to deal with such factors.

Replication Stored Procedures

Before I show you how to modify existing publications, you need to become somewhat familiar with stored procedures executed on subscribers in lieu of commands issued on the publisher.

As discussed in previous articles, transactional replication replaces the INSERT, UPDATE, and DELETE commands that affect published articles with stored procedures. Although this is the default behavior, you can choose to replicate the INSERT, UPDATE, and DELETE commands as they are executed on the publisher. To do so, you can uncheck the check boxes on the Commands tab of the Table Article Properties screen, as shown in the following figure.

Figure 1

Keep in mind that the default behavior of executing stored procedures on the subscribers usually provides better performance than simply forwarding commands as they occur on the publishing server.

Stored procedures that are to be executed on subscribers are created during the initial synchronization; when you run the snapshot agent to create a snapshot of published articles, and the distribution agent moves data and indexes to the subscriber(s). Let's take a look at the stored procedures used for transactional replication of the author's table. Here is the procedure replacing the INSERT commands:

CREATE PROCEDURE [sp_MSins_authors] 
@c1 varchar(11),
@c2 varchar(40),
@c3 varchar(20),
@c4 char(12),
@c5 varchar(40),
@c6 varchar(20),
@c7 char(2),
@c8 char(5),
@c9 bit


AS
BEGIN
 
INSERT [authors]( 
[au_id], 
[au_lname], 
[au_fname], 
[phone], 
[address], 
[city], 
[state], 
[zip], 
[contract]
 ) 
values ( @c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9 )
 
END 

This simple procedure takes nine parameters, one for each column in the authors table. If you happened to add a column to the authors table, you could easily modify sp_msins_authors by adding the new column name and parameter number 10 for the new column.

The DELETE procedure is also very simple—it deletes a row based on the value of the primary key, which is the only parameter this procedure accepts:

CREATE PROCEDURE [sp_MSdel_authors] 
@pkc1 VARCHAR(11) 
AS 
DELETE [authors] 
WHERE [au_id] = @pkc1 

if @@ROWCOUNT= 0 	

if @@microsoftversion>0x07320000 		
exec sp_MSreplraiserror 20598 
GO

Notice that this procedure would raise an error if the value of the @@ROWCOUNT global variable is zero—meaning that no rows were affected on the subscriber when applying the replicated command. Unless you change the primary key column in the article, there is no need to modify the DELETE procedure executed on subscribers.

The stored procedure replacing the UPDATE commands is by far the most complicated. This procedure checks the bitmap parameter to see which columns have changed; it also executes a different subset of code depending on whether the primary key value is being updated. I added some comments and formatted the procedure for readability:

CREATE PROCEDURE [sp_MSupd_authors]  
@c1 VARCHAR(11),
@c2 VARCHAR(40),
@c3 VARCHAR(20),
@c4 CHAR(12),
@c5 VARCHAR(40),
@c6 VARCHAR(20),
@c7 CHAR(2),
@c8 CHAR(5),
@c9 BIT,
@pkc1 VARCHAR(11) ,
@bitmap BINARY(2) 
AS 

IF SUBSTRING(@bitmap,1,1) & 1 = 1 
/* that means primary key value has been modified */
BEGIN 

  UPDATE [authors] SET 
   [au_id] = CASE SUBSTRING(@bitmap,1,1) & 1 WHEN 1 THEN @c1 ELSE [au_id] END ,
   [au_lname] = CASE SUBSTRING(@bitmap,1,1) & 2 WHEN 2 THEN @c2 ELSE [au_lname] END ,
   [au_fname] = CASE SUBSTRING(@bitmap,1,1) & 4 WHEN 4 THEN @c3 ELSE [au_fname] END ,
   [phone] = CASE SUBSTRING(@bitmap,1,1) & 8 WHEN 8 THEN @c4 ELSE [phone] END ,
   [address] = CASE SUBSTRING(@bitmap,1,1) & 16 WHEN 16 THEN @c5 ELSE [address] END ,
   [city] = CASE SUBSTRING(@bitmap,1,1) & 32 WHEN 32 THEN @c6 ELSE [city] END ,
   [state] = CASE SUBSTRING(@bitmap,1,1) & 64 WHEN 64 THEN @c7 ELSE [state] END ,
   [zip] = CASE SUBSTRING(@bitmap,1,1) & 128 WHEN 128 THEN @c8 ELSE [zip] END ,
   [contract] = CASE SUBSTRING(@bitmap,2,1) & 1 WHEN 1 THEN @c9 ELSE [contract] END 
  WHERE [au_id] = @pkc1 

IF @@ROWCOUNT = 0 	
IF @@microsoftversion>0x07320000 		
EXEC sp_MSreplraiserror 20598 
END 

ELSE /* PRIMARY KEY VALUE HASN'T BEEN CHANGED */ 

BEGIN 
  UPDATE [authors] SET 
   [au_lname] = CASE SUBSTRING(@bitmap,1,1) & 2 WHEN 2 THEN @c2 ELSE [au_lname] END ,
   [au_fname] = CASE SUBSTRING(@bitmap,1,1) & 4 WHEN 4 THEN @c3 ELSE [au_fname] END ,
   [phone] = CASE SUBSTRING(@bitmap,1,1) & 8 WHEN 8 THEN @c4 ELSE [phone] END ,
   [address] = CASE SUBSTRING(@bitmap,1,1) & 16 WHEN 16 THEN @c5 ELSE [address] END ,
   [city] = CASE SUBSTRING(@bitmap,1,1) & 32 WHEN 32 THEN @c6 ELSE [city] END ,
   [state] = CASE SUBSTRING(@bitmap,1,1) & 64 WHEN 64 THEN @c7 ELSE [state] END ,
   [zip] = CASE SUBSTRING(@bitmap,1,1) & 128 WHEN 128 THEN @c8 ELSE [zip] END ,
   [contract] = CASE SUBSTRING(@bitmap,2,1) & 1 WHEN 1 THEN @c9 ELSE [contract] END ,
  WHERE [au_id] = @pkc1 

IF @@ROWCOUNT = 0 	
IF @@microsoftversion>0x07320000 		
EXEC sp_msreplraiserror 20598 
END 

As you can tell, the UPDATE procedure uses various portions of the bitmap parameter to see which column has changed. If the column hasn't changed, the procedure sets the column to its existing value. If you add a column to an existing article, the UPDATE procedure will have to change slightly to reflect the new column. We'll see how to do that in a bit.