- Data on the timed synchronization server
- Example: - Test Environment, SQL Server2000, Remote Server Name: XZ, User Name: SA, No Password, Test Database: Test - The table on the server (Query Analyzer Connect to the server) CREATE TABLE [ User] (ID INT Primary Key, Number VARCHAR (4), Name Varchar (10)) GO - The following is in the local area network (native operation) - the table of this machine, State Description: NULL indicates a new record, 1 means modification Over-record, 0 indicates no changed record if exists (select * from dbo.sysObjects where id = Object_id (n '[user]') And ObjectProperty (ID, n'uusrtable ') = 1) DROP TABLE [USER] GOCREATE Table [User] (ID ID IDETENTITY (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 UpdateAsUpdate [user] set state = 1from [user] a join inserted b on a.id = B.Idwhere a.state is not nullogo
- To facilitate synchronization processing, create a link server to the server to synchronize - the remote server here is: XZ, user name: SA, no password if exists (SELECT 1 from master..sysservers where srvname = 'srv_lnk' ) exec sp_dropserver 'srv_lnk', 'droplogins'goexec sp_addlinkedserver' srv_lnk ',' ',' SQLOLEDB ',' xz'exec sp_addlinkedsrvlogin 'srv_lnk', 'false', null, 'sa'go
- Create a stored procedure for synchronous processing 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] GOCREATE PROC P_SYNCHROAS - SET XACT_ABORT ON - Start the MSDTC Service of the Remote Server --Exec Master "XZ_cmdshell 'ISQL / S" XZ "/ U" SA "/ P" "/ q" Exec Master. .xp_cmdshell '' Net Start MSDTC '', NO_OUTPUT ", NO_OUTPUT
- Start the machine's MSDTC service --Exec master..xp_cmdshell 'net start msdtc', NO_OUTPUT
- Distributed transaction processing, if the meigo list is the primary key, use the following method - Segin Distribute Transaction - Synchronous Delete from SRV_LNK.TEST.DBO. [User] Where id NOT IN (SELECT ID FROM [ User])
- Synchronous new data INSERT INTO SRV_LNK.TEST.DBO. [User] Select ID, Number, Name from [user] where state is null - Synchronous modified data Update SRV_LNK.TEST.DBO. [USER] set Number = B.Number, Name = B.Name from srv_lnk.test.dbo. [user] a join [user] b on a.id = B.ID where b.State = 1 - Update the native logo Update after synchronization [user] set state = 0 Where isnull (state, 1) = 1 - Commit TRANGO - Create job, timing execute data synchronization stored procedure if EXISTS (SELECT 1 from msdb..sysjobs where name = 'data processing ") Execute msdb.dbo.sp_delete_job @ Job_name = 'data processing' exec MSDB..SP_ADD_JOB @ Job_name = 'Data Processing
- Create Job Step Declare @SQL VARCHAR (800), @ DBNAME VARCHAR (250) SELECT @ SQL = 'Exec P_Synchro' - Data Processing Command, @ DBNAME = DB_NAME () - Data Processing Database Name
EXEC MSDB..SP_ADD_JOBSTEP @ Job_Name = 'Data Process', @Step_name = 'Data Sync', @SUBSystem = 'TSQL', @ Database_name = @ DBNAME, @Command = @SQL, @retry_attempts = 5, - Retrase @retry_interval = 5 - Retry Interval
- Create schedule EXEC MSDB..SP_ADD_JOBSCHEDULE @JOB_NAME = 'Data Process', @Name = 'Time Arranger', @freq_type = 4, - @freq_interval = 1 daily, - Performed once a day @Active_Start_Time = 00000 --0 Point execution Go
