I have 2 production servers and 2 development servers. All SQL Server 2008 r2.
In production, I had one of our DBAs create a replication publisher and distributor on ProdServer1 and Subscriber on ProdServer2. This has been running for a few months with out issue.
Now, I'm working on a new project and asked for replication to be set up in our dev environment, identical to production. So publisher and distributor on DevServer1 and Subscriber on DevServer2.
Now that this is complete, I'm looking at my databases and in DEV I have 3 stored procedures for each replicated table. They appear to be simple crud procedures (Insert, Update and Delete).
In production, I do not have these stored procedures. DBA number 2 is saying these are required for replication. However, Production is still working and these stored procs aren't there.
The DBA who set up the original replication in production isn't available so I can't ask what was done differently there.
Can anyone tell me what would cause these stored procs to be created in one instance yet not in another? This is very confusing to me, and has me concerned because my development environment is so much different from production.
Best Answer
Jeff - Have a look at the parameters for sp_addarticle. Notice the parameters @ins_cmd, @upd_cmd, and @del_cmd can be configured to replicate inserts, updates, and deletes as either stored procedure calls or straight SQL.
DBA number 1 likely setup the production publication to replicate inserts, updates, and deletes as SQL. DBA number 2 likely setup the development publication to replicate inserts, updates, and deletes through called stored procs.