基于存儲(chǔ)過程的主從服務(wù)器數(shù)據(jù)庫(kù)同步的實(shí)現(xiàn)

摘要：數(shù)據(jù)庫(kù)系統(tǒng)是信息系統(tǒng)的基礎(chǔ)，為了保證信息系統(tǒng)的高可用性，需要對(duì)主從服務(wù)器數(shù)據(jù)庫(kù)進(jìn)行同步，保證數(shù)據(jù)的一致性，本文根據(jù)實(shí)際需求摸索一種基于存儲(chǔ)過程的主從服務(wù)器數(shù)據(jù)庫(kù)同步方法，首先介紹存儲(chǔ)過程，然后用實(shí)例說明如何利用存儲(chǔ)過程實(shí)現(xiàn)主從服務(wù)器數(shù)據(jù)庫(kù)同步，該方法對(duì)于特定系統(tǒng)具有一定的應(yīng)用指導(dǎo)意義。



關(guān)鍵詞：存儲(chǔ)過程；數(shù)據(jù)庫(kù)；數(shù)據(jù)同步

中圖分類號(hào)：TP393 文獻(xiàn)標(biāo)識(shí)碼：A



Relization of the Master and Backup Server Database Synchronization Based on Store Procedure



JI Gang

（Anhui Meteorological Information Center Communication Department，Hefei230031，China）

Abstract：The database system is the foundations of the information system， In order to ensure the information system with high usability and the consistency of the data， the master and backup server database synchronization was need. According to the actual need， this paper explorated a master and backup server database synchronous method based on store procedure. Firstly， it introduced store procedure.And then， an example is presented for demonstrating how to use store procedure to realize the master and backup server database synchronization.For some particular system， it had practical significance.



Key words：store procedure；database； data synchronization



1引言

隨著計(jì)算機(jī)技術(shù)、網(wǎng)絡(luò)技術(shù)的飛速發(fā)展，各行各業(yè)對(duì)計(jì)算機(jī)應(yīng)用的廣度和深度也不斷提高，同時(shí)對(duì)計(jì)算機(jī)的依賴性也越來越強(qiáng)，如何提高計(jì)算機(jī)應(yīng)用系統(tǒng)的及時(shí)性和準(zhǔn)確性，這就需要在處理數(shù)據(jù)時(shí)要達(dá)到數(shù)據(jù)庫(kù)同步處理。目前全省氣象開發(fā)了大量的業(yè)務(wù)信息系統(tǒng)，在氣象信息共享、氣象信息綜合處理、氣象預(yù)報(bào)預(yù)警、各類統(tǒng)計(jì)乃至日常辦公自動(dòng)化應(yīng)用，都有相應(yīng)的信息系統(tǒng)支撐，任何系統(tǒng)如果不能夠做到及時(shí)準(zhǔn)確，那必將帶來巨大的損失。本文根據(jù)實(shí)際需求摸索了一種基于存儲(chǔ)過程的主從服務(wù)器數(shù)據(jù)庫(kù)同步方法，實(shí)現(xiàn)主從數(shù)據(jù)庫(kù)的同步。

2存儲(chǔ)過程介紹

存儲(chǔ)過程[1]是存儲(chǔ)在服務(wù)器上的一組預(yù)編譯的Transact—SQL語句，是一種封裝重復(fù)任務(wù)操作的方法，支持用戶提供的變量，具有強(qiáng)大的編程功能。它類似于DOS系統(tǒng)中的批處理文件，在批處理文件中，可以包含一組經(jīng)常執(zhí)行的命令，這組命令通過批處理文件的執(zhí)行而被執(zhí)行。同樣的道理，可以把要完成某項(xiàng)任務(wù)的許多Transact—SQL語句寫在一起，組織成存儲(chǔ)過程的形式，通過執(zhí)行該存儲(chǔ)過程就可以完成這項(xiàng)任務(wù)。存儲(chǔ)過程與批處理文件又有差別，即存儲(chǔ)過程己經(jīng)進(jìn)行了預(yù)編譯。

