Wednesday, March 21, 2012

Errors with combined use of transactional and merge replication - SQL2005

I am investigating the feasibility of a configuration with 3 databases on SQL2005

DB_A is an OLTP database and serves up transactional publication pub_txn - with updateable subscriptions

DB_B is a subscriber database which subscribes to pub_txn

DB_B is also a publisher which serves up merge publication pub_merge

DB_C is a subscriber database which pulls pub_merge

===============================

Updates on DB_A are successfully replicated to DB_B

Howvever, when DB_C pulls updates, it doesn't find the update sent to DB_B

===============================

Updates on DB_B are successfully replicated to both DB_A and DB_C

===============================

Updates on DB_C initially failed with the error

Msg 916, Level 14, State 1, Procedure trg_MSsync_upd_course_type, Line 0
The server principal "repllinkproxy" is not able to access the database
"DB_C" under the current security context.

I then changed the login repllinkproxy to be a db_owner in DB_C

I now get the error

Msg 208, Level 16, State 1, Procedure sp_check_sync_trigger, Line 23
Invalid object name 'dbo.MSreplication_objects'.

=================================

I have three questions as a result
1) Is there anything fundamentally wrong with what I am trying to achieve?

2) Why is update on DB_A not reaching DB_C

3) Why can't I update DB_C?

Any suggestions gratefully received

aero1

Just to clarify - I am replicating the same table between all the databases - without any filtering|||quick question - in which order did you create the publications/subscriptions? For the tran pub, are you using immediate updating subscriber, or queue updating subscriber?|||

Setup order was:

1) Transactional publication

2) Transactional subscription (immediate updating)

3) Merge publication

4) Merge subscription

|||

Some progress:

I have got rid of the errors on updating DB_C

- My initial setup was unrepresentative with all the databases, DB_A, DB_B and DB_C on the same server

- I dropped and recreated the whole setup with DB_C on a separate machine - which is more representative of what I am ultimately trying to achieve

- There were then no errors when attempting to update DB_C

However, updates on DB_A don't reach DB_C and updates on DB_C don't reach DB_A.

Updates on DB_B reach DB_A and DB_C OK

|||

Hi aero1, republishing is not supported when using updatable subscriptions, this is documented in SQL 2005 Books Online topic "Updatable Subscriptions for Transactional Replication". Your best bet is to implement Peer-to-Peer instead of updatable subscriptions.

|||

Hi Greg

Thanks - I am investigating peer to peer

aero1

|||

Hello *,

I have a problem very similar to this post, so I don’t open a new thread. The only different think is I use transactional publication WITHOUT updateable subscriptions.

I can cascade n SQL Servers 2005 and every update, insert and delete command change every beneath Database (already described by aero1). Because the end-users work with mobile clients the last Replication must be from type Merge. When I change some data on the top level server these changes comes only to the last transactional subscriber:

Example:

ServerA.DBA ===TR==>ServerB.DBB==TR==>ServerC.DBC==MR==>MobileDB

When I change a datarow on DBB the changes are available on DBC but not on MobileDB (after pull the data from there).

I hope someone can help me

Regards

Markus

|||

You need to set this article property to true: published_in_tran_pub

See the articles below and that should solve your problem of merge subscribers not receiving the data changes. However note that if you make updates at the merge subscribers, they will not flow all the way to the Tran root publisher and can cause non-convergence.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_repl_11et.asp

http://msdn2.microsoft.com/en-us/library/ms152559.aspx

|||

The issues that I had only involved 2 databases communicating with transactional replication - with merge subscribers hanging off one of these

For this scenario, bidirectional transactional replication seems to work well - but customisation of the replication stored procs is needed to handle conflicts.

Markus appears to have 3 databases communicating with transactional replication. I am not sure whether a chain of 3 bidirectionally replicating databases would work, but I can't think of any reason why it shouldn't. If it did then the merge changes would flow back to the root tran publisher.

Note that peer to peer wouldn't work in my scenario because the updates could not be partitioned suitably

No comments:

Post a Comment