locked
maintaining 3 identical tables in 3 different servers RRS feed

  • Question

  • Hello,

    I have a requirement to maintain 3 identical tables in 3 different servers. The tables are roughly 300 rows and contain configurations for SSIS jobs.

    The basic function of the table is that 1 row can only be updated by one server and then that row needs to be updated to the other two servers. Each server has it's own 'master' rows.

    e.g row A updated by server1 needs to be updated to server2 and 3 (there is logic in each server so only certain rows can be updated by each server. e.g. server2 and 3 can never update row A)

    My first idea was to link servers and use triggers after the table updates. this works fine, however I have concerns on the security implactions of using linked servers. Thoughts?

    Is there a replication technology that would be better? Maybe transactional? My concern is that I dont want to replicate the full table to each server. Only the rows updated. And this would be a 3 way replication, each table will have 'master' rows that need to be updated on the other two servers.

    Any help would be great.

    Thanks

    Wednesday, September 23, 2015 3:07 PM

All replies

  • Transactional replication will do what you want.

    Wednesday, September 23, 2015 3:44 PM
  • Triggers perfom very badly over a network. If the link between one or the servers goes down, you will be unable to make any updates to the table the trigger is on as it will hang for 20 seconds trying to make a network call.

    Transactional replication will work for this, as you can choose to only replicate updates but you will have an endless loop here. You update table 1 on server a. It updates table 1 on server b, which updates table 1 on server c, which updates table 1 on server a. Etc.

    You can use custom replication stored procedures for this but it will be tricky.


    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, September 23, 2015 4:49 PM
    Answerer
  • You can implement this with transactional replication, however please follow the below link to see how to avoid loop back detection.

    https://support.microsoft.com/en-us/kb/820675

    Regards,

    Kranp.

    Wednesday, September 23, 2015 6:10 PM
  • Loopback only detects 2 nodes, not 3 notes.

    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, September 23, 2015 6:15 PM
    Answerer
  • Hi Hilary,

    I Agree.

    The following replication scenario would work using BI-Directional Transactional replication with loop back detection. (updatable subscription)

    Publication AB: Replicates from Server A to B and B to A

    Publication BC: Replication from Server B to C and C to B

    Publication CA: Replicates from C to A and A to C

    The other option would be to use merge-replication.

    Regards,

    -Kranp.

    Wednesday, September 23, 2015 6:56 PM
  • Updateable subscription(s) is deprecated. Bi-directional replication is something completely different.

    With a 3 node topology data originating on Server A goes to server B and Server C. The change arrives on Server B and Server C and both servers see it comes from A, so it does not go back to A, but Server B's change goes to Server C, Server C's change goes to server B. Loop back detection is done and the change from server b that hit server c does not loopback. Similarly the change from server c which hit server B does not go back. BUT the change that went form A to B now goes to C and from there back to A. A picks it up and see it comes from C, so it does not send it back to C, but sends it to B. and this loop continues forever.

    Merge replication will not allow the type of filtering he is looking for.


    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, September 23, 2015 7:11 PM
    Answerer
  • Would having a master flag column per table avoid the endless loop. So row A has master flag 1 in server1 but 0 in server 2 and 3. The tables then only replicate rows with master flag set to 1. So in this case there would be no loop right?

    Wednesday, September 23, 2015 7:59 PM
  • With custom replication stored procedures you can do this, but you would probably have to carry an originator column to every table.

    BTW - I used to live in New Plymouth, North Island.


    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

    Thursday, September 24, 2015 12:17 AM
    Answerer
  • New Plymouth..nice. I'm from Wellington.

    Ok, so I have been testing with three servers.  Replication seems to work fine in all directions, keeping in mind a row can only be replicated in 1 direction due to a filter placed on the article (from the master svr to the 2nd svrs)

    one issue I did find is that if a row is deleted from the non master svr then this is not replicated due to not metting the filter requirements. same if the row is updated on a non master svr, it wont be replicated... but this is ok for me. our logic states a row can only be updated on it's master server

    You mention the endless loop, how do I check for this? 


    • Edited by kiwiNspain Thursday, September 24, 2015 1:30 PM update
    Thursday, September 24, 2015 10:30 AM