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.
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 procedureor 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.
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.
Please refer to my InformIT article called "Setting Up Transactional Replication with SQL Server" for details.
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.