Hi,
I'm confused. I have bidirectional transactional
replication configured between 2 servers (sql server 2000
sp3). Same table is publisher and subscriber on both
servers.
Execution of:
sp_helpsubscription <publication name>
shows that loopback_detection is set to "No" on both
servers, and still, table changes are not being replicated
back, to the orginating server.
How is that posible? Is there any other way to prevent
changes to get back to the originating server?
Thanks,
OJ
It should be set to 1 or true for it to work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0e5701c4a656$16a33710$a401280a@.phx.gbl...
> Hi,
> I'm confused. I have bidirectional transactional
> replication configured between 2 servers (sql server 2000
> sp3). Same table is publisher and subscriber on both
> servers.
> Execution of:
> sp_helpsubscription <publication name>
> shows that loopback_detection is set to "No" on both
> servers, and still, table changes are not being replicated
> back, to the orginating server.
> How is that posible? Is there any other way to prevent
> changes to get back to the originating server?
> Thanks,
> OJ
Showing posts with label bidirectional. Show all posts
Showing posts with label bidirectional. Show all posts
Sunday, March 11, 2012
Bidirectional transactional replication and remote distributors
I want to be able to setup the following configuration using bidirectional transactional replication on SQL 2005
instance A lives on machine 1
instance B lives on machine 2
Instance A publishes to a transactional subscription on Instance B
Instance B does the reverse and publishes to a transactional subscription on Instance A
Instance A pushes to a distribution database on machine 2
Instance B pushes to a distribution database on machine 1
Problems Implementing Configuration
I can setup each instance as a distributor and create separate distribution databases using
sp_adddistributor and sp_adddistributiondb
I can then enable each publisher to use the correct distribution database using sp_adddistpublisher
However, when I try and run sp_replicationdboption to setup a database for publication, I get the error:
Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 55
The Distributor has not been installed correctly. Could not enable database for publishing.
If I try and configure a publication with the wizard, it says that instance A must be enabled as a publisher before you can create a publication - and then presents the dialog to add instance A to be handled by the local distribution database - which I don't want.
It appears that I need to run sp_adddistributor on the publisher as well as the distributor for the appropriate instance, but if I do this I get the error:
Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 109
The server 'instance A' is already defined as a Distributor.
It seems that you can't select a remote distributor if you already have a local distributor configured.
Is there a way round this limitation?
Note that configuring the environment with a single distribution database works fine
Try calling sp_adddistpublisher on machine 1 to add machine 2 as publisher and do the same on machine 2 to add machine 1 as publisher.
Regards,
Gary
|||Check out the new Peer-Peer replication in 2005 which does exactly what you want.|||Gary - I have tried what you suggested and got the following error as per my original post
Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 109
The server 'instance A' is already defined as a Distributor.
=====================
Dinakar - thanks for the suggestion - I have been checking out peer to peer, but I am not sure if I can live with the restrictions
=====================
I will probably go with a single distribution database located on the publisher with the lowest load. This should be satisfactory for the requirements
Thanks
aero1
Bidirectional Synchronization for SQL Server Mobile?
I create a distributed database for mobile application. I replicate a table that distribute on mobile device. I follow instruction how to create distributor, publication, replication, web synchronization, and subscriber database. I have done fine for synchronization between mobile database into desktop database (in this case SQL Server 2005 Standard Edition). But the problem is how can setup publication so it can bidirectional, not only from mobile database into desktop database, but also from desktop database into mobile database. So in the mobile database can have same data with desktop database even on mobile database lost some old data.
Its like data exchange between both engine. Desktop and mobile have same data. For filtering I can put filter on the desktop server for replicated table, so don't worry how I split the data.
Thanks a lot.
Look into Merge replication, or transactional replication with updatable subscriptions.
Martin
bidirectional snapshot replication?
I currently have a list of users in a SQL 2005 database. Users are
authenticated based on their email address, and each user has a number
of areas they do and don't have access to. A website is used to add
users to the database, or to add area permissions for an exisiting
user.
I would like to set up a number of replicated servers around the world.
At least one would have very high latency and very low bandwidth to it.
All Sites are connected by VPN.
My question is: Is there a way to do bidirectional replication between
all of the servers? All servers would ideally be both publishers and
subscribers.. If so, which type of replication would you use? I was
thinking snapshot replication twice a day, but that may not make
sense..
Thanks in advance..
Have a look at peer-to-peer transactional replication - this sounds like
what you require.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
authenticated based on their email address, and each user has a number
of areas they do and don't have access to. A website is used to add
users to the database, or to add area permissions for an exisiting
user.
I would like to set up a number of replicated servers around the world.
At least one would have very high latency and very low bandwidth to it.
All Sites are connected by VPN.
My question is: Is there a way to do bidirectional replication between
all of the servers? All servers would ideally be both publishers and
subscribers.. If so, which type of replication would you use? I was
thinking snapshot replication twice a day, but that may not make
sense..
Thanks in advance..
Have a look at peer-to-peer transactional replication - this sounds like
what you require.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
BiDirectional Replication
Has anyone successfully implemented Bi-Directional Transactional replication?
The documentation on BoL is very limited and there are not too many info
available outside too. We are planning to have active-active SQLServers, that
needs dynamic transactional replication.
I would really appreciate practical advise on BiDirectional Transactional
Repl.
This should help:
http://support.microsoft.com/default...b;en-us;820675
http://msdn.microsoft.com/library/de...lsamp_3ve6.asp
Rgds,
Paul Ibison
|||Paul,
I did already find this info from BoL.
I am looking for someone who has done this already. I spoke to a few DBAs
who haven't heard of this, but they have implemented Transaction Repl before.
"Paul Ibison" wrote:
> This should help:
> http://support.microsoft.com/default...b;en-us;820675
> http://msdn.microsoft.com/library/de...lsamp_3ve6.asp
> Rgds,
> Paul Ibison
>
>
|||This info is not in BOL - there are many setup scripts in the links I
posted. I have implemented it but didn't like the fact that I had to write
my own conflict resolvers and opted for merge instead, despite its overall
slower transfer rate (unless you are updating the same row many times I
would expect this to be generally the case). Also, schema changes will be
more restrictive. Hilary had implemented it in his work, so he can probably
add more if you want to go down this path.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks, Paul. I do appreciate your time.
Hillary, Can you please shed some light on this?
PS : Paul : The updated BoL is available as a download contains the link
that you specified in your first response. I thought of providing this info,
so that people are aware of the availability of the updated BoL.
"Paul Ibison" wrote:
> This info is not in BOL - there are many setup scripts in the links I
> posted. I have implemented it but didn't like the fact that I had to write
> my own conflict resolvers and opted for merge instead, despite its overall
> slower transfer rate (unless you are updating the same row many times I
> would expect this to be generally the case). Also, schema changes will be
> more restrictive. Hilary had implemented it in his work, so he can probably
> add more if you want to go down this path.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Well spotted - I'm using the old one at home. Thanks for the correction
Rgds,
Paul Ibison
"HowdyDowdy" <HowdyDowdy@.discussions.microsoft.com> wrote in message
news:A961EFBF-5E13-4178-902B-4C50F8972038@.microsoft.com...
> Thanks, Paul. I do appreciate your time.
> Hillary, Can you please shed some light on this?
> PS : Paul : The updated BoL is available as a download contains the link
> that you specified in your first response. I thought of providing this
info,[vbcol=seagreen]
> so that people are aware of the availability of the updated BoL.
>
> "Paul Ibison" wrote:
write[vbcol=seagreen]
overall[vbcol=seagreen]
be[vbcol=seagreen]
probably[vbcol=seagreen]
The documentation on BoL is very limited and there are not too many info
available outside too. We are planning to have active-active SQLServers, that
needs dynamic transactional replication.
I would really appreciate practical advise on BiDirectional Transactional
Repl.
This should help:
http://support.microsoft.com/default...b;en-us;820675
http://msdn.microsoft.com/library/de...lsamp_3ve6.asp
Rgds,
Paul Ibison
|||Paul,
I did already find this info from BoL.
I am looking for someone who has done this already. I spoke to a few DBAs
who haven't heard of this, but they have implemented Transaction Repl before.
"Paul Ibison" wrote:
> This should help:
> http://support.microsoft.com/default...b;en-us;820675
> http://msdn.microsoft.com/library/de...lsamp_3ve6.asp
> Rgds,
> Paul Ibison
>
>
|||This info is not in BOL - there are many setup scripts in the links I
posted. I have implemented it but didn't like the fact that I had to write
my own conflict resolvers and opted for merge instead, despite its overall
slower transfer rate (unless you are updating the same row many times I
would expect this to be generally the case). Also, schema changes will be
more restrictive. Hilary had implemented it in his work, so he can probably
add more if you want to go down this path.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks, Paul. I do appreciate your time.
Hillary, Can you please shed some light on this?
PS : Paul : The updated BoL is available as a download contains the link
that you specified in your first response. I thought of providing this info,
so that people are aware of the availability of the updated BoL.
"Paul Ibison" wrote:
> This info is not in BOL - there are many setup scripts in the links I
> posted. I have implemented it but didn't like the fact that I had to write
> my own conflict resolvers and opted for merge instead, despite its overall
> slower transfer rate (unless you are updating the same row many times I
> would expect this to be generally the case). Also, schema changes will be
> more restrictive. Hilary had implemented it in his work, so he can probably
> add more if you want to go down this path.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Well spotted - I'm using the old one at home. Thanks for the correction
Rgds,
Paul Ibison
"HowdyDowdy" <HowdyDowdy@.discussions.microsoft.com> wrote in message
news:A961EFBF-5E13-4178-902B-4C50F8972038@.microsoft.com...
> Thanks, Paul. I do appreciate your time.
> Hillary, Can you please shed some light on this?
> PS : Paul : The updated BoL is available as a download contains the link
> that you specified in your first response. I thought of providing this
info,[vbcol=seagreen]
> so that people are aware of the availability of the updated BoL.
>
> "Paul Ibison" wrote:
write[vbcol=seagreen]
overall[vbcol=seagreen]
be[vbcol=seagreen]
probably[vbcol=seagreen]
Bidirectional Replication
Hello,
I am trying to perform 2 way replication in SQL 2000.
SERVERA = OrdersTable -> I want data to replicate to this
server from SERVERB on UPDATE only.
SERVERB = OrdersTable -> I want data to replicate to this
server from SERVERA on INSERT only.
This is what I have done.
I created a publication on SERVERA and in the article
properties, I checked only INSERT.
Then I created a push subscription to SERVERB.
I then created a publication on SERVERB and in the article
properties, I checked only UPDATE. Then I created a push
subscription to SERVERA.
Is this the correct way to achieve my goal?
Thanks in advance.
I don't think so. First are you using merge replication? It sounds like you are. I think you are talking about the merging
changes tab options on the articles properties tab.
This verifies that a merge agent has rights to insert, update or delete. So you can have 1 pull subscriber which can do all three depending on his/her rights, and another that can't, or can have a subset or rights.
bi-directional transactional replication might be the way to go with this as you can write custom stored procedures to incorporate your business logic.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
I am trying to perform 2 way replication in SQL 2000.
SERVERA = OrdersTable -> I want data to replicate to this
server from SERVERB on UPDATE only.
SERVERB = OrdersTable -> I want data to replicate to this
server from SERVERA on INSERT only.
This is what I have done.
I created a publication on SERVERA and in the article
properties, I checked only INSERT.
Then I created a push subscription to SERVERB.
I then created a publication on SERVERB and in the article
properties, I checked only UPDATE. Then I created a push
subscription to SERVERA.
Is this the correct way to achieve my goal?
Thanks in advance.
I don't think so. First are you using merge replication? It sounds like you are. I think you are talking about the merging
changes tab options on the articles properties tab.
This verifies that a merge agent has rights to insert, update or delete. So you can have 1 pull subscriber which can do all three depending on his/her rights, and another that can't, or can have a subset or rights.
bi-directional transactional replication might be the way to go with this as you can write custom stored procedures to incorporate your business logic.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Bidirectional Replication
Hello everybody:
I'm having trouble with bidirectional replication.
This is the case:
As an introduction i'm implementig a transactional replication and I have 2
Servers called A and B, and i made a tabled called Table 1 with 2 fields
number int key and Name nvarchar(50). I modified the sps of replication
avoiding inserting duplicated keys.
I made a trans pub in A and the same in B, later a subscription from the
each other. When i insert a row, no probl. neither when i modified in a row,
but if a modification ocurrs in the same row with the same key in both
servers, the commands with its respective transaction are inserted.
But the tables MSRepl_commands and MSRepl_Transactions begun to grow in a
desmesurate way until the log of the distribution databse collapse or the
log of database in server A or B.
Then i look in MSRepl_commands and the field command is the same.
How can i resolve this trouble?
I manually modified the following Sps.
dbo.sp_MSadd_repl_command and dbo.sp_MSadd_repl_commandHP and all about
dbo.sp_MSadd_repl_command avoiding insercion of a duplicated command.
Is that right?
I couldnt find any help on internet and i spend several days in this
problem....
HELPPPPPPPPPP!!!!!!!!!!!!!!!!!!!!!!!
You nee to set up the @.loopback_detection to TRUE in sp_addsubscription.
"Eng. Leandro Pérez Guió" wrote:
> Hello everybody:
> I'm having trouble with bidirectional replication.
> This is the case:
> As an introduction i'm implementig a transactional replication and I have 2
> Servers called A and B, and i made a tabled called Table 1 with 2 fields
> number int key and Name nvarchar(50). I modified the sps of replication
> avoiding inserting duplicated keys.
> I made a trans pub in A and the same in B, later a subscription from the
> each other. When i insert a row, no probl. neither when i modified in a row,
> but if a modification ocurrs in the same row with the same key in both
> servers, the commands with its respective transaction are inserted.
> But the tables MSRepl_commands and MSRepl_Transactions begun to grow in a
> desmesurate way until the log of the distribution databse collapse or the
> log of database in server A or B.
> Then i look in MSRepl_commands and the field command is the same.
> How can i resolve this trouble?
> I manually modified the following Sps.
> dbo.sp_MSadd_repl_command and dbo.sp_MSadd_repl_commandHP and all about
> dbo.sp_MSadd_repl_command avoiding insercion of a duplicated command.
> Is that right?
> I couldnt find any help on internet and i spend several days in this
> problem....
> HELPPPPPPPPPP!!!!!!!!!!!!!!!!!!!!!!!
>
>
|||Hello!!!
But what happens if the subscription is a pull subscription?
Best regards...
Leandro
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:4E69CB52-5EA2-4152-A0FF-E77828FBF879@.microsoft.com...[vbcol=seagreen]
> You nee to set up the @.loopback_detection to TRUE in sp_addsubscription.
> "Eng. Leandro Prez Gui" wrote:
have 2[vbcol=seagreen]
row,[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
I'm having trouble with bidirectional replication.
This is the case:
As an introduction i'm implementig a transactional replication and I have 2
Servers called A and B, and i made a tabled called Table 1 with 2 fields
number int key and Name nvarchar(50). I modified the sps of replication
avoiding inserting duplicated keys.
I made a trans pub in A and the same in B, later a subscription from the
each other. When i insert a row, no probl. neither when i modified in a row,
but if a modification ocurrs in the same row with the same key in both
servers, the commands with its respective transaction are inserted.
But the tables MSRepl_commands and MSRepl_Transactions begun to grow in a
desmesurate way until the log of the distribution databse collapse or the
log of database in server A or B.
Then i look in MSRepl_commands and the field command is the same.
How can i resolve this trouble?
I manually modified the following Sps.
dbo.sp_MSadd_repl_command and dbo.sp_MSadd_repl_commandHP and all about
dbo.sp_MSadd_repl_command avoiding insercion of a duplicated command.
Is that right?
I couldnt find any help on internet and i spend several days in this
problem....
HELPPPPPPPPPP!!!!!!!!!!!!!!!!!!!!!!!
You nee to set up the @.loopback_detection to TRUE in sp_addsubscription.
"Eng. Leandro Pérez Guió" wrote:
> Hello everybody:
> I'm having trouble with bidirectional replication.
> This is the case:
> As an introduction i'm implementig a transactional replication and I have 2
> Servers called A and B, and i made a tabled called Table 1 with 2 fields
> number int key and Name nvarchar(50). I modified the sps of replication
> avoiding inserting duplicated keys.
> I made a trans pub in A and the same in B, later a subscription from the
> each other. When i insert a row, no probl. neither when i modified in a row,
> but if a modification ocurrs in the same row with the same key in both
> servers, the commands with its respective transaction are inserted.
> But the tables MSRepl_commands and MSRepl_Transactions begun to grow in a
> desmesurate way until the log of the distribution databse collapse or the
> log of database in server A or B.
> Then i look in MSRepl_commands and the field command is the same.
> How can i resolve this trouble?
> I manually modified the following Sps.
> dbo.sp_MSadd_repl_command and dbo.sp_MSadd_repl_commandHP and all about
> dbo.sp_MSadd_repl_command avoiding insercion of a duplicated command.
> Is that right?
> I couldnt find any help on internet and i spend several days in this
> problem....
> HELPPPPPPPPPP!!!!!!!!!!!!!!!!!!!!!!!
>
>
|||Hello!!!
But what happens if the subscription is a pull subscription?
Best regards...
Leandro
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:4E69CB52-5EA2-4152-A0FF-E77828FBF879@.microsoft.com...[vbcol=seagreen]
> You nee to set up the @.loopback_detection to TRUE in sp_addsubscription.
> "Eng. Leandro Prez Gui" wrote:
have 2[vbcol=seagreen]
row,[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
Bidirectional Replication
Hi,
can you pls help me to find the suitable Version of SQL Server?
SQL Server will work on the serverside. MS Access on the clientside.
Between these two DBs i need asynchronous bidirectional replication.
What version of SQL Server would be the best solution for this?
Thanx for ur help
Greetz CreanCan you upgrade the databse engine on the MS-Access side to MSDE instead of Jet? If not, I think you've got a real challenge.
-PatP|||Yes i can upgrade to msde.
So what version of sql server should i take?
Thx
Crean|||Use Windows Explorer to check to see if MSDE is provided on your MS-Access CD. If so, use that version of MSDE, otherwise grab the first version that you find (the average user almost always finds the most stable supported version first if they aren't looking for a specific version).
-PatP|||Ok, thx Pat
Greetz Crean
can you pls help me to find the suitable Version of SQL Server?
SQL Server will work on the serverside. MS Access on the clientside.
Between these two DBs i need asynchronous bidirectional replication.
What version of SQL Server would be the best solution for this?
Thanx for ur help
Greetz CreanCan you upgrade the databse engine on the MS-Access side to MSDE instead of Jet? If not, I think you've got a real challenge.
-PatP|||Yes i can upgrade to msde.
So what version of sql server should i take?
Thx
Crean|||Use Windows Explorer to check to see if MSDE is provided on your MS-Access CD. If so, use that version of MSDE, otherwise grab the first version that you find (the average user almost always finds the most stable supported version first if they aren't looking for a specific version).
-PatP|||Ok, thx Pat
Greetz Crean
Bidirectional replication
Hello,
is it possible to implement bidirectional replication with queued updating subscriptions in SQL Server 2000? I am currently testing bidirectional replication on two servers and it works well so far. My concern is how to I update the subscriber or publisher once both servers become disconnected? How to I resync? thank you for your help,
Lars
Bidirectinal replication does not support queued updating. But because both servers in bidirectional replication are publishers, if the two servers are disconnected, replication agents will just retry until it can connect to another server.
Keep in mind that bidirectional replication does not support conflict detection/handling.
Peng
|||Thank you for the info!
L
Subscribe to:
Posts (Atom)