如何做好完善的 SQL Server 2000 資料庫系統備援 與提昇高可用度 資策會教育訓練中心講師 許致學

個人簡介 現任 經歷 認證 MCP 台灣微軟 特約顧問 MCP+I 詮力科技 商業智慧顧問 MCSA 環保署廢棄物管制系統 資料庫顧問 環保署廢棄物管制系統 資料庫顧問 資策會教育訓練中心 講師 經歷 國立中央大學 資訊管理碩士 RUN!PC雜誌 MIS實戰專欄作者 Microsoft 資訊安全日活動 講師 Microsoft EMBA 系列課程 講師 Microsoft TechNet 技術研討會 講師 Microsoft TechEd 2000 / 2002 / 2003 / 2004 講師 Microsoft Windows Server 2003 上市全省發表會活動 講師 Microsoft SQL Server 2000 High Availability 行易課程 講師 Oracle to SQL Server Migration 研討會 講師 Microsoft MSDN Trend 開發技術戰情高峰會活動 講師 Microsoft SQL Server 2000 安全性管理資源與教學光碟 製作 Microsoft Virtual Server 2005 快速建置營 講師 Microsoft SQL Server 2005 實作一日營 講師 認證 MCP MCP+I MCSA MCSE MCDBA MCT PMP

本場次內容 高可用度簡介 如何做好高可用度 叢集服務—虛擬伺服器 交易記錄檔傳送 複寫 備份與還原策略

高可用度的要求取決於商業需求 商業需求決定了高可用度的要求 需要仰賴多種軟硬體的技術 產品或服務的可用度 需要持續不斷地改善產品、服務和流程 醫院有相對高的停機成本 需要仰賴多種軟硬體的技術 產品或服務的可用度 需要持續不斷地改善產品、服務和流程

高可用度的定義 何謂 99.999%? 高可用度的計算公式 目標: 思考： A = (F – R) / F (5個9) 何謂 99.999%? 目標: 提供使用者全年無休 (24X7) 的服務 每年只能有小於 5 分鐘 16 秒的停機時間 思考： 到底需要達到多少個 9？ 高可用度的計算公式 A = (F – R) / F A = 可用度 F = 平均發生毀損的時間 (MTBF) R = 平均所需的修復時間 2個？ 3個？ 5個？

9 9 9 9 9 高 可 用 度 Unmanaged < 3 days and 15.6 hours Well-managed Nodes < 8.76 hours Masks some hardware failures Masks hardware failures, operations tasks (e.g. software upgrades) Masks some software failures Well-managed Packs and Clones < 52.56 minutes 99% = 87.6 hours = 3 days and 15.6 hours 99.9 % = 8.76 hours 99.99% = 52.56 minutes 99.999% = 5.26 minutes Well-managed Geoplex Masks site failures (power, network, fire, move…) Masks some operations failures < 5.26 minutes

為了達到高可用度的成本

高可用度的時間和成本 擁有總成本的影響因素 1: 硬體和軟體 7: 最終使用者 2: 管理成本或程序成本 3: 支援 4: 開發 6: 停機 1: 硬體和軟體 7: 最終使用者 2: 管理成本或程序成本 3: 支援 4: 開發 6: 停機 5: 通訊費用

SQL 2000 高可用度的成功案例 Windows 2000 Datacenter Server: FreeMarkets.com 99.999% Windows 2000 Server & Advanced Server: Nasdaq.com 99.97% CommerceOne.net 99.97% HotBot.com 99.99% DataReturn.com 99.93% Buy.com 99.98% Barnes&Noble.com 99.98% CBSMarketWatch.com 99.98%

備援待命選項之比較 Hot Standby Warm Standby Cold Standby 結合作業系統叢集服務 備援伺服器亦擁有主要伺服器的資料 提供與主要伺服器交易一致的資料 能自動偵測主要伺服器故障狀況與自動接續服務 Warm Standby 備援伺服器亦擁有主要伺服器的資料，不過… 不一定能提供與主要伺服器一致的資料 可能需要手動偵測主要伺服器毀損或停機狀況與手動接續服務 Cold Standby 還原資料之備份 必須有正確的作業系統和相關軟體等的備份 必須考慮繼續恢復正常服務的延遲狀況是否能接受 交易記錄檔傳送和複寫 備份與還原

