SQL server数据库实现远程跨服务器定时同步传输数据

项目背景

公司新建项目，需要访问生产数据，但是规定不能直接访问生产数据库服务器，所以得考虑通过中间库的形式实现。经过评估项目需求 ，以及当前拥有的环境。
需求：
1.用户不需要实时获取生产数据
2.用户只需要指定的某部分数据的部分字段信息
当前环境：
1.有一个备份服务器已经打通到生产服务器的端口
2.有一个中间库服务器供外部系统使用
3.生产服务器数据库类型是SQL server 2012
4.备份服务器同时也打通了到中间库的服务器端口

实现方式

1.通过在备份服务器上创建远程访问到生产服务器，远程访问到中间库服务器，
2.通过远程访问查询生产服务器数据并写入到中间库服务器
3.创建JOB每小时执行一次

相关脚本

备份服务器脚本

--创建访问生产库数据库的别名
use testdb
go
exec sp_addlinkedserver 't1mj2', ' ', 'SQLOLEDB', '172.xx.xx.xx'
exec sp_addlinkedsrvlogin 't1mj2', 'false', null, 'testuser', 'password'

exec sp_serveroption @server='t1mj2', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='t1mj2', @optname='rpc out', @optvalue='true'
exec sp_serveroption @server='t1mj2', @optname='remote proc transaction promotion', @optvalue='true'

--创建访问中间库的数据库别名
use testdb
go
exec sp_addlinkedserver 'zkevent39', ' ', 'SQLOLEDB', '10.xx.xx.xx'
exec sp_addlinkedsrvlogin 'zkevent39', 'false', null, 'zkevent', 'password'

exec sp_serveroption @server='zkevent39', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='zkevent39', @optname='rpc out', @optvalue='true'
exec sp_serveroption @server='zkevent39', @optname='remote proc transaction promotion', @optvalue='true'

–按照小时同步数据 --查询XX的数据插入到镜像库 -无flag

use Testdb
go
create procedure sync_event_data
as 
begin
	SET NOCOUNT ON;
-- 获取当前时间
DECLARE @CurrentTime datetime = GETDATE();
-- 截取到整点
DECLARE @RoundedTime datetime = DATEADD(minute, DATEDIFF(minute, 0, @CurrentTime) / 60 * 60, 0);
-- 减去8小时
DECLARE @EightHoursAgo datetime = DATEADD(hour, -8, @RoundedTime);
-- 1.格式化输出 
--SELECT FORMAT(@EightHoursAgo, 'yyyy-MM-dd HH:mm:ss.fff') AS FormattedDateTime; --2024-08-28 15:00:00.000
-- 获取当前时间
--DECLARE @CurrentTime datetime = GETDATE();
-- 截取到整点前一个小时
DECLARE @RoundedTime1 datetime = DATEADD(minute, DATEDIFF(minute, 0, @CurrentTime) / 60 * 60 - 60, 0);
-- 减去8小时
DECLARE @PreHoursAgo datetime = DATEADD(hour, -8, @RoundedTime1);
-- 2.格式化输出
--SELECT FORMAT(@PreHoursAgo, 'yyyy-MM-dd HH:mm:ss.fff') AS FormattedDateTime;
	insert into zkevent39.zkevent.dbo.event_data(passcode,passtime,passregion,inout) 
SELECT c.CardNumber,dateadd(hh,8, a.OccurrenceTime) OccurrenceTime,b.FTItemName,
        case when a.Message like '%entry%' or a.Message like '%进入%' then 'entry' else 'exit' end 'in_out'
  FROM t1mj2.db.dbo.Event a, t1mj2.CCFTEvent.dbo.RelatedItems b, t1mj2.CCFTEvent.dbo.CardEvent c
  where a.ID=b.EventID
  and b.RelationCode=2
  --and b.FTItemID in(select id from db.dbo.tab where name like '%25323%' and TypeID=11 and DeletionTime is null)
  and b.FTItemID in(1573,59986,60010,85115,101318,101544,154977,154979,157091,219876246,219877495,219877508,219877669,219909572)
  and c.EventID=a.ID
  --and a.OccurrenceTime between '2024-08-27 09:00:00.000' and '2024-08-27 10:00:00.000'
and  a.OccurrenceTime >= FORMAT(@PreHoursAgo, 'yyyy-MM-dd HH:mm:ss.fff')
and a.OccurrenceTime < FORMAT(@EightHoursAgo, 'yyyy-MM-dd HH:mm:ss.fff');
END;

--exec sync_event_data;
--drop procedure sync_t1_event 
--select * from  zkevent39.zkevent.dbo.event_data;

----------

查询XX的数据插入到镜像库 --有flag，这部分属于备用脚本，由于系统较多，主要看用户那边是否需要做区分，如果需要则加一个flag字段即可

use Testdb
go
create procedure sync_event_data_flag
as 
begin
	SET NOCOUNT ON;
-- 获取当前时间
DECLARE @CurrentTime datetime = GETDATE();
-- 截取到整点
DECLARE @RoundedTime datetime = DATEADD(minute, DATEDIFF(minute, 0, @CurrentTime) / 60 * 60, 0);
-- 减去8小时
DECLARE @EightHoursAgo datetime = DATEADD(hour, -8, @RoundedTime);
-- 1.格式化输出 
--SELECT FORMAT(@EightHoursAgo, 'yyyy-MM-dd HH:mm:ss.fff') AS FormattedDateTime; --2024-08-28 15:00:00.000
-- 获取当前时间
--DECLARE @CurrentTime datetime = GETDATE();
-- 截取到整点前一个小时
DECLARE @RoundedTime1 datetime = DATEADD(minute, DATEDIFF(minute, 0, @CurrentTime) / 60 * 60 - 60, 0);
-- 减去8小时
DECLARE @PreHoursAgo datetime = DATEADD(hour, -8, @RoundedTime1);
-- 2.格式化输出
--SELECT FORMAT(@PreHoursAgo, 'yyyy-MM-dd HH:mm:ss.fff') AS FormattedDateTime;
	insert into zkevent39.zkevent.dbo.event_data_flag(passcode,passtime,passregion,inout,flag) 
SELECT c.CardNumber,dateadd(hh,8, a.OccurrenceTime) OccurrenceTime,b.FTItemName,
        case when a.Message like '%entry%' or a.Message like '%进入%' then 'entry' else 'exit' end 'in_out','T1'
  FROM t1mj2.db.dbo.Event a, t1mj2.CCFTEvent.dbo.RelatedItems b, t1mj2.CCFTEvent.dbo.CardEvent c
  where a.ID=b.EventID
  and b.RelationCode=2
  --and b.FTItemID in(select id from db.dbo.tab where name like '%25323%' and TypeID=11 and DeletionTime is null)
  and b.FTItemID in(1573,59986,60010,85115,101318,101544,154977,154979,157091,219876246,219877495,219877508,219877669,219909572)
  and c.EventID=a.ID
  --and a.OccurrenceTime between '2024-08-27 09:00:00.000' and '2024-08-27 10:00:00.000'
and  a.OccurrenceTime >= FORMAT(@PreHoursAgo, 'yyyy-MM-dd HH:mm:ss.fff')
and a.OccurrenceTime < FORMAT(@EightHoursAgo, 'yyyy-MM-dd HH:mm:ss.fff');
END;

--exec sync_event_data_flag;
--drop procedure sync_event_data_flag 

中间服务器脚本

中间服务器只需要创建接收数据的表即可。

最后在备份服务器上创建定时任务的JOB即可

其他设置保持默认即可。