1

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?

  • 2
    Transactional replication replicates transactions, not the commands that are execute to create those transactions. – Max Vernon May 4 '15 at 16:52
2

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.

  • Ok. So this is possible. But how do we stop the rows that are inserted/updated/deleted in the source (production) from being replicated to the destination (reporting)? Does the publishing of the execution of the stored procedure someone stop this from happening? – Randy Minder May 4 '15 at 17:23
0

I have since come to learn, from this SO thread, that it is possible to do what I asked in my question. And it prevents rows inserted/updated/deleted on the source from being replicated to the destination.

  • I was thinking in a different context using we need to add a new column to the table this statement(deleted my current answer- even though I feel that it is somewhat relevant as people think that adding a column will require a new snapshot). Glad that you found my answer useful. – Kin Shah May 4 '15 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.