MS SQL Server Replication or Synchronization Peer-to-Peer Topology

SQL Server database synchronization refers to the process of keeping multiple databases consistent with each other, ensuring that they contain the same data and are up-to-date with changes made on any of the synchronized databases. There are various methods to achieve

What is SQL Server synchronization?

To synchronize databases in Microsoft SQL Server, you have several options depending on your requirements and environment. Here's a basic overview of some common methods

SQL Server replication

SQL Server Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. It supports various replication types such as snapshot replication, transactional replication, and merge replication, each catering to different synchronization needs.

We are going to see the above methodology with Peer To Peer briefly below.

1. SQL server always on availability groups: This feature provides high availability and disaster recovery solutions, but it can also be used for database synchronization between primary and secondary replicas. Always On Availability Groups provide automatic failover and readable secondary replicas, making it suitable for scenarios where you need real-time or near-real-time synchronization.
2. SQL server integration services (SSIS): SSIS is a platform for building enterprise-level data integration and data transformation solutions. You can create SSIS packages to extract, transform, and load (ETL) data between databases, including synchronizing data between them.
3. Database mirroring: Database Mirroring is a deprecated feature of SQL Server 2012, but if you're using an older version, you might still encounter it. It's mainly used for high availability, but it can also be configured to provide database synchronization between a principal database and one or more mirror databases.
4. Custom scripts or applications: Depending on your specific requirements, you might develop custom scripts or applications using SQL Server Management Objects (SMO), SQLCMD utility, or programming languages like C# with SQL Server libraries to synchronize data between databases.

SQL Server transactional replication using peer-to-peer topology

