基于存儲過程的主從服務器數據庫同步的實現

摘要：數據庫系統是信息系統的基礎，為了保證信息系統的高可用性，需要對主從服務器數據庫進行同步，保證數據的一致性，本文根據實際需求摸索一種基于存儲過程的主從服務器數據庫同步方法，首先介紹存儲過程，然后用實例說明如何利用存儲過程實現主從服務器數據庫同步，該方法對于特定系統具有一定的應用指導意義。



關鍵詞：存儲過程；數據庫；數據同步

中圖分類號：TP393 文獻標識碼：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引言

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

2存儲過程介紹

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

2.1存儲過程的創建

創建存儲過程可以使用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語句中，方括號“[]”中的內容是可選的，花括號“{}”中的內容是必須出現的，不能省略，[ ，…n]表示前面的參數樣式，可以重復出現。豎線“|”表示兩邊的選項可以任選一個。

存儲過程參數的命名必須以@開頭。參數按其用途可分為入口參數和出口參數兩大類。入口參數可以用來把值傳遞給存儲過程使用?？梢允褂枚喾N方式向存儲過程傳遞參數，可以按值傳遞，可以按名傳遞，還可以使用變量傳遞。出口參數采用變量的形式向過程或程序返回結果值。調用帶有出口參數的存儲過程時，必須在調用過程中聲明一個類型相同的變量來存儲返回值。SQL Server 提供了幾個可在編制存儲過程中使用的全局變量，全局變量使用前綴@@來與標準變量進行區別，@@error是最常見的全局變量之一。

2.2存儲過程的優點

存儲過程的執行在數據庫服務器中，應用程序只需傳遞相關的存儲過程的命令至數據庫服務器即可執行[2—6]，其優點如下：

1）執行速度快，存儲過程只在創造時進行編譯，以后每次執行存儲過程都不需再重新編澤，而一般SQL語句每執行一次就編譯一次，所以使用存儲過程可提高數據庫執行速度。

2） 存儲過程可以很方便的接收參數，進一步增強了數據庫處理的靈活性。

3）減少了網絡通信量，當客戶端發出執行存儲過程的請求時，只有執行存儲過程的命令在內部網絡上傳送，當它們到達數據庫服務器時，運行存儲過程，客戶端在網上只接收返回結果或狀態信息，使得客戶機與服務器的通信量降至最小，大大減少了網絡通信量。

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

5） 存儲過程可以重復使用，可減少開發人員的工作量。

3主從服務器數據庫同步的實現

3.1主從服務器數據庫同步原理

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

計算技術與自動化2012年9月

第31卷第3期季剛：基于存儲過程的主從服務器數據庫同步的實現

圖1同步過程

3.2主從服務器數據庫同步實現步驟

下面以gps數據庫同步為例介紹主從服務器數據庫同步的實現步驟，gps數據庫主從服務器上分別安裝了sql server 2005企業版，gps數據庫表結構如下：

表名

sd2012

列名

數據類型

空/非空

約束條件

站號char（10）not 1primary key

觀測時間smalldatetimenot 1primary key

天頂總延遲float

1

氣壓float

1

溫度float

1

濕度float

1水汽總量float1

（1） 創建數據庫鏈接

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）創建同步處理的存儲過程

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 

啟動遠程服務器的MSDTC服務 

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 

啟動本機的MSDTC服務 

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

同步刪除的數據 

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

where not exists （select * from srv_lnk.[sd].[dbo].[sd2012] A where A. 站號=站號and A.觀測時間=觀測時間）

同步新增的數據 

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

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

where not exists （select * from [sd].[dbo].[sd2012] where站號=A. 站號and觀測時間=A. 觀測時間） 

同步修改的數據 

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

where （B.站號=A. 站號and B.觀測時間=A. 觀測時間） and （B. 天頂總延遲<>A.天頂總延遲or B.氣壓<>A.氣壓 or B. 溫度<>A. 溫度or B. 濕度<>A. 濕度orB. 水汽總量<>A. 水汽總量）

GO

（3） 創建作業和調度，定時執行數據同步的存儲過程

if exists（SELECT 1 from msdb..sysjobs where name= ''數據處理 ''） 

EXECUTE msdb.dbo.sp_delete_job @job_name= ''數據處理 '' 

execmsdb..sp_add_job @job_name= ''數據處理 '' 

——創建作業步驟 

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

select@sql= ''exec p_synchro '' ——數據處理的命令 

，@dbname=db_name（） ——執行數據處理的數據庫名 

execmsdb..sp_add_jobstep@job_name= ''數據處理 ''， 

@step_name = ''數據同步 ''， 

@subsystem = ''TSQL ''， 

@database_name=@dbname， 

@command = @sql， 

@retry_attempts = retry_attempts，——重試次數

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

創建調度 

exec msdb..sp_add_jobschedule @job_name = ''數據處理 ''， 

@name = ''name''， 

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

@freq_recurrence_factor = freq_recurrence_factor，

@active_start_time=active_start_time開始時間

go

4結論

數據庫存儲過程具有執行速度快、網絡通信量小、安全性高、可重復使用等優點，通過數據庫的存儲過程技術實現主從服務器數據庫同步，可以大大提高數據庫系統的穩定性和可用性，滿足氣象系統中各種業務系統的服務需求。

參考文獻

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

[2]汪維富.基于存儲過程的高性能數據庫應用模型研究[J].計算機工程與設計，2008，29（10）：2573—2575.

[3]何健.樹型層次結構數據中遍歷子樹結點的方法[J].計算機技術與發展，2008，18（4）：95—97.

[4]夏義全.數據庫應用系統優化方法的研究[J].計算機技術與發展，2008，18（7）：149—152.

[5]孫偉東.利用存儲過程實現簡單分布式計算的方法[J].沈陽航空工業學院學報，2008，25（1）：53—55.

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