SQL Server: Replicating SP execution- Will the subscriber Database be updated twice if the table and SP on the same table (that inserts\updates\deletes) are the articles in the same or different publi

While many of you might have tested and implemented the stored procedure execution already, many might still be considering it and surely would be Googling or Binging around to find the right solution. I have done some testing that might help you .For testing I have I have used instances of SQL Server 2005 SP3 (CU2) and SQL Server is 2008 SP1.

Background:

Imagine a situation where your databases are in TBs or in hundreds of GBs and you are using transactional Replication without updatable subscription option. The subscribers are used for reporting propose only. The OLTP database goes thorough millions of inserts and updates everyday (with no deletes).Since there are no big batches of inserts and updates, it does not make sense to replicate the execution of stored procedure to subscriber in this case. However, if we decide to archive the data to keep the huge database size in control, then it makes sense to replicate the execution of stored procedures. Yes this is a real life scenario .We have a very large OLTP environment where millions of small queries do inserts and updates (No deletes) .The same is replicated to other subscribers .The data is so much that most of the time we firefight latency . So because of the size of the data we started archiving it .Archiving had two steps: Archive and then delete the data which is archived. All the deletes (in batches) from production environment are replicated to multiple subscribers through one distributor server.This adds to latency sometimes.

Proof of concept:

To reduce latency, we started thinking of replicating the execution of Stored Proc that deletes the rows in batches .This would not only give respite to log reader agent (which is one for every database ,irrespective of number of publications for that database ),But to multiple distributor agents as well that rely on msrepl_commands and msrepl_transactions tables in the distribution database .We will also save a lot of network bandwidth .As a result we will almost SKIP using distribution database for archiving because now we are just replicating the execution of Stored proc and not the actual deletes.

Issue that we thought we might face:
Things were not so easy for us (or might be for you as well as you read through) as I am typing here J .We already had all the required tables added as articles in the respective publications .So we had 2 doubts:

1)If we add the stored procedures in the current publication then we thought Replication will try to delete the data twice. For example Lets say there are 2 articles in the publication .The first one is a table (say REPL_TAB) and the second one is an SP (say REPL_SP) .REPL_SP deletes x rows from REPL_TAB.We feared that if we execute REPL_SP,it might affect the Subscriber table twice i.e. deletes 2 times .One: when the SP deletes the rows and two: since the rows are being deleted REPL_TAB should also replicate the same .So we thought this might not work .

2)We then thought of creating another publication with this SP added as an article but had same reservations. We cannot remove the previous articles because we not only delete but also insert and update.

But we think logically, Log Reader agent should pick up the command from the T-Log and should be smart enough to replicate it only once .I mean if I run EXEC XYZ which deletes 10 rows in a table ABC ,then it should only replicate EXEC XYZ and not the delete command because I am not firing the individual delete commands .

I first tested this in SQL Server 2005 SP3 CU2 and got it working .Let me show you what I mean:

-- Creating table
USE [AdventureWorksDW]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[REPL_test]([j] [int] Primary key) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

-- Inserting rows 
Declare @val int 
set @val = 10000
while (@val >0)
begin 
insert into DBO.REPL_TEST values (@val)
set @val=@val-1
end

-- Creating Stored procedure 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Create proc REPL_SP as 
delete top (10) from DBO.REPL_TEST
GO
SET ANSI_PADDING ON
GO

--Creating 1 Publication and 1 subscription (Note: Some parameter values might differ as per requirements .These scripts are for testing .Please refer BOL for more information):

-----------------BEGIN: Script to be run at Publisher-----------------
use [AdventureWorksDW]
exec sp_replicationdboption @dbname = N'AdventureWorksDW', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [AdventureWorksDW]
exec sp_addpublication 
		@publication = N'SP_TAB_PUB', 
		@description = N'Transactional publication of database ''AdventureWorksDW'' from Publisher ''L412APRIL2011''.', 
		@sync_method = N'concurrent', 
		@retention = 0, 
		@allow_push = N'true', 
		@snapshot_in_defaultfolder = N'true', 
		@repl_freq = N'continuous', 
		@status = N'active', 
		@independent_agent = N'true', 
		@replicate_ddl = 1, 