2.1存儲(chǔ)過程的創(chuàng)建

創(chuàng)建存儲(chǔ)過程可以使用CREATE PROCEDURE語句，其語法形式如下：

CREATE PROC[EDURE] procedure_name[；number]

[{@parameter data_type} [output] [VARYING] [=default] [OUTPUT] ][ ，…n]

[WITH 

{RECOMPILE | ENCRYPTION | RECOMPILE， ENCRYPTION} ] 

[FOR REPLICATION]

AS sql_statement [… n]

在上面的CREATE PROCEDURE語句中，方括號(hào)“[]”中的內(nèi)容是可選的，花括號(hào)“{}”中的內(nèi)容是必須出現(xiàn)的，不能省略，[ ，…n]表示前面的參數(shù)樣式，可以重復(fù)出現(xiàn)。豎線“|”表示兩邊的選項(xiàng)可以任選一個(gè)。

存儲(chǔ)過程參數(shù)的命名必須以@開頭。參數(shù)按其用途可分為入口參數(shù)和出口參數(shù)兩大類。入口參數(shù)可以用來把值傳遞給存儲(chǔ)過程使用。可以使用多種方式向存儲(chǔ)過程傳遞參數(shù)，可以按值傳遞，可以按名傳遞，還可以使用變量傳遞。出口參數(shù)采用變量的形式向過程或程序返回結(jié)果值。調(diào)用帶有出口參數(shù)的存儲(chǔ)過程時(shí)，必須在調(diào)用過程中聲明一個(gè)類型相同的變量來存儲(chǔ)返回值。SQL Server 提供了幾個(gè)可在編制存儲(chǔ)過程中使用的全局變量，全局變量使用前綴@@來與標(biāo)準(zhǔn)變量進(jìn)行區(qū)別，@@error是最常見的全局變量之一。

2.2存儲(chǔ)過程的優(yōu)點(diǎn)

存儲(chǔ)過程的執(zhí)行在數(shù)據(jù)庫(kù)服務(wù)器中，應(yīng)用程序只需傳遞相關(guān)的存儲(chǔ)過程的命令至數(shù)據(jù)庫(kù)服務(wù)器即可執(zhí)行[2—6]，其優(yōu)點(diǎn)如下：

1）執(zhí)行速度快，存儲(chǔ)過程只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過程都不需再重新編澤，而一般SQL語句每執(zhí)行一次就編譯一次，所以使用存儲(chǔ)過程可提高數(shù)據(jù)庫(kù)執(zhí)行速度。

2） 存儲(chǔ)過程可以很方便的接收參數(shù)，進(jìn)一步增強(qiáng)了數(shù)據(jù)庫(kù)處理的靈活性。

3）減少了網(wǎng)絡(luò)通信量，當(dāng)客戶端發(fā)出執(zhí)行存儲(chǔ)過程的請(qǐng)求時(shí)，只有執(zhí)行存儲(chǔ)過程的命令在內(nèi)部網(wǎng)絡(luò)上傳送，當(dāng)它們到達(dá)數(shù)據(jù)庫(kù)服務(wù)器時(shí)，運(yùn)行存儲(chǔ)過程，客戶端在網(wǎng)上只接收返回結(jié)果或狀態(tài)信息，使得客戶機(jī)與服務(wù)器的通信量降至最小，大大減少了網(wǎng)絡(luò)通信量。

4）安全性高，把對(duì)數(shù)據(jù)進(jìn)行的操作編寫成存儲(chǔ)過程存放在SQL Server數(shù)據(jù)庫(kù)中，通過數(shù)據(jù)庫(kù)加密技術(shù)，使這些操作代碼更安全，從而提高了數(shù)據(jù)的安全性。

5） 存儲(chǔ)過程可以重復(fù)使用，可減少開發(fā)人員的工作量。

3主從服務(wù)器數(shù)據(jù)庫(kù)同步的實(shí)現(xiàn)