1. Create DB for Sync_Publisher

   CREATE TABLE  AGENTS
   (  
       AGENT_CODE CHAR(6) NOT NULL PRIMARY KEY,
       AGENT_NAME CHAR(40),
       WORKING_AREA CHAR(35),
       COMMISSION decimal(10,2),
       PHONE_NO CHAR(15),
       COUNTRY varchar(25)
   );

   INSERT INTO AGENTS VALUES ('A007', 'Ramasundar', 'Bangalore', '0.15', '077-25814763', '');
   INSERT INTO AGENTS VALUES ('A003', 'Alex ', 'London', '0.13', '075-12458969', '');
   INSERT INTO AGENTS VALUES ('A008', 'Alford', 'New York', '0.12', '044-25874365', '');
   INSERT INTO AGENTS VALUES ('A011', 'Ravi Kumar', 'Bangalore', '0.15', '077-45625874', '');
   INSERT INTO AGENTS VALUES ('A010', 'Santakumar', 'Chennai', '0.14', '007-22388644', '');
   INSERT INTO AGENTS VALUES ('A012', 'Lucida', 'San Jose', '0.12', '044-52981425', '');
   INSERT INTO AGENTS VALUES ('A005', 'Anderson', 'Brisban', '0.13', '045-21447739', '');
   INSERT INTO AGENTS VALUES ('A001', 'Subbarao', 'Bangalore', '0.14', '077-12346674', '');
   INSERT INTO AGENTS VALUES ('A002', 'Mukesh', 'Mumbai', '0.11', '029-12358964', '');
   INSERT INTO AGENTS VALUES ('A006', 'McDen', 'London', '0.15', '078-22255588', '');
   INSERT INTO AGENTS VALUES ('A004', 'Ivan', 'Torento', '0.15', '008-22544166', '');
   INSERT INTO AGENTS VALUES ('A009', 'Benjamin', 'Hampshair', '0.11', '008-22536178', '');

   CREATE TABLE  CUSTOMER
   (   
       CUST_CODE VARCHAR(6) NOT NULL PRIMARY KEY,
       CUST_NAME VARCHAR(40) NOT NULL,
       CUST_CITY CHAR(35),
       WORKING_AREA VARCHAR(35) NOT NULL,
       CUST_COUNTRY VARCHAR(20) NOT NULL,
       GRADE decimal,
       OPENING_AMT decimal(12,2) NOT NULL,
       RECEIVE_AMT decimal(12,2) NOT NULL,
       PAYMENT_AMT decimal(12,2) NOT NULL,
       OUTSTANDING_AMT decimal(12,2) NOT NULL,
       PHONE_NO VARCHAR(17) NOT NULL,
       AGENT_CODE CHAR(6) NOT NULL REFERENCES AGENTS
   );

   INSERT INTO CUSTOMER VALUES ('C00013', 'Holmes', 'London', 'London', 'UK', '2', '6000.00', '5000.00', '7000.00', '4000.00', 'BBBBBBB', 'A003');
   INSERT INTO CUSTOMER VALUES ('C00001', 'Micheal', 'New York', 'New York', 'USA', '2', '3000.00', '5000.00', '2000.00', '6000.00', 'CCCCCCC', 'A008');
   INSERT INTO CUSTOMER VALUES ('C00020', 'Albert', 'New York', 'New York', 'USA', '3', '5000.00', '7000.00', '6000.00', '6000.00', 'BBBBSBB', 'A008');
   INSERT INTO CUSTOMER VALUES ('C00025', 'Ravindran', 'Bangalore', 'Bangalore', 'India', '2', '5000.00', '7000.00', '4000.00', '8000.00', 'AVAVAVA', 'A011');
   INSERT INTO CUSTOMER VALUES ('C00024', 'Cook', 'London', 'London', 'UK', '2', '4000.00', '9000.00', '7000.00', '6000.00', 'FSDDSDF', 'A006');
   INSERT INTO CUSTOMER VALUES ('C00015', 'Stuart', 'London', 'London', 'UK', '1', '6000.00', '8000.00', '3000.00', '11000.00', 'GFSGERS', 'A003');
   INSERT INTO CUSTOMER VALUES ('C00002', 'Bolt', 'New York', 'New York', 'USA', '3', '5000.00', '7000.00', '9000.00', '3000.00', 'DDNRDRH', 'A008');
   INSERT INTO CUSTOMER VALUES ('C00018', 'Fleming', 'Brisban', 'Brisban', 'Australia', '2', '7000.00', '7000.00', '9000.00', '5000.00', 'NHBGVFC', 'A005');
   INSERT INTO CUSTOMER VALUES ('C00021', 'Jacks', 'Brisban', 'Brisban', 'Australia', '1', '7000.00', '7000.00', '7000.00', '7000.00', 'WERTGDF', 'A005');
   INSERT INTO CUSTOMER VALUES ('C00019', 'Yearannaidu', 'Chennai', 'Chennai', 'India', '1', '8000.00', '7000.00', '7000.00', '8000.00', 'ZZZZBFV', 'A010');
   INSERT INTO CUSTOMER VALUES ('C00005', 'Sasikant', 'Mumbai', 'Mumbai', 'India', '1', '7000.00', '11000.00', '7000.00', '11000.00', '147-25896312', 'A002');
   INSERT INTO CUSTOMER VALUES ('C00007', 'Ramanathan', 'Chennai', 'Chennai', 'India', '1', '7000.00', '11000.00', '9000.00', '9000.00', 'GHRDWSD', 'A010');
   INSERT INTO CUSTOMER VALUES ('C00022', 'Avinash', 'Mumbai', 'Mumbai', 'India', '2', '7000.00', '11000.00', '9000.00', '9000.00', '113-12345678','A002');
   INSERT INTO CUSTOMER VALUES ('C00004', 'Winston', 'Brisban', 'Brisban', 'Australia', '1', '5000.00', '8000.00', '7000.00', '6000.00', 'AAAAAAA', 'A005');
   INSERT INTO CUSTOMER VALUES ('C00023', 'Karl', 'London', 'London', 'UK', '0', '4000.00', '6000.00', '7000.00', '3000.00', 'AAAABAA', 'A006');
   INSERT INTO CUSTOMER VALUES ('C00006', 'Shilton', 'Torento', 'Torento', 'Canada', '1', '10000.00', '7000.00', '6000.00', '11000.00', 'DDDDDDD', 'A004');
   INSERT INTO CUSTOMER VALUES ('C00010', 'Charles', 'Hampshair', 'Hampshair', 'UK', '3', '6000.00', '4000.00', '5000.00', '5000.00', 'MMMMMMM', 'A009');
   INSERT INTO CUSTOMER VALUES ('C00017', 'Srinivas', 'Bangalore', 'Bangalore', 'India', '2', '8000.00', '4000.00', '3000.00', '9000.00', 'AAAAAAB', 'A007');
   INSERT INTO CUSTOMER VALUES ('C00012', 'Steven', 'San Jose', 'San Jose', 'USA', '1', '5000.00', '7000.00', '9000.00', '3000.00', 'KRFYGJK', 'A012');
   INSERT INTO CUSTOMER VALUES ('C00008', 'Karolina', 'Torento', 'Torento', 'Canada', '1', '7000.00', '7000.00', '9000.00', '5000.00', 'HJKORED', 'A004');
   INSERT INTO CUSTOMER VALUES ('C00003', 'Martin', 'Torento', 'Torento', 'Canada', '2', '8000.00', '7000.00', '7000.00', '8000.00', 'MJYURFD', 'A004');
   INSERT INTO CUSTOMER VALUES ('C00009', 'Ramesh', 'Mumbai', 'Mumbai', 'India', '3', '8000.00', '7000.00', '3000.00', '12000.00', 'Phone No', 'A002');
   INSERT INTO CUSTOMER VALUES ('C00014', 'Rangarappa', 'Bangalore', 'Bangalore', 'India', '2', '8000.00', '11000.00', '7000.00', '12000.00', 'AAAATGF', 'A001');
   INSERT INTO CUSTOMER VALUES ('C00016', 'Venkatpati', 'Bangalore', 'Bangalore', 'India', '2', '8000.00', '11000.00', '7000.00', '12000.00', 'JRTVFDD', 'A007');
   INSERT INTO CUSTOMER VALUES ('C00011', 'Sundariya', 'Chennai', 'Chennai', 'India', '3', '7000.00', '11000.00', '7000.00', '11000.00', 'PPHGRTS', 'A010');

   CREATE TABLE  ORDERS
   (
       ORD_NUM decimal(6,0) NOT NULL PRIMARY KEY,
       ORD_AMOUNT decimal(12,2) NOT NULL,
       ADVANCE_AMOUNT decimal(12,2) NOT NULL,
       ORD_DATE DATE NOT NULL,
       CUST_CODE varchar(6) NOT NULL REFERENCES CUSTOMER,
       AGENT_CODE CHAR(6) NOT NULL REFERENCES AGENTS,
       ORD_DESCRIPTION varchar(60) NOT NULL
   );

   INSERT INTO ORDERS VALUES('200100', '1000.00', '600.00', '08/01/2008', 'C00013', 'A003', 'SOD');
   INSERT INTO ORDERS VALUES('200110', '3000.00', '500.00', '04/15/2008', 'C00019', 'A010', 'SOD');
   INSERT INTO ORDERS VALUES('200107', '4500.00', '900.00', '08/30/2008', 'C00007', 'A010', 'SOD');
   INSERT INTO ORDERS VALUES('200112', '2000.00', '400.00', '05/30/2008', 'C00016', 'A007', 'SOD');
   INSERT INTO ORDERS VALUES('200113', '4000.00', '600.00', '06/10/2008', 'C00022', 'A002', 'SOD');
   INSERT INTO ORDERS VALUES('200102', '2000.00', '300.00', '05/25/2008', 'C00012', 'A012', 'SOD');
   INSERT INTO ORDERS VALUES('200114', '3500.00', '2000.00', '08/15/2008', 'C00002', 'A008', 'SOD');
   INSERT INTO ORDERS VALUES('200122', '2500.00', '400.00', '09/16/2008', 'C00003', 'A004', 'SOD');
   INSERT INTO ORDERS VALUES('200118', '500.00', '100.00', '07/20/2008', 'C00023', 'A006', 'SOD');
   INSERT INTO ORDERS VALUES('200119', '4000.00', '700.00', '09/16/2008', 'C00007', 'A010', 'SOD');
   INSERT INTO ORDERS VALUES('200121', '1500.00', '600.00', '09/23/2008', 'C00008', 'A004', 'SOD');
   INSERT INTO ORDERS VALUES('200130', '2500.00', '400.00', '07/30/2008', 'C00025', 'A011', 'SOD');
   INSERT INTO ORDERS VALUES('200134', '4200.00', '1800.00', '09/25/2008', 'C00004', 'A005', 'SOD');
   INSERT INTO ORDERS VALUES('200108', '4000.00', '600.00', '02/15/2008', 'C00008', 'A004', 'SOD');
   INSERT INTO ORDERS VALUES('200103', '1500.00', '700.00', '05/15/2008', 'C00021', 'A005', 'SOD');
   INSERT INTO ORDERS VALUES('200105', '2500.00', '500.00', '07/18/2008', 'C00025', 'A011', 'SOD');
   INSERT INTO ORDERS VALUES('200109', '3500.00', '800.00', '07/30/2008', 'C00011', 'A010', 'SOD');
   INSERT INTO ORDERS VALUES('200101', '3000.00', '1000.00', '07/15/2008', 'C00001', 'A008', 'SOD');
   INSERT INTO ORDERS VALUES('200111', '1000.00', '300.00', '07/10/2008', 'C00020', 'A008', 'SOD');
   INSERT INTO ORDERS VALUES('200104', '1500.00', '500.00', '03/13/2008', 'C00006', 'A004', 'SOD');
   INSERT INTO ORDERS VALUES('200106', '2500.00', '700.00', '04/20/2008', 'C00005', 'A002', 'SOD');
   INSERT INTO ORDERS VALUES('200125', '2000.00', '600.00', '10/10/2008', 'C00018', 'A005', 'SOD');
   INSERT INTO ORDERS VALUES('200117', '800.00', '200.00', '10/20/2008', 'C00014', 'A001', 'SOD');
   INSERT INTO ORDERS VALUES('200123', '500.00', '100.00', '09/16/2008', 'C00022', 'A002', 'SOD');
   INSERT INTO ORDERS VALUES('200120', '500.00', '100.00', '07/20/2008', 'C00009', 'A002', 'SOD');
   INSERT INTO ORDERS VALUES('200116', '500.00', '100.00', '07/13/2008', 'C00010', 'A009', 'SOD');
   INSERT INTO ORDERS VALUES('200124', '500.00', '100.00', '06/20/2008', 'C00017', 'A007', 'SOD');
   INSERT INTO ORDERS VALUES('200126', '500.00', '100.00', '06/24/2008', 'C00022', 'A002', 'SOD');
   INSERT INTO ORDERS VALUES('200129', '2500.00', '500.00', '07/20/2008', 'C00024', 'A006', 'SOD');
   INSERT INTO ORDERS VALUES('200127', '2500.00', '400.00', '07/20/2008', 'C00015', 'A003', 'SOD');
   INSERT INTO ORDERS VALUES('200128', '3500.00', '1500.00', '07/20/2008', 'C00009', 'A002', 'SOD');
   INSERT INTO ORDERS VALUES('200135', '2000.00', '800.00', '09/16/2008', 'C00007', 'A010', 'SOD');

