Replicating Code Modules with SQL Server 2005
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.
Article Type |
Prerequisite |
View |
Tables referenced by the view must exist on the subscriber. Tables don’t have to be replicated, however. |
Indexed View |
Tables referenced by the indexed view must exist on the subscriber. Tables don’t have to be replicated, however. |
Indexed View |
Subscriber servers must be running SQL Server 2000 or later. All subscribers must use Enterprise Edition of SQL Server. |
Stored Procedure, UDF |
All objects referenced by the stored procedure or UDF must exist on the subscriber. Referenced objects don’t have to be replicated, however. |
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:
)
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.
Article Type |
Option/Value |
Description |
View |
Copy User Triggers: True or False |
Creates triggers on the replicated view in the subscription database if they exist on the publisher. |
View Indexed View Stored Procedure User Defined Function |
Copy Extended Properties: True or False |
Creates extended properties of the replicated view on subscriber. |
View Indexed View Stored Procedure User Defined Function |
Destination Object Name/Destination Object Owner |
You can create the replicated object with the same name and owner as on the publisher or different name and/or owner. |
View Indexed View Stored Procedure User Defined Function |
Action if name in use: Keep existing object unchanged OR Drop existing object and create a new one |
Note that if you keep the existing object, the definition of the code module on publisher and subscriber might be different. |
View Indexed View Stored Procedure User Defined Function |
Create Schemas at Subscriber: True or False |
Determines whether the CREATE SCHEMA statement should be executed on the subscriber if the schema to which an object belongs does not exist. |
Stored Procedure |
Replicate: Stored procedure definition only Execution of the stored procedure Execution in a serialized transaction of the SP |
Determines whether execution of the replicated stored procedure should be replicated. Note that the ALTER PROCEDURE statement will replicate schema changes even for publications that execute replicated stored procedures, so stored procedure definition changes will always be delivered to the subscribers (unless you explicitly turn off replicating schema changes, as shown later in this article). |
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).
)
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