SQL Server 2000之記錄傳送功能 - 設定

server SQL Server 2000之記錄傳送功能 - 設定（1）

      記錄傳送功能可自動複製資料庫的交易記錄檔，並回存到備援伺服器 (standby server) 的另外一個資料庫。因此可大幅提高SQL Server資料庫之可用性。因為備援資料庫完整地接收來來源資料庫的異動情況，所以它就是一份來來源資料庫的複本 - 差別僅在於資料複製與載入過程所產生的時間差。然而，當主要伺服器停擺時，您就可以將備援伺服器更改為新的主要伺服器。如果原來的主要伺服器可重新上線使用，那麼您可以將其設定為新的備援伺服器 - 事實上就是對調兩台伺服器的角色。
 
       在SQL Server 2000企業版或開發版之中，Microsoft在Enterprise Manager內提供了一項記錄傳送(Log Shipping)的功能 - 為資料庫維護計劃精靈的其中一部份。在使用之前的SQL Server時，您需要自行建立記錄傳送系統。
 
設定記錄傳送

      主要伺服器(primary server) 即是實際處理資料的正式伺服器；此伺服器內擁有來來源資料庫。次要伺服器(secondary server)上存放目的資料庫，用來複製與回存來來源資料庫的交易記錄檔。監控伺服器(monitor server)用來監控主要伺服器與次要伺服器。與SQL Server 7.0不同的是(SQL Server 7.0是在次要伺服器上監控記錄傳送動作)，SQL Server 2000使用Enterprise Manager的記錄傳送監控工具來監控每一組傳送中的日誌資料。Microsoft建議您在另外一台監控用伺服器安裝這個工具程式。
 
       您可以利用Enterprise Manager的資料庫維護計劃精靈設定SQL Server 2000的記錄傳送。但是在您啟用精靈之前，您必須先進行某些準備工作。一開始請先遵循下列步驟：
       1.決定一組要設定記錄傳送的伺服器(即記錄傳送過程之中，主要伺服器與次要伺服器為何)。
      2. 選擇一台監控伺服器。最好不同於主要伺服器或次要伺服器。
      3. 設定所有伺服器之安全性。您用來設定記錄傳送的Windows帳號必須擁有所有伺服器上SQL Server系統管理者(sa)的許可權。
       4. 在主要/次要伺服器上建立分享資料夾。首先，將來來源資料庫交易記錄檔所在的目錄設定為分享目錄。接著在次要伺服器上，將您打算回存交易記錄檔的目錄也分享出來。為了清楚辨別各分享目錄，請在分享名稱內註明伺服器與資料庫之名稱。如果分享目錄名稱已存在，您可能需要從分享目錄中刪除或是搬移其它檔案，特別是舊的記錄備份檔案。然後再將這些分享目錄的許可權開放給每一台伺服器上SQL Agent所使用的Windows帳號。
       5. 決定如何建立並初始化目的地資料庫。您可以在記錄傳送設定過程就先建立與首次同步處理化目的地資料庫，否則您必須手動進行初始資料庫之回存動作。
       6. 在Enterprise Manager註冊此三台伺服器(即主要、次要與監控伺服器)。
 在您完成這些準備動作時，您就可以準備啟用資料庫維護計劃精靈來設定記錄傳送。您可以先檢視記錄傳送過程的五個連續步驟，如圖1所示：

圖1：SQL Server 2000記錄傳送的設定步驟。

       前兩個為選擇性(optional)步驟。如果您尚未同步化來源與目的資料庫，則步驟1會為您先備份來來源資料庫，然後執行同步化動作。在步驟2時，精靈會將備份檔案複製到次要伺服器，並回存到目的地資料庫。
       精靈一定會執行其餘三項步驟。在步驟3時，精靈將在主要伺服器上建立一個SQL Agent工作(job)。此工作將會周期性地把交易記錄檔內容備份到磁碟檔案內。精靈也會在次要伺服器上建立一個傳送記錄的資料庫維護計劃；此計畫包含兩個SQL Agent工作：一個是將交易記錄檔複製到次要伺服器(步驟4)，另一個則是將交易記錄檔回存到目的資料庫(步驟5)。這些步驟將建立一組記錄傳送伺服器(互相有記錄傳送關係的兩個資料庫)。如果您想要額外提供容錯功能或是設定一台報表伺服器，那麼您可以將主要伺服器與另外一台次要伺服器組合在一起，再設定一組記錄傳送配對伺服器。

