Replication options - merge or peer-to-peer

  • I have read a lot of articles on this but still have trouble seeing the differences in these and when I should use one over the other.

    I have 2 servers and need to sync about 30 tables. I need both nodes to be able to update the data. From what I have read, it seems like either merge or peer-to-peer would support this but peer-to-peer requires an enterprise license. Also, since I only want to sync 30 tables (there are other tables with data that is unique to its server and should not sync across), it seems that merge is the better option. With merge, I can create the snapshot containing only those tables. With peer-to-peer, initialization is done via a restore and would have to include all tables.

    I think the terminology is confusing me - merge says it has one publish and multiple subscribers. Aren't the subscribers actually functioning as publishers as well?

  • If only 30 tables could you write a manual process to sync them using Except? Here's an example:

    IF EXISTS (SELECT name FROM sys.objects WHERE name = 'DataA') DROP TABLE dbo.DataA

    IF EXISTS (SELECT name FROM sys.objects WHERE name = 'DataB') DROP TABLE dbo.DataB

    CREATE TABLE DataA (ID INT PRIMARY key, NAME NVARCHAR(50))

    CREATE TABLE DataB (ID INT PRIMARY key, NAME NVARCHAR(50))

    INSERT INTO DataA (ID,Name) VALUES (1,'Mike Smith'),(2,'James King'),(3,'Scott Thompson'),(5,'Michael Moore'),(7,'Tony Bennet')

    INSERT INTO DataB (ID,Name) VALUES (1,'Mike Smith'),(2,'James King'),(4,'Jeff Smith'),(6,'Susan Adams'),(8,'Parker Campbell')

    INSERT INTO DataB

    SELECT ID,Name FROM DataA EXCEPT SELECT * FROM DataB

    INSERT INTO DataA

    SELECT ID,Name FROM DataB EXCEPT SELECT * FROM DataA

    -- Data is now synced

    SELECT * FROM DataA

    SELECT * FROM DataB

    I do this with a few systems I need to keep in check, and though it depends on how your data is keyed this may be an option.

  • Thanks samalex. This doesn't really work for my situation though. These servers are in very different geographical locations and there is too much data to try to do this with a linked server.

  • If you are using SQL Server 2008 (which I have to assume you are since this is a 2008 forum), you should use Transactional Replication with Updateable Subscriptions. This is good to keep 2 servers in sync.

    Merge replication is best for server to client replication. Example, I have a main server that houses all of the data for my company, but each branch has their own subscription and their own data. Also, it is not "transactional," so if changes are made to 1 row several times, only the most recent change is synced. Not true with transactional replication.

    Peer-to-peer is really transactional from serverA to serverB and serverB to serverA. Both acting as a publisher and subscriber.

    Jared
    CE - Microsoft

  • I think this option (Transactional Replication with Updateable Subscriptions) is deprecated. It won't be support after SQL 2012.

  • ktalley.dba (12/6/2012)


    I think this option (Transactional Replication with Updateable Subscriptions) is deprecated. It won't be support after SQL 2012.

    Well... The option is not there in the GUI in 2012, but it can still be set through script. Peer-to-peer is probably your best option. However, you will have to build in conflict handling. Merge is really for separate clients working with their own data and then updating a main database. I wouldn't use it for stuff that is highly transactional on both sides. Still... I am continuing to use Transactional Replication with Updateable Subscriptions as long as it is still available.

    Jared
    CE - Microsoft

  • By reseed only we can manage the insert conflicts. But how come we manage conflicts of updates and deletes in peer to peer.

  • appu.cherukuri (5/7/2013)


    By reseed only we can manage the insert conflicts. But how come we manage conflicts of updates and deletes in peer to peer.

    http://technet.microsoft.com/en-us/library/bb934199%28v=sql.105%29.aspx

    Jared
    CE - Microsoft

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply