Two SQL Server server synchronization issues.

server| Server | Issues-Data on a scheduled synchronization server (synchronization of data modifications on the server to local)

--Example:
--Test environment, SQL Server2000, remote server name: Rserver, username: ruser, password: rpwd,
Testing the database: Test

--Tables on the server (Query Analyzer is connected to create on the server)
--state field for secondary update, field value Description: null indicates new record, 1 indicates modified record, 0 indicates unchanged record
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (n ' [Luser] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
drop table [Luser]
Go
CREATE TABLE [Luser] (id int identity (1,1) primary key,[user] varchar (4), pwd varchar (x), address varchar (m), state bit)
Go

--Create a trigger to maintain the value of the State field
Create trigger T_state on [Luser]
After update
As
Update [Luser] set state=1
From [Luser] A join inserted B on a.id=b.id
Where a.state is not null
Go

----The following on the local area network (native operation)
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (n ' [Luser] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
drop table [Luser]
CREATE TABLE [Luser] (ID int primary key,[user] varchar (4), pwd varchar (TEN), address varchar (50))
Go

--To facilitate synchronization, create a linked server to the server to be synchronized
--Here's the remote server name: Rserver, username: ruser, password: rpwd
if exists (select 1 from Master. sysservers where srvname= ' Srv_lnk ')
exec sp_dropserver ' srv_lnk ', ' droplogins '
Go
exec sp_addlinkedserver ' srv_lnk ', ', ' SQLOLEDB ', ' rserver '
exec sp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' Ruser ', ' rpwd '
Go

--Creating a stored procedure for the synchronization process
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
/*--because the MSDTC service (which is required for distributed transaction processing) is not stable, the following section is the selection
--set Xact_abort on
--Start the MSDTC service for the remote server
--exec Master. xp_cmdshell ' isql/s ' rserver '/u ' ruser '/P ' rpwd '/q ' exec master. xp_cmdshell ' net start msdtc ', No_output ', no_output

--Start the MSDTC service for this machine
--exec Master. xp_cmdshell ' net start msdtc ', no_output

--For distributed transactions, if the table uses the Identity column key, use the following method
--begin Distributed TRANSACTION
--*/
--data that is deleted synchronously
Delete FROM [Luser]
where ID not in (the Select ID from srv_lnk.test.dbo.[ Luser])

--Synchronizing the new data
insert INTO [Luser]
Select Id,[user],pwd,address from Srv_lnk.test.dbo. [Luser]
where is null

--Synchronizing the modified data
Update [Luser] Set
[User]=b.[user],pwd=b.pwd,address=b.address
From [Luser] A
Join Srv_lnk.test.dbo. [user] B on a.id=b.id
where b.state=1

--Update flags on the server after synchronization
Update srv_lnk.test.dbo. [Luser] Set state=0 where IsNull (state,1) =1
--commit TRAN--If you enable Distributed transaction processing, add this sentence
Go

--Create a job to perform a stored procedure for data synchronization at timed intervals
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 (@dbname), varchar (250)
Select @sql = ' EXEC p_synchro '--commands for data processing
, @dbname =db_name ()--database name to perform data processing

EXEC msdb.. Sp_add_jobstep @job_name = ' data processing ',
@step_name = ' data synchronization ',
@subsystem = ' TSQL ',
@database_name = @dbname,

@command = @sql,
@retry_attempts = 5--Number of retries
@retry_interval = 5--Retry interval

--Create a schedule
EXEC msdb.. sp_add_jobschedule @job_name = ' data processing ',
@name = ' time schedule ',
@freq_type = 4,--4 per day, 8 per week, 16 per month
@freq_interval = 1--Number of days the job executes
@freq_subday_type = 0,--whether repeated execution, 0x1 at the specified time, 0x4 minutes, 0x8 hours
@freq_subday_interval = 1,--recurrence cycle
@freq_recurrence_factor = 0,--repeat, set to 1, otherwise set to 0
@active_start_time = 00000--0 points begin execution

--Add target server
EXEC Msdb.dbo.sp_add_jobserver
@job_name = @jobname,
@server_name = N ' (local) '
Go



You can also create a copy of the database directly.
That is, publishing servers and subscribers.