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
    SQL
    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)
);
    SQL
    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', '');
    SQL
    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
);
    SQL
    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');
    SQL
    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
);
    SQL
    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
    System database(1)-
  7. Create Publisher
  8. Select a New publication like below
    Local publications(2)-
    Sync publisher(3)-
    Peer to peer publication(4)-
    Articles(5)-
    Do not replicate delete statements(6)-
  9. Then click next
    Table(7)-
    Agent security(7)-
    Log reader agent security (8)-
    Run under the following window account(10)-
    Wizard actions
    Complete the wizard(14)-
    Create publication-
  10. Then Configure peer-to-peer topology like the one below.a
    Sync publisher(14)-
    Configure peer-to-peer topology wizard(15)-
    Publication(16)-
    configure topology
    SQL server(19)-
    Add a new peer node(20)-
    Configure peer-to-peer topology wizard
    Log reader agent security 22
    Log reader agent security(23)-
    Distribution agent security
    Distribution agent security click ok
    New peer initialization(24)-
    Complelte wizard click finish
    Building the peer to peer topology
  11. Peer to Peer topology was created successfully, we can see.
    Local subscriptions(27)-
    Syn publisher desktop (28)-
  12. If it is not required, we stop the service.
    View synchronization status
  13. Now both DB records count like the below.
    Results
  14. Final output verifications.
  15. If we insert the data in the publisher, it will be reflected in the subscriber.
    SQL
     INSERT INTO AGENTS VALUES ('A888', 'Benjamin', 'Hampshair', '0.11', '008-22536178', '');

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

Monitor and maintain the replication

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.