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即可

其他设置保持默认即可。