本篇介绍从远程库中拉取数据表到本地库(sql server)
背景
为解决值班手机替换方案,且报警不方便查看问题(短信报警也有成本问题)
业务逻辑
远程库中有一张表MobileMessages,实时存放有zabbix和hostmonitor的报警信息(短信类)。
1.拉取远程表到监控主机的sql server中的DBcenter库中(库中建立需要的字段信息,参照远程表结构)。
说明:Status字段默认0表示为发送的信息,发送接收之后更改为1表示已发送。
同时更新SentTime字段为getdate()表示发送的时间。
重点字段是Content为报警内容信息。
需要做两部分内容(2篇)
- 实现从Sql Server数据库读取报警数据并发送到企业微信01(拉取)
- 实现从Sql Server数据库读取报警数据并发送到企业微信02(发送)
设计思路
- 在本地(监控主机)建立远程主机的链接数据库
- 建立存储过程(使用计划任务定时拉取远程表的数据)
(注意:因为如果存在多个手机接收者,报警信息会重复,之后发生出来的信息就是重复的;
解决方法:拉取时指定只拉取其中一台手机的信息 and Mobiles in (18537******))
监控主机上MobileMessages表结构
USE [DBCenter]
GO
/****** Object: Table [dbo].[MobileMessages] Script Date: 2020/10/29 17:02:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MobileMessages](
[ID] [bigint] NOT NULL,
[Category] [int] NOT NULL,
[Mobiles] [varchar](256) NOT NULL,
[Content] [nvarchar](1024) NOT NULL,
[Status] [int] NOT NULL,
[CreateTime] [datetime] NOT NULL,
[SentTime] [datetime] NOT NULL,
CONSTRAINT [PK_MOBILEMESSAGES] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MobileMessages] ADD DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[MobileMessages] ADD DEFAULT (getdate()) FOR [CreateTime]
GO
ALTER TABLE [dbo].[MobileMessages] ADD DEFAULT (getdate()) FOR [SentTime]
GO
01
创建链接服务器
declare @serverip nvarchar(50) = N'192.168.66.129'
declare @remote_user nvarchar(50) = N'sa2'
declare @remote_password nvarchar(50) = N'####'
exec master.dbo.sp_addlinkedserver @server=@serverip,@srvproduct=N'SQL Server'
域登录方式(线上)
exec sp_addlinkedsrvlogin @rmtsrvname='''+@serverip+''',@useself=N'True',
@locallogin=NULL
(用户登录方式)(线下)
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@serverip,@useself=N'False',
@locallogin=NULL, @rmtuser=@remote_user, @rmtpassword=@remote_password
测试查询示例
select count(*) from [192.168.66.129].MobileMessages.dbo.MobileMessages with(nolock)
02
存储过程
使用时需要修改远程链接服务器的信息和拉取的手机号的信息
USE [DBCenter]
GO
/****** Object: StoredProcedure [dbo].[DBA_getinfo_SMS_From_MobileMessages]
Script Date: 2020/10/23 20:43:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*获取远程数据库表的数据插入本地数据库*/
create proc [dbo].[DBA_getinfo_SMS_From_MobileMessages]
as
begin
--添加判断本地表中id小于远程主机表中id的时候,执行以下语句
declare @rmtMaxID bigint --远程表max(ID)
declare @localMaxID bigint --本地表max(ID)
declare @SQL varchar(max)
--创建一个临时表只存储远程表的max(ID)
create table #ID(ID bigint NOT NULL)
if (select count(*) from #ID ) = 0
begin
insert into #ID(ID) select max(ID)
from [192.168.66.129].MobileMessages.dbo.MobileMessages
with(nolock) where Category in (7003,7017)
end
set @rmtMaxID = (select max(ID) from #ID)
set @localMaxID = (select ISNULL(max(ID),0)
from [Dbcenter].[dbo].[MobileMessages] with(nolock))
if (@localMaxID < @rmtMaxID) --对比两张表的max(ID)
begin
set @SQL = 'insert into [Dbcenter].[dbo].[MobileMessages] (
[ID],[Category],[Mobiles],[Content],[CreateTime])
select ID,Category,Mobiles,Content,CreateTime
from [192.168.66.129].MobileMessages.dbo.MobileMessages
with(nolock) where Category in (7003,7017)
and Mobiles = (1853******) and id > '+str(@localMaxID)+'
EXEC(@SQL)
end
end
drop table #ID
GO
注释 7003,7017分别zabbix和hostmonitor报警类型。
测试
在66.129(远程库) 66.128(监控主机)
--以后不再使用时删除链接服务器
--exec master.dbo.sp_dropserver '192.168.66.129' , '192.168.66.129'
以上到达的目的为远程库表如果有新的报警信息,在监控主机上定时执行完此存储过程之后。监控主机上DBcenter库MobileMessages表更新新的数据。(本次测试为每周大约200+数据的新增,不适用大数据的情况(没有测))
(此篇应该不存在以下问题)
本文说明,主要技术内容来自互联网技术大佬的分享,还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问,请留言,将确认之后,执行侵权必删