Have 3 SQL Server Database in different 3 Windows Server Machine

• Adelia

  SSC-Addicted

  Points: 431

  More actions

  February 21, 2025 at 1:42 am

  #4542646

  Hello,

  I am newbies in SQL Server Administration

  My planning is to setup - 3 SQL Server Database in Server A, Server B & Server C. All these 3 SQL Server Database have identical schema and the data should be identical. Other word, SQL Server Services without shared storage. All the database files are on their respective machine

  My question is,

  1. How to keep all of these SQL Server Database always identical ?
  2. If data is updated on Server A - Server B & Server C will sync the data immediately
  3. If data is updated on Server C - Server A & Server C will sync the data immediately. And so on

   

  I have read about

  1. Database Mirroring. This approach will obsolete soon
  2. Database Replication
  3. Database Service Broker

  Please help me to make an accurate planning and decision

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  February 22, 2025 at 2:07 am

  #4543444

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• Abhinav

  Grasshopper

  Points: 14

  More actions

  February 23, 2025 at 5:15 am

  #4544061

  Hi Adelia,

  My vote is to configure either Peer-to-Peer replication or Merge replication so you can form a multi-master ring.

  If you're on SQL Server 2019 or later, Peer-to-Peer replication now supports built-in conflict detection—something that wasn’t available in earlier versions. Personally, I'd lean towards Peer-to-Peer replication in that case. Merge replication is also a good option, but I’ve seen many people struggle with its complexities over the years.

  You can read more about the new conflict detection mechanism in Peer-to-Peer replication here.

  https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-conflict-detection-in-peer-to-peer-replication?view=sql-server-ver16

  Thanks,
  Abhinav

• shukla.chandan12

  Newbie

  Points: 2

  More actions

  February 23, 2025 at 10:53 am

  #4544177

  Given your scenario, your best bets are:

  1. Merge Replication--> (Not real time, Inbuilt mechanisms to handle conflict and schema changes easy )
  2. SQL Server Peer to Peer Replication(Near real time, If you need to keep all servers read-write but you have to resolve conflict on application level, also schema changes are difficult, i.e stop and start replication for making schema level change)
  3. SQL Server Always on (If you have to keep two servers read only, and one read-write)
  4. Introduce Service broker or Kafka , if wanted to do on application level