利用SQL存储过程实现数据分布存储

王 珺，倪 雷，李 扬，黄世芹，简薇薇

(贵州省贵阳市气象局，贵州 贵阳 550002)

1 引言

当前区域自动站数据库的应用现状:自从全省大范围增建区域自动站，并提高区域自动站的数据发送频次以来，截止目前，全省有近2 000个区域自动站，每10 min就有一次气象数据。高覆盖、高频次的气象要素为实时气象的监控分析和防灾减灾提供了非常关键的数据支持。同时，这些数据也已不再是文本数据，而是基于SQL的数据库数据，查询统计使用更方便快捷。也正因此如此，各种基于SQL气象数据的应用系统广泛开发和使用，不光省级部门开发，地州级部门也在开发各种适于自身的应用系统。但是，由于所有的区域自动站数据都是由省气象统一接收、管理和使用，所有地州、区县都通过气象专线连接省局SQL服务器获取数据，而随着过多应用系统的使用，和省局SQL服务器的连接数量剧增，查询统计应用的频次也相应剧增，给SQL服务器的负担也越来越重，另外，如果应用系统的SQL操作代码不正确或不完善，将会给SQL服务器带来更大的压力，甚至SQL服务器因无法处理而崩溃，2012年就数次出现SQL服务器崩溃的情况，导致大量使用该服务器的应用系统无法正常使用。此外，过多的数据连接、过多的数据交换也占用了气象专线的带宽，虽然每次数据交换的量不算很多，但是随着系统的过多使用，无形中，这样的占用还是会造成一定的影响。

2 数据分布式存储的思考

由于全省都集中使用省局的SQL服务器，考虑到负担和网络开销的问题，省局会根据自身的需要调整SQL服务器的存储，一般只保存当年的数据或近段时间的数据，这就给其他部门在使用上带来不便，只能查询近期一段时间的数据或实时数据，而不能进行历史数据的统计应用等。如果放开限制，可以任意查询，则服务器是很难承受和处理如此众多的查询请求的。因此有必要考虑数据的分布存储，将省局的数据分布存储到9个地州级部门，形成省局是一级SQL服务器，地州局是二级SQL服务器的形势，省局只负责一级SQL服务器的维护和各种应用，而每个地州局及其县级部门的应用系统使用该地州局的SQL服务器，以这样的方式布局，则能很大程度上减轻省级服务器的负担、降低的网络带宽的消耗、同时，提升了地县一级部门的应用系统的统计查询和应用效率。

2.1 分布式存储的方法

省级SQL服务器到地州级服务器的存储应该是实时存储的，即当省级服务器有更新数据时，也要在地州级数据库上同步更新同样的数据，并且，省级数据库在进行删除和其他操作时不会对地州级数据库有任何影响，只需保证有更新数据时传输给地州级数据库即可。

实现以上步骤的方法可以考虑在地州级固定某IP的服务器，然后在省级SQL上做对该IP的发布与订阅，但是，该方法需要省级SQL上主动发布，如果地州级服务器出现故障不能及时传输数据，则需要省级管理人员通知地州级管理员，这也在一定程度上增加了省级管理人员的工作负担。

另外，可以由地州级编写合适的更新数据软件来同步更新SQL数据，但是编写程序繁琐，而且如果程序编写得不好，一方面会造成程序的维护困难，另一方面可能会造成省级服务器的负担增加或直接瘫痪，2012年省局服务器就出现类似的瘫痪故障。而采用在地州级服务器上做SQL的存储过程和代理作业，则省级部门只需要管理维护好省级的服务器即可，有任何硬件故障只需地州级管理员自行解决，地州级服务器会自行检测并自动更新数据，且很稳定。

2.2 SQL上存储过程的代码实现

建立SQL存储过程，实现实时更新数据的原理为:

①获取全省的站点的站号;

②使用循环，查询每个站点在地州一级数据库的最新数据的时间;

③使用循环，查询每个站点在省级数据库上大于本地最新数据时间的数据;

④将查询出的更新数据写入到本地数据库中。

以下是相应的存储过程代码(以小时数据库为例):

Create Procedure［dbo］.［tabhourdata_CC］

AS

declare@StaID varchar(10)

declare@BD_Observtime datetime