SQL 2000 之備援待命選項  (> 20 Sec)    （需搭配NLB）  （唯讀） 功能 Hot Standby Warm Standby Cold Standby 叢集服務 交易式複寫 交易記錄檔傳送 備份／還原 自動偵測服務中斷  (> 20 Sec)  自動容錯接續服務   （需搭配NLB） 資料遺失 可能會有部分資料尚未複寫 可能會有部分資料尚未傳送 可能會有部分資料尚未備份 共用儲存磁碟組 備援主機可支援 報表之資料來源  （唯讀） 伺服器登入帳戶 是否同步 資料庫權限 是否同步 備援主機資料表結構異動是否同步

Hot Standby--叢集服務 Windows Server 的叢集服務 自動偵測主要伺服器故障狀況與自動接續服務 如何自動偵測主要伺服器故障狀況與自動接續服務

Windows Server 的叢集服務 虛擬伺服器 叢集服務所需要的硬體 從使用者或是應用程式來看，是以虛擬伺服器的名稱或是共用 IP 來存取 叢集服務所需要的硬體 伺服器主機（2~8部） 外部網路卡（供用戶端存取） 內部網路卡（Heartbeat) 叢集的共用磁碟陣列 Server Cluster Public Network Shared Disk Array Node A Node B Heartbeat

自動偵測主要伺服器故障狀況 與自動接續服務 用戶端電腦 Node A Node B SQL Server SQL Server Heartbeat 共用之磁碟陣列

如何自動偵測主要伺服器故障狀況與自動接續服務 作業系統的檢查 透過內部網路卡 (Heartbeat) 檢查另一部主機是否正常 SQL Server 的檢查 每隔五秒執行 LooksAlive 檢查 執行 SELECT @@SERVERNAME 查詢 另一部主機如何自動接續服務 Windows 叢集服務會嘗試在同一部主機重新啟動服務，或是移轉至另一部主機繼續提供服務 啟動 SQL Server 服務，master 資料庫重新上線 資料庫復原 使用者與應用程式必須重新連線 The MSCS service account must be a part of the administrator group on each node of the cluster for SQL Server. The IsAlive thread runs under the context of the cluster service account, and not the SQL Server service account. If you remove the MSCS service account, the IsAlive thread will no longer be able to create a trusted connection, and you will lose access to the virtual server.

Warm Standby--複寫 發行者、散發者和訂閱者 散發資料的方式 複寫的基本類型 集中式發行者和散發者 建立再度發行者（Republisher） 複寫與其他分散資料方式之比較

發行者、散發者和訂閱者 發行者 散發者 訂閱者 資料可以複寫至其他伺服器，擁有一個或以上的發行集，每個發行集代表一組相關的資料 包含散發資料庫和儲存歷程記錄資料及交易與中繼資料的伺服器 接收複寫資料的伺服器，也可將資料變更傳回給發行者，或將資料重新發行到其他的訂閱者 散發者 訂閱者

散發資料的方式 自主性愈高 時間差愈長 自主性愈低 時間差愈短 合併式複寫 快照式複寫 較適於高可用度 之複寫機制 交易式複寫 允許訂閱者立即更新或 佇列更新發行者的快照式複寫 分散式交易 自主性愈低 時間差愈短 自主性愈高 時間差愈長 合併式複寫 較適於高可用度 之複寫機制 允許訂閱者立即更新或 佇列更新發行者的交易式複寫

複寫的基本類型 快照式複寫 交易式複寫 合併式複寫 資料及資料庫物件在某一時間點的情狀，完整即時複製及散發給訂閱者端 資料初始快照集會被傳用至訂閱者端 當發行者端有資料異動，交易會傳送至訂閱者端 合併式複寫 允許發行者端和訂閱者端獨立運作，稍後再將發行者端和訂閱者端的資料更新合併為相同的結果 SQL Server 會追蹤發行者端與訂閱者端已發行資料的變更

集中式發行者和散發者 集中式發行者和散發者 遠端散發者 Moscow Berlin headquarters London Paris Rome Athens 1 2 3

建立再度發行者 (Republisher) Tokyo republishes to Beijing, Hong Kong, and Sydney Seattle Slow link San Francisco Tokyo Beijing Dallas San Francisco publishes to Seattle, Dallas, and Tokyo Hong Kong Sydney

