Replicating Code Modules with SQL Server 2005

• By Baya Dewald
• Sep 15, 2006

Like this article? We recommend
Administrator's Guide to SQL Server 2005

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