Regularly synchronize data on the server

-- Regularly synchronize data on the server
-- Example:
-- Test environment: SQL Server2000, remote server name: xz, User name: SA, no password, Test Database: Test
-- Tables on the server (query analyzer is connected to a table created on the server)
Create Table [user] (ID int primary key, number varchar (4), name varchar (10 ))
Go
-- Perform the following operations on the LAN (Local Machine)
-- Local table, State Description: NULL indicates new record, 1 indicates modified record, 0 indicates no change record
If exists (select * From DBO. sysobjects where id = object_id (n' [user] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [user]
Go
Create Table [user] (ID int identity (1, 1), number varchar (4), name varchar (10), State bit)
Go
-- Create a trigger to maintain the value of the state field
Create trigger t_state on [user]
After update
As
Update [user] Set state = 1
From [user] A join inserted B on A. ID = B. ID
Where a. State is not null
Go -- to facilitate synchronization, create a linked server to the server to be synchronized
-- The remote server name is xz, the user name is SA, and no password is required.
If exists (select 1 from Master .. sysservers where srvname = 'srv _ lnk ')
Exec sp_dropserver 'srv _ lnk ', 'droplogins'
Go
Exec sp_addmediaserver 'srv _ lnk ', '', 'sqloledb', 'xz'
Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'sa'
Go -- create a stored procedure for synchronization
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_synchro] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_synchro]
Go
Create proc p_synchro
As
-- Set xact_abort on
-- Start the MSDTC Service of the remote server
-- Exec master .. xp_mongoshell 'isql/s "xz"/u "sa"/P ""/Q "Exec master .. xp_cmdshell ''net start MSDTC '', no_output" ', no_output -- start the MSDTC Service of the local machine.
-- Exec master.. xp_mongoshell 'net start MSDTC ', no_output -- performs distributed transaction processing. If the table uses the ID column as the primary key, use the following method
-- Begin Distributed Transaction
-- Synchronize Deleted Data
Delete from srv_lnk.test.dbo. [user]
Where id not in (select ID from [user]) -- synchronize new data
Insert into srv_lnk.test.dbo. [user]
Select ID, number, name from [user] Where state is null
 
-- Synchronize modified data
Update srv_lnk.test.dbo. [user] Set
Number = B. number, name = B. Name
From srv_lnk.test.dbo. [user]
Join [user] B on A. ID = B. ID
Where B. State = 1
 
-- Update the local flag after synchronization
Update [user] Set state = 0 where isnull (State, 1) = 1
-- Commit tran
Go -- create a job and regularly execute the data synchronization Stored Procedure
If exists (select 1 from MSDB .. sysjobs where name = 'data ')
Execute MSDB. DBO. sp_delete_job @ job_name = 'data'
Exec MSDB .. sp_add_job @ job_name = 'data' -- create a job
Declare @ SQL varchar (800), @ dbname varchar (250)
Select @ SQL = 'exec p_synchro '-- Data Processing Command
, @ Dbname = db_name () -- Name of the database that executes data processing exec MSDB .. sp_add_jobstep @ job_name = 'data ',
@ Step_name = 'data synchronization ',
@ Subsystem = 'tsql ',
@ Database_name = @ dbname,
@ Command = @ SQL,
@ Retry_attempts = 5, -- number of retries
@ Retry_interval = 5 -- Retry Interval -- create Scheduling
Exec MSDB .. sp_add_jobschedule @ job_name = 'data ',
@ Name = 'schedule ',
@ Freq_type = 4, -- daily
@ Freq_interval = 1, -- run once a day
@ Active_start_time = 00000 -- execute at 0
Go