declare staid_cursor SCROLL CURSOR for

select distinct StationID

from OpenDataSource('SQLOLEDB'，'Data Source=10.203.6.35;User ID=＊＊＊;Password=＊＊＊').［AWSGZ］.DBO.StationPar

begin

open staid_cursor

Fetch First from staid_cursor Into@StaID

While(@@Fetch_Status=0)

begin

set@ BD_Observtime =(selecttop 1 observtime from

tabhourdata where stationID=@Staid order by observtime desc)

insert into tabhourdata select* from

OpenDataSource('SQLOLEDB'，'Data Source=10.203.6.35;User ID=＊＊＊;Password=＊＊＊').［AWSGZ］.DBO.tabhourdata

where observtime＞@BD_Observtimeand stationID=@staID

Fetch next from staid_cursor into@staID

end

end

close staID_cursor

deallocate staid_cursor

go

代码中，要先创建游标，之后通过两个begin….end循环完成数据的查询和写入，但是一定要注意代码where observtime＞@BD_Observtime and stationID=@staID，这一句代码涉及到地数据和省局数据对比查询的条件，一个是时间对比查询，一个是对应站点的查询，这里的代码如果没写好，将会造成无限循环，从而导致存储过程的运行无法成功，甚至还会给两端的数据库系统造成负担。

以上存储过程的代码也相应适用于分钟数据库，只需更改代码中相应的数据表名即可，需要注意的是，地州一级的SQL数据库在库结构和表结构上须和省级保持一致。

3 SQL上的代理作业设置流程

建立存储过程后，需要在SQL上做代理作业，类似Windows系统中的计划任务，使得存储过程能按计划运行，步骤如下。

①设置自动启动SQL Server代理:首先运行SQL企业管理器，在企业管理器的界面中，在“SQL Server组”选择本地服务器(Local(Windows NT))，在其上单击鼠标右键，进入“属性设置”，在属性配置的常规选项卡中，将“自动启动SQL Server代理”勾选上(图1)。

图1 属性配置选项卡

②启动代理作业:展开本地服务器，进入“管理”选项中，选中“SQL Server代理”，并启动该代理(图2a)。

③新建作业:启动“SQL Server代理”后，选择其中的“作业”选项，并“新建作业”(图2b)。

图2 启动代理、新建作业选项卡

④设置新建作业:在“新建作业”中，分别对“常规”、“步骤”、“调度”中分别设置，以更新小时数据库为例，在“常规”选项卡中，为该作业命名为“HourData”。

在“步骤”选项卡中，同样新建一个步骤名“Hour”，需要注意的是，下方的“数据库”选项默认是“master”，需要选择为本地的数据库“AWSGZ”，否则不能正确运行;在“命令”文本框里，将上面的存储过程的代码复制并粘贴到文本框中(图3)。最后单击“确定”完成“步骤”的设置，这一步很关键。

图3 数据库选项设置

其次设置“调度”作业，在“调度”选项卡中，设置命令的运行时间，类似windows系统的任务计划，首先是“新建调度”，命名后，在“调度类型”上一定要选择“反复出现”，并点击“更改”，设置其重复时间，发生频率上设定为“每天”，并且“每日频率”上设定为1 h运行一次，考虑到省局数据库实在整点10 min内完成数据的入库，因此，在起始时间这里设置为整点15 min运行(图4)。

图4 “调度”选项卡设置

修改参数后，最后确定，完成整个作业的设置，自此，该存储过程就会在每小时整点15分由SQL自带的管理器自动运行。

同样的，分钟数据库的同步作业可按照上面的步骤建立即可。

4 使用测试总结

经过在贵阳市气象局的运行测试，该SQL存储过程和代理作业运行非常好，以普通性能的台式机做测试，若每个站点只有一条数据记录更新，全省近2 000个站点的记录从查询到写入本地数据库基本运行5～6 min，这其中大约只有5%的时间是和省级数据库连接查询产生，绝大部分时间都是消耗在写入本地数据库中，考虑到若只有9个连接数连接到省级服务器，这样的时间效费比是很高的，省级的服务器压力更小，运行也将更顺畅。

［1］蒋秀英，张建成.SQL Server 2000数据库与应用［M］.北京:清华大学出版社，2006.