Replication

Question

Replication

rhu rhu 0

Im seeking some help. Im trying to do a replication from a main to secondary server. This is well. After I insert records to the tables in the DB here then do another replication back to main from secondary, The records are replicated back but the main DB is rendered unsable ie I can not insert data into the tables. Im using SQL server 2016 standard. Is there a way around this issue. Thanks in advance.

0 comments

1 answer

Your answer

Answer 1

Hi @rhu rhu

Thanks for the details. I can't see your servers, so I can't say for sure what made the main database unusable, but here's what I'd check.

Why this can happen Standard transactional replication is one-way, and the subscriber is meant to be read-only. If you set up a second replication from the secondary back to the main server, the two can conflict, for example through snapshot re-initialization, duplicate keys, or identity values colliding. That could be why inserts fail on the main database.

Options for two-way replication on SQL Server 2016 Standard

Bidirectional transactional replication with loopback detection, so changes aren't sent back to the server they came from: https://learn.microsoft.com/en-ca/sql/relational-databases/replication/transactional/bidirectional-transactional-replication?view=sql-server-2016
Merge replication, which is designed for changes on both sides and handles conflicts: https://learn.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-ver17
Peer-to-peer replication is Enterprise edition only, so it isn't available on Standard: https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-replication?view=sql-server-ver16

To help you further, please share:

Which replication type you are using (snapshot, transactional, or merge).
The exact error message when you insert into the main database.
Whether your tables use identity columns.
Whether the second replication re-initialized or re-created the tables.

Please don't post passwords or server names.

If this helps, please click Accept Answer.

rhu rhu 0 Reputation points

2026-10-01T08:37:10.7+00:00

So the servers are hosted on different VMs in separate locations but they have the same instances and same service accounts and same Ip addrs. They replication to secondary works fine. I insert new records in the DB and kill the replication from Primary to secondary then replicate back to Primary.... The DB publication replicates fine the DB is now unusable because I see some new ms stored procedures.