複寫與其他分散資料方式之比較 方式 複寫 分散式交易 分散式查詢 備份和還原 自主性 隨複寫類型而異 低 高 資料轉換服務 特殊用途 時間差

Warm Standby--交易記錄檔傳送 什麼是交易記錄檔傳送？ 交易記錄檔傳送的運作方式 交易記錄檔傳送之相關設定 SQL Server 版本之設定差異 變更伺服器電腦名稱的步驟 交易記錄檔傳送結合網路平衡負載

什麼是交易記錄檔傳送？ 待命伺服器 資料庫 還原 主要伺服器 資料庫 備份 傳送交易記錄檔備份

交易記錄檔傳送的運作方式 監控伺服器 待命伺服器 主要伺服器 1. 備份 交易記錄檔 交易記錄檔 備份 3. 還原 交易記錄檔 1. 備份 交易記錄檔 交易記錄檔 備份 3. 還原 交易記錄檔 2. 複製交易記錄檔之備份 交易記錄檔 備份 傳送登入帳戶

交易記錄檔傳送之相關設定 復原模型 安全性 完整復原模型 大量記錄復原模型 建議採用 Windows 驗證 主要伺服器和待命伺服器需要對監控伺服器有寫入事件的權限

SQL Server 版本之設定差異 交易記錄檔傳送功能之版本差異 企業版 其他版本 內建於資料庫維護計劃精靈之設定步驟 必須自行設定所有相關之備份、還原與監控機制

注意：上述步驟不適用於叢集服務之 SQL Server 虛擬伺服器 變更伺服器電腦名稱的步驟 變更伺服器的電腦名稱，重新啟動電腦 SELECT @@SERVERNAME 回傳舊的電腦名稱 sp_dropserver '舊的電腦名稱' sp_addserver '新的電腦名稱' , LOCAL 重新啟動 MSSQLSERVER 服務 回傳新的電腦名稱 注意：上述步驟不適用於叢集服務之 SQL Server 虛擬伺服器

交易記錄檔傳送結合網路平衡負載 Client VIP NLB Cluster DIP1 DIP2 Log Shipping Primary SQL Server Secondary DIP1 DIP2 Log Shipping VIP

Cold Standby--備份與還原 備份簡介 備份時機 如何執行備份 備份方式 備份策略的規劃 還原簡介 如何進行還原 還原毀損的系統資料庫

 備份簡介 避免資料遺失 資料庫異動與交易日誌檔 設定資料庫復原模型 SQL Server 的備份

避免資料遺失 依據下列事項制定備份策略 備份週期 如何讓遺失的資料最少 如何復原遺失的資料 如何以最小的花費和時間復原遺失的資料 備份媒體的存放與測試 備份對系統效能的影響 備份週期 線上交易處理系統：較常 線上分析處理系統：不常

資料庫異動與交易日誌檔 用戶端 藉由應用程式異動資料 1 交易記錄檔磁碟 將異動資料寫入 交易記錄檔磁碟 3 將資料讀出並寫入 緩衝區快取，然後修改 2 緩衝區快取 SQL Server 資料檔磁碟 當 Checkpoint 發生，將已完成的 交易寫入資料庫 4

以備份還原資料之回復情況 試著回答下列狀況： 資料庫最後一次完整備份於週一晚上十點順利完成，但於週二下午二點毀損，請問資料庫可以回復至何時的狀態？ 若資料庫選項為完整復原模型，資料庫的資料檔和交易記錄檔分別儲存於不同的磁碟組上，且交易記錄檔並未毀損，則可以還原至下午二點 若資料庫選項為簡易復原模型，或資料庫的資料檔和交易記錄檔儲存於相同的磁碟組已經毀損，則僅可以還原至週一晚上十點

以備份還原資料之回復情況  假設備份週期如下圖所示，試著回答下列狀況： Monday Tuesday 差異備份 ... Log Data  完整備份 交易記錄檔備份 如果下午二點有個 UPDATE 忘了指定 WHERE，而三點的交易記錄檔已經備份了，請問資料庫可以回復至何時的狀態？