2. Create DB Sync_Subcriber
3. Get backup from Sync_Publisher DB
4. Then restore into Sync_Subcriber
5. Now both DBs are ready and updated date, we can synchronize both DBs using replication peer-to-peer methodology.
6. Before that, we wanted to make sure that, the SQL server replication component was installed and enabled the distribution service in our machine. See the below snapshot. It looks like this, you can configure it after installing the replication component using SQL server management studio
   
7. Create Publisher
8. Select a New publication like below
   
   
   
   
   
9. Then click next
   
   
   
   
   
   
   
10. Then Configure peer-to-peer topology like the one below.a
    
    
    
    
    
    
    
    
    
    
    
    
    
    
11. Peer to Peer topology was created successfully, we can see.
    
    
12. If it is not required, we stop the service.
    
13. Now both DB records count like the below.
    
14. Final output verifications.
15. If we insert the data in the publisher, it will be reflected in the subscriber.

     INSERT INTO AGENTS VALUES ('A888', 'Benjamin', 'Hampshair', '0.11', '008-22536178', '');
    
    INSERT INTO AGENTS VALUES ('A889', 'Benjamin', 'Hampshair', '0.11', '008-22536178', '');

Monitor and maintain the replication

• Monitor replication using SQL Server Management Studio or system stored procedures.
• Regularly check for any replication errors or latency.
• Perform maintenance tasks as needed, such as reinitialization or adding more articles to the publication.

That's a basic overview of setting up Transactional Replication in SQL Server. Remember that this is just a starting point, and there are many additional configuration options and considerations depending on your specific requirements and environment. Refer to the SQL Server documentation for detailed guidance on replication setup and maintenance.

---