SQL Server Database Synchronization example

 

I. Application scenarios:

1) The execution script of the job is used when two databases are synchronized at regular intervals using the job;

2) execute scripts for manual data synchronization.

 

Ii. Example:

Use localdatabasename;

Go

-- Create a linked server

If not exists (select * From SYS. servers where name = 'remoteserver ')

Begin

Exec sp_addmediaserver 'deleteserver', '', 'sqlncl', 'xx. XX. xx'

End

Go

-- Log on to the linked server

Exec sp_add1_srvlogin 'remoteserver', 'false', null, 'remoteserver username', 'remoteserver password'

Go

-- Synchronize the latest data from the local table to the corresponding table on the linked server

Insert into openquery ([remoteserver], 'select * From remotedatabasename. DBO. remotetablename ')

Select R. * From localtablename R

Where R. ID> (

Select maxid from openquery ([remoteserver], 'select max (ID) maxid from remotedatabasename. DBO. remotetablename ')

)

Go

-- Exit the linked server

Exec sp_droplinkedsrvlogin 'remoteserver', null

Go

-- Delete a linked server

Exec sp_dropserver 'remoteserver', 'droplogins'

Go

 

Iii. Related commands:

Sp_addmediaserver (TRANSACT-SQL)

Http://msdn.microsoft.com/zh-cn/library/ms190479.aspx

 

Sp_add1_srvlogin (TRANSACT-SQL)

Http://msdn.microsoft.com/zh-cn/library/ms189811.aspx

 

Sp_droplinkedsrvlogin (TRANSACT-SQL)

Http://msdn.microsoft.com/zh-cn/library/75a4a040-72d5-4d29-8304-de0aa481ad4b

 

Sp_dropserver (TRANSACT-SQL)

Http://msdn.microsoft.com/zh-cn/library/ms174310.aspx

 

Openquery (TRANSACT-SQL)

Http://msdn.microsoft.com/zh-cn/library/ms188427.aspx