利用SQL存儲(chǔ)過(guò)程實(shí)現(xiàn)數(shù)據(jù)分布存儲(chǔ)

王 珺，倪 雷，李 揚(yáng)，黃世芹，簡(jiǎn)薇薇

(貴州省貴陽(yáng)市氣象局，貴州 貴陽(yáng) 550002)

1 引言

當(dāng)前區(qū)域自動(dòng)站數(shù)據(jù)庫(kù)的應(yīng)用現(xiàn)狀:自從全省大范圍增建區(qū)域自動(dòng)站，并提高區(qū)域自動(dòng)站的數(shù)據(jù)發(fā)送頻次以來(lái)，截止目前，全省有近2 000個(gè)區(qū)域自動(dòng)站，每10 min就有一次氣象數(shù)據(jù)。高覆蓋、高頻次的氣象要素為實(shí)時(shí)氣象的監(jiān)控分析和防災(zāi)減災(zāi)提供了非常關(guān)鍵的數(shù)據(jù)支持。同時(shí)，這些數(shù)據(jù)也已不再是文本數(shù)據(jù)，而是基于SQL的數(shù)據(jù)庫(kù)數(shù)據(jù)，查詢統(tǒng)計(jì)使用更方便快捷。也正因此如此，各種基于SQL氣象數(shù)據(jù)的應(yīng)用系統(tǒng)廣泛開(kāi)發(fā)和使用，不光省級(jí)部門(mén)開(kāi)發(fā)，地州級(jí)部門(mén)也在開(kāi)發(fā)各種適于自身的應(yīng)用系統(tǒng)。但是，由于所有的區(qū)域自動(dòng)站數(shù)據(jù)都是由省氣象統(tǒng)一接收、管理和使用，所有地州、區(qū)縣都通過(guò)氣象專(zhuān)線連接省局SQL服務(wù)器獲取數(shù)據(jù)，而隨著過(guò)多應(yīng)用系統(tǒng)的使用，和省局SQL服務(wù)器的連接數(shù)量劇增，查詢統(tǒng)計(jì)應(yīng)用的頻次也相應(yīng)劇增，給SQL服務(wù)器的負(fù)擔(dān)也越來(lái)越重，另外，如果應(yīng)用系統(tǒng)的SQL操作代碼不正確或不完善，將會(huì)給SQL服務(wù)器帶來(lái)更大的壓力，甚至SQL服務(wù)器因無(wú)法處理而崩潰，2012年就數(shù)次出現(xiàn)SQL服務(wù)器崩潰的情況，導(dǎo)致大量使用該服務(wù)器的應(yīng)用系統(tǒng)無(wú)法正常使用。此外，過(guò)多的數(shù)據(jù)連接、過(guò)多的數(shù)據(jù)交換也占用了氣象專(zhuān)線的帶寬，雖然每次數(shù)據(jù)交換的量不算很多，但是隨著系統(tǒng)的過(guò)多使用，無(wú)形中，這樣的占用還是會(huì)造成一定的影響。

2 數(shù)據(jù)分布式存儲(chǔ)的思考

由于全省都集中使用省局的SQL服務(wù)器，考慮到負(fù)擔(dān)和網(wǎng)絡(luò)開(kāi)銷(xiāo)的問(wèn)題，省局會(huì)根據(jù)自身的需要調(diào)整SQL服務(wù)器的存儲(chǔ)，一般只保存當(dāng)年的數(shù)據(jù)或近段時(shí)間的數(shù)據(jù)，這就給其他部門(mén)在使用上帶來(lái)不便，只能查詢近期一段時(shí)間的數(shù)據(jù)或?qū)崟r(shí)數(shù)據(jù)，而不能進(jìn)行歷史數(shù)據(jù)的統(tǒng)計(jì)應(yīng)用等。如果放開(kāi)限制，可以任意查詢，則服務(wù)器是很難承受和處理如此眾多的查詢請(qǐng)求的。因此有必要考慮數(shù)據(jù)的分布存儲(chǔ)，將省局的數(shù)據(jù)分布存儲(chǔ)到9個(gè)地州級(jí)部門(mén)，形成省局是一級(jí)SQL服務(wù)器，地州局是二級(jí)SQL服務(wù)器的形勢(shì)，省局只負(fù)責(zé)一級(jí)SQL服務(wù)器的維護(hù)和各種應(yīng)用，而每個(gè)地州局及其縣級(jí)部門(mén)的應(yīng)用系統(tǒng)使用該地州局的SQL服務(wù)器，以這樣的方式布局，則能很大程度上減輕省級(jí)服務(wù)器的負(fù)擔(dān)、降低的網(wǎng)絡(luò)帶寬的消耗、同時(shí)，提升了地縣一級(jí)部門(mén)的應(yīng)用系統(tǒng)的統(tǒng)計(jì)查詢和應(yīng)用效率。

