Replication Procedures Explained Part 1

I already wrote in few other posts about so called “replication procedures”. What are these you wonder? These procedures are used by transactional replication to do INSERT/UPDATES/DELETES on Subscriber DB.

If you did not rename it (or modify it on Publisher) the default “prefix” will be sp_MSins%, sp_MSupd%, sp_MSdel% etc. The exact proc names you can find in properties of any article published while using SSMS (Publication Properties->Articles):

procs_guI

Or just run select on published database and sysarticles table:

procs_tsql

Here is an example of Insert procedure:

procedure_text
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
if object_id(N'[sp_MSins_dboTableA]', 'P') <> 0 
                                        drop proc [sp_MSins_dboTableA]
go
if object_id(N'dbo.MSreplication_objects') is not null 
                                    delete from dbo.MSreplication_objects where object_name = N'sp_MSins_dboTableA'
go
create procedure [sp_MSins_dboTableA] 
  @c1 int,@c2 nvarchar(400),@c3 bigint
as 
begin 
insert into [dbo].[TableA]( 
 [id]
,[TableA_text]
,[id_temp]
 )
values ( 
 @c1
,@c2
,@c3
 ) 
end
go
if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') is not null 
exec ('insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values (
                                + N''sp_MSins_dboTableA'' , N''MAINTEST\PUBLISHER'' , N''AdventureWorks2014'' , N''Publication1'' , N''TableA'' ,''P'')')

 

These procs are executed on Subscriber when Insert/Update/Delete is performed. You most probably noticed that there is CALL or SCALL before each procedure. This determine how these commands are created and how data is replicated down. That topic is quite big and I will cover it in separate blog post.

These procedures DON’T exists as an objects on Publisher, however you can find these on Subscriber.

proc_subscriber

Replication procedures are delivered in two ways. If you initialize subscription with Snapshot the code for replication procedures is included in SCH (schema) files of the snapshot for given table. If you choose to not initialize the procedures will be delivered as normal command (you can actually browse it in distribution database). Important thing is that any changes to schema on published table will force replication to UPDATE all the procedures code  for changed tables and new version of procedures will be send down to Subscriber. Just a note on that, if you going over the WAN these procedures are delivered slowly, you just need to be aware of that especially if u changing hundreds on articles.

I already mentioned that these procedures do not exists as an object on Publisher. The question is how I can get code for these procedures on the Publishers? That is quite important question as there are some cases you will need the most “recent” procedure definition from Publisher. Remember that the procedure definition on Publisher is the actual one that is used by your replication at that point. You may have cases that code of replication procs been changed by someone or something on Subscriber and it caused replication to fail. In these cases you need the actual code from Publisher…. There are two ways of doing it.

  1. Script out all replication procedures for given Publication. Run following code:  exec sp_scriptpublicationcustomprocs @publication = ‘#publication name#’
  2. You can script out specific article and specific procedures (delete, insert, update etc.) First you need an article ID from sysarticles. Then once you have that you need to run following procedures in order to get procedure code:                                           exec  sp_scriptinsproc  #article_id# –for insert procedure                                           exec  sp_scriptupdproc  #article_id# –for update procedure                                          exec  sp_scriptdelproc  #article_id# –for delete procedure

Now there is problem with 2nd option. I already mention there are other ways of sending Updates or Deletes like SCALL, MCALL etc. If you using SCALL for example for updates you need to execute following: exec sp_scriptsupdproc. For MCALL  exec sp_scriptmappedupdproc. For XCALL: sp_scriptxupdproc etc. If you not sure what “call” you using just check sysarticles table on your Publisher.

Last thing you need to be aware is that when you generating code for these procedures into Text Results… you need to extend maximum characters for Text Results from default 256 to max which is 8192 (new setting will be only “active” when you open new query window). Otherwise some large procedures will be truncated and incomplete. I know small thing but still you need to be aware of this.

These are basics of replication procedures in next post I will focus on what problems and issues you may encounter with these.

One Reply to “Replication Procedures Explained Part 1”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s