Server SQL2008 链接服务器数据同步

/*
--author：Bruce Qin*/

/*--
-- 删除链接和链接服务器
Exec sp_droplinkedsrvlogin syncDB,Null
Exec sp_dropserver syncDB

EXEC sp_addlinkedserver
      @server='syncDB',--被访问的服务器别名
      @srvproduct='',
      @provider='SQLOLEDB',
      @datasrc='202.**.***.43\MSSQLSERVER2008' --要访问的服务器实例名

---- @datasrc='servername\instancename'
---- servername 是运行 SQL Server 的计算机名称(或者IP)，instancename 是用户将被连接到的特定 SQL Server 实例的名称

EXEC sp_addlinkedsrvlogin
     'syncDB', --被访问的服务器别名
     'false',
     NULL,
     'sa', --帐号
     'sa' --密码

exec sp_serveroption 'syncDB43','rpc out','true';  --允许调用链接服务器上的存储过程

-- 在远程服务器43的作业中调用本服务器的存储过程saveSync_process_ON187     
--exec('exec Mydb_sync187.dbo.saveSync_process_ON187') at syncDB43;

**/

/*--链接服务器同步数据示例 

服务器HAI：
       hostname:HAI
	   实例名：HAI\BRUCESERVER	 
	  （测试）数据库名：Mydb_sync187 

服务器CHOW：	   
	   hostname:CHOW
       实例名：CHOW\SQL2008	   
	  （测试）数据库名：Mydb_sync10 

同步要求：
       1、在服务器HAI在的My_sync187数据库出现save、update、delete操作后，将数据同步到服务器CHOW的Mydb_sync10数据库中，保持数据一致性。
	   2、在服务器CHOW在的Mydb_sync10数据库出现save、update、delete操作后，将数据同步到服务器HAI的Mydb_sync187数据库中，保持数据一致性。
--*/ 

/*
-- 在服务器HAI上完成save、update、delete操作后将数据单向同步到服务器CHOW上的设置如下列步骤所示（在服务器CHOW的设置相似）
*/

--步骤1：在服务器HAI上创建链接服务器（链接到服务器CHOW，202.**.***.10）： 
-------
Exec sp_droplinkedsrvlogin syncDB10,Null
Exec sp_dropserver syncDB10
EXEC sp_addlinkedserver
      @server='syncDB10',--被访问的服务器别名
      @srvproduct='',
      @provider='SQLOLEDB',
      @datasrc='202.**.***.10\SQL2008' --要访问的服务器

EXEC sp_addlinkedsrvlogin
     'syncDB10', --被访问的服务器别名
     'false',
     NULL,
     'sa', --帐号
     'sa' --密码


--步骤2：在服务器HAI上启动 msdtc(分布式事务处理服务),并且设置为自动启动 

--  我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动 
--  注：如果在第1步Distributed Transaction Coordinator 无法启动，则是因为丢失了日志文件,重新创建日志文件,再启动就行了。重新创建 MSDTC 日志,并重新启动服务的步骤如下：
--    (1) 单击"开始",单击"运行",输入 cmd 后按"确定"。
--    (2) 输入:msdtc -resetlog (注意运行此命令时,不要执行挂起的事务)
--    (3) 最后输入:net start msdtc 回车。


--步骤3： 在服务器HAI上的数据库Mydb_sync187 创建一个作业定时远程调用服务器CHOW数据库Mydb_sync10上的同步处理存储过程。

--  企业管理器 
--  管理 
--  SQL Server代理 
--  右键作业 
--  新建作业 
--  "常规"项中输入作业名称,如 Mydb_sync187_job 
--  "步骤"项 
--   新建 
--  "步骤名"中输入步骤名，如 Mydb_sync187_step 
--  "类型"中选择"Transact-SQL 脚本(TSQL)" 
--  "数据库"选择执行命令的数据库，如 Mydb_sync187 
--  "命令"中输入要执行的语句: 

    EXEC sp_addlinkedserver
      @server='syncDB10',--被访问的服务器别名
      @srvproduct='',
      @provider='SQLOLEDB',
      @datasrc='202.**.***.10\SQL2008'; --要访问的服务器

	EXEC sp_addlinkedsrvlogin
     'syncDB10', --被访问的服务器别名
     'false',
      NULL,
     'sa', --帐号
     'sa'; --密码

    --允许调用链接服务器syncDB10上的存储过程
	EXEC sp_serveroption 'syncDB10','rpc out','true';  

	EXEC('exec Mydb_sync10.dbo.saveSync_process_ON10') at syncDB10;
	EXEC('exec Mydb_sync10.dbo.updateSync_process_ON10') at syncDB10;
	EXEC('exec Mydb_sync10.dbo.deleteSync_process_ON10') at syncDB10;

    Exec sp_droplinkedsrvlogin syncDB10,Null;
    Exec sp_dropserver syncDB10; 