設定資料庫復原模型 建立新資料庫時，新資料庫會繼承 model 資料庫的復原模型 完整復原模型 大量記錄復原模型 簡易復原模型 ALTER DATABASE Northwind SET RECOVERY BULK_LOGGED

完整復原模型 資料庫備份與交易記錄檔備份提供完整的媒體錯誤保護 如果一或多個資料檔毀損的話，媒體復原可以還原所有已確認的交易，未確認的交易則不會還原 完全復原能將資料庫復原至系統毀損時的情況或特定時間點 包括 SELECT INTO、CREATE INDEX 與 BULK INSERT 等大量作業在內的所有作業都會完整記錄下來 交易記錄檔所需的空間較大，需要定期備份或是清除交易記錄檔

大量記錄復原模型 當記錄備份包含大量變更時，大量記錄復原模型只允許資料庫復原至交易記錄檔備份結束時 下列作業不會逐一記錄每一筆異動 SELECT INTO、bcp、BULK INSERT、CREATE INDEX（包括索引檢視表）、WRITETEXT 與 UPDATETEXT 若要執行資料庫的大量變更時，可以先變更為大量記錄模型，完成後再變更回完整復原模型，如此可以提高效能與減少所需的交易記錄檔空間，同時又能維護伺服器的防護 無法支援特定時間點的復原

簡易復原模型 藉由簡單復原模式，可將資料庫復原至最後一次備份時 交易記錄檔所需的空間較小 無法將資料庫復原至系統毀損時的情況或特定時間點 類似 SQL Server 7.0 或更早版本的 trunc. log on chkpt. 資料庫選項

SQL Server 的備份 允許使用者可以在備份期間繼續存取資料庫 備份資料庫原始的檔案位置與資料內容 備份過程的資料庫異動情況會一併記錄至備份中 發動一個 checkpoint 並記錄目前交易記錄檔所在的位置（LSN） 將資料庫的所有頁寫入備份媒體 將備份過程期間所發生的全部交易記錄一併寫入備份媒體中

 備份時機 備份系統資料庫的時機 備份使用者資料庫的時機 備份執行期間的限制

備份系統資料庫的時機 master 資料庫異動後 msdb 資料庫異動後 model 資料庫異動後 CREATE DATABASE、ALTER DATABASE 或 DROP DATABASE 執行系統預存程序 sp_addmessage、sp_grantlogin、sp_addlogin、sp_addumpdevice… msdb 資料庫異動後 新增或修改作業、警示和操作員 model 資料庫異動後

備份使用者資料庫的時機 新建資料庫之後 新建索引之後 清除交易記錄檔之後 執行 WRITETEXT 或 UPDATETEXT 之後 BACKUP LOG WITH NO_LOG 或是 BACKUP LOG WITH TRUNCATE_ONLY 執行 WRITETEXT 或 UPDATETEXT 之後 執行 SELECT...INTO 之後

備份執行期間的限制 下列動作於備份期間無法執行 新建或修改資料庫 執行資料庫自動成長 新建索引 壓縮資料庫 執行非日誌之操作

 如何執行備份 建立備份裝置 直接備份至檔案 使用多個備份檔案執行備份 常用的 BACKUP 選項 用 BACKUP 選項保護備份內容 如何備份至磁帶機

建立備份裝置 建立備份裝置的好處 可以重複使用 方便設定自動備份 備份裝置類型 備份裝置名稱 備份裝置實體檔案 USE master EXEC sp_addumpdevice 'disk', 'mybackupfile', 'C:\Backup\MyBackupFile.bak' 備份裝置實體檔案

直接備份至檔案 只執行一次的備份 測試備份作業 直接用 BACKUP DATABASE 陳述式 指定媒體類型 (disk, tape, pipe) 磁碟機、磁帶機、具名管道 指定完整路徑和檔案名稱 USE master BACKUP DATABASE Northwind TO DISK = 'C:\Temp\Mycustomers.bak'

使用多個備份檔案執行備份 Database A Database A Database B Database B Media Set File 1 BackupA1 BackupA2 BackupA3 File 2 File 3 Database A Backup Set Database A Database B Database B BackupB1 Backup Database Media Set File 1 File 2 File 3 Backup Set BackupA1 BackupA1 BackupA1 BackupB1 BackupA2 BackupA3 BackupA2 BackupA3 BackupA2 BackupA3 BackupB1

