项目01(实现从Sql Server数据库读取报警数据并发送到企业微信01)

本篇介绍从远程库中拉取数据表到本地库(sql server)

背景

为解决值班手机替换方案，且报警不方便查看问题(短信报警也有成本问题)

业务逻辑

远程库中有一张表MobileMessages，实时存放有zabbix和hostmonitor的报警信息(短信类)。
1.拉取远程表到监控主机的sql server中的DBcenter库中(库中建立需要的字段信息，参照远程表结构)。
说明:Status字段默认0表示为发送的信息，发送接收之后更改为1表示已发送。
同时更新SentTime字段为getdate()表示发送的时间。
重点字段是Content为报警内容信息。

需要做两部分内容(2篇)

1. 实现从Sql Server数据库读取报警数据并发送到企业微信01(拉取)
2. 实现从Sql Server数据库读取报警数据并发送到企业微信02(发送)

设计思路

1. 在本地(监控主机)建立远程主机的链接数据库
2. 建立存储过程（使用计划任务定时拉取远程表的数据）

（注意：因为如果存在多个手机接收者，报警信息会重复，之后发生出来的信息就是重复的；
解决方法：拉取时指定只拉取其中一台手机的信息 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+数据的新增，不适用大数据的情况(没有测))

（此篇应该不存在以下问题）
本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​