Database Synchronization using stored procedures or triggers + Job Scheduling

/* -- Synchronize example references of two databases)

Test environment and synchronization requirements:

There are database servers srv1 and srv2. The two computers can access each other and have data.
Srv1. the database name. Author has the following fields: ID, name, phone,
Srv2. database name... author has fields: ID, name, telphone, adress

Requirements:
Srv1. database name... author add record, then srv1. database name... author record is added
If the phone field of srv1. library name... author is updated, the telphone field of srv1. library name... author is updated.
--*/

-- General handling steps
-- 1. Create a connection server on srv1 to operate srv2 in srv1 for synchronization
Exec sp_addrole server 'srv2', '', 'sqloledb', 'SQL Instance name or IP address of srv2'
Exec sp_add1_srvlogin 'srv2', 'false', null, 'username', 'Password'
Go

-- 2. Start MSDTC (Distributed Transaction Processing Service) in the srv1 and srv2 brains and set it to Automatic startup.
My computer -- control panel -- Administrative Tools -- service -- Right-click Distributed Transaction Coordinator -- Property -- start -- and set the start type to automatic start
Go

-- 3. synchronous processing

-- A. Create a trigger in srv1. author to implement real-time data synchronization.
-- Add Synchronization
Create trigger tr_insert_author on author
For insert
As
Set xact_abort on
Insert srv2. database name. DBO. Author (ID, name, telphone)
Select ID, name, telphone from inserted
Go

-- Modify Synchronization
Create trigger tr_update_author on author
For update
As
Set xact_abort on
Update B set name = I. Name, telphone = I. telphone
From srv2. database name. DBO. Author B, inserted I
Where B. ID = I. ID
Go

-- Delete Synchronization
Create trigger tr_delete_author on author
For Delete
As
Set xact_abort on
Delete B
From srv2. database name. DBO. Author B, deleted d
Where B. ID = D. id
Go

-- 3. synchronous processing method 2: timed synchronization

-- Create the following synchronization processing stored procedure in srv1
Create proc p_process
As
-- Update modified data
Update B set name = I. Name, telphone = I. telphone
From srv2. database name. DBO. Author B, author I
Where B. ID = I. ID and
(B. Name <> I. Name or B. telphone <> I. telphone)

-- Insert new data
Insert srv2. database name. DBO. Author (ID, name, telphone)
Select ID, name, telphone from author I
Where not exists (
Select * From srv2. database name. DBO. Author where id = I. ID)

-- Delete Deleted Data (if needed)
Delete B
From srv2. database name. DBO. Author B
Where not exists (
Select * from author where id = B. ID)
Go

-- Create a job and call the above synchronization processing stored procedure on a regular basis.

Enterprise Manager
-- Manage
-- SQL Server proxy
-- Right-click a job
-- Create a job
-- Enter the job name in "general"
-- "Step"
-- New
-- Enter the step name in "Step name"
-- Select "Transact-SQL script (tsql)" in "type )"
-- "Database": select the database for Command Execution
-- Enter the statement to be executed in "command": exec p_process
-- OK
-- "Scheduling" item
-- Create Scheduling
-- Enter the scheduling name in "name"
-- Select your job execution schedule in "scheduling type"
-- If "repeated appears" is selected"
-- Click "change" to set your schedule

Then start the SQL Agent service and set it to Automatic startup. Otherwise, your job will not be executed.

Setting method:
My computer -- control panel -- Administrative Tools -- service -- Right-click SQLServerAgent -- properties -- start type -- select "auto start" -- OK.