3.1主從服務(wù)器數(shù)據(jù)庫(kù)同步原理

主從服務(wù)器數(shù)據(jù)庫(kù)同步原理主要是先由從服務(wù)器捕獲主服務(wù)器數(shù)據(jù)的變化，包括數(shù)據(jù)添加、數(shù)據(jù)更新和數(shù)據(jù)刪除，然后從服務(wù)器根據(jù)捕獲的變化來更新自己的數(shù)據(jù)庫(kù)，從而達(dá)到主從服務(wù)器數(shù)據(jù)庫(kù)的復(fù)制或是同步功能，具體數(shù)據(jù)同步過程如圖1所示：

計(jì)算技術(shù)與自動(dòng)化2012年9月

第31卷第3期季剛：基于存儲(chǔ)過程的主從服務(wù)器數(shù)據(jù)庫(kù)同步的實(shí)現(xiàn)

圖1同步過程

3.2主從服務(wù)器數(shù)據(jù)庫(kù)同步實(shí)現(xiàn)步驟

下面以gps數(shù)據(jù)庫(kù)同步為例介紹主從服務(wù)器數(shù)據(jù)庫(kù)同步的實(shí)現(xiàn)步驟，gps數(shù)據(jù)庫(kù)主從服務(wù)器上分別安裝了sql server 2005企業(yè)版，gps數(shù)據(jù)庫(kù)表結(jié)構(gòu)如下：

表名

sd2012

列名

數(shù)據(jù)類型

空/非空

約束條件

站號(hào)char（10）not 1primary key

觀測(cè)時(shí)間smalldatetimenot 1primary key

天頂總延遲float

1

氣壓float

1

溫度float

1

濕度float

1水汽總量float1

（1） 創(chuàng)建數(shù)據(jù)庫(kù)鏈接

if exists（select 1from master..sysservers where srvname= ''srv_lnk ''） 

execsp_dropserver ''srv_lnk ''， ''droplogins '' 

go 

exec sp_addlinkedserver ''srv_lnk ''， '' ''， '' SQLOLEDB ''， ''192.168.1.16'' 

exec sp_addlinkedsrvlogin ''srv_lnk ''， ''1 ''， 1， ''sa '' ，''123''

go

（2）創(chuàng)建同步處理的存儲(chǔ)過程

if exists （select * from dbo.sysobjects whereid = object_id（N ''[dbo].[p_synchro] ''） and OBJECTPROPERTY（id， N ''IsProcedure ''）=1） 

drop procedure [dbo].[p_synchro] 

GO 

create proc p_synchro 

as 

啟動(dòng)遠(yuǎn)程服務(wù)器的MSDTC服務(wù) 

exec master..xp_cmdshell ''isql /S \"192.168.1.16\" /U \"sa\" /P \"123\" /q\"exec master..xp_cmdshell '' ''net start msdtc '' ''， no_output \" ''， no_output 

啟動(dòng)本機(jī)的MSDTC服務(wù) 

exec master..xp_cmdshell ''net start msdtc ''，no_output 

同步刪除的數(shù)據(jù) 

delete from [sd].[dbo].[sd2012] 

where not exists （select * from srv_lnk.[sd].[dbo].[sd2012] A where A. 站號(hào)=站號(hào)and A.觀測(cè)時(shí)間=觀測(cè)時(shí)間）

同步新增的數(shù)據(jù) 

insert [sd].[dbo].[sd2012]

select * from srv_lnk.[sd].[dbo].[sd2012] A

where not exists （select * from [sd].[dbo].[sd2012] where站號(hào)=A. 站號(hào)and觀測(cè)時(shí)間=A. 觀測(cè)時(shí)間） 

同步修改的數(shù)據(jù) 

update B set 天頂總延遲=A.天頂總延遲， 氣壓=A.氣壓， 溫度=A. 溫度， 濕度=A. 濕度，水汽總量=A. 水汽總量 from [sd].[dbo].[sd2012] B ， srv_lnk.[sd].[dbo].[sd2012] A

