一種數據庫遷移和日志傳送方案的實現

摘要：服務器性能不佳，會影響SQL Server數據庫的運行效率。當服務器崩潰時，數據庫里的大量數據如何恢復？用SQL Server 2000標準版支持的日志傳輸功能，可以實現一種切實可行的數據庫服務器遷移和日志傳送方案。

關鍵詞：數據庫遷移；日志傳輸；備份

A Feasible Way For Database Server Tranfer And Log Transmission

LI Na1，DONG Yong-chu1，LIANG Yuan-yuan2

(1.Guangdong Service Center For Meteorological Science And Technology，Guangdong Guangzhou，510080；2.Guangdong Meteorological Information Center，Guangdong Guangzhou，510080)

Abstract: The operating efficiency of SQL Server Database can be effected by the bad performance of server. When the server crashs， how to recover the data of database. This article achieves a feasible way for database server tranfer and log transmission by using log transfer function of SQL Server 2000 standand.

Key words:Database transfer; Log transmssion; Backup

1 引言

在維護海量數據的時候，經常會遇到這樣的情況：SQL Server數據庫運行一段時間后，可能由于存放SQL Server數據庫的硬盤空間不足或服務器性能、質量不佳，使數據庫運行效率大大下降，甚至不能工作。同時，我們還常常擔心如果計算機崩潰了，已經存儲的大量數據怎么樣恢復？又如何進行完整的數據備份？本文就數據庫服務器遷移和日志傳送討論一種切實可行的方案。

2 數據庫現狀

我們的數據庫采用SQL Server 2000標準版，數據量達到幾千萬級，大小為90GB，經常需要進行新建表、修改數據庫結構等操作，允許最大限度宕機6小時。在服務器硬件故障等情況發生時，采用備份和恢復一系列操作很難保證在限定時間內能恢復正常。

由于SQL Server 2000標準版支持日志傳輸，成本較低，我們決定采用高可用性的溫備方案——使用現有的SQL Server 2000 標準版來實現日志傳輸。

日志傳輸有如下的特點：

自動同步：由主服務器的SQL Server Agent服務定期在主服務器上備份日志，備用服務器的SQL Server Agent 服務定期從主服務器的共享文件夾復制日志備份文件到備用服務器，再定義日志備份的還原。

數據副本間斷性只讀：在正常情況下備用服務器的數據可以只讀訪問，但是在恢復日志備份的時候要中斷訪問。

手工轉移：如果主服務器出現故障，需要將備用服務器轉成正常狀態，并修改服務器的IP地址或者服務器名。

數據庫級轉移：每個數據庫需要單獨配置，如果有大量數據庫需要實現高可用性，則不建議使用日志傳輸。

有數據損失：由于主服務器和備用服務器采用備份日志進行傳輸，而不是實時同步，因此會在備份的間隔期內出現該間隔期中插入、修改的操作丟失。丟失數據的量取決于日志傳輸的三個自動化任務的時間安排。當然備份、傳輸、恢復的間隔越短，數據的損失就越少，而性能的損失就是增加[1]。

我們選用了一臺與要備份的服務器性能差不多的機器作為備用服務器。

3 準備工作

（1）在主服務器Primary上設置SQL Server 和SQL Server Agent服務的啟動方式為自動啟動，并設置服務啟動帳號為本地系統帳號。

在備用服務器Primary_BAK上設置SQL Server 和 SQL Server Agent服務的啟動方式為自動啟動，并設置服務啟動帳號為域帳號SQL Agent User及其密碼。

（2）在主服務器Primary和備用服務器Primary_BAK的SQL Server代理——“屬性”——“高級”中，將“SQL Server代理意外停止時自動重新啟動”選項勾選，確保“SQL Server代理”時刻保持運行狀態。

（3）在主服務器Primary和備用服務器Primary_BAK上準備日志備份的空間，大小推薦是日志文件的兩倍。

