0

I need the best way for the scenario because of which I got irritated. I do have 3 Production database Servers Srv1, Srv2 and Srv3. I configure transactional replication among these servers. Srv1 is Publisher and Srv2 and Srv3 are subscribers.

On production server everything running fine without any problem.

I have QA environment same as production replication configured. On request of QA team, I need to refresh QA with production databases weekly (almost), which cause to fail replication in QA environment.

Because I have 1000 tables dropping and re-creating publications takes too much time on Srv1.

If I refresh database without dropping publication and recreate publication using backup script I get The process could not execute ‘sp_repldone/sp_replcounters’

Thanks

1

What @Kin said is correct but may be time-consuming (if you have really big tables involved in replication) when you need to run Snapshot.

Here are the steps that you can omit the snapshot

  • Script out QA replication and change @sync_type parameter value (highlighed below)

    exec sp_addsubscription @sync_type = 'Replication Support Only', ... (other paramaters should remain the same)

  • Remove replication (you can write a script to do this, note, first remove subscriptions then publisher, but do NOT remove distributor)

  • Restore PROD db on QA publisher

  • Restore PROD db on QA subscribers.

  • Run the replication setup script on QA publisher

  • Test your replication by inserting/updating a few test records and see whether the replication works as expected.

This link talks about another ways that can avoid snapshot and may be of help (but I never tried myself)

https://blogs.msdn.microsoft.com/repltalk/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication/

1st step of scripting out QA replication is one time work and you can actually automate all these steps (after step 1 modification is done) via t-sql scripts, and using sqlcmd.exe or powershell to drive the t-sql scripts. I used this way frequently before.

0

Unfortunately that what you have to deal with when you refresh your QA with PROD backups that is involved in T-Rep.

What I have done is

  • Script out QA replication
  • Remove replication
  • Restore with overwrite QA from PROD
  • Run the QA replication scripts
  • start snapshot - as now your subscribers are outdated in QA.
  • Let the log reader agent replicate the transactions.

You may want to look into

  • Initialize replication using backup and
  • Restore database .. with KEEP_REPLICATION option.
  • I used this method, but it creates all publications available at production database server. When I try to remove subscribers, it says the Subscriber is not configured for the publisher (QA Publisher) and doesn't delete that. – Rajesh Ranjan Dec 21 '16 at 10:16

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.