Replicating Code Modules with SQL Server 2005

Date: Sep 15, 2006

Return to the article

Previous articles in this series taught you how to replicate table articles using SQL Server 2005. As with previous releases of the software, SQL Server 2005 enables you to also replicate code modules: stored procedures, views (including indexed views), and user-defined functions (UDFs). This article will give you an overview of replicating code modules and explain when it is beneficial to do so.

Code Module Replication Setup

You can configure code module replication much the same way as table replication. In fact, the same publication can contain table, indexed view, view, UDF, and stored procedure articles. Prerequisites for replicating each type of code module are detailed in the following table.

You can use the Publication Wizard to configure replication for views, UDFs, and stored procedures. Once you choose the database in which you’re about to create the publication and publication type you can choose articles—the following figure adds a view, an indexed view, a stored procedure, and a UDF to a publication:

Figure 1

You can click the Article Properties button to configure options for each article you add to the publication. You can set several options for each type of replicated code module. You can also replicate the schema of views, indexed views and UDFs. Stored procedures provide additional flexibility—you can replicate their execution as well as their definition. The following table summarizes the options you can configure when replicating code modules.

Once you have set the properties of each article, you can advise SQL Server to create a snapshot of the publication immediately and/or create a schedule for the Snapshot agent. Next, you specify the security settings for the Snapshot agent and Log Reader agent, review the summary of the actions the wizard is about to undertake, and click the Finish button to create the publication. For a detailed review of each screen of the Publication Wizard, please refer to the first article of this series (the screens for code module articles are nearly identical to those used for table articles).

The Publication Wizard warns you of prerequisites for each type of code module you’re trying to replicate. However, you can create the publication even if dependent objects do not exist on the subscribers.

The process of creating a subscription for a publication that replicates code modules is identical to that of replicating table articles. If any of the objects referenced by replicated code module doesn’t exist on the subscriber, the Distribution agent will fail, but the subscription will still be created. The error message generated by the Distribution agent will help you determine the cause of the problem because it provides the attempted command (see the following figure).

Figure 2

The following script creates a publication with stored procedure, view, and UDF articles:

-- Adding the transactional publication
exec sp_addpublication @publication = n’pub_name’, 
@description = 
N’Transactional publication of database ’’AdventureWorksDW’’ .’, 
@sync_method = N’concurrent’, @retention = 0, @allow_push = N’true’, 
@allow_pull = N’true’, @allow_anonymous = N’true’, 
@enabled_for_internet = N’false’, @snapshot_in_defaultfolder = N’true’, 
@compress_snapshot = N’false’, @ftp_port = 21, @ftp_login = N’anonymous’, 
@allow_subscription_copy = N’false’, @add_to_active_directory = N’false’, 
@repl_freq = N’continuous’, @status = N’active’, 
@independent_agent = N’true’, @immediate_sync = N’true’, 
@allow_sync_tran = N’false’, @autogen_sync_procs = N’false’, 
@allow_queued_tran = N’false’, @allow_dts = N’false’, @replicate_ddl = 1, 
@allow_initialize_from_backup = N’false’, @enabled_for_p2p = N’false’, 
@enabled_for_het_sub = N’false’
GO

-- Adding the transactional articles
-- UDF:
exec sp_addarticle @publication = n’pub_name’, 
@article = N’udfMinimumDate’, @source_owner = N’dbo’, 
@source_object = N’udfMinimumDate’, @type = N’func schema only’, 
@description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’, 
@schema_option = 0x0000000008000001, @destination_table = N’udfMinimumDate’, 
@destination_owner = N’dbo’, @status = 16
GO
-- stored procedure:
exec sp_addarticle @publication = n’pub_name’, 
@article = N’update_factFinance’, @source_owner = N’dbo’, 
@source_object = N’update_factFinance’, @type = N’proc exec’, 
@description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’, 
@schema_option = 0x0000000008000001, @destination_table = N’update_factFinance’, 
@destination_owner = N’dbo’, @status = 0
GO
-- indexed view:
exec sp_addarticle @publication = n’pub_name’, 
@article = N’View_DimCustomer_Young’, @source_owner = N’dbo’, 
@source_object = N’View_DimCustomer_Young’, @type = N’indexed view schema only’, 
@description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’, 
@schema_option = 0x0000000008000001, @destination_table = N’View_DimCustomer_Young’, 
@destination_owner = N’dbo’, @status = 16
GO
-- view:
exec sp_addarticle @publication = n’pub_name’, 
@article = N’vTimeSeries’, @source_owner = N’dbo’, 
@source_object = N’vTimeSeries’, @type = N’view schema only’, @description = N’’, 
@creation_script = N’’, @pre_creation_cmd = N’drop’, 
@schema_option = 0x0000000008000001, @destination_table = N’vTimeSeries’, 
@destination_owner = N’dbo’, @status = 16
GO

Replicating Schema Changes

Recall that with previous versions of SQL Server you had to run the Snapshot agent to deliver replicated code modules’ definition changes. This is no longer the case with SQL Server 2005: the replication propagates ALTER VIEW, ALTER FUNCTION, ALTER PROCEDURE, and ALTER TRIGGER statements to the subscribers in real time. Replicating triggers isn’t one of the options you can pick within the article types, but you can copy triggers defined on tables and views in the publication database. Keep in mind that you cannot replicate data definition language (DDL) triggers.

Let’s see how we can replicate a change to an indexed view. I created a very simple indexed view on the publisher using the following commands:

CREATE VIEW [dbo].[View_DimCustomer_Young]
WITH SCHEMABINDING 
AS
SELECT CustomerKey, GeographyKey, FirstName, LastName, BirthDate
FROM  dbo.DimCustomer
WHERE (BirthDate > CONVERT(SMALLDATETIME, ’1/1/1980’, 101))
GO

CREATE UNIQUE CLUSTERED INDEX [ix_DCY_CustomerKey] ON [dbo].[View_DimCustomer_Young] 
(
	[CustomerKey] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) 
ON [PRIMARY]

This view returns a few columns from the DimCustomer table for customers who were born after January 1, 1980. I added this indexed view to a transactional publication and created a subscription to it from another server.

Now let me change the view slightly to return customers born after January 1, 1978. I executed the following ALTER VIEW statement:

ALTER VIEW [dbo].[View_DimCustomer_Young]
WITH SCHEMABINDING 
AS
SELECT CustomerKey, GeographyKey, FirstName, LastName, BirthDate
FROM  dbo.DimCustomer
WHERE (BirthDate > CONVERT(SMALLDATETIME, ’1/1/1978’, 101))

Now if I execute sp_browserplcmds system procedure against the distribution database, I’ll find the same ALTER VIEW statement being delivered to the subscribing database.

Replicating code modules is particularly useful if you work in a multiserver environment in which you balance the application load across multiple servers with identical views, UDFs and stored procedures. Instead of running scripts to apply the same schema changes on 30 different servers, you can simply deploy changes to a single publishing server and let those changes replicate to multiple subscribers. In previous versions of SQL Server you had to run the Snapshot agent to deliver the schema changes, but with version 2005, schema changes will be delivered much like data changes. This should make your deployments considerably faster and easier.

In rare cases, you might not want to replicate schema changes until the Snapshot agent runs; for example, you might want to test your stored procedure changes against a single server before deploying these changes to all production servers. You can turn off replicating schema changes in the subscription options’ page of publication properties dialog box shown below.

Figure 3

Replicating Indexed Views as Tables

Indexed views support the capability to be replicated as tables; in this case, SQL Server creates a table on the subscriber that contains the same data as the indexed view on the publisher. Data changes to the indexed view on the publisher are replicated to the table on the subscriber. Note that the table on which the indexed view is based does not have to exist on the subscriber.