常用的 BACKUP 選項 指定 INIT 或 NOINIT 選項 指定 FORMAT NOINIT 選項：附加至備份檔案 覆寫備份檔案的內容 將原先媒體集的備份檔案拆開

用 BACKUP 選項保護備份內容 指定 PASSWORD 選項 指定 MEDIAPASSWORD 選項 為備份集設定密碼，則執行該備份集的任何還原都必須提供備份集的密碼 有密碼保護的備份集只有在重新格式化時才會被覆寫 指定 MEDIAPASSWORD 選項 為媒體集設定密碼，要執行從媒體集還原之作業時，也必須提供該媒體集的密碼 有密碼保護的媒體集只有在重新格式化時才會被覆寫

如何備份至磁帶機 磁帶機必須安裝於 SQL Server 的本機上 會在磁帶標籤上記錄備份的摘要資訊 磁帶可以同時存放 SQL Server 和非 SQL Server 的備份 採用 Microsoft Tape Format

 備份方式 完整備份 差異備份 交易記錄檔備份 使用 NO_TRUNCATE 選項 清除交易記錄檔 檔案及檔案群組備份

完整備份 提供後續其他備份的基準 備份原始檔案、物件和資料 備份部分的交易記錄檔 USE master EXEC sp_addumpdevice 'disk', 'NwindBac', 'D:\MyBackupDir\NwindBac.bak' BACKUP DATABASE Northwind TO NwindBac D:\ NwindBac Backup Data Log Northwind

差異備份 適用於異動頻繁的資料庫 必須先做完整備份 備份自從最後一次完整備份之後所有異動過的資料 節省備份和還原的時間 BACKUP DATABASE Northwind TO DISK = 'D:\MyData\MyDiffBackup.bak' WITH DIFFERENTIAL

交易記錄檔備份 必須先做完整備份 不可以設定為簡易復原模型 備份自從最後一次交易記錄檔備份之後，到目前為止交易記錄檔內所有的資料 截斷（清除）交易記錄檔之中已經寫入資料庫的交易 如果經常備份交易記錄檔，就可以確保交易記錄檔不至於太大 USE master EXEC sp_addumpdevice 'disk', 'NwindBacLog', 'D:\Backup\NwindBacLog.bak' BACKUP LOG Northwind TO NwindBacLog

使用 NO_TRUNCATE 選項 儲存整個交易記錄檔，即使資料庫已經無法存取 不會截斷（清除）交易記錄檔之中已經寫入資料庫的交易 將資料庫回復至系統有問題時 可以使資料損失最少

清除交易記錄檔 用 BACKUP LOG 陳述式清除交易記錄檔 將資料庫設定為簡易復原模型 搭配 TRUNCATE_ONLY 或 NO_LOG 選項 將資料庫設定為簡易復原模型 等同於舊版的 trunc. log on chkpt. 選項 當 checkpoint 發生時，自動將已經完成交易的資料寫入資料庫

檔案及檔案群組備份 適用於超大型資料庫 針對資料庫檔案分別備份 要確定每個資料庫檔案都有備份 搭配交易記錄檔備份，確保資料的一致性 資料表和所屬的索引要一起備份 BACKUP DATABASE Phoneorders FILE = Orders2 TO OrderBackup2 BACKUP LOG PhoneOrders to OrderLog

 備份策略的規劃 完整備份的策略 交易記錄檔備份的策略 差異備份的策略 檔案及檔案群組備份的策略 備份的效能考量

完整備份的策略 建立資料庫 和完整備份 完整備份 完整備份 Sunday Monday Tuesday Data Log Data Log

交易記錄檔備份的策略 Sunday Monday 完整備份 Log Data 交易記錄檔備份

差異備份的策略 Monday Tuesday 差異備份 ... Log Data  完整備份 交易記錄檔備份

以交易記錄檔備份還原  差異備份 完整備份 交易記錄檔備份 Monday Tuesday ... BACKUP LOG Northwind Data  完整備份 交易記錄檔備份 BACKUP LOG Northwind TO NwindBackLog WITH NO_TRUNCATE RESTORE DATABASE Northwind FROM NwindBac WITH NORECOVERY FROM NwindDiff WITH NORECOVERY RESTORE LOG Northwind FROM NwindBacLog WITH FILE = 1, NORECOVERY WITH FILE = 2, NORECOVERY WITH FILE = 3, RECOVERY

