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

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

Error-14010-remote server not defined as subscription server

Running SQL Server Personal Ed (Server A) on Tablet PC (publication and distribution setup here) trying to push data to SQL Server 2000 Enterprise Ed. (Server B). When I start sync I get two failures. First is 'remote server not defined as subscription server'. Second, subscription to publication xx not valid (error# -2147201019). The snapshot is succeeding fine. What do I need to setup on Server B? I have the same db on both servers.

Try with running sp_addsubscriber 'Server B' to see if it will be fixed.|||

I did run sp_add subscriber only to verify Server B already exists. A subscription shows up on server B; listed as never started.

I have not been able to succeed this task.

Server A db properties-replication tab-configure-subscribers tab-set agent connection to subscriber to impersonate-rerun PUSH suscription-response-login fail for anonymous logon.

How to setup both servers to accept anonymous logon?

|||

This issue has been resolved.

Security settings was the problem on both servers.

thx..bt

error: the publication does not exist

I had a publication that I removed from the Local Publications folder but it still shows up in Replication Monitor (with a red 'X'). No jobs exist related to this publication either.

Everything is working ok, but I can't get rid of this from the Replication monitor.

Any help would be appreciated.

Thanks.

SQL 2000 ?

Get into Enterprise Manager.

Right Click on the server connection.

Select Properties , in the window select Replication.

Select Disable, it will remove all your references as to the Replication.

Let me know if it works.

|||if this is SQL 2005, look in distribution database for table MSmerge_publications and see if your deleted publication still exists. if so, delete it.|||

Yes, I'm using SQL Server 2005.

I tried opening the Distribution database via Management Studio to do what you said but when I click on a table the option to open it is greyed out. This is only the case for tables in the Distribution db and for the temp_db. I can open all tables everywhere else.

I tried looking into permissions but don't see any differences between settings on dbs that allow me to open tables and those that don't.

I'm kind of a noob to SQL Server 2005 so I may just be missing something simple.

Thanks.

Scott

|||

You'll have to try TSQL queries to access the table. Open a new query window:

use distribution

go

select * from MSmerge_publications

go

|||

Yes, that did it!

However, the problematic data was in another set of tables...

There was a record in the MSReplication_monitordata table and an associated record in the MSSnapshot_agents and MSSnapshot_history that were associated with the publication that no longer existed.

Not sure why this data was still in these tables but once I deleted these records, the error in Replication Monitor dissapeared.

Thanks!

|||

Hi, there,

I have the same problem. I could not find the distribution database which mentioned in the posts. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status.

Could you anyone has ideas to delete this publication? Thanks.

error: the publication does not exist

I had a publication that I removed from the Local Publications folder but it still shows up in Replication Monitor (with a red 'X'). No jobs exist related to this publication either.

Everything is working ok, but I can't get rid of this from the Replication monitor.

Any help would be appreciated.

Thanks.

SQL 2000 ?

Get into Enterprise Manager.

Right Click on the server connection.

Select Properties , in the window select Replication.

Select Disable, it will remove all your references as to the Replication.

Let me know if it works.

|||if this is SQL 2005, look in distribution database for table MSmerge_publications and see if your deleted publication still exists. if so, delete it.|||

Yes, I'm using SQL Server 2005.

I tried opening the Distribution database via Management Studio to do what you said but when I click on a table the option to open it is greyed out. This is only the case for tables in the Distribution db and for the temp_db. I can open all tables everywhere else.

I tried looking into permissions but don't see any differences between settings on dbs that allow me to open tables and those that don't.

I'm kind of a noob to SQL Server 2005 so I may just be missing something simple.

Thanks.

Scott

|||

You'll have to try TSQL queries to access the table. Open a new query window:

use distribution

go

select * from MSmerge_publications

go

|||

Yes, that did it!

However, the problematic data was in another set of tables...

There was a record in the MSReplication_monitordata table and an associated record in the MSSnapshot_agents and MSSnapshot_history that were associated with the publication that no longer existed.

Not sure why this data was still in these tables but once I deleted these records, the error in Replication Monitor dissapeared.

Thanks!