repserver 2 way query

[Former Member]

I have setup master MSA replication from server msa01 to msa02.

This works fine. When I add login to msa01 or change password it gets replicated to msa02. No threads down and looks good.

Then I added MSA replication from msa02 to msa01. create db repdf and subscription. (2 way MSA replication)

Now when I add login syb_test on msa02, it replicates to msa01 and I see the syb_test created on msa01, but the rep agent picked up this transaction and tried to create the login again on msa02 (where it already exists) and thread goes down. It kinda goes in ifinite loop.

The master rep agent on both mas01 and mas02 has 'send maint xacts to replicate' false and 'semd warm standby xact' is true.

Also during setup I had run following reading manuals:

alter connection to msa01.master set dsi_replication to 'on'

alter connection to msa02.master set dsi_replication to 'on'

What other setting is needed to avoid rep agent pick up and replicate the maint user transactions ?

[former_member285566 · Accepted solution]

Ravi,

Please try:

alter connection to msa01.master set dsi_replication to 'off'

go

alter connection to msa02.master set dsi_replication to 'off'

go

alter connection to msa01.master set dsi_replication_ddl to 'on'

go

alter connection to msa02.master  set dsi_replication_ddl to 'on'

go

grant the maint.  user "set session authorization" permission

use master

go

grant set session authorization to <master_maint_user>

go

suspend and resume connections.

Regards,

Binh Liu

[former_member619798]

I had run into similar issue few years back when I setup bi-directional/ 2 way master database MSA. In my case the master maint user was set as dbo which would not allow running set session authorization. I had to switch it to a normal user and grant role with specific privileges along with session authorization to the master maint user for the setup to work.

As Hunter Liu has suggested you will need to grant session authorization to master maint user.