AnswerBun.com

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.

Limitations:

  • 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!

Related Questions

Dbca hangs at 59%

0  Asked on December 6, 2020 by user133197

 

Performance issue in Postgresql 12

0  Asked on December 3, 2020 by raghu-mutyam

   

Linked Server throws metadata error

1  Asked on November 30, 2020 by cam

     

What is an ODBK file and how to restore that?

1  Asked on November 14, 2020 by hasnain

   

Calculating percentage over sub query SQL

1  Asked on October 29, 2020 by patrick-kusebauch

   

How to select the rows affected by an update

2  Asked on October 27, 2020 by enharmonic

 

Corruption in mariadb binlog

2  Asked on October 18, 2020 by ctutte

     

“SQL command not properly ended” with trigger

1  Asked on September 28, 2020 by exilevoid

     

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP