Complete Guide to Two-Way Replication in
SQL Server
Establish and configure two-way replication between two SQL Server instances (e.g., VM1 and
VM2) to synchronize data bi-directionally for high availability, reporting, or distributed
environments.
1. Prerequisites
Hardware and OS:
Two machines with SQL Server 2019 Standard Edition installed (VM1 and VM2).
Both machines should be on the same network with static IPs and proper hostnames.
Ensure SQL Server Agent is enabled and running.
Windows Firewall:
Open TCP and UDP port 1433 for SQL Server and port 1434 for SQL Browser.
In Windows Defender Firewall, enable Inbound Rules for:
o SQL Server TCP (1433)
o SQL Server UDP (1434)
o Any custom ports used by SQL instances.
Region and Language Settings:
Set both servers to the same language and region format (e.g., English - United States)
to avoid collation mismatches.
SQL Server Configuration:
Use SQL Server Configuration Manager to:
o Confirm TCP/IP is enabled under SQL Server Network Configuration.
o Start services: SQL Server, SQL Server Agent, SQL Browser.
o Check SQL Server instance port (default is 1433).
Folder and Path Permissions:
Ensure proper privileges on snapshot folders used for replication:
o Right-click folder (e.g., C:\Replication\Snapshot) > Properties > Security.
o Add SQL Server Agent service account with Read/Write permissions.
o Service account can be verified via SQL Server Configuration Manager.
www.linkedin.com/in/daniyaldba/ +923408801269 [email protected]
Database Requirements:
Databases should have identical schemas on both servers.
All tables must have a Primary Key.
Each table must have a rowguid column for merge replication.
Alter table tablename add rowguid uniqueidentifier rowguidcol not null default newid();
Two-Way Replication Setup (Merge Replication)
Step 1: Configure Distributor on Both Servers
1. In SSMS: Right-click Replication > Configure Distribution.
2. Choose "Use the server as its own Distributor."
3. Set snapshot folder (e.g., C:\Replication\Snapshot).
4. Complete the wizard.
Step 2: Create Publications
On VM1:
1. Right-click Local Publications > New Publication.
2. Select the database > Choose Merge replication.
3. Select tables > Add rowguid column if prompted.
4. Set snapshot agent schedule.
5. Name the publication (e.g., Pub_VM1).
On VM2:
Repeat above steps to create Pub_VM2.
Step 3: Create Subscriptions
On VM1:
1. Right-click Pub_VM1 > New Subscription.
2. Choose subscriber (VM2).
3. Select Push or Pull subscription.
4. Schedule synchronization.
www.linkedin.com/in/daniyaldba/ +923408801269 [email protected]
On VM2:
Repeat steps for Pub_VM2 with VM1 as subscriber.
Step 4: Synchronization
Use Replication Monitor to monitor status.
Set agents to run continuously or on a schedule.
Why Not Transactional Replication for Two-Way Sync?
Transactional Replication:
One-way: Publisher ➔ Subscriber.
No built-in conflict resolution.
Bi-directional setup risks conflicts, duplicate keys, and data corruption.
Merge Replication:
Built for bi-directional sync.
Uses conflict resolution via rowguid and priority.
Supports disconnected environments.
Common Issues & Solutions
Issue 1: Missing rowguid column
Fix: Add a rowguid column using SQL.
Issue 2: Subscription initialization error
Fix:
Skip snapshot.
Use existing schema/data.
Issue 3: Snapshot Agent not running
Fix: Start from SQL Server Agent > Jobs.
www.linkedin.com/in/daniyaldba/ +923408801269 [email protected]
Issue 4: Permissions denied to snapshot folder
Fix: Add SQL Agent account to folder permissions.
Issue 5: Collation conflicts or locale mismatch
Fix: Ensure region/language settings match on both servers.
Issue 6: Services not running
Fix: Start all SQL-related services from Configuration Manager.
Best Practices
Avoid changing primary keys after replication.
Regularly monitor Replication Monitor.
Perform full backups of replicated databases.
Schedule off-peak replication to reduce load.
Use static IPs and ensure hostname resolution.
Useful Commands
Check SQL Server Port:
select local_net_address, local_tcp_port from sys.dm_exec_connections where session_id =
@@spid;
Check SQL Agent Service Account:
Select servicename, service_account from sys.dm_server_services;
============================GOOD LUCK============================
www.linkedin.com/in/daniyaldba/ +923408801269 [email protected]
