- Replicating Non-Table Articles
- Setup
- Synchronizing Replicated Code Modules
- Stored Procedure Execution Options
- Executing Procedures Inside Serializable Transactions
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Stored Procedure Execution Options
Stored procedure articles can be replicated each time they're invoked on the publisher or only when executed within a serializable transaction. If you go back to the publication properties screen and attempt to modify the article properties, you'll see that you can't change the stored procedure execution options—they're grayed-out (see the following figure).
)
Figure
6To make stored procedure execution options available, you must drop any subscriptions from this publication and drop the article from the publication. After you add the article back to the publication, all options will be available to you again.
Why you would want to replicate the execution of a stored procedure? The sample procedure I supplied with this article populates the discounts table and then updates any related rows in the sales table. Suppose that I had replicated discounts and sales tables instead of replicating the execution of the populate_discounts procedure. And suppose that I executed the procedure as follows:
EXEC populate_discounts 'great discount', 6380, 10, 90, 20
This would result in the following three commands sent to the distribution database and then further down to the subscriber server:
{CALL sp_MSupd_sales (NULL,NULL,NULL,NULL,'Net 120',NULL,'6380','6871','BU1032',0x10)}
{CALL sp_MSupd_sales (NULL,NULL,NULL,NULL,'Net 120',NULL,'6380','722a','PS2091',0x10)}
{CALL sp_MSins_discounts ('great discount', '6380', 10, 90, 20.00)}
Note that for every row updated in the sales table, replication will issue a separate call to the sp_MSupd_sales procedure; similarly, every row inserted into the discounts table will translate into a call of sp_MSins_discounts. Now let's see what happens if we replicate only the execution of populate_discounts. First, as soon as we choose to replicate the execution of a stored procedure SQL Server will issue the following judicious warning.
)
Figure
7For now, choose Yes and continue. After we subscribe to the publication containing the populate_discounts procedure, let's execute the procedure on the publisher as follows:
EXEC populate_discounts 'fabulous discount', 7067, 100, 1000, 25
If we check the distribution database, this execution translates into a single command on the subscriber:
{call "dbo"."populate_discounts" ('fabulous discount',7067,100,1000,25)}
Because we have a fewer commands to move from the publisher to the distributor and then to the subscriber, the replicated transactions will be delivered more efficiently. This will result in a more scalable application that can support more transactions than if we were to replicate changes to individual tables.
However, SQL Server did not raise the warning we saw earlier by mistake; to ensure that all data changes get replicated to the subscribers, we would have to replicate the execution of all stored procedures that modify data. Furthermore, we would have to ensure that publisher and subscriber have the same data in all tables affected by the replicated stored procedures at all times. Otherwise, the stored procedure call that succeeds on the publisher might fail on the subscriber.