準備工作
        1. 準備 Primary Server (以下為Ztao-1) 及 Secondary Server (以下為IntronTest)
        2. 將要作 Log Shipping 的資料庫(以下為IntronERP)之還原模型(Recovery Model)設定為完整(FULL)。
 
        3. 將兩台電腦的SQL Server服務帳號加入Administrator群組
        4. 建立Primary Server 備份Log的資料夾
            a. 建立C:\Logfile，以存放Primary Server資料庫Transaction Log的備份
            b. 將C:\Logfile作資料分享，分享目錄的許可權開放給SQL Agent所使用的Windows帳號。
        5. 建立 Secondary Server 還原的資料夾(在Secondary Server)
            a. 建立C:\Shippedlog資料夾以存放從Primary Server傳送過來的Transaction log 的備份
            b. 建立 C:\Logfile資料夾，當角色交換後，可存放新Primary Server的資料庫Transaction Log
            c. 將C:\Logfile資料夾作資源共用，分享目錄的許可權開放給SQL Agent所使用的Windows帳號。
        6. 在Primary Server中，新增 Secondary Server的註冊資訊

 

if exists(
 select * from sysobjects
  where name='pr_backup_db' and xtype='p'
 )
begin
 drop proc pr_backup_db
end
go
/*備份資料庫*/
create proc pr_backup_db
@flag varchar(10) out,
@backup_db_name varchar(128),
@filename varchar(1000)  --路徑＋檔案名稱字
as
declare @sql nvarchar(4000),@par nvarchar(1000)
select @par='@filename varchar(1000)'
select @sql='BACKUP DATABASE '+@backup_db_name+' to disk=@filename with init'
execute sp_executesql @sql,@par,@filename
select @flag='ok'
go

 if exists(
 select * from sysobjects
  where name='fn_GetFilePath' and xtype='fn'
 )
begin
 drop function fn_GetFilePath
