In this post, we shall talk about reinitializing the replication to warm standby when it is already up and running for MSA(Multiple Subscriber Agent) to reporting or some other server.
Actually, I faced this issue few days ago when we had commissioned a new reporting server and wanted to offload the reporting load to this server and keep production only for transactions. When we started using this server as reporting, noticed that few tables aren’t getting updated from primary server. while checking, we found out that there are number of procedures where replication is set as off in the beginning of the code and marked as replication on at the end of the procedure – this was causing the operation inside procedure to not get replicated to secondary or reporting server. we commented this piece of code from most of the procedures and re-initialized replication to MSA and warm standby.
All were working as expected however, one of the procedure was missed which was responsible to generate monthly statement for customers, this was modified later on. When the monthly statement was getting generated, replication failed as data was out of sync and when replication process was trying to insert record – either it was already there, thus insert command failure(primary key constraint) or was trying to delete record which wasn’t there. In either case, replication failed and transactions started piling in the replication queue.
I discussed the same with core banking manager and since it was statement table, it was ok to drop the primary key at secondary/reporting server as statement deletes records of previous month for all the customers and inserts fresh set of records for current month with their transaction details. I dropped the primary key at secondary and reporting server – since reporting server is in the same data center – skipping number of transactions for delete and suspend and resume worked well and MSA kept running smoothly however for secondary due to different data center, it couldn’t catch up – replication kept failing and was stuck. Waited for long time however no lock. Finally, I had no choice but to re-initialize replication for secondary server without impacting MSA replication to reporting server.
I have covered this scenario in my video:
This video has all the scripts however if you would like to get a readily available scripts with more details, you may buy it at a very nominal price: