SQL Server Replication: MSins, MSdel, and MSupd Procedures Creation

sql serverssmsstored-procedurestransactional-replication

I have single sql instance and I have created replication of my database in same instance. Now when I have inserted data into the tables I found out that replication of insert stored procedure is getting created in the replica database. This happens for delete and update also. So now my replica database has MSins, MSupd and MSdel for all of my tables available in database. How to stop this?

Best Answer

That's the default design for SQL Server subscribers. Those procedures get created and hidden as system stored procedures, so, they don't normally display. If you're doing queries and picking them up then be sure to exclude system objects.

You can modify it to use INSERT/UPDATE/etc which is the default for non-SQL Server subscribers by following the instructions from BOL.

On the Properties tab of the Article Properties - dialog box, in the Statement Delivery section, specify the propagation method for each operation using the INSERT delivery format, UPDATE delivery format, and DELETE delivery format menus.

... or ...

execute sp_addarticle 
@ins_cmd - controls the replication of INSERT commands. 
@upd_cmd - controls the replication of UPDATE commands.
@del_cmd - controls the replication of DELETE commands.

When specifying a value of SQL for any of the above parameters, commands of that type will be replicated to the Subscriber as the appropriate Transact-SQL command.

Related Question