end
go
/*建立函數,得到檔案得路徑*/
create function fn_GetFilePath(@filename nvarchar(260))
returns nvarchar(260)  
as
begin
 declare @file_path nvarchar(260)
 declare @filename_reverse nvarchar(260)
 select @filename_reverse=reverse(@filename)
 select @file_path=substring(@filename,1,len(@filename)+1-charindex('\',@filename_reverse))
 return @file_path
end

go

if exists(
 select * from sysobjects
  where name='pr_restore_db' and xtype='p'
 )
begin
 drop proc pr_restore_db
end
go
create proc pr_restore_db    /*恢複資料庫*/
@flag varchar(20) out,    /*過程啟動並執行狀態標誌,是輸入參數*/     
@restore_db_name nvarchar(128),  /*要恢複的資料名字*/
@filename nvarchar(260)         /*備份檔案存放的路徑+備份檔案名字*/
as
declare @proc_result tinyint  /*返回系統預存程序xp_cmdshell運行結果*/
declare @loop_time smallint  /*迴圈次數*/
declare @max_ids smallint    /*@tem表的ids列最大數*/
declare @file_bak_path nvarchar(260)  /*原資料庫存放路徑*/
declare @flag_file bit   /*檔案存放標誌*/
declare @master_path nvarchar(260)  /*資料庫master檔案路徑*/
declare @sql nvarchar(4000),@par nvarchar(1000)
declare @sql_sub nvarchar(4000)
declare @sql_cmd nvarchar(4000)
/*
判斷參數@filename檔案格式合法性，以防止使用者輸入類似d: 或者 c:\a\ 等非法檔案名稱
參數@filename裡面必須有'\'並且不以'\'結尾
*/
if right(@filename,1)<>'\' and charindex('\',@filename)<>0
begin
 select @sql_cmd='dir '+@filename
 EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
 IF (@proc_result<>0)  /*系統預存程序xp_cmdshell傳回碼值:0(成功）或1（失敗）*/
 begin
  select @flag='not exist'   /*備份檔案不存在*/
  return  /*退出過程*/
 end
 /*建立暫存資料表,儲存由備份組內自主資料庫和記錄檔列表組成的結果集*/
 create table #tem(
     LogicalName nvarchar(128), /*檔案的邏輯名稱*/
     PhysicalName nvarchar(260) , /*檔案的實體名稱或作業系統名稱*/
     Type char(1),  /*資料檔案 (D) 或記錄檔 (L)*/
     FileGroupName nvarchar(128), /*包含檔案的檔案組名稱*/
     [Size] numeric(20,0),  /*當前大小（以位元組為單位）*/
     [MaxSize] numeric(20,0)  /*允許的最大大小（以位元組為單位）*/
   )
 /*
 建立表變數，表結構與暫存資料表基本一樣
 就是多了兩列,
 列ids（自增編號列）,
 列file_path,存放檔案的路徑
 */
 declare @tem table(      
     ids smallint identity,  /*自增編號列*/
     LogicalName nvarchar(128),
     PhysicalName nvarchar(260),
     File_path nvarchar(260),
     Type char(1), 
     FileGroupName nvarchar(128)
   )
 insert into #tem
  execute('restore filelistonly from disk='''+@filename+'''')
 /*將暫存資料表匯入表變數中,並且計算出相應得路徑*/
 insert into @tem(LogicalName,PhysicalName,File_path,Type,FileGroupName) 
  select LogicalName,PhysicalName,dbo.fn_GetFilePath(PhysicalName),Type,FileGroupName
   from #tem
 if @@rowcount>0
 begin
  drop table #tem
 end
 select @loop_time=1
 select @max_ids=max(ids)  /*@tem表的ids列最大數*/
  from @tem
 while @loop_time<=@max_ids
 begin
  select @file_bak_path=file_path
   from @tem where ids=@loop_time
  select @sql_cmd='dir '+@file_bak_path
  EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
  /*系統預存程序xp_cmdshell傳回碼值:0(成功）或1（失敗）*/
  IF (@proc_result<>0)
   select @loop_time=@loop_time+1 
  else
   BREAK /*沒有找到備份前資料檔案原有存放路徑，退出迴圈*/
 end
 select @master_path=''
 if @loop_time>@max_ids
  select @flag_file=1   /*備份前資料檔案原有存放路徑存在*/
 else
 begin
  select @flag_file=0  /*備份前資料檔案原有存放路徑不存在*/
  select @master_path=dbo.fn_GetFilePath(filename)
   from master..sysdatabases where name='master'
 end
 select @sql_sub=''
 /*type='d'是資料檔案,type='l'是記錄檔 */
 /*@flag_file=1時新的資料庫檔案還是存放在原來路徑，否則存放路徑和master資料庫路徑一樣*/
 select @sql_sub=@sql_sub+'move '''+LogicalName+''' to '''
   +case type
         when 'd' then case @flag_file
             when 1 then  File_path
      else @master_path
          end   
         when 'l' then case  @flag_file
      when 1 then  File_path
      else @master_path
          end   
   end
   +case type
    when 'd' then @restore_db_name+'_'+LogicalName+'_data.mdf'',' 
    when 'l' then @restore_db_name+'_'+LogicalName+'_log.ldf'',' 
    end
   from @tem
 select @sql='RESTORE DATABASE @db_name FROM DISK=@filename with '
 select @sql=@sql+@sql_sub+'replace'
 select @par='@db_name nvarchar(128),@filename nvarchar(260)'
 print @sql
 execute sp_executesql @sql,@par,@db_name=@restore_db_name,@filename=@filename
 select @flag='ok'   /*操作成功*/
end
else
begin
 SELECT @flag='file type error'  /*參數@filename輸入格式錯誤*/
end

--備份資料庫test_database
declare @fl varchar(10)
execute pr_backup_db @fl out,'test_database','c:\test_database.bak'
select @fl

--恢複資料庫,輸入的參數錯誤
declare @fl varchar(20)
exec pr_restore_db @fl out,'sa','c:\'
select @fl

--恢複資料庫,即建立資料庫test_database的複本test_db
declare @fl varchar(20)
exec pr_restore_db @fl out,'test_db','c:\test_database.bak'
select @fl

變更記錄檔傳送之組態設定

        您可以使用資料庫維護計劃之【屬性】對話盒來變更記錄檔傳送相關設定。在【交易記錄檔案備份】設定頁提供的選項可變更記錄檔傳送過程中交易記錄檔備份的組態。

        【記錄傳送】設定頁顯示出您先前在維護計劃內設定的記錄傳送配對伺服器；如果您設定了其它組記錄傳送配對伺服器，也會列在此處。本設定頁也包含下列選項：新增目的資料庫(用以建立新的記錄傳送配對伺服器)、刪除既有記錄傳送配對伺服器、編輯目前的記錄傳送配對伺服器之屬性，以及移除整個記錄傳送功能。

        當您在【記錄傳送】設定頁之中點選【編輯】時，將開啟【編輯目的資料庫】對話盒。您可以在對話盒內【一般】設定頁檢視與修改次要伺服器的交易記錄檔之目錄位置，以及未來做為主要伺服器時分享目錄之路徑。【初始化】設定頁則可讓您更改複原模式，以及次要伺服器上複製與回存之頻率。【臨界值】頁可以設定記錄傳送之臨界周期。

        在【超出同步臨界值】項目可設定：當記錄傳送監控程式產生警示訊息之前所能允許的最大時間間隔 (介於最近一次來來源資料庫交易記錄檔備份以及最新的交易記錄檔回存動作之間)。您也可以在記錄傳送監控程式之中設定此參數。【在入時間延遲】、【檔案保留期限】以及【曆程記錄保留期限】則是與次要伺服器相關的設定。
 
註：監控伺服器在這些組態選項中扮演相當重要的角色。因為【記錄傳送】設定頁的大部分資訊取決於監控伺服器，所以一但監控伺服器停擺時，您將無法變更記錄檔傳送的組態設定值。在監控伺服器執行SQL Server 2000 Profiler時，主要伺服器會連到監控伺服器，然後從記錄傳送資料表中取得既有的記錄傳送計劃。因此，要改變記錄傳送計劃的設定時，您必須確定在Enterprise Manager內可以串連到監控伺服器。

檢查與監控記錄傳送動作

        SQL Server 2000的記錄傳送功能還提供了一項記錄傳送監控程式，可讓您安裝在另一台獨立監控用伺服器。
        在SQL Server企業版與開發版的msdb資料庫中共有七個關於記錄傳送的資料表：
            log_shipping_plans 
            log_shipping_plan_databases 
            log_shipping_databases 
            log_shipping_plan_history 
            log_shipping_monitor 
            log_shipping_primaries 
            log_shipping_secondaries
 
        上述每一個資料表都存在於主要、次要以及監控伺服器上。各伺服器也會使用某些資料表儲存資料，視該伺服器在記錄傳送系統的角色為何。
 
        在主要伺服器上檢視記錄傳送動作 從Enterprise Manager 裡，您可以登入主要伺服器，並觀察與監控記錄傳送動作。如果某個資料庫已設定要進行記錄傳送，在資料庫【內容】對話盒的【一般】頁可得知該資料庫的角色(來來源資料庫；或是目的資料庫)，也可知道記錄傳送監控程式是位於那一台伺服器上。您可以在Enterprise Manager內SQL Server Agent的【作業】節點，檢視記錄傳送與交易記錄檔備份工作所執行的狀態與曆史紀錄。主要伺服器只使用msdb資料庫的兩個記錄傳送資料表。在log_shipping_databases資料表中，SQL Server新增的每一筆資料將會把資料庫維護計劃ID以及記錄傳送來來源資料庫連結在一起。在log_shipping_monitor資料表中，SQL Server新增的每一筆資料包含了監控伺服器的名稱，以及登入資料庫的方式。
 
        在次要伺服器上檢視記錄傳送動作 記錄傳送計劃存在於次要伺服器。您可在次要伺服器監控SQL Agent工作(複製交易記錄檔到次要伺服器，並回存至目的資料庫)。 您也可檢視目的資料庫的屬性對話盒，以決定該資料庫在記錄傳送過程所扮演的角色。
 
        在次要伺服器上，SQL Server使用msdb資料庫的四個記錄傳送資料表。當SQL Server建立一個記錄傳送計劃之後，它會新增一筆資料到log_shipping_plan資料表，用以紀錄：主要與次要伺服器的名稱、檔案位置、複製與回存工作ID(來自於次要伺服器之sysjobs系統資料表)。在log_shipping_plan_databases資料表，SQL Server會連結維護計劃以及來源/目的資料庫名稱，而且儲存最後一次進行檔案複製與載入動作的相關資訊。log_shipping_plan_history資料表則是將每次記錄傳送的複製與回存事件紀錄下來，連同該工作是否成功的資訊。SQL Server也會新增一筆資料在log_shipping_monitor資料表，用以參照監控伺服器。
 
        如果您勾選了【Allow database to assume primary role】複選框，您將在次要伺服器上看到一個重要的額外項目：另一個資料庫維護計劃(與您先前所建立的維護計劃名稱相同)，但是並沒有啟用記錄傳送。您也會看到一個非作用中(disabled)的SQL Agent工作(備份該資料庫的交易日誌)。也許您會被這些項目所混淆。儘管它們的名字相同，但是此額外產生的維護計劃卻不同於當初所建立的那個。SQL Server保留第二個逆向維護計劃是為了以後可能發生的主要/次要伺服器角色對調動作所準備。

        在監控伺服器上檢視記錄傳送動作 當您正確設定記錄傳送之後，SQL Server 會啟用監控伺服器上Enterprise Manager 的記錄傳送監控工具程式。此外，SQL Server會建立兩個SQL Agent 警示工作(alert job)：一個用來執行工作，另一個處理out-of-sync情況。
 
        使用監控工具程式的方式是，開啟Enterprise Manager並連至監控伺服器，展開【Management】節點，然後點選【記錄傳送監視器(Log Shipping Monitor)】。當您點選此工具程式時，其內會列出記錄傳送配對伺服器的清單。您可在配對伺服器上按下滑鼠右鍵，檢視其備份、複製與回存等工作的執行曆史紀錄。這些曆史紀錄十分有用，因為您從這裡得到的錯誤訊息會比從次要伺服器上(SQL Agent 複製與回存工作)得到的更為詳盡。
        如圖所示：當您開啟配對伺服器之屬性對話盒，並進入【Status】設定頁時，您可檢視此配對伺服器執行備份與回存程式之狀態。

        其狀態(Status)可以是Normal 或是Out-of-Sync。如果SQL Server Agent尚未複製或回存交易記錄檔，對話盒內將會顯示記錄檔名為first_file_000000000000.trn。這並不是實際的檔案名稱，只不過是用來標示SQL Server Agent尚未處理任何檔案而已。在【Status】設定頁也會顯示備份、複製以及載入(回存)等動作執行時所耗費的時間。此設定頁之資訊不會自動更新，所以您必須將此對話盒關閉後再開啟，才能更新其資料。
  
        SQL Server只使用msdb資料庫內兩個資料表來儲存記錄傳送伺服器之相關資料。SQL Server在這兩個資料表中都給予一個ID做為連結，以及一個外來鍵(foreign key)。該外來鍵是設定在log_shipping_secondaries資料表上，並參照log_shipping_primaries資料表的primary_id欄位(這兩個是所有記錄傳送資料表中唯一具有外來鍵關係的資料表)。在log_shipping_primaries資料表內的每筆資料都包含記錄傳送的相關資訊，例如：來來源資料庫名稱、交易記錄檔備份工作執行之狀態，以及已規劃的停擺資訊(可避免不必要的警示訊息)。而log_shipping_secondaries 資料表之每筆資料關於目的資料庫之資訊；每個目的資料庫附屬於特定的記錄傳送來來源資料庫。這兩個資料表互相連結的結果就是記錄傳送監控程式內所顯示的配對伺服器資訊。

移除與重新組態記錄傳送功能

        如果您想從資料庫維護計劃中移除記錄傳送功能，可參考下列方式：開啟該計劃的屬性對話盒，選擇【記錄傳送】設定頁，然後點選【移出記錄傳送】。此動作將從次要伺服器上移除SQL Server Agent的備份與回存工作，並清除記錄傳送資料表內的所有相關資料。此外，記錄傳送監控程式的相關資訊也會一併被清除。然而此動作將會適當地保留主要伺服器上SQL Server Agent的交易記錄備份工作。只有在刪除資料庫維護計劃時，該工作才會被移除。假如您想從監控伺服器內移除掉記錄傳送監控程式，請用手動方式將log_shipping_primaries與log_shipping_secondaries這兩個資料表(位於監控伺服器的msdb資料庫)的資料刪除即可。

        如果您在資料庫維護計劃內設定記錄傳送時，就已允許目的資料庫可以做為新的記錄傳送來來源資料庫。當您刪除主要伺服器的維護計劃時，次要伺服器上仍然會保留其資料庫維護計劃，以及交易記錄檔備份工作。刪除這些項目的方式是將次要伺服器上與記錄傳送相關的資料庫維護計劃直接刪除。