0

We wanted to have a DR setup for our SQL Server databases.

So for testing purpose we went ahead with Transactional Replication.

Setup

  • Server 1 (SQL Server 2019): Acting as Distributor and Publisher
  • Server 2 (SQL Server 2019): Acting as Subscriber
  • IP's are used for configuration, not Server Names (explicitly mentioning this point, since I have went through few blogs were the problems and issues are highlighted due to IP)

The system is working as expected, but following behaviours are observed:

Problem

  • Deleted all records in Server 1
  • Due to transactional replication all of the records are deleted in Server 2
  • Post delete inserted records in Server 1
  • Expected behaviour was Server 2 will have new records, but it did not
  • Re-initialize the subscription via Replication Monitor in SSMS
  • Then this worked and records are replicated

I wanted to take this same setup to Production, but this behaviour of Insert after Delete is stopping me to proceed ahead.

What the root cause of this issue?

Is there a way to overcome it or this is the natural behaviour?

How to avoid such situations?

11
  • Post delete inserted records in Server 1 what does this mean exactly? Jan 4 at 9:31
  • We deleted all of the Records first (Server 1), it was reflected in another Server (Subscriber) all of the records were delete. Then we inserted few records in Server 1, but it did not reflected/inserted in Server 2. Jan 4 at 9:34
  • Does just inserting records work? Jan 4 at 9:35
  • Yes, when I Re-initialized the Subsciber, then all of the records were reflected, also I added more records those all reflected Jan 4 at 9:36
  • Did you execute CHECKPOINT after your insert / delete tests? How is your replication configured? Jan 4 at 9:44

1 Answer 1

1

Unless you need features offered by transactional replication that are not offered by Availability Groups, Log Shipping or Database Mirroring, using one of the latter solutions for disaster recovery will yield an environment that is more reliable and simpler to maintain once everything is up an running.

https://learn.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr?view=sql-server-ver15

Availability Groups are supported in the standard edition of SQL Server from SQL Server 2016.

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.