（4）在主服務器Primary上要備份的數據庫NN_SUB——“屬性”——“選項”——“故障還原”中將模型選為“完全”，只有完整模式才能做日志備份。

（5）在主服務器Primary上建立共享文件夾，創建的目錄為E:\\ProductLogs，共享名稱為ProductLogs，用于存儲產生的日志備份，并設置共享文件夾的NTFS權限和共享權限，需要給予域帳號SQLAgentUser共享文件夾的只讀權限，刪除默認的EveryOne的只讀權限；需要給予SQLAgentUser共享文件夾的NTFS讀寫權限。

（6）如果主服務器上Primary上NN_SUB數據庫上除了SA帳號，還有其他的帳號，在備用服務器Primary_BAK上需要建立同名同密碼同SID的登錄帳號[2]。

4 手工備份

（1）因為這時備用服務器Primary_BAK上還沒有數據庫，所以我們需要先進行一個全備。利用SQL Server 2000自帶的“備份數據庫”完整備份主服務器Primary上的數據庫NN_SUB，設置備份的目標路徑為主服務器Primary的E盤。

（2）在備用服務器Primary_BAK上從遠程服務器Primary的E盤的NN_SUB完整備份中恢復數據庫，設置數據庫恢復之后的模式為只讀模式。

（3）利用SQL Server 2000的“數據庫維護計劃”——“新建維護計劃”——選擇要維護的數據庫NN_SUB，按默認設置繼續下一步，選擇“作為維護計劃的一部分來備份事務日志”，在“指定事務日志的備份磁盤目錄”處指定備份的目錄為之前創建的共享文件夾目錄 E:\\ProductLogs，并勾選“刪除早于此時間的文件”，設置為1周。調度設為每小時備份一次。繼續下一步到完成。這個計劃將自動每小時產生一個事務日志備份文件，文件格式為：NN_SUB_tlog_yyyymmddhh.trn，確保備用服務器Primary_BAK可以按照順序進行恢復[3]。

5 日志傳送

如何將主服務器Primary上的日志備份轉移到備用服務器Primary_BAK上呢？我們通過幾個SQL腳本來實現。

5.1 Instlog.sql腳本

Instlog.sql腳本實現2個功能：

（1）用于在MSDB數據庫中創建三個表：

backup_movement_plans：存放創建的維護計劃名稱。

backup_movement_plan_databases：存放日志傳送作業的源服務器和目標服務器，裝載延遲時間、最后復制的日志及其復制時間、最后裝載的日志及其裝載時間。

backup_movement_plan_history：存儲復制日志備份和裝載日志備份的作業執行記錄。

（2）在MSDB數據庫中安裝兩個存儲過程

sp_create_backup_movement_plan：調用該存儲過程用于創建備份和恢復數據庫的維護計劃和作業，以及傳送日志的源路徑和目標路徑，裝載的頻率和復制的頻率。關鍵代碼如下：

//創建備份數據庫的作業

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_name = @CopyJobName

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_name = @CopyJobName， @step_id = 1， @step_name = N'step1'，

@command = @CopyCommand， @subsystem = N'TSQL'， @on_success_step_id = 0， @on_success_action = 1，

@on_fail_step_id = 0， @on_fail_action = 2， @flags = 4

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name = @CopyJobName，

@freq_subday_interval = @copy_job_freq，

@name = N'sch1'， @enabled = 1， @freq_type = 4， @active_start_date = 19980402，

@active_start_time = 0， @freq_interval = 1， @freq_subday_type = 4， @freq_relative_interval = 0，

@freq_recurrence_factor = 0， @active_end_date = 99991231， @active_end_time = 235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_name = @CopyJobName， @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

sp_add_db_to_backup_movement_plan：調用該存儲過程，用戶指定進行日志傳送的數據庫。

5.2 sp_create_backup_movement_plan.sql腳本

sp_create_backup_movement_plan.sql腳本用于創建事務日志備份的文件復制計劃，需要在備用服務器Primary_BAK的MSDB數據庫上執行，腳本參數的名稱和功能以及具體設置如下：

@name維護計劃名稱（日志傳送）

@source_dir日志源文件夾（\\\\主服務器Primary\\ProudctLogs）

@dest_dir日志目標文件夾（E:\\ShippedLogs）

@sub_dir子文件夾標記（0）

@load_job_freq裝載作業的頻率，分鐘

@copy_job_freq復制作業的頻率，分鐘

5.3 sp_add_db_to_backup_movement_plan.sql腳本

sp_add_db_to_backup_movement_plan.sql腳本調用存儲過程sp_add_db_to_backup_movement_plan ，添加數據庫到日志傳送計劃，需要在備用服務器Primary_BAK的MSDB數據庫上執行，腳本參數的名稱和功能以及具體設置如下：

@plan_name日志傳送計劃名稱（日志傳送）

@source_db主數據庫名（NN_SUB）

@dest_db備用數據庫名（NN_SUB）

@load_delay裝載延時，分鐘

@load_all裝載所有未裝載的日志（1）

@source_server日志傳送的源服務器（主服務器Primary）

@retention_period備用服務器上日志保留時間，小時

6 故障轉移

故障轉移即主服務器出現故障之后，用備用服務器取代主服務器的過程。由于應用程序訪問數據庫服務器的方式不一樣，恢復過程也不一樣，這里討論應用程序使用機器名訪問數據庫服務器的轉移過程。

（1）在備用服務器Primary_BAK上，手工啟動SQL Server Agent 下面的復制作業和裝載作業，完成剩余日志的復制和裝載。

（2）將復制作業和裝載作業禁用。

（3）將主服務器Primary斷開網絡。

（4）在備用服務器Primary_BAK上執行下面的腳本，執行完成之后，數據庫的狀態會由只讀狀態變為可讀可寫狀態。

Use Master

Restore Database NN_SUB With Recovery

（5）修改備用服務器Primary_BAK的IP地址。

（6）從AD用戶和計算機工具中在AD中刪除主服務器Primary。

（7）在備用服務器Primary_BAK上執行下面語句，否則重命名后SQL Server服務無法啟動：

Sp_DropServer Primary_BAK

Sp_AddServer Primary

（8）將備用服務器Primary_BAK重命名為Primary，重啟服務器。

7 注意事項

不要長時間訪問備用服務器Primary_BAK上的NN_SUB數據庫不斷開。Primary_BAK上的NN_SUB數據庫平時是只讀狀態，由于恢復數據庫的時候要排他訪問，因此，如果備用服務器Primary_BAK上的NN_SUB數據庫裝載傳送過來的日志的時候，有用戶訪問正在進行，這時候恢復會失敗，但不影響日志傳送的正常工作，下一次還會繼續啟動進行恢復。但是如果有用戶長時間不斷開訪問Primary_BAK，會導致長時間日志無法加載。未被加載的日志備份是不會被刪除的，累積下去，會導致日志文件越來越多，耗盡硬盤剩余空間，進而導致數據庫因空間不足復制日志文件失敗[3]。

參考文獻：

[1]鄒建. 中文版SQL Server2000開發與管理應用實例[M]. 北京：人民郵電出版社，2005.

[2]葛美紅，徐超. 一種將SQL數據庫遠程備份與恢復的方法[J]. 電腦知識與技術學術交流，2006，12:3－5.

[3]趙杰，李濤，朱慧. SQL Server 數據庫管理、設計與實現教程[M]. 北京：清華大學出版社，2004.

[4]王俊偉，史創明. SQL Server 2000中文版數據庫管理與應用標準教程[M]. 北京清華大學出版社，2006.

收稿日期：2008-01-12

作者簡介：李娜（1980-），女，工程師，碩士，研究方向：數據庫管理。