Replicating indexed views as tables can be useful if you need only a subset of a given table’s data on the subscriber. For example, if I want to have only customers that have a birth date after 1/1/1978 on the subscriber, I can replicate the indexed view shown earlier in the article instead of replicating the entire dimCustomer table.

To replicate an indexed view as a table, you need to modify the @type parameter of the sp_addarticle system procedure. By default, this parameter has a value of N’indexed view schema only’ for indexed views; you need the value of N’indexed view logbased’. For example, the following statement View_DimCustomer_Young adds an indexed view to an existing publication to replicate as a table:

exec sp_addarticle 
@publication = N’publication_name’, 
@article = N’View_DimCustomer_Young’, 
@source_owner = N’dbo’, 
@source_object = N’View_DimCustomer_Young’, 
@type = N’indexed view logbased’, 
@description = null, 
@creation_script = null, 
@pre_creation_cmd = N’none’, 
@schema_option = 0x0000000008000001, 
/* table name doesn’t have to be the same as view name */
@destination_table = N’View_DimCustomer_Young’, 
@destination_owner = N’dbo’
GO

Once you configure an indexed view to replicate as a table, the INSERT, UPDATE, and DELETE statements executed against the view on the publisher will be replicated to the respective table on the subscriber.

Replicating Execution of Stored Procedures

You can replicate the execution of stored procedures, which is very useful if you want to make bulk changes to the existing data and if you’re certain that data values on the publisher and subscriber are identical. What happens if your UPDATE statement affects 1,000 rows of a replicated table? By default, SQL Server translates a single UPDATE command into 1,000 executions of the replication stored procedure. This works well because each call of the replication stored procedure affects a single row, and you don’t experience many locking/contention issues on the subscriber.

But what if you have a stored procedure that makes batch updates to millions of rows in multiple tables? Your distribution database will grow exponentially, and replication latency could become unacceptable. SQL Server reads replicated commands from the msrepl_commands table in the distribution database before delivering these commands to the subscriber; the distribution cleanup job reads the same table and deletes any transactions that have already been delivered. If msrepl_commands contains many millions of rows, reading and deleting data from this table will become very slow. In addition, replicating bulk changes using table articles will place a considerable burden on your network.

Replicating the execution of stored procedures provides a viable alternative because it executes the same stored procedure on the publisher and subscriber, thereby minimizing the network traffic and number of commands in the distribution database. If I need to make bulk changes to 50 million rows and I know that publisher and subscriber(s) have the same data, it would be more efficient to replicate the execution of a stored procedure that implements these changes.

Another alternative for replicating bulk changes to a single table is to replicate the table by executing the same UPDATE statement on publisher and subscriber. (Refer to the first article in this series to learn how.) Replicating stored procedure execution is still a better choice, however, if you’re making bulk changes to multiple tables.

For example, suppose that I have a stored procedure that updates certain rows in the factFinance table in the AdventureWorksDW sample database based on the supplied parameters, like this:

CREATE PROC update_factFinance (
 @PercentChange NUMERIC (3,2), 
 @OrganizationKey TINYINT, 
 @TimeKey INT)
AS

/*
Change the amount for given organization key 
*/
UPDATE factFinance
SET amount = amount * @PercentChange
WHERE OrganizationKey = @OrganizationKey
AND TimeKey = @TimeKey 

I’ll create a publication that replicates the execution of this stored procedure. Every time I execute this procedure on the publisher, the Distribution agent will deliver a command similar to the following to the subscriber:

{call "dbo"."update_factFinance " (1.10, 3, 32)}

Keep in mind that replication will simply deliver this command; replication won’t check whether the command affects the same rows on the publisher and subscriber. So to maintain data consistency on publisher and subscriber, you need to ensure that the same data values exist on both servers prior to executing the replicated stored procedure.

Replicating Execution of Stored Procedures within a Serializable Transaction

