Can a subscriber DB to other server to be the publisher DB in a new merge replication.

Question

Post reply

Can a subscriber DB to other server to be the publisher DB in a new merge replication.

jack_hc

Old Hand

Points: 344
More actions
February 27, 2016 at 9:18 am

#325190

Guys, I have an very urgent support request in my organization. And im new to Replication, but at least learned some during the setup, and worked on it for past months facing every kind of difficulties, Google search helped much, but after dozens try, I'm stuck here with one issue/question:
Can a subscriber DB to other server to be the publisher DB in a new merge replication.
If my statement is hard to understand, here's background.
We have Server A, B, C is running SQL 2005 which we need to retire them all.
Server A is a publisher setup merge replication with B & C respectively.
Now we setup X, Y, Z running SQL 2014 to replace the A, B, C.
Server X is a publisher setup merge replication with Y & Z respectively.
As there's no downtime can be allowed, there're multiple applications relies on the DB, we need to move app pointers in some period of time. So, we need both environment work for a couple of weeks, to slowly migrate to X,Y,Z. Any changes happened on server A need to be sync to X, and X is also getting new data but no need to sync back to A.
Because per MS, SQL 2005 to SQL 2014 beyond 2 version steps, replication may not work well may have unexpected result not suggested, so i have setup a interim server T running SQL 2008 R2 as broker to pass data between A & X, A -> T setup transactional replication and verified working fine, and server T pass data to X for transactional replication is fine. (Oh yeah!)
as earlier said, X setup as publisher and merge with Y & Z, it is working as expected, verified if change occurred in X, it updated to Y & Z. (Oh yeah!!)
BUT, the change happened from A, pushed to T, pushed to X, it is not reflected in Y & Z. only direct change happened on X can be reflected in Y & Z. UNBELIEVABLE
I hope my description is clear enough for any suggestion. I hope someone has also gone through the same complicated setup.
Any word would help.
thanks,
Jack

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

oscar.spicewood Valued Member Points: 61 More actions · Answer 1

Hi,

Please send additional information ,

From T to X , what time the data is being replicated ?

Are you using transactional replication from T to X?or Are you using Log shipping ?

Thank you,

OScar

jack_hc Old Hand Points: 344 More actions · Answer 2

oscar.spicewood (2/29/2016)
Hi,
Please send additional information ,
From T to X , what time the data is being replicated ?
Are you using transactional replication from T to X?or Are you using Log shipping ?
Thank you,
OScar

Hi Oscar,

thanks for the reply.

Yes, I'm using transactional replication from T to X, so T is subsicber of A, T is also publisher of X.

thanks,

Jack

oscar.spicewood Valued Member Points: 61 More actions · Answer 3

Jack,

When a merge replication is enabled on tables, There are some triggers created on tables ., such as ms_merge......, you can see under the triggers of merge replication tables ,

My guess ::

If you want to load the bulk data on merge enabled tables using techniques such as SSIS , Import/export , Those process will disable the merge triggers on tables , So my guess is the data that is being loaded by transactional replication from T to X might be turning off these triggers while loading data,

Please look on FIRE_TRIGGERS, see if you can enable the FIRE_TRIGGERS option while the data is being loaded ,

Hope that helps,

Thank you,

Oscar.

jack_hc Old Hand Points: 344 More actions · Answer 4

oscar.spicewood (2/29/2016)
Jack,
When a merge replication is enabled on tables, There are some triggers created on tables ., such as ms_merge......, you can see under the triggers of merge replication tables ,
My guess ::
If you want to load the bulk data on merge enabled tables using techniques such as SSIS , Import/export , Those process will disable the merge triggers on tables , So my guess is the data that is being loaded by transactional replication from T to X might be turning off these triggers while loading data,
Please look on FIRE_TRIGGERS, see if you can enable the FIRE_TRIGGERS option while the data is being loaded ,
Hope that helps,
Thank you,
Oscar.

Hi Oscar,

Actually the merge from T to X is working, however X is not sending the updates to Y & Z if the change occurred in A->T->X sequence.

X is only send updates when the change directly happened on X.

Regards,

Jack

oscar.spicewood Valued Member Points: 61 More actions · Answer 5

Jack,

I understand that T to X is working but you need to check whether triggers are being turned off in this process,

If the merge triggers on X is turned off while the data load from T to X , then triggers wont write changes to Ms_merge... tables and merge replication wont sync these changes to Y and Z ,

Please check the status of triggers manually while syncing data from T to X ,

Hope this helps,

Thank you,

Oscar