1

I'm currently working on a project for a company, the company has 8 branches, each with their own server. They task me to combine the db into one to be used at the headquarter. Maybe I'm stupid but the only way that I can come up with is to replicate each database to HQ. So then HQ will have 8 database of each branches. Problem is they want data changes to be real time, so I figured snapshot isn't going to work.

So I opted to transaction, which works but from my testing, I tried deleting some record at HQ database first, then I deleted the same record from the publisher/branches. And I got this error when it tries to replicate the database,

"The row was not found at the Subscriber when applying the replicated DELETE command for Table '[dbo].[repl_tbl]' with Primary Key(s): [id] = 1 (Source: MSSQLServer, Error number: 20598)"

Another problem that I found is that, If I were to add new data with let say primary key '15' to the branch database at my HQ which currently doesn't exist yet, and then I add data with primary key '15' to the branch/publisher database, now this result in error of

"Violation of PRIMARY KEY constraint 'PK_repl_tbl'. Cannot insert duplicate key in object 'dbo.repl_tbl'. The duplicate key value is (13). (Source: MSSQLServer, Error number: 2627)"

I'm expecting the data at HQ to changed according to branch.

Any suggestion is greatly appreciated. Thanks in advance.

Daim Yakub
  • 31
  • 6
  • It sounds like you have not configurated bi-directional replication. In one-way, only changes can be made on the articles being published. There are checks to enforce integrity when the command reach the subscriber, that is why you are getting the errors. You have to either painstakingly find and fix the issue or begin fresh from a new publication snapshot. – Ross Bush Aug 06 '19 at 12:40
  • Thanks for the comment. Yes, I didn't set it up to be bi-directional. But, I'm thinking of using merge transaction for now since I just got a new request that, sometimes the HQ needed to update data that needs to be transfer back to branches. And also do more research on Always On technology as suggested by andrews. – Daim Yakub Aug 08 '19 at 08:11

1 Answers1

2

If changes must be reflected at each side in real time and are to be made at both publisher and subscriber then you may want to look at Merge Replication, not Transaction or Snapshot one:

https://learn.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-2017

Merge replication is a bit more complex and if any issues happen to it you can't simply reinitialize subscription at Publisher because you will loose non-synchronized data at subscriber like the issue described in this question . But from your description it may be a way to go.

However, merge, snapshot or transaction replication mostly deals with individual published tables then whole databases. If you need to make available at HQ complete databases from branches then you may want to look at another technology called Always On Availability Groups. With AGs you can easily make remote DBs available for HQ but it requires Windows Failover Cluster setup between branches and HQ first. Among limitations of AGs is that the secondary replica databases are read-only. Changes can only be done at Primary replica.

HTH

andrews
  • 2,173
  • 2
  • 16
  • 29