--  确定 
--  "调度"项 
--  新建调度 
--  "名称"中输入调度名称 
--  "调度类型"中选择你的作业执行安排 
--  如果选择"反复出现" 
--  点"更改"来设置你的时间安排 （设置合理的时间安排）


--  然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行 
--  设置方法: 
--  我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定. 

--步骤4：在服务器HAI上调用服务器CHOW上的实现同步处理的存储过程如下 

--  服务器CHOW上Mydb_sync10数据库中创建如下的同步处理存储过程： 
GO
--新增数据操作存储过程
create proc saveSync_process_ON187 
as
begin tran --开始事物

--将远程服务器新增的数据同步到本服务器中 
insert t_park_account(status,user_id,car_id,cardNo,cardType,cost,lowestBalanceAfterConsume,tollyLowestBalanceAC,noEntryBalance,lowBalanceNotice,openAccountTime,statusUpdateTime,remark,parkNo,balance) 
	select status,user_id,car_id,cardNo,cardType,cost,lowestBalanceAfterConsume,tollyLowestBalanceAC,noEntryBalance,lowBalanceNotice,openAccountTime,statusUpdateTime,remark,parkNo,balance 
	from syncDB187.Mydb_sync187.dbo.t_park_account _account
	where not exists( 
					select * from t_park_account where sys_id=_account.sys_id)

--@@error的值不等于0表示上一条SQL语句执行结果有错，即采取事物回滚措施
if @@ERROR<>0 
begin
	rollback tran
end

insert t_park_user(sys_id,userName,credentialsType,credentialsNo,phone,mobileNumber,address) 
	select sys_id,userName,credentialsType,credentialsNo,phone,mobileNumber,address 
	from syncDB187.Mydb_sync187.dbo.t_park_user _user
	where not exists( 
					select * from t_park_user where sys_id=_user.sys_id)

if @@ERROR<>0 
begin
rollback tran
end

insert t_park_car(vehPlate,carType,carColour) 
	select vehPlate,carType,carColour 
	from syncDB187.Mydb_sync187.dbo.t_park_car _car
	where not exists(
					select * from t_park_car where sys_id=_car.sys_id)

if @@ERROR<>0 
begin
rollback tran
end

commit tran --提交事物


GO
--更新数据操作存储过程
create proc updateSync_process_ON187 
as
begin tran --开始事物

--将远程服务器更新的数据更新到本服务器中 
update account 
set status=_account.status,user_id=_account.user_id,car_id=_account.car_id,cardNo=_account.cardNo,cardType=_account.cardType,cost=_account.cost,lowestBalanceAfterConsume=_account.lowestBalanceAfterConsume,tollyLowestBalanceAC=_account.tollyLowestBalanceAC,noEntryBalance=_account.noEntryBalance,lowBalanceNotice=_account.lowBalanceNotice,openAccountTime=_account.openAccountTime,statusUpdateTime=_account.statusUpdateTime,remark=_account.remark,parkNo=_account.parkNo,balance=_account.balance 
from syncDB187.Mydb_sync187.dbo.t_park_account _account,t_park_account account 
where account.sys_id=_account.sys_id 

if @@ERROR<>0 
begin
rollback tran
end

update _user_ 
set sys_id=_user.sys_id,userName=_user.userName,credentialsType=_user.credentialsType,credentialsNo=_user.credentialsNo,phone=_user.phone,mobileNumber=_user.mobileNumber,address=_user.address
from syncDB187.Mydb_sync187.dbo.t_park_user _user,t_park_user _user_ 
where _user_.sys_id=_user.sys_id

if @@ERROR<>0 
begin
rollback tran
end

update car 
set  vehPlate=_car.vehPlate,carType=_car.carType,carColour=_car.carColour
from syncDB187.Mydb_sync187.dbo.t_park_car _car,t_park_car car 
where car.sys_id=_car.sys_id 

if @@ERROR<>0 
begin
rollback tran
end

commit tran --提交事物


GO
--删除数据操作存储过程
create proc deleteSync_process_ON187 
as 
begin tran --开始事物

--删除已删除的数据
delete account 
from t_park_account account
where not exists( 
				select * from syncDB187.Mydb_sync187.dbo.t_park_account where sys_id=account.sys_id) 

if @@ERROR<>0 
begin
rollback tran
end

delete _user_ 
from t_park_user _user_
where not exists( 
				select * from syncDB187.Mydb_sync187.dbo.t_park_user where sys_id=_user_.sys_id)

if @@ERROR<>0 
begin
rollback tran
end

delete car 
from t_park_car car
where not exists( 
				select * from syncDB187.Mydb_sync187.dbo.t_park_car where sys_id=car.sys_id)

if @@ERROR<>0 
begin
rollback tran
end

commit tran --提交事物