You can configure stored procedure articles to replicate only if they’re executed within a serializable transaction; this requires two conditions to be met before the execution of the procedure is propagated to the subscriber:

• Transaction isolation level of the connection executing the stored procedure must be set to SERIALIZABLE.
• You must enclose the execution of the stored procedure within an explicit transaction using BEGIN TRANSACTION / COMMIT TRANSACTION statements.

If either of those criteria is not met, the execution of the stored procedure is not replicated. In addition to satisfying the above criteria, you should also use the SET XACT_ABORT ON option. This option ensures that the transaction encapsulating the execution of the stored procedure is automatically rolled back if any run-time errors are encountered.

Replicating the execution of a stored procedure within a serializable transaction is a recommended option if you need to ensure data consistency on publisher and subscribers. Why? Each stored procedure could include multiple implicit or explicit transactions; you might encounter situations when some transactions within the stored procedure succeed and others fail. If you advise SQL Server to replicate every execution of the stored procedure, even those executions in which transactions failed will be forwarded to the subscriber. Serializable isolation, which is the most restrictive isolation level, ensures that locks are held on all tables affected by the stored procedure until you commit the transaction. Therefore, replicating only the executions within a serializable transaction enables you to guarantee that a procedure completes its work successfully on the publisher before it is sent to subscribers.

Let’s use the update_factFinance procedure again to demonstrate how you can replicate its execution within a serializable transaction. The following execution will be replicated:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET XACT_ABORT ON
BEGIN TRAN
EXEC update_factFinance 1.10, 3, 32

COMMIT

However, the following command will not be replicated because it is not enclosed in an explicit transaction:

EXEC update_factFinance 1.10, 3, 32

Now we intentionally break the execution of the procedure to demonstrate the significance of the XACT_ABORT setting. I change the amount column of the factFinance table to have the SMALLINT data type instead of INT by executing the following statement:

ALTER TABLE factFinance ALTER COLUMN amount SMALLINT

The largest value supported by the SMALLINT data type is 32768; multiplying the maximum value of the amount column by 1.15 will result in a value greater than 32768, so the following execution of the update_factFinance stored procedure will generate an error:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
/* CORRECT setting*/
SET XACT_ABORT ON
BEGIN TRAN
EXEC update_factFinance 1.15, 3, 32

COMMIT

The result is the following:

Msg 8115, Level 16, State 2, Procedure update_factFinance, Line 10
Arithmetic overflow error converting expression to data type smallint.

The transaction is rolled back, and this execution of the stored procedure is not sent to the subscriber.

Next, I execute the same set of commands, except with an incorrect setting of XACT_ABORT:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
/* INCORRECT setting! For demonstration purposes ONLY! */
SET XACT_ABORT OFF
BEGIN TRAN
EXEC update_factFinance 1.15, 3, 32

COMMIT

The execution of the procedure fails, generating the same error as before. However if I check the distribution database now, I will find the following command sent to the subscriber:

{call "dbo"."update_factFinance" (1.15,3,32)} 

The execution of the procedure is replicated to the subscriber even though its execution failed on the publisher. Doing so could cause the Distribution agent to fail. More importantly, it could cause inconsistent data values on publisher and subscriber. So always use SET XACT_ABORT ON when replicating the execution of stored procedures.

Summary

This article showed you how to replicate stored procedures, views, and UDFs using SQL Server 2005. When compared with previous versions, perhaps the most important improvement is the support for applying code changes to the subscribers without running the Snapshot agent.

This series of articles introduced you to transactional replication with SQL Server 2005. Building on the solid foundation of previous releases of the software replication with SQL Server 2005 is a mature technology that can support enterprise class applications. Much like any technology, replication works well if it is used appropriately and for the right purpose. Be sure to learn how replication is implemented and plan carefully before implementing a replication solution.

© 2024 Pearson Education, Informit. All rights reserved.

800 East 96th Street, Indianapolis, Indiana 46240