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