We have a production server and a reporting server, with transactional replication in place to move all inserts/updates/deletes from production to reporting. We're using SQL Server 2008 R2.
We have one table with ~2.5B rows in it, and we need to add a new column to the table, and then put a calculated value in the column for each of the 2.5B rows. We'll create a T-SQL script that populates the new column with values. We want the new column and values replicated to reporting.
What we initially considered doing is to create a SQL job that executed every 30 seconds, and the job would have an embedded T-SQL statement that updated a batch of 25,000 rows for each iteration. At this rate, we determined we wouldn't overwhelm replication, even though it would take a long time to backfill all the rows. But this isn't a problem.
We just had one of our DBAs tell us that if we put the T-SQL that updated the 25,000 rows each time it's run, into a stored proc, and put the stored proc on both the production server and reporting server, then replication could be set up so that when the stored proc is run on the production server, the 25K rows that are updated will not replicate to reporting, but the stored proc on the reporting server will get called and update the same batch of rows on the reporting server, thus, essentially, eliminating any chance replication will get backed up on this table because, essentially, nothing is getting replicated.
I'd never heard of this being possible before. Can this be done? The DBA that is recommending this works remotely (out of the country) and I'm not able to talk to him about this at the moment (he's off for a while). But he is an experienced DBA. If replication cannot do this, what might he have in mind?
Best Answer
I believe your DBA is referring to Publishing Stored Procedure Execution in Transactional Replication.
With Transactional Replication you have the option to published the definition of the stored procedure or the execution of the stored procedure. Publishing stored procedure execution can provide significantly better performance for large batch operations since only the procedure execution is replicated, bypassing the operation being sent to Subscribers as a large, multi-step transaction.
If you have anymore questions please let me know. I hope this helps.