none
Which is better as a update anywhere technology: P2P or Merge Replication

    Question

  • With less than 10 servers, which is better as an update anywhere technology: P2P replication or Merge replication, why.

    Thanks,

    Wednesday, November 21, 2012 8:43 PM

Answers

All replies

  • Depending on your hardware and network bandwidth and workload it could be either. Merge is more processor intensive, but only the final image of the row is what is replicated. So if you update a row 1000 times between sync's only the final update is replicated. With p2p each change is replicated.

    If I was to bet money not knowing anything about your workload or LAN/WAN connection, I would probably use merge, just because of the licensing issues. P2P requires the Enterprise Edition of SQL Server, merge can use the Standard edition as a publisher and SQL Server Express as subscribers.


    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, November 21, 2012 8:57 PM
    Moderator
  • I don't think we're concerned so much about latency or cost.

    My DBA wants to start out close to using all defaults.

    Our database updates are just to accumulate a list of facts. We don't want to lose anything at all. This data is our main reason for existance.

    Our database writes are to increment a few global counters (we never decrement them). They're secondary to our database updates.

    Consequently, latency is not an issue at all for us. We, eventually, want all our updated data. We, eventually, want a correct representation of all our global counters. We don't want to lose data. We will have conflicts.

    I feel that any simulations that I built would be useless because we'll probably start out with near to default systems.

    Again, I ask with less than 10 servers which is better for us Merge replication or P2P replication.

    Thanks,


    Thursday, November 22, 2012 4:53 AM
  • It is simply not prudent to make such a decision without trying each of them out and seeing what works for you.

    I would probably go with merge because that is where my skill set it and you don't know what your customers versions are. The cost of the Enterprise Edition is significant and I doubt you could force your customers to upgrade to a more costly edition for a feature set you require.


    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, November 22, 2012 5:01 AM
    Moderator
  • It is simply not prudent to make such a decision without trying each of them out and seeing what works for you.

    How do I go about doing this.
    Thursday, November 22, 2012 6:02 AM
  • About trying stuff out first, I don't want to try and access the actual SQL Server database involved. I may be able to get away with removing (from my version of the non-web based java program) some of the web services and replacing them with my own access to a different testing SQL database using Visual Studio 2010 or java.

    What sorts of things should I be comparing. For example, should I be worried only about the CPU load. I can attempt to look into a stored procedure to do conflict resolution with Merge replication. What's the best way to simulate all my clients accessing my less than 10 servers.

    Can you point me to the correct Microsoft forums where I can get answers to my future simulation questions. Or, give me other useful links. I'm particularly interested in learning how to access a SQL Server database using C++ (using Visual studio 2010) or java (using NetBeans).

    Do you have any suggestions for me.

    Thanks 

    Thursday, November 22, 2012 1:55 PM
  • There is a tool called sotress which is part of RML utilities.

    http://www.microsoft.com/en-us/download/details.aspx?id=8161

    In SQL 2012 you have distributed playback manager.

    These tools allow you to capture a workload and replay it against different databases and servers.

    It is hard to say where you bottleneck will be. It could be cpu, it could be network bandwidth, it could be IO. With merge the first bottleneck you will likely see if CPU. With p2p it will be network bandwidth.

    I run the PAL tool (http://pal.codeplex.com/) and it tells me where the bottlenecks are.

    What is most important to me is to stress the systems and see which has greater throughput.

    There is no forum for this afaik.


    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, November 22, 2012 4:00 PM
    Moderator