3

I have a little problem...

I have this case:

  • 2 server instances

  • 2 Databases

  • 1 Table (5 columns)

From server 1 I created publication to replicate all columns of tables I have in DB1.

From server 2 I created subscription to pull all columns from tables which is in DB1.

But now, I need to publish a column of the same table from server 2 to server 1 and also it has to be in same DB...

I tried with using logic and creating publication for server 2 and subscription on server 1 but there is error appearing:

You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.

I need it to be A to B and B to A replication

I hope someone understood my problem and have an answer for me.

p.s. Ask for more info if you need ...

Improve this question
4
  • Merge or Transactional?
    – Remus Rusanu
    Commented Dec 17, 2012 at 8:52
  • Actually I Dont know, Im new in replications so you can make me advice...
    – Nidzaaaa
    Commented Dec 17, 2012 at 9:07
  • I used transactional
    – Nidzaaaa
    Commented Dec 17, 2012 at 9:08
  • but I read somewhere that Merge is better
    – Nidzaaaa
    Commented Dec 17, 2012 at 9:09

2 Answers 2

Reset to default
5

From your description it seems as if you require bidirectional replication. You may want to consider using Merge Replication, Bidirectional Transactional Replication, or Peer-to-Peer Replication.

I suggest reading through Selecting the Appropriate Type of Replication to see what type of replication best meets your application requirements.

Improve this answer
3
  • Peer-To-Peer is only for enterprise users, I need it for some cheaper versions of SQL Server 2000/2005 or 2008 (the best will be for 2008) Is there any link where I can see "Step By Step" how to setup bidirectional replication? Something without using too much TSQL, just with wizards (like standard one direction replication) ??
    – Nidzaaaa
    Commented Dec 17, 2012 at 22:02
  • You cannot setup Bidirectional Transactional Replication using the Wizard, T-SQL only. You can setup Transactional Publication with updatable subscriptions in SQL Server 2008 using the Wizards; note: this feature is deprecated in 2012 and should be avoided. You are correct, Peer-to-Peer is Enterprise edition only. I believe you will want to use Merge as it is available in Standard edition and can be done through the Wizards. With Merge, articles are bidirectional by default and can also be set as download-only if required.
    – Brandon Williams
    Commented Dec 18, 2012 at 1:30
  • @BrandonWilliams, +1 for Appropriate selection for replication.
    – Md Haidar Ali Khan
    Commented Jul 2, 2015 at 7:12
2

Here's a step-by-step guide on implementing bi-directional transactional replication.

This should meet your requirements based on the following MSDN description:

For example, if you have two servers (Server A and Server B), the servers are said to be in bidirectional transactional replication if both of the following conditions are true:

The changes that are made to Table T1 at Server A are replicated to Table T1 at Server B.

The changes that are made to Table T1 at Server B are replicated to Table T1 at Server A.

However, this isn't simple to implement or to maintain, and it won't be possible to properly manage it without using T-SQL; the SSMS GUI is often deficient in what it allows you to do.

Are you sure that you need to use replication? A simpler solution would be ideal... maybe a Sql Server agent job would suffice, or maybe even an SSIS package?

Improve this answer
2
  • Is there any better (free) solution I can use to connect two servers and sync them everytime I want (or scheduled) ??
    – Nidzaaaa
    Commented Dec 18, 2012 at 8:47
  • 1
    The easiest solution would be to use SSIS or T-SQL to perform the sync. Simple and free. If you're not comfortable with writing the necessary T-SQL to do this, then I wouldn't recommend attempting the same thing using any form of replication, because this would be much more complex than writing the necessary insert, update, and delete commands to perform the sync.
    – Michael Fredrickson
    Commented Dec 18, 2012 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.