none
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

    Tuesday, February 28, 2012 12:57 PM

Answers

All replies

  • 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

    Tuesday, February 28, 2012 3:30 PM
    Moderator
  • 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

    Tuesday, February 28, 2012 3:38 PM
  • 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

    Tuesday, February 28, 2012 3:57 PM
    Moderator
  • 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

    Wednesday, February 29, 2012 11:00 AM
  • 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

    Wednesday, February 29, 2012 2:16 PM
    Moderator
  • 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

    Thursday, March 01, 2012 8:59 AM
  • 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
    Thursday, March 01, 2012 9:04 AM
  • 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

    • Marked as answer by Stephanie Lv Thursday, March 08, 2012 7:30 AM
    • Unmarked as answer by Nibras33 Monday, March 12, 2012 9:33 AM
    Thursday, March 01, 2012 2:53 PM
    Moderator
  • 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

    Monday, March 12, 2012 9:24 AM
  • 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

    • Marked as answer by Stephanie Lv Thursday, March 15, 2012 8:34 AM
    Monday, March 12, 2012 3:37 PM
    Moderator
  • Thank you so much.

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

    Wednesday, March 14, 2012 6:27 AM
  • 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

    • Marked as answer by Stephanie Lv Thursday, March 15, 2012 8:34 AM
    • Unmarked as answer by Nibras33 Friday, March 23, 2012 1:27 PM
    Wednesday, March 14, 2012 2:15 PM
    Moderator
  • 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;

    • Marked as answer by Nibras33 Monday, March 26, 2012 8:09 AM
    • Unmarked as answer by Nibras33 Monday, March 26, 2012 8:09 AM
    Friday, March 23, 2012 1:38 PM
  • 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

    • Marked as answer by Nibras33 Monday, March 26, 2012 9:54 AM
    • Unmarked as answer by Nibras33 Monday, March 26, 2012 9:54 AM
    Monday, March 26, 2012 8:11 AM
  • 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


    • Marked as answer by Nibras33 Monday, March 26, 2012 12:31 PM
    • Unmarked as answer by Nibras33 Monday, March 26, 2012 12:32 PM
    Monday, March 26, 2012 9:59 AM