Sync two sql server database(Active/Active) Mode

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).

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

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

• Merge Replication
• Peer-to-Peer Replication

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.