The most concerning issue with SQL Server merge replication is that it is not adequately designed to scale past a very small amount of subscribed instances.
From a DBA’s perspective, we find merge replication the least desirable of the Microsoft replication technologies. The main reasons for this are the lack of scalability because of poor management tools and the time it takes to restore the replication when there is a problem that requires the deletion and recreation of the publication to resolve errors. In addition to this, we also dislike that it modifies the physical data structure of the table to add a GUID to each row.
Microsoft focused its efforts on SQL Server technology and supporting the latest replication technology AlwaysOn, leaving the basic replication options intact however limited, still with no effort being put into making them better. Unfortunately, AlwaysOn is only attempting to replace traditional mirroring and log shipping scenarios, and would not be an appropriate technology for a Merge replication scenario. This has been a constant theme with every version of SQL Server to date.
SymmetricDS is not focused on the SQL Server engine and it is in fact data agnostic, meaning the software doesn’t really care what platform the data is in any replication scenario that it is used for. Because of the focus on replication, the management tools and implementation are much better thought out than in SQL Server. This is especially true for Merge replication scenarios.
The SymmetricDS management console is a web-based interface, backed by an apache web service. The management console not only shows pertinent details about the status of your replication architecture but is also the portal in which you configure and modify your replication configuration. It is much more detailed than SQL Server offerings. Merge replication targets that are offline for a long period of time in SQL Server do not pick up where they left off and must be re-initialized which can often require manual intervention and the restoration of the target database. SymmetricDS stores all the changes that have been made during the time the target is offline and deploys them once it does come back online.
Conflict resolution is at the heart of keeping data consistent. SQL Server does support some customizable conflict resolution, SymmetricDS expands on this and adds even more options and customizability through its management console.
Implementation does not require modification to replicated table structures like SQL Server does. SQL Server merge replication relies on adding a GUID column to your tables in order to track changes. This can be resource intensive on storage and adds extra complexity where it is not needed. SymmetricDS uses a trigger based system so that when data changes do occur they are captured in real-time and stored until transmitted.
Lastly, the product scales the replication configuration much easier than SQL Server, and adding more nodes to the system doesn’t add the additional management overhead that occurs in SQL Server. After creating an empty database or restoring the latest backup, setting up a new node is really simple in the SymmetricDS management console. While it does not create the database or its table structure, the software will perform an initial load to ensure that the replicated objects are the same. Managing 20 nodes isn’t much more difficult to manage than a single node replication scenario.
SymmetricDS may not be the best solution for all situations, but in any situation where there is a complex merge replication architecture, I would make it my first choice. In a scenario where it is a single source and a single target, nothing is going to beat SQL Server replication. When there is a single source and multiple targets SymmetricDS is a much better replication tool. While there is an additional cost associated with solution, that cost will be offset by the man-hours saved troubleshooting and managing traditional merge replication configuration.