2.1 分布式存儲(chǔ)的方法

省級(jí)SQL服務(wù)器到地州級(jí)服務(wù)器的存儲(chǔ)應(yīng)該是實(shí)時(shí)存儲(chǔ)的，即當(dāng)省級(jí)服務(wù)器有更新數(shù)據(jù)時(shí)，也要在地州級(jí)數(shù)據(jù)庫(kù)上同步更新同樣的數(shù)據(jù)，并且，省級(jí)數(shù)據(jù)庫(kù)在進(jìn)行刪除和其他操作時(shí)不會(huì)對(duì)地州級(jí)數(shù)據(jù)庫(kù)有任何影響，只需保證有更新數(shù)據(jù)時(shí)傳輸給地州級(jí)數(shù)據(jù)庫(kù)即可。

實(shí)現(xiàn)以上步驟的方法可以考慮在地州級(jí)固定某IP的服務(wù)器，然后在省級(jí)SQL上做對(duì)該IP的發(fā)布與訂閱，但是，該方法需要省級(jí)SQL上主動(dòng)發(fā)布，如果地州級(jí)服務(wù)器出現(xiàn)故障不能及時(shí)傳輸數(shù)據(jù)，則需要省級(jí)管理人員通知地州級(jí)管理員，這也在一定程度上增加了省級(jí)管理人員的工作負(fù)擔(dān)。

另外，可以由地州級(jí)編寫(xiě)合適的更新數(shù)據(jù)軟件來(lái)同步更新SQL數(shù)據(jù)，但是編寫(xiě)程序繁瑣，而且如果程序編寫(xiě)得不好，一方面會(huì)造成程序的維護(hù)困難，另一方面可能會(huì)造成省級(jí)服務(wù)器的負(fù)擔(dān)增加或直接癱瘓，2012年省局服務(wù)器就出現(xiàn)類(lèi)似的癱瘓故障。而采用在地州級(jí)服務(wù)器上做SQL的存儲(chǔ)過(guò)程和代理作業(yè)，則省級(jí)部門(mén)只需要管理維護(hù)好省級(jí)的服務(wù)器即可，有任何硬件故障只需地州級(jí)管理員自行解決，地州級(jí)服務(wù)器會(huì)自行檢測(cè)并自動(dòng)更新數(shù)據(jù)，且很穩(wěn)定。

2.2 SQL上存儲(chǔ)過(guò)程的代碼實(shí)現(xiàn)

建立SQL存儲(chǔ)過(guò)程，實(shí)現(xiàn)實(shí)時(shí)更新數(shù)據(jù)的原理為:

①獲取全省的站點(diǎn)的站號(hào);

②使用循環(huán)，查詢每個(gè)站點(diǎn)在地州一級(jí)數(shù)據(jù)庫(kù)的最新數(shù)據(jù)的時(shí)間;

③使用循環(huán)，查詢每個(gè)站點(diǎn)在省級(jí)數(shù)據(jù)庫(kù)上大于本地最新數(shù)據(jù)時(shí)間的數(shù)據(jù);

④將查詢出的更新數(shù)據(jù)寫(xiě)入到本地?cái)?shù)據(jù)庫(kù)中。

以下是相應(yīng)的存儲(chǔ)過(guò)程代碼(以小時(shí)數(shù)據(jù)庫(kù)為例):

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

代碼中，要先創(chuàng)建游標(biāo)，之后通過(guò)兩個(gè)begin….end循環(huán)完成數(shù)據(jù)的查詢和寫(xiě)入，但是一定要注意代碼where observtime＞@BD_Observtime and stationID=@staID，這一句代碼涉及到地?cái)?shù)據(jù)和省局?jǐn)?shù)據(jù)對(duì)比查詢的條件，一個(gè)是時(shí)間對(duì)比查詢，一個(gè)是對(duì)應(yīng)站點(diǎn)的查詢，這里的代碼如果沒(méi)寫(xiě)好，將會(huì)造成無(wú)限循環(huán)，從而導(dǎo)致存儲(chǔ)過(guò)程的運(yùn)行無(wú)法成功，甚至還會(huì)給兩端的數(shù)據(jù)庫(kù)系統(tǒng)造成負(fù)擔(dān)。

以上存儲(chǔ)過(guò)程的代碼也相應(yīng)適用于分鐘數(shù)據(jù)庫(kù)，只需更改代碼中相應(yīng)的數(shù)據(jù)表名即可，需要注意的是，地州一級(jí)的SQL數(shù)據(jù)庫(kù)在庫(kù)結(jié)構(gòu)和表結(jié)構(gòu)上須和省級(jí)保持一致。

3 SQL上的代理作業(yè)設(shè)置流程

