Transactional push replication over internet

Last Post 18 Mar 2005 12:20 AM by tapan_maniar. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
tapan_maniar
New Member
New Member

--
17 Mar 2005 07:41 AM
Hi,

I am trying to configure Transactional push replication with immediate updating subscribers. The problem I am facing is with the setup of replication. Initial replication with uni-directional setup works fine. I get problems with bi-directional transactional replication.

1. Records get updated at subscriber without problem. But, as I make use of 2PC mechanism with immediate updating subscribers, at subscriber when I try to update record, it gives me error

Server: Msg 6, Level 16, State 1, Line 5
Specified SQL server not found.

Hence no changes are possible at subscriber even when I configure replication with immediate updating subscriber with 2PC.
At subscriber I am able to ping and telnet publisher on the port on which publisher is listening.

2. Since replication uses 2PC mechanism, it is supposed to update records at both publisher and subscriber and if either of them fails, it has to roll back transaction. This is not happening. At publisher's end if I update record when publisher is not able to communicate to subscriber, it allows to make changes. As per definition, it is not supposed to allow changes in this case.

3. I need to configure it only through IP address. Both publisher and subscriber are registered at both ends and I can connect to either of them from both ends.

Thanks & Regards,

Tapan Maniar.
tapan_maniar
New Member
New Member

--
17 Mar 2005 09:01 AM
Yes, I did. And after that also I am getting error

Server: Msg 6, Level 16, State 1, Line 5
Specified SQL server not found.

Anyways, in this

sp_link_publication @publisher='publisher', @publisher_db='db_name', @publication='pub_name', @security_mode=0, @login='sa', @password='sa's pwd'

I can not put IP address. I need to do it only through IP address as publisher and subscriber are geogrophically far away.

Thanks.
tapan_maniar
New Member
New Member

--
17 Mar 2005 11:59 PM
OK, no I'm getting this error on subscriber when I try to update record of table being replicated:

Server: Msg 7391, Level 16, State 1, Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].


I even did :

1. Start Registry Editor (Regedt32.exe).
2. Locate the following key in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC
3. On the Edit menu, click Add Value, and then add the following registry value: Value name Data type Value
TurnOffRpcSecurity REG_DWORD 1

4. Quit Registry Editor.

as per microsoft KB http://support.microsoft.com/kb/827805.

still didn't work.

Please guide me.

Thanks.
tapan_maniar
New Member
New Member

--
18 Mar 2005 12:20 AM
I also found some KB about opening RPC ports on custom port range. I must not use RPC over internet. It may pose a greater security risk.

Thanks.
tapan_maniar
New Member
New Member

--
19 Mar 2005 06:09 AM
Dear Guru,

Thank you very much. It worked.

Replication is working fine now. Still one more question,

Since I am using this setup to provide load balancing, in case publisher server goes down, subscriber has to bear all the load and has to be able to make changes on it's own without contacting publisher.

But on subscriber, after manually breaking communication with publisher, I issued following command:

sp_subscription_cleanup [ @publisher = ] 'publisher'
, [ @publisher_db = ] 'publisher_db'
[ , [ @publication = ] 'publication']


I got following error:

Server: Msg 7391, Level 16, State 1, Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: Connection failure]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].

How do I make subscriber free of being able to make changes to database in publisher's absence?

Thanks,

Tapan Maniar.
tapan_maniar
New Member
New Member

--
28 Mar 2005 12:22 AM
Dear Guru,

I need to setup transactional 2 way replication with sole intention of being able to provide database to users even when one of them is down.

What you have suggested is to stop replication. In that case I need to have at least access to the publisher. Suppose that my publisher is down because of hardware failure and is not scheduled to come up for at least 24 hours.

In this kind of scenario, I can not stop replication because I don't have access to publisher.

And I need to configure subscriber to take up all the load for this duration in publisher's absence. Otherwise the whole setup to provide redundancy is nullified. It will be as good as having one DB server and if it goes down, setup new one and restore database from backup. Log shipping does not help as it can not work together with primary. In that case I have to configure stand by server to become primary when primary goes down. And that too, is not based on transactions but it's based on backups of transaction logs and their application to backup server. It does not provide transactional redundancy.

2PC bidirectional replication provides that transactional redundancy but if stopping the replication is the only way, then I don't think it serves the purpose.


Thanks & Regards,

Tapan Maniar.
tapan_maniar
New Member
New Member

--
29 Mar 2005 12:56 AM
Dear Guru,

I appreciate the help from you throughout the thread. But still it doesn't solve the problem. Is there anything else I can try besides replication to make it work this way? Anything you are even remotely aware of? That will help me alot.

Thanks,

Tapan Maniar.
You are not authorized to post a reply.

Acceptable Use Policy