GO

exec sp_addpublication_snapshot 
		@publication = N'SP_TAB_PUB', 
		@frequency_type = 1, 
		@frequency_interval = 0, 
		@frequency_relative_interval = 0, 
		@frequency_recurrence_factor = 0, 
		@frequency_subday = 0, 
		@frequency_subday_interval = 0, 
		@active_start_time_of_day = 0, 
		@active_end_time_of_day = 235959, 
		@active_start_date = 0, 
		@active_end_date = 0, 
		@job_login = <Login_name>, 
		@job_password = <Password>, 
		@publisher_security_mode = 1

-- Adding articles to publication
use [AdventureWorksDW]
exec sp_addarticle 
		@publication = N'SP_TAB_PUB', 
		@article = N'REPL_test', 
		@source_owner = N'dbo', 
		@source_object = N'REPL_test', 
		@type = N'logbased', 
		@pre_creation_cmd = N'drop', 
		@schema_option = 0x000000000803509F, 
		@identityrangemanagementoption = N'manual', 
		@destination_table = N'REPL_test', 
		@destination_owner = N'dbo', 
		@vertical_partition = N'false',			
		@ins_cmd = N'CALL sp_MSins_dboREPL_test', 
		@del_cmd = N'CALL sp_MSdel_dboREPL_test', 
		@upd_cmd = N'SCALL sp_MSupd_dboREPL_test'
GO

use [AdventureWorksDW]
exec sp_addarticle 
		@publication = N'SP_TAB_PUB', 
		@article = N'REPL_SP', 
		@source_owner = N'dbo', 
		@source_object = N'REPL_SP', 
		@type = N'proc exec', 
		@description = N'', 
		@creation_script = null, 
		@pre_creation_cmd = N'drop', 
		@schema_option = 0x0000000008000001, 
		@destination_table = N'REPL_SP', 
		@destination_owner = N'dbo'
GO

--Creating subscriber 
use [AdventureWorksDW]
exec sp_addsubscription 
@publication = N'SP_TAB_PUB', 
@subscriber = N'<Subscriber Instance name>', 
@destination_db = N'ADW_SUB', 
@subscription_type = N'Push', 
@sync_type = N'automatic', 
@article = N'all', 
@update_mode = N'read only', 
@subscriber_type = 0
		
exec sp_addpushsubscription_agent 
@publication = N'SP_TAB_PUB', 
@subscriber = N'<Subscriber Instance name>', 
@subscriber_db = N'ADW_SUB', 
@job_login = <Login name>, 
@job_password = <Password>, 
@subscriber_security_mode = 1, 
@frequency_type = 64, 
@frequency_interval = 0, 
@frequency_relative_interval = 0, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 0, 
@frequency_subday_interval = 0, 
@active_start_time_of_day = 0, 
@active_end_time_of_day = 235959, 
@active_start_date = 20120126, 
@active_end_date = 99991231, 
@enabled_for_syncmgr = N'False', 
@dts_package_location = N'Distributor'
GO

-----------------END: Script to be run at Publisher-----------------

Once this is done you need to generate the snapshot to sync up the subscriber (If your subscriber already has the data, then please do not initialize the subscriber.

Let’s execute the stored procedure now and see if the data is replicated twice:

it worked like a charm and the logReader agent did not replicate the transaction twice .Our 1^st assumption was proved wrong and we were happy .Then we were asked to test the same by creating 2 new publications but keeping the subscriber database same. One with the table as an article and other with the SP as an article .This was asked (and it was a good question by the way )because many publications had same articles and different subscriptions .

We did that setup (skipping the script part):

We tested it again with fingers crossed:

Execution of SP :

Deleting top 10 rows :

This worked well too .With this test we were satisfied that Replication will not try to replicate twice to the subscriber database.You can query msrepl_transactions and ms_repl_commands as well to make sure that replication is behaving correctly .

Though this looks simple but many a time we are asked some questions to which we are unable to answer with 100% surity .This was one such example.

Hope you have found this post helpful . If you liked the posts, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks

Happy Learning 

Abhay