建立存儲(chǔ)過(guò)程后，需要在SQL上做代理作業(yè)，類(lèi)似Windows系統(tǒng)中的計(jì)劃任務(wù)，使得存儲(chǔ)過(guò)程能按計(jì)劃運(yùn)行，步驟如下。

①設(shè)置自動(dòng)啟動(dòng)SQL Server代理:首先運(yùn)行SQL企業(yè)管理器，在企業(yè)管理器的界面中，在“SQL Server組”選擇本地服務(wù)器(Local(Windows NT))，在其上單擊鼠標(biāo)右鍵，進(jìn)入“屬性設(shè)置”，在屬性配置的常規(guī)選項(xiàng)卡中，將“自動(dòng)啟動(dòng)SQL Server代理”勾選上(圖1)。

圖1 屬性配置選項(xiàng)卡

②啟動(dòng)代理作業(yè):展開(kāi)本地服務(wù)器，進(jìn)入“管理”選項(xiàng)中，選中“SQL Server代理”，并啟動(dòng)該代理(圖2a)。

③新建作業(yè):啟動(dòng)“SQL Server代理”后，選擇其中的“作業(yè)”選項(xiàng)，并“新建作業(yè)”(圖2b)。

圖2 啟動(dòng)代理、新建作業(yè)選項(xiàng)卡

④設(shè)置新建作業(yè):在“新建作業(yè)”中，分別對(duì)“常規(guī)”、“步驟”、“調(diào)度”中分別設(shè)置，以更新小時(shí)數(shù)據(jù)庫(kù)為例，在“常規(guī)”選項(xiàng)卡中，為該作業(yè)命名為“HourData”。

在“步驟”選項(xiàng)卡中，同樣新建一個(gè)步驟名“Hour”，需要注意的是，下方的“數(shù)據(jù)庫(kù)”選項(xiàng)默認(rèn)是“master”，需要選擇為本地的數(shù)據(jù)庫(kù)“AWSGZ”，否則不能正確運(yùn)行;在“命令”文本框里，將上面的存儲(chǔ)過(guò)程的代碼復(fù)制并粘貼到文本框中(圖3)。最后單擊“確定”完成“步驟”的設(shè)置，這一步很關(guān)鍵。

圖3 數(shù)據(jù)庫(kù)選項(xiàng)設(shè)置

其次設(shè)置“調(diào)度”作業(yè)，在“調(diào)度”選項(xiàng)卡中，設(shè)置命令的運(yùn)行時(shí)間，類(lèi)似windows系統(tǒng)的任務(wù)計(jì)劃，首先是“新建調(diào)度”，命名后，在“調(diào)度類(lèi)型”上一定要選擇“反復(fù)出現(xiàn)”，并點(diǎn)擊“更改”，設(shè)置其重復(fù)時(shí)間，發(fā)生頻率上設(shè)定為“每天”，并且“每日頻率”上設(shè)定為1 h運(yùn)行一次，考慮到省局?jǐn)?shù)據(jù)庫(kù)實(shí)在整點(diǎn)10 min內(nèi)完成數(shù)據(jù)的入庫(kù)，因此，在起始時(shí)間這里設(shè)置為整點(diǎn)15 min運(yùn)行(圖4)。

圖4 “調(diào)度”選項(xiàng)卡設(shè)置

修改參數(shù)后，最后確定，完成整個(gè)作業(yè)的設(shè)置，自此，該存儲(chǔ)過(guò)程就會(huì)在每小時(shí)整點(diǎn)15分由SQL自帶的管理器自動(dòng)運(yùn)行。

同樣的，分鐘數(shù)據(jù)庫(kù)的同步作業(yè)可按照上面的步驟建立即可。

4 使用測(cè)試總結(jié)

經(jīng)過(guò)在貴陽(yáng)市氣象局的運(yùn)行測(cè)試，該SQL存儲(chǔ)過(guò)程和代理作業(yè)運(yùn)行非常好，以普通性能的臺(tái)式機(jī)做測(cè)試，若每個(gè)站點(diǎn)只有一條數(shù)據(jù)記錄更新，全省近2 000個(gè)站點(diǎn)的記錄從查詢到寫(xiě)入本地?cái)?shù)據(jù)庫(kù)基本運(yùn)行5～6 min，這其中大約只有5%的時(shí)間是和省級(jí)數(shù)據(jù)庫(kù)連接查詢產(chǎn)生，絕大部分時(shí)間都是消耗在寫(xiě)入本地?cái)?shù)據(jù)庫(kù)中，考慮到若只有9個(gè)連接數(shù)連接到省級(jí)服務(wù)器，這樣的時(shí)間效費(fèi)比是很高的，省級(jí)的服務(wù)器壓力更小，運(yùn)行也將更順暢。

［1］蔣秀英，張建成.SQL Server 2000數(shù)據(jù)庫(kù)與應(yīng)用［M］.北京:清華大學(xué)出版社，2006.