where （B.站號(hào)=A. 站號(hào)and B.觀測(cè)時(shí)間=A. 觀測(cè)時(shí)間） and （B. 天頂總延遲<>A.天頂總延遲or B.氣壓<>A.氣壓 or B. 溫度<>A. 溫度or B. 濕度<>A. 濕度orB. 水汽總量<>A. 水汽總量）

GO

（3） 創(chuàng)建作業(yè)和調(diào)度，定時(shí)執(zhí)行數(shù)據(jù)同步的存儲(chǔ)過程

if exists（SELECT 1 from msdb..sysjobs where name= ''數(shù)據(jù)處理 ''） 

EXECUTE msdb.dbo.sp_delete_job @job_name= ''數(shù)據(jù)處理 '' 

execmsdb..sp_add_job @job_name= ''數(shù)據(jù)處理 '' 

——?jiǎng)?chuàng)建作業(yè)步驟 

declare @sql varchar（800）， @dbname varchar（250） 

select@sql= ''exec p_synchro '' ——數(shù)據(jù)處理的命令 

，@dbname=db_name（） ——執(zhí)行數(shù)據(jù)處理的數(shù)據(jù)庫(kù)名 

execmsdb..sp_add_jobstep@job_name= ''數(shù)據(jù)處理 ''， 

@step_name = ''數(shù)據(jù)同步 ''， 

@subsystem = ''TSQL ''， 

@database_name=@dbname， 

@command = @sql， 

@retry_attempts = retry_attempts，——重試次數(shù)

@retry_interval = retry_interval，——重試問隔

創(chuàng)建調(diào)度 

exec msdb..sp_add_jobschedule @job_name = ''數(shù)據(jù)處理 ''， 

@name = ''name''， 

@freq_type =freq_type，每天/每周/每月 @freq_interval = freq_interval， 間隔

@freq_recurrence_factor = freq_recurrence_factor，

@active_start_time=active_start_time開始時(shí)間

go

4結(jié)論

數(shù)據(jù)庫(kù)存儲(chǔ)過程具有執(zhí)行速度快、網(wǎng)絡(luò)通信量小、安全性高、可重復(fù)使用等優(yōu)點(diǎn)，通過數(shù)據(jù)庫(kù)的存儲(chǔ)過程技術(shù)實(shí)現(xiàn)主從服務(wù)器數(shù)據(jù)庫(kù)同步，可以大大提高數(shù)據(jù)庫(kù)系統(tǒng)的穩(wěn)定性和可用性，滿足氣象系統(tǒng)中各種業(yè)務(wù)系統(tǒng)的服務(wù)需求。

參考文獻(xiàn)

[1]高守傳.SQL—結(jié)構(gòu)化查詢語言詳解[M].北京：人民郵電出版社，2007：303—317.

[2]汪維富.基于存儲(chǔ)過程的高性能數(shù)據(jù)庫(kù)應(yīng)用模型研究[J].計(jì)算機(jī)工程與設(shè)計(jì)，2008，29（10）：2573—2575.

[3]何健.樹型層次結(jié)構(gòu)數(shù)據(jù)中遍歷子樹結(jié)點(diǎn)的方法[J].計(jì)算機(jī)技術(shù)與發(fā)展，2008，18（4）：95—97.

[4]夏義全.數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)優(yōu)化方法的研究[J].計(jì)算機(jī)技術(shù)與發(fā)展，2008，18（7）：149—152.

[5]孫偉東.利用存儲(chǔ)過程實(shí)現(xiàn)簡(jiǎn)單分布式計(jì)算的方法[J].沈陽航空工業(yè)學(xué)院學(xué)報(bào)，2008，25（1）：53—55.

[6]郭琳.SQL Server中的存儲(chǔ)過程研究[J].重慶文理學(xué)院學(xué)報(bào)：自然科學(xué)版，2007，26（4）：48—49.