Sync two sql server database(Active/Active) Mode

Database Administrators Asked by Rai Micheal on December 11, 2020

I have two sql server(SQL2016) Production-1 and Production-2 under one cluster with same database.I need real-time sync between both databases simultaneously like what ever changes in either A or B database will update on both.

Note:In my database more tables don’t have primary key so replication not working for me.
Using Mirroring and log shipping solution also not suitable for me because secondary database will be on Restoring/read mode only.

So probably we need both database on Active/Active mode like read/write option for both databases.

3 Answers

Create primary key on the tables that doesn't have them and set up a Peer-to-peer replication. That would be the best way to achieve it at the SQL Server level. (You will then have to deal with conflict resolution if that happens).

You should ask yourself why you are using 2 different SQL server (at the same location).

If you do this for some kind of "load balancing", keep in mind that this replication process will use ressources. Would be simpler to just have all your app connection pointing to 1 server and have an availability group to replicate it to the other site (if you need high availability).

Answered by Dominique Boucher on December 11, 2020

So far there is no standard feature available within SQL Server (other than Replication) to leave replicated copy in read-write state.

However, If the limitation is acceptable, you can develop a custom script (Powershell/T-SQL) similar to Log Shipping, which can perform following activity and can be scheduled via SQL Agent.

  1. Backup Database
  2. Copy Database Backups
  3. Restore Database Backups (WITH RECOVERY) on Secondary

Basically, the Log Shipping consists of same steps, but restore options limited to NORECOVERY or STANDBY only. This is something has to be managed in custom solution.


  • Consider the secondary copy cannot be accessible during every restore operation
  • Any changes happened on Secondary-only will be lost during next restoration
  • No real-time synchronization, but depending on database size and network band-width, you can set minimal sync-interval time.

Some examples for your reference: Ref1, Ref2, Ref3

Answered by Shekar Kola on December 11, 2020

Active/Active writes possible only for some of the Replications solutions.

If you want to divide the load on the nodes, you can use AlwaysOn with a load balancing and orient your all write process on Primary Node and use it to your read processes all of the nodes you have.

Answered by Yunus UYANIK on December 11, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP