bidirectional replication

Question

Hi,

What is the best way to setup bidirectional replication? Is it not available through GUI.

I don't want to go for peer-to-peer as this will work only in EE.

Best Regards,

N

Answer 1

Follow the directions here:

http://sqlblog.com/blogs/hilary_cotter/archive/2011/10/28/implementing-bi-directional-transactional-replication.aspx

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Hi,

You can create Merge replication, which is a two way replication, which can be setup with GUI.

http://www.codeproject.com/Articles/28951/SQL-Server-2005-Merge-Replication-Step-by-Step-Pro

If you want bi-directional transactional replication check this link http://msdn.microsoft.com/en-us/library/ms151855.aspx

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 3

Good point Ashwin - merge replication is easier to set up but

1) requires tracking triggers and an additional guid column on each table being replication

2) is slower by bi-directional transactional replication

3) the change tracking will slow down all DML occuring on replicated tables.

In its favor is that it detects and resolves conflicts and can replicate to SQL CE databases.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Thanks Hilary and Ashwin,

But the link what Ashwin gave shows script for bidirectional replication. If I need to setup for the first time, how can I have the script. Is it the way to setup the bidirectional replication

Answer 5

Use this.

USE master
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name='BIDINodeA')
BEGIN
EXEC sp_removedbreplication 'BIDINodeA'
ALTER DATABASE BIDINodeA SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE BIDINodeA
END
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name='BIDINodeB')
BEGIN
EXEC sp_removedbreplication 'BIDINodeB'
ALTER DATABASE BIDINodeB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE BIDINodeB
END
CREATE DATABASE BIDINodeA
GO
EXEC sp_replicationdboption 'BIDINodeA', 'publish', true
GO
CREATE DATABASE BIDINodeB
GO
EXEC sp_replicationdboption 'BIDINodeB', 'publish', true
GO
USE BIDiNodeA
GO
IF exists(SELECT * FROM sys.objects WHERE type='u' AND name='Table1')
DROP TABLE Table1
GO
CREATE TABLE Table1(PK INT IDENTITY (1,2) NOT FOR REPLICATION CONSTRAINT Table1PK PRIMARY KEY, Charcol VARCHAR(20), originatingDB sysname DEFAULT db_name(), dtstamp DATETIME DEFAULT GETDATE())
GO
EXEC sp_addpublication 'BIDINodeA', @Status=ACTIVE
GO
EXEC sp_addarticle 'BIDINodeA', @article='Table1',@source_object='Table1' ,@identityrangemanagementoption='manual'
GO
EXEC sp_addsubscription 'BIDINodeA','ALL', @@ServerName, 'BIDINodeB', 'Replication support only', @loopback_detection='true'
GO
USE BIDiNodeB
GO
IF exists(SELECT * FROM sys.objects WHERE type='u' AND name='Table1')
DROP TABLE Table1
GO
CREATE TABLE Table1(PK INT IDENTITY (2,2) NOT FOR REPLICATION CONSTRAINT Table1PK PRIMARY KEY, Charcol VARCHAR(20), originatingDB sysname DEFAULT db_name(), dtstamp DATETIME DEFAULT GETDATE())
GO
EXEC sp_addpublication 'BIDINodeB', @Status=ACTIVE
GO
EXEC sp_addarticle 'BIDINodeB', @article='Table1',@source_object='Table1', @identityrangemanagementoption='manual'
GO
EXEC sp_addsubscription 'BIDINodeB','ALL', @@ServerName, 'BIDINodeA', 'Replication support only', @loopback_detection='true'
GO

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

Hi Hillary,

Thanks for the script.

I have tables already created in a database, should I need to alter the script and create a bidirectional replication.

How I can go about this as I am not so good in scripting.

Regards

Answer 7

Just to add to the above clarification, normally for transactional replication there is a one time snapshot being run. Should I need to run it in a bidirectional as well

Answer 8

If you add the articles like this:

GO
EXEC sp_addarticle 'BIDINodeA', @article='Table1',@source_object='Table1' ,@identityrangemanagementoption='manual'
GO
EXEC sp_addsubscription 'BIDINodeA','ALL', @@ServerName, 'BIDINodeB', 'Replication support only', @loopback_detection='true'
GO

You should fine.

With the replication support only option you will not need to run the replication agent.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 9

Hi,

Created the bidirectional using the script, but I have some questions.

1. Do we have to have 2 different tables in publication and subscription like table1 and table2 (but both the tables structure are same)

2. How do we handle the snapshot like how we do for transactional replication, do we need to run the snapshot once manually.

Thanks

Answer 10

The tables must be in place on both sides and all the data in them before you start.

The snapshot agent will not run if you set it up according the script provided. The subscriptions are for replication support only (so they will generate the stored procedures), and will not generate a snapshot.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 11

Thank you so much.

Can I have 2 different table names in publisher and subscriber.

Answer 12

They should be the same name.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 13

Hi Hillary,

I just customized the stored procedure since the table columns are different than the two_way_test1, but later on when I test the replication by updating the columns in publisher, but in subscriptiion it is getting appended to the existing value.

However the insert and delete works perfectly.

CREATE proc sp_upd_two_way_test1 @old_pkcol int,
    @old_intcol int,
    @old_charcol char(100),
    @old_datecol datetime,
    @pkcol int, @intcol int,
    @charcol char(100),
    @datecol datetime
AS
    -- IF intcol conflict is detected, add values
    -- IF charcol conflict detected, concatenate values
    DECLARE  @curr_intcol int, @curr_charcol char(100);

    SELECT @curr_intcol = intcol, @curr_charcol = charcol
    FROM two_way_test1 WHERE pkcol = @pkcol;

    IF @curr_intcol != @old_intcol
        SELECT @intcol = @curr_intcol +
            (@intcol - @old_intcol);

    IF @curr_charcol != @old_charcol
        SELECT @charcol = rtrim(@curr_charcol) +
            '_' + rtrim(@charcol);

    UPDATE two_way_test1 SET intcol = @intcol,
        charcol = @charcol, datecol = GETDATE()
    WHERE pkcol = @old_pkcol;

Answer 14

Hi,

Can anyone let me know how the update stored procedure works, because when I test it is rather appending when we update the table.

Also how can we handle if we have multiple tables in a database to be replicated using bidirectional, do we need to write each stored procedure for insert, update and delete.

Thanks

Answer 15

Hi,

Can anyone let me know how the update stored procedure works, because when I test it is rather appending when we update the table.

Also how can we handle if we have multiple tables in a database to be replicated using bidirectional, do we need to write each stored procedure for insert, update and delete.

Thanks