檔案及檔案群組備份的策略 交易記錄檔 備份 檔案 備份 完整備份 Monday Tuesday Wednesday Thursday Data File 1 Data File 3 Data File 2 Log Data 檔案 備份 完整備份 交易記錄檔 備份

指定還原的時間點  差異備份 完整備份 交易記錄檔備份 USE master RESTORE LOG Northwind Monday Tuesday 差異備份 ... Log Data  完整備份 交易記錄檔備份 USE master RESTORE LOG Northwind FROM NwindBacLog WITH FILE = 3, RECOVERY, STOPAT = 'January 3, 2000 1:30 AM'

備份的效能考量 利用多個備份裝置同時執行備份 備份裝置的類型會決定備份執行的速度 指定備份排程在系統非尖峰時段

 還原簡介 還原過程 確認備份的內容 執行備份前的準備事項

還原過程 執行安全檢查 資料庫是否已經存在 資料庫檔案是否不同 資料庫檔案是否不完整 重建資料庫和所有相關檔案

確認備份的內容 RESTORE HEADERONLY RESTORE FILELISTONLY RESTORE LABELONLY 回存特定備份裝置上所有備份集的摘要資訊 RESTORE FILELISTONLY 包含在備份集中的資料庫檔案 RESTORE LABELONLY 傳回特定備份所包含備份媒體的資訊 RESTORE VERIFYONLY 驗證備份但不還原備份 檢查備份集是否完整以及所有媒體是否可讀取 但不會嘗試驗證備份媒體所含資料的結構

執行備份前的準備事項 限制資料庫存取 備份交易記錄檔 只允許 db_owner、dbcreator 或是 sysadmin 角色的成員可以存取資料庫 備份交易記錄檔 確保資料庫的一致性 如果交易記錄檔尚未毀損，利用 BACKUP LOG WITH NO_TRUNCATE ，備份自從最後一次交易記錄檔備份之後的所有資料

 如何進行還原 RESTORE 陳述式 指定復原選項 常用的 RESTORE 選項

RESTORE 陳述式 還原毀損的使用者資料庫 不需要先刪除毀損的資料庫 自動重建資料、資料庫物件和相關檔案 USE master RESTORE DATABASE Northwind FROM NwindBac

指定復原選項 指定 RECOVERY 選項 指定 NORECOVERY 選項 用於最後一份的資料庫備份，後面沒有需要再還原其他備份時 允許使用者可以存取資料庫 指定 NORECOVERY 選項 用於還原最後一份的資料庫備份之前的所有備份 不允許使用者存取資料庫 RECOVERY NORECOVERY NORECOVERY

常用的 RESTORE 選項 FILE MOVE … TO … REPLACE RESTART 指定欲還原的備份集 指定資料庫檔案要移動至新的位置 將資料庫複製到不同的伺服器 REPLACE 即使已有相同名稱的資料庫，也會建立指定的資料庫與其相關檔案，將現存的資料庫刪除 RESTART 指定在還原作業中斷處重新啟動還原 僅適用於磁帶媒體所導向的還原，以及連結多重磁帶媒體的還原

WITH STOPATMARK WITH PARTIAL, MOVE…TO 特殊的還原選項 WITH STOPATMARK WITH PARTIAL, MOVE…TO

還原毀損的系統資料庫 如果 MSSQLSERVER 服務可以正常啟動 如果 MSSQLSERVER 服務無法啟動 master 資料庫正常，但其他系統資料庫有問題，以系統資料庫的備份進行還原 如果 MSSQLSERVER 服務無法啟動 master 資料庫不正常，先用 rebuildm 重建所有的系統資料庫 以參數 –m 啟動 MSSQLSERVER 服務 用 master 資料庫的備份進行還原 正常重新啟動 MSSQLSERVER 服務（不用任何參數） 視需要還原 msdb、model、distribution 資料庫 附加或還原所有的使用者資料庫

結論 高可用度簡介 如何做好高可用度 叢集服務—虛擬伺服器 交易記錄檔傳送 複寫 備份與還原策略

如何做好完善的 SQL Server 2000 資料庫系統備援 與提昇高可用度 Q & A