[SQL]在 Workgroup 環境下設定 SQL Server 轉送交易紀錄 ( Log Shipping )
SQL Server 的 Log Shipping 的功能，從很久之前就有了，可以設定為「不復原模式」( No Recovery ) 和「待命模式」( Standby )，但如果有人破壞備份的狀況，或者是把 Secondary 變成正式，則又要重新設定了，因此以往雖然知道有這樣的功能，但個人比較少拿到正式環境中使用。
而最近遇到一個狀況，有朋友在正式環境中有個線上交易頻繁的系統，但為了作一些管理報表，因此導入一些報表工具。這些報表工具透過 GUI 的設定和調整，會自動組合出一些非常非常非常複雜的 SQL 指令，這些指令雖然可以正常運作，但造成線上交易的系統在操作上會有很大的延遲。為了避免這樣的問題困擾大部份線上操作的使用者，因此跟報表工具使用者確認相關報表的使用，是否都需要抓取即時資料，使用者也都表示只需要昨天之前的資料即可，加上使用的版本又是 SQL Server Standard 的版本，所以就決定使用 「Log Shipping 搭配 Standby 模式」來成為這個問題的解決方案。
環境檢查
第一台 ( Primary )
電腦名稱 : Logsource
IP : 10.40.40.76
資料庫 : SQL Server 2012 Standard
第二台 ( Secondard )
電腦名稱 : Logtarget
IP : 10.40.40.77
資料庫 : SQL Server 2012 Standard
因為我們的測試環境是在 Workgroup下，並沒有 AD 可以設定網域帳號來存取，所以一開始我們就先在兩台主機上都各先建立一個相同名稱的帳號 「Logcopy」，並且設定相同的密碼。如果您是有在 AD 的環境下，那就在 AD 上去建立一個網域帳號來使用，也就不用那麼麻煩了。
由於 Log Shipping 基本上是透過三個步驟：「備份」→「複製」→「還原」，因此我們必須在 Primary 和 Secondary 的環境上，都先各建立一個目錄，一個用來放讓 Primary 放備份檔，一個用來讓 Secondary 放要被還原的檔案。所以在 Primary 上我們將目錄規劃在 D:\Logship
這個目錄因為我們只考慮讓 SQL Server 存放備份，因此在「安全性」的部分使用「進階設定」，開啟後我們先使用「停用繼承」，重新設定整個子目錄的安全性權限。
配合 SQL Server 2012 在安裝的時候預設使用服務帳號，因此這裡我們設定允許 「MSSQLSERVER」( NT Service\MSSQLSERVER ) 和 「SQLSERVERAGENT」( NT Service\SQLSERVERAGENT ) 這兩個服務帳號，可以完全控制這個目錄。
在 Log Shipping 的過程中，這個目錄下的檔案會由 Secondary 透過網路將檔案複製到 Secondary 的目錄下，所以這個目錄接下來將透過，一開始我們所建立的帳號來讀取該目錄下的檔案，所以除了前面安全性的設定之外，我們還要設定這個目錄的共用權限。另外也避免網路上其他使用者也看到這個目錄，所以這裡我們透過「進階設定」來做一些變化的處理。
在設定目錄分享的名稱，我們將目錄設定為「Logship$」，刻意在後面多加上一個 $，避免透過網路查詢的時候會顯示出這個目錄名稱，而使用上我們還是一樣指定這個名稱，並不會有任何使用上的問題。
設定好之後會如同這樣
接著再透過「共用」的設定，來設定目錄的分享權限，此時我們要指定一開始所建立用來讀取這個共用目錄的帳號 Logcopy，可以對這個目錄具有「讀取」權限，設定好之後案下下方「共用」的按鈕，即可完成 Primary 主機上目錄的相關設定。
接下來我們來到 Secondary 主機上，在 Secondary 上我們將目錄規劃在 D:\Logcopy
因為這個目錄基本上會由 Secondary 上的 SQL Server Agent 去啟動「複製」的步驟，將檔案從 Primary 的目錄下複製過來，因此要注意一下安全性的權限設定，而不需要設定「共用」的分享。所以這裡我們一樣把 SQL Server 的服務帳號給加入，並賦予完全控制的權限。
初步測試
在設定 Log Shipping 時候如果會發生問題，多半都是因為「權限」設定所導致，而權限又可以細分為「目錄權限」和「帳號權限」，所以在設定完前面相關的的步驟，我們先來測試一下目錄權限的部分設定是否有問題。於是一開始我們先進行備份測試，這個步驟主要是確認 SQL Server 在備份的時候，可以正常的寫入到我們的備份目錄下，因此可以直接用 SSMS 或者是透過 BACKUP DATABASE 的語法都可以，只要能成功備份進去就可以，這裡我們就直接使用 SSMS 來進行備份。
在這裡有個地方可以注意一下，如果是 SQL Server 2008 R2 以後，SQL Server 的「壓縮備份」已經可以提供在 Standard 的版本使用，只是這個部分預設是沒有啟用，所以最好能把這個給開啟，這樣備份的檔案會比較小，傳輸也比較省時間。
當可以備份之後，就可以測試複製的步驟是否可以順利，因此這個時候我們要到 Secondary 的環境，透過 runas 的指令來切換使用者身分，來測試使用 logcopy 帳號是否可以順利複製檔案到 Secondary 下所指定的目錄中。這裡我開一個 DOS 視窗，然後用 runas 切換使用者身分開一個 cmd 來進行，執行的時候他會問你帳號的密碼，這裡我們就先手動輸入，完成後就會出現一個是 logcopy 身份執行的命令提示自元的視窗，我們就可以測試檔案複製是否可以順利進行。
我們利用簡單的 copy 指令測試一下，看起來是可以順利複製檔案過來，那這下就沒有問題了。
如果上述的方式沒有問題，那表示我們設定的帳號和密碼兩台都有相同，且 Primary 電腦上共用的權限也設定正確。因此我們就可以進行最後一個部分還原資料庫的測試，基本上只要在 Secondary 這上面透過 SSMS 進行剛剛複製過來的檔案，確認沒有問題，那麼我們就算完成相關的目錄權限。這裡我就偷懶一下沒有抓還原檔案的圖片了，還原好之後因為這個資料庫還有些設定沒有調整，因此測試完之後我們就在 Secondary 上先將剛剛還原的資料庫給刪除，預備等下會正式來過一次。
設定過程
接下來我們就要正式進入 Log Shipping 的設定，SSMS 提供不錯的設定精靈，可以讓我們省掉不少麻煩。我們可以在所要進行處理的資料庫上，選擇「工作」→「轉送交易紀錄」
首先要先設定「啟用」，將這個選項給勾選。
如果你的資料庫復原模式沒有設定為「完整」或「大量紀錄」，則會出現以下的錯誤訊息，並且不讓你啟用轉送交易紀錄。
由於一開始我們的資料庫是設定復原模式為簡單，所以這裡我們來到「選項」的設定，這裡我們將資料庫復原模式變更為「完整」，這樣這個資料庫也就可以來進行 Log Shipping 的處理了。
可以順利啟用之後，則接下來就是要進行「備份設定」
主要是上半部的紅色區塊，要把相關的目錄給設定正確，並且確認備份檔案保留的天數。至於排程的部分，我個人是建議先使用預設值，後續都可以正常運作之後，再按照需求到 SQL Server Agent 的作業下，選擇作業名稱去調整就可以了。設定好之後就可以按下「確定」，完成備份的設定。
完成「備份設定」之後，此時會發現中間的次要資料庫設定部份就可以使用了，我們可以選擇「加入」進行加入次要資料庫，設定 Secondary 的相關資訊。
按下「連接」的按鈕，用來指定連線到 Secondary 主機的資訊
接下來我們要設定「初始化次要資料庫」，在這裡因為我們不是使用網域的帳號，因此如果選前兩項，則會發生複製檔案的過程因為權限問題造成失敗。因此這裡我們會選最後一個選項，也就是說我們自己來負責初始化資料庫，而不交由 SSMS 來幫我們處理，透過這些精靈只要幫我們產生 Log Shipping 的設定和 SQL Server Agent 內的作業 就好了，剩下的我們再來調整相關設定，避免權限問題造成影響。
也因為上個步驟我們要選擇手動，因此在 Secondary 主機上並沒有我們所要的資料庫，那我們就自己使用指令的方式，先手動備份資料庫，然後再將該備份檔案給複製到 Secondary 上囉。為了跟剛剛測試用的有所區別，這裡我們在使用 BACKUP DATABASE 指令的時候，把備份檔案的名稱改為 LSDemo_Full.BAK。
在 Secondary 將剛剛備份的檔案複製過來，這裡懶得抓兩個畫面，就 runas 直接配合 xcopy 的指令來複製檔案。
透過 SSMS 選擇還原資料庫，並且指定剛才所複製過來的備份檔案。
因為我們的解決方案是希望提供一個可以讀取的資料庫，因此要還原資料庫的時候，要特別調整「選項」，將復原狀態指定為「RESTORE WITH STANDBY」，這樣後續我們才可以繼續將交易紀錄檔還原到這資料庫上，且該資料庫還可以用 Readonly 的方式進行讀取。沒有問題我們就可以按下下方的「確定」，來進行還原資料庫。
待 Secondary 上資料庫都還原好之後，那我們就可以繼續回到 Log Shipping 的設定精靈上，來完成後續的相關步驟。剛才我們是做到「初始化次要資料庫」，接著我們來到「複製檔案」下進行設定，這裡也是要注意檔案存放目錄和檔案保留的期限。
而最後一個步驟「還原交易紀錄」，則跟我們之前手動還原資料庫的時候要設定一樣，把這裡設定為「待命模式」，至於排程的部份我們一樣先不理他，後續再來做調整。
如果你在設定這些選項的時候，沒有先去手動還原資料庫的情況下，當你按下確定的時候，則會出現以下的錯誤訊息，提醒你要記得先去手動還原資料庫。
而在我們設定都沒有問題的狀況下，當我們設定完所有的「次要資料庫」，就可以按下「確定」，確定完成相關設定。
設定代理
接著我們來到 Secondary 上，因為前面有提到要 SQL Server Agent 複製檔案的時候，用不同的身分去進行，於是我們在 SSMS 下選擇「認證」→「新增認證」
建立一個認證，這裡我們設定為「LogCopyAccount」認證名稱，用這個認證代表為「LOGTarget\Logcopy」 的帳號，這裡有些時候會容易出錯，特別是你自己手動輸入「識別」的時候，很容易忘記輸入電腦名稱，這部份要稍微注意一下。
建立好認證之後，我們就可以到「SQL Server Agent」→「Proxy」→「作業系統 (CmdExec)」→「新增 Proxy」
建立一個新 Proxy 帳戶，「Proxy 帳戶名稱」為「LogcopyProxy」，並且指定配合使用認證名稱為前一個步驟所建立的 LogCopyAccount
建立好之後，我們就可以調整剛剛透過 Log Shipping 建立精靈所產生的作業 LSCopy_LOGSOURCE_LSDeno
選擇左方的「步驟」，因為只有一個步驟，所以就直接選下方的「編輯」
將「執行身分」調整為前面所設定的 Proxy 帳號 「LogcopyProxy」
這樣看起來雖然可以讓 SQL Server Agent 去使用 Logcopy 的帳號去進行複製，但這個帳號並沒有在 SQL Server 內具有相關權限，因此我們還需要賦予它可以在 SQL Server 內也具備有權限，所以我們在 Secondary 上 SSMS 的「安全性」→「登入」，建立新的登入。
並且讓這個帳號具有 sysadmin 的伺服器角色，才能正常執行 Log Shipping。
測試驗證
經過前面的所有步驟之後，我們來查看在 SQL Agent 下有相關的作業是否都可以正常執行。首先在 Primary 下面，可以看到有個交易紀錄備份的作業。
我們來測試一下執行有沒有問題，在這個 LSBackup_LSDemo 的作業上面，按下滑鼠右鍵，選擇「從下列步驟啟動作業」，正常狀況下應該可以順利執行。
接著依序選擇 Secondary 上的作業 LSCopy_LOGSOURCE_LSDemo 和 LSRestore_LOGSOURCE_LSDemo，看看是否都可以正常執行。
完成之後我們連線測試看看是否可以正常讀取，在次要資料庫上面我們可以看到，此時有 1,000,000 筆的資料，看起來讀取也都沒有問題。
接著我們來到主要資料庫上面，確認資料筆數也是相同的，並且透過指令將部份資料給刪除，讓資料總筆數來到 999,900。
然後手動再執行一次「備份」→「複製」→「還原」的作業，然後再看看次要資料庫的資料，看起來資料有減少成為跟主要資料庫相同，並且還可以使用唯讀的方式來存取資料。如果前面你第一次查詢資料之後，沒有把視窗關閉，當「備份」→「複製」→「還原」的作業執行完之後，因為我們設定 Log Shipping 的時候，有指定當還原交易備份的時候，會把原有的連線給關閉，故此有可能會出現連線中斷的錯誤訊息，只要再執行一次恢復連線，應該就會有正常資料了。
後記
交易紀錄傳送是個不錯的好方法，而且還是一個便宜的解決方式，一般在設定上多半都是權限問題居多，只要稍加注意就可以避免了。但正式上線之後，像是新增帳號要如何同步、索引重建或大量資料異動造成交易紀錄檔過大、不小心執行到備份作業造成交易紀錄備份歷程中斷、新增資料庫檔案等問題，都需要特別去注意一下。
