altering a db repdef may desynchronize subscription replicates?

[#1]

I want to alter a database replication definition (used for a db subscription) to turn off ddl replication. I'll be doing this in a primary database where user transactions will be active.

Looking at the doc page "Altering Database Replication Definitions", it says:

"Altering a database replication definition with associated subscriptions may desynchronize replicate tables"

I'm pretty sure this is talking about situations where you add/remove/change columns in the repdef which might corresponding schema changes in the subscription replicate databases, but.... it doesn't say that.

So, will altering a database replication definition "desynchronize" my MSA database subscription replicates? (if there are no schema/column changes involved)

Thanks in advance
Ben

[former_member89972]

Ben

How about an alternate way ( if you get the activity on primary suspended during the steps below ).

1. Stop activity on source

2. Drop the database repsub without purge

3. Drop + Re-create database repdef

or alter database repdef with new clause(s) as needed

4. New database repsub Define (no materialization) + Activate + Validate + Check

5. Resume activity on source, check with rs_ticket or something similar

HTH

Avinash

[sladebe]

Are you saying that you *know* that this is needed? Or just that this would be an extremely safe way to do it?

I'm thinking about locking tables on the primary db to briefly block user activity, let the rep_agent and replication flush, altering the repdef, then unlocking tables on the primary db.

But I'm guessing that even stopping user activity may not be necessary.

I found something that I remembered reading about repdef versions:

New Features in 15.7.1 SP303 -> "Enhancements to Replication Definition Version"

https://help.sap.com/viewer/3193f0548ba14c999b0275b5e9013760/16.0.3.4/en-US/7dde12644a484c5e910bf94b...

which says:

"Several enhancements are introduced to avoid and address issues when a replication definition version is dropped too early from the system due to the replication definition being altered in the middle of a primary transaction."

And, in fact, that page talks about using rs_send_repserver_cmd in the primary db with the "ptable" option (primary table), where the primary table is locked before submitting the alter repdef to the repserver.

So this seems to say that you want to make sure the alter repdef doesn't happen in the middle of a transaction on the primary. But this seems to imply that if user activity is blocked (including no open transactions), the alter db repdef is safe.

[former_member89972]

AFAIK rs_helprepversion is for table level repdefs

( Try it on a db repdef and see what you get 🙂 )

What I *know* from experince is stoppage of replication data flow is the best way of fixing the replication plumbing.

So it applies to any maintenance activity on rep server.

If you can, get the users off in one of the many ways to ensure that there is NO activity.

It also simplfies your diagnostics, because you KNOW that issuses if any are caused by YOUR maintenance activity. Imagine your rs_ticket stuck behind a huge transaction while your twiddle your thumbs wondering why it did not make it to replicate so far !!

Also rollback -- just in case -- is simple when there is no user activity, because you would have all backups after stoppage of user activity and BEFORE you started your work.

HTH

Avinash

[sladebe]

Re: AFAIK rs_helprepversion is for table level repdefs ( Try it on a db repdef and see what you get 🙂 )

I tried it on a db repdef and it said, basically, "no" ("not a valid object or replication definition id")

Re: What I *know* from experince is stoppage of replication data flow is the best way of fixing the replication plumbing.

But it's not best of it causes unecessary user outages.

I locked user tables for the db, made sure the repserver had flushed for that db, then ran the alter db repdef. It was less than a minute pause in application access and it was basically transparent to the users.

Seems to have worked fine.