A Merge Replication Scenario..

  • Article

One of my recent engagements on Merge Replication, I faced a scenario as below diagram:

clip_image002

Scenario Details:

  • Single copy of database at the publisher end which is working as the Publisher DB
  • Database is the backend of Custom ERP application.
  • Database size is very small (4-5 GB)
  • They have written custom SP which can write in backend apart from the FE application.
  • Subscribers have Push subscription and running with SQL Express (On Demand /Custom Logic)
  • More than 100+ subscribers.
  • Users can write text using special characters such as ‘%&/*’ etc. in the application.
  • Similarly the custom SP would process from remote files and write data in the application backend publisher db.

Issue Description:

When Publisher finds a special character in the replication articles, it fails. This causes entire environment failure. To fix this, they manually clean those special characters and they need to reinitialize the replication. They don’t want to have re-initialization and address the issue by some other way.

RCA Performed:

In the past, in most cases special characters were inserted either from FE application or through the custom SP based job which is actually processing some exported file and inserting data straight to database. But when a similar issue occurs at the subscriber level, only that subscriber fails and not the entire replication topology.

Solution Recommended:

Considering various facts such as Licensing Cost, TCO, and Administration overhead, we recommended the customer to setup a local subscription on the same server.

clip_image004

In this architecture, we recommended:

  • A Staging DB where there will not be any direct write operations.
  • ERP App and Custom SP based write operations would be happening to ERP Live DB.
  • This DB would be a bi-directional subscriber with Staging DB with separate Publisher.
  • This subscription would be running on regular frequency (automatic).
  • Another publisher which would address remote subscribers’ requirement such as one way replication for few articles.

Benefits with this architecture:

  • Consistency would be managed at the subscriber level.
  • Subscription failure would not impact the rest of the replication topology.
  • There will not be any locking issue at ERP App tier due to isolated remote subscriber sync process.

Challenges with this architecture:

  • Extra storage might be required depending upon DB size.
  • Separate maintenance jobs would be needed for staging DB as well.
  • Higher CPU/Memory usage might be a concern.