DBA 必知 : 如何成功進行資料庫系統整合 以 Microsoft SQL Server 2008 為例

1. DBA必知:如何成功進行資料庫系統整合以Microsoft SQL Server 2008為例 台灣微軟 特約顧問 許致學

2. 如何成功進行資料庫系統整合以Microsoft SQL Server 2008為例 • 整合的好處 • 整合的策略 • 單一執行個體，多個資料庫 • 單一主機，多個執行個體 • 單一實體主機，多個虛擬主機 • 整合前的資訊收集 • 商業需求 • 效能瓶頸與需求 • 安全性與可用性 • 整合的規畫考量 • 整合的案例分享

3. 整合的好處 • 商業環境面臨的挑戰 • 整合的驅動力量 • 整合的效益 • 整合的好時機 • 常見的整合阻力 • 整合後的挑戰 • 評估合適的整合方案

4. 商業環境面臨的挑戰 • 為何會建置多部SQLServer主機 • 因為硬體效能、網路頻寬或應用程式需求 • 多部SQLServer主機衍生的困擾 • 增加管理複雜性 • 硬體設備與能源耗用費用 • 整體的硬體資源使用率偏低 • 軟體授權費用 • 缺乏可用性、安全性完整規劃 • 軟體生命週期

5. 整合的驅動力量 硬體資源使用率低 硬體處理能力 資料庫／應用程式的數量 管理人員負擔沉重 DBA的數量 1990 2000 2010

6. 整合的效益 • 減少資本支出 • 軟體／硬體升級 • 減少硬體數量 • 減少機房空間需求 • 減少電力需求 • 降低機房溫度 • 減少作業支出 • 提高硬體資源使用率 • 改善管理效率 • 降低複雜性 • 基礎架構的靈活度 • 負載平衡與動態調整 • 伺服器的標準化

7. 整合的好時機 • 降低成本 • 硬體設備、能源耗用、軟體授權、人事成本 • 硬體汰換升級 • 硬體使用年限不穩定停機風險 • 新主機硬體可應付更大的負荷 • 軟體版本已停止技術支援 • 新功能管理更容易，提升效能、安全性、可用性 • 因應企業新需求導入新系統 • 重新檢視既有架構

8. 常見的整合阻力 • 應用程式 • 委外開發：廠商支援不佳、或無法升級 • 自行開發：文件不完整、開發人員世代交替 • 資料安全性 • 人事資料、財會資料、研發資料... • 最佳的因應策略 • 主管的魄力 • 訂定為重要的績效目標 • 以新的應用系統取代舊有系統

9. 整合後的挑戰--高可用性 • 單一伺服器的停機成本增加 • 計畫性停機與非計畫性停機影響程度更大 • 適當的高可用性規劃 • 熱備援：Windows 容錯叢集、資料庫鏡像 • 暖備援：交易記錄檔傳送、點對點交易式複寫 • 冷備援：備份壓縮、資料庫快照

10. 整合後的挑戰--效能 • 單一伺服器的效能負荷增加 • 處理器：記憶體配置 • 每個處理器核心分配4GB~6GB記憶體 • 磁碟系統 • 磁碟容量愈來愈大以硬碟數量提升效能成本增加 • tempdb資料庫 • 一個執行個體只有一個tempdb資料庫 • 提升效能的新功能 • 資源管理員 • 資料分割 • 資料壓縮 • 篩選索引 • ...

11. 整合後的挑戰--管理與安全性 • 單一伺服器的管理與安全性更重要 • 應用程式數量 • 資料庫數量 • 帳戶管理 • ... • 提升管理與安全性的新功能 • 中央管理伺服器 • 原則管理 • 稽核 • 透明資料庫加密 • ...

12. 評估合適的整合方案 • 評估的主要因素 • 應用系統之間的隔離性 • 安全性的隔離性 • 效能資源的隔離性 • 高可用度系統異常的隔離性 • 應用系統的密集程度 • 系統管理面的影響 • 效能高效益的資源使用率 • 高可用度規避單點異常的風險 • 整合方案完成上線的時程 • 整合方案完成需要歷時多久? • 整合方案可彈性調整與擴充嗎？ 減少資本支出 減少作業支出 基礎架構的靈活度

13. 整合的策略 資料密度愈高，成本愈低 資料隔離性愈高，成本愈高

14. 單一執行個體，多個資料庫 • 多個資料庫整併於單一執行個體 • 硬體設備與軟體授權成本低 • 共同的管理與安全性層級 • 既有的應用程式可能需要修改 • tempdb資料庫只有一個 • 資源分配挑戰大

15. 資源管理員 SQL Server • 可區分工作負荷 • 應用程式 • 登入帳號 • 資料庫 • … • 可限制 • 記憶體使用率% • CPU使用率% • CPU使用時間 • 授與逾時 • 最大要求數 Backup OLTP Activity Executive Reports Admin Tasks Ad-hoc Reports High Admin Workload Report Workload OLTP Workload Min Memory 10% Max Memory 20% Max CPU 20% Max CPU 90% Admin Pool Application Pool

16. 單一主機，多個執行個體 • 多個執行個體執行於單一主機 • 硬體設備低 • 硬體資源可依系統負荷進行部分調配 • 管理及安全性可部分隔離 • 軟體授權成本並未節省

17. 中央管理伺服器 • 中央管理伺服器 • Central Management Servers • 伺服器群組 • 多伺服器查詢 • 原則管理

18. 階層式伺服器群組執行多伺服器查詢

19. 中央管理伺服器 + 原則管理

20. 原則評估後進行套用

21. 單一實體主機，多個虛擬主機 • 伺服器硬體數量減少，虛擬主機數量與原本的SQLServer執行個體數量相同 • 提升硬體資源使用率 • 資源負載可彈性調配 • 可搭配內建的高可用性功能 • 軟體可維持原本的版本 • 磁碟系統需要高效能規劃

22. 整合前的資訊收集 • 商業需求 • 技術需求 • 效能瓶頸與需求 • 安全性與可用性

23. 技術需求 • 訂定整合的基本原則 • 伺服器現況資訊 • 系統資料庫檢查清單 • msdb資料庫檢查清單 • 定序和排序的檢查清單 • 安全性檢查清單 • 登入帳戶檢查清單 • 執行個體設定檢查清單 • 應用程式檢查清單

24. 訂定整合的基本準則 • 通用準則 • 有多少伺服器需要整合？ • 每部伺服器有多少資料庫需要整合？ • Capacity管理 • 每部伺服器的效能現況 • 每部伺服器的資源使用現況 • 資料庫的資料量成長預估 • 連線方式 • 用戶端如何連接（存取）資料庫？ • 資料庫物件 • 是否有客製化的擴充預存程序（extended stored procedures）？ • 是否有相同名稱的物件？如登入帳戶、資料庫名稱... • 安全性 • 每部伺服器的安全性原則的標準作業程序 評估伺服器的資源使用率是否過高或過低？

25. 伺服器現況資訊 • 系統基本資訊 • 硬體規格 • 軟體版本 • 應用程式... • 處理器效能資訊 • 記憶體效能資訊 • 磁碟配置與效能資訊 • 磁碟數量、磁碟容量、RAID等級、效能... • SQL Server伺服器選項資訊 • 可利用sp_configure收集

26. 伺服器效能資料收集 利用logman進行自動化效能監控記錄

27. 系統資料庫檢查清單 • 系統資料表是否有任何非預設安裝的其他物件？ • 系統資料表是否有任何非預設的設定？ • 是否有相同的物件名稱？ • 檢視每個物件指定的參照名稱，如路徑名稱、伺服器名稱、作業名稱... • 搜尋在tempdb資料庫之中是否有非預設的物件？

28. msdb資料庫檢查清單 • 警示 • 作業 • 操作員 • DTS / SSIS 封裝 • 32-bit 資料來源/ 64-bit 資料來源 • 複寫 • 交易記錄檔傳送 • SQL Server Mail / Agent Mail 的設定 • 使用者自訂物件 • 預存程序、使用者自訂函數...

29. 定序和排序的檢查清單 • 執行個體與資料庫的定序和排序設定 • 是否有任何物件與執行個體的定序和排序設定不同？ • SQL Server驗證的登入帳戶的密碼 • 安裝為不分大小寫的 SQL Server 2000 執行個體，會先轉換為大寫後再儲存與使用 • 安裝為區分大小寫的 SQL Server 2000 執行個體，則不會轉換為大寫 • SQL Server 2005 執行個體無論區分或不分大小寫，都不會轉換為大寫

30. 安全性檢查清單(I) • 是否有相同的登入帳戶？ • sp_helplogins • 是否有跨網域存取？網域之間的信任關係？ • 使用者帳戶 guest 是否啟用？ • 登入帳戶是否有管理者的權利？ • 資料庫角色 public 的權限？ • 是否有特定的登錄檔需要指定特殊的權限？ • 是否需要為擴充預存程序指定特殊的權限？ • xp_cmdshell...

31. 安全性檢查清單(II) • SQL Server 相關服務的登入帳戶是？ • 用戶端連線使用何種驗證方式？ • Windows驗證或SQLServer驗證 • 收集資料庫選項的設定 • sp_dboption • 移轉加密後的密碼至新的伺服器 • sysadmin所有的登入帳戶

32. 登入帳戶檢查清單 • 相同登入帳戶名稱在不同的執行個體可能有不同的密碼 • 整合前必須先將密碼改為一致 • 為提升安全性，只給使用者必須的權限 • BUILTIN\ADMINISTRATORS登入帳戶 • 是否為sysadmin伺服器角色的成員 • 是否有停用或已失效的登入帳戶？ • 是否有應用程式以sa登入帳戶存取？ • 移轉登入帳戶和使用者帳戶 • 規劃、測試、實作

33. 執行個體設定檢查清單 • 安裝執行個體之後是否有變更為非預設的選項 • 是否啟用 Windows Fibers 模式(輕量型共用) • XML預存程序與交易記錄檔傳送並不支援 • 是否使用 XML 預存程序與 OLE Automation 物件 • 是否使用其他共用資源 • MS DTC • Microsoft Search service • 每個資料庫執行sp_help_fulltext_catalogs • 是否有自訂錯誤訊息 • select * from master..sysmessageswhere error >= 50000 • SQL Server Mail / Agent Mail 設定選項

34. 應用程式檢查清單 • SQLServer的主機名稱與執行個體名稱 • 連線字串是否容易更改 • 包括 COM 物件與 ODBC DSN 之設定 • 是否有TCP/IP之外的連線方式 • 應用程式參數是否容易更改 • 應用程式開發廠商是否支援 • 應用程式產生的效能負荷 • 網域與相關資源的設定 • 信任關係、共享檔案、網路服務... • 多伺服器查詢 • 連結伺服器、OPENROWSET、OPENQUERY • 應用程式 • 檢視表、預存程序、使用者自訂函數、觸發程序

35. 移轉登入帳戶與密碼 • SQL Server 2005/2008 執行個體之間移轉登入帳戶與密碼 • KB918992 • SQL Server 2000 執行個體 SQL Server 2005/2008 執行個體之間移轉登入帳戶與密碼 • KB 246133

36. 整合的規畫考量 • 效能 • 硬體資源 • 處理器、記憶體、磁碟、網路 • SQLServer • Tempdb、檔案群組和檔案、分割資料表、資料壓縮、資源管理員、資料收集、計畫指南 • 可用性 • 資料庫移轉 • 安全性 • 應用程式

37. 伺服器硬體資源使用率 (以處理器為例)

38. Tempdb資料庫之規劃 • Tempdb資料庫之用途 • 暫存資料表、子查詢、HASH JOIN、ORDER BY、GROUP BY、SELECT DISTINCT、快照式交易隔離等級、線上索引維護作業… • 多個執行個體整合至單一執行個體 • 多個Tempdb資料庫一個Tempdb資料庫 • 大量使用 Tempdb資料庫時之設定 • 確保 Tempdb資料庫有足夠的資料檔大小 • 將 Tempdb資料庫的資料檔指定至不同的磁碟組 • 如果是 SQL Server 有多個 CPU 時，建議 Tempdb資料庫的資料檔個數與 CPU 核心數相同 • MS KB328551 • 適當規範應用程式撰寫方式，Tempdb資料庫減少使用量

39. 資料錯誤復原 可用性綜合應用 • 資料庫鏡像 • 資料庫容錯 (本地／異地) • 交易記錄檔傳送 • 資料庫容錯 (多個備援主機) • 資料錯誤復原 • 複寫 • 報表資料庫 (多主機) • 查詢橫向擴充兼資料容錯 • 叢集 • 本地伺服器容錯 • Always On 儲存設備夥伴解決方案 • 儲存設備高可用度 (異地) 複寫 查詢/ 報表 資料庫鏡像 叢集 熱備援 正式資料庫 交易紀錄檔傳送 暖備援 交易紀錄檔傳送(延遲還原) 備份

40. 使用者資料庫移轉 停機時間 天 時  分  秒 BCP 搭配交易式複寫縮短移轉時間 匯入／匯出精靈 SSIS 工具移轉SQLServer物件 移轉權限與遺漏之物件 資料庫檔案可重新配置為多個檔案 附加至SQL2008後，就無法再附加回SQL2005 卸離／附加 資料庫檔案無法重新配置為多個檔案 完整備份+差異備份 搭配備份壓縮縮短移轉時間 交易記錄檔傳送 執行個體須相同版本 無需再移轉權限與遺漏之物件 資料庫鏡像高效能／高保護

41. 備份壓縮實測案例縮短備份時間與減少儲存空間備份壓縮實測案例縮短備份時間與減少儲存空間 • 執行時間 • B 較 A 節省 33% 的執行時間 • C 較 A 節省 73% 的執行時間 • D 較 A 節省 74% 的執行時間 • D 較 C 節省 6% 的執行時間 • 壓縮比 • C 較 A 節省 84% 的儲存空間 • PS. • 備份壓縮比例會因資料庫內的資料型態等因素而有所差異 • 測試主機 : HP ProLiant DL360 G4p • CPU: Xeon 3.0GHz Dual-Core * 2 • Memory: 4GB • HDD: 73GB * 2 10,000 rpm (RAID 0)

42. 整合的案例分享 • 多個主機，多個執行個體 • 升級與整合 • 單一主機，多個虛擬主機  單一執行個體

43. 案例一：多個主機，多個執行個體 • 整合前：三部獨立的SQLServer主機 • 考慮提高系統可用性，建立容錯叢集 • SQLServerA，三年內資料庫 >200GB • SQLServer B，三年內資料庫 <10GB • SQLServer C，三年內資料庫 <150GB • 整合後：二組容錯叢集 • 第一組採用Active-Active • 二部主機各自執行一個主要的SQLServer執行個體 • 互為另一部主機的SQLServer執行個體的備援主機 • 第二組採用Active-Passive • 僅安裝一個SQLServer執行個體

44. 案例一：多個主機，多個執行個體 • 第一組(A+B) 採用Active-Active • 整合前先進行效能調校，降低處理器負荷 • 設定每個執行個體最大記憶體之限制 • 第二組(C)採用Active-Passive • 整合前先進行效能調校，降低磁碟負荷

45. 案例二：升級與整合 • 整合前：共有19部SQL Server 2000主機 • 其中有 8 部主機為容錯叢集 • Active-Active, 2-node * 4 • External7部主機 • Internal 共有12部主機 • 其中3部主機因委外的應用程式考量，且資料庫負荷不大，考慮採用VM方式維持既有環境 • 整合後目標 • SQLServer 2000 升級至 SQLServer 2008 • External：1組容錯叢集 • Internal：1組容錯叢集

46. 案例二：整合前硬體資源分析(External)

47. 案例二：整合前硬體資源分析(Internal)

48. 案例二：整合後硬體架構 • CPU(Cores)=Total Process(sqlservr)\% Processor Time (max) / 0.75 • Memory = Total SQLServer:Memory Manager\Target Server Memory(GB) *1.5 • Disk I/O 大多數主機都很忙碌，規劃時需特別重視磁碟配置 SQLServer ClusterExternal SQL Server ClusterInternal * 新伺服器硬體規格：處理器 8-Core * 4 + 記憶體 96GB

49. 案例二：整合前應用程式現況 • 當初因為廣域網路頻寬小，採用分散式架構 • 多個資料庫利用複寫分散至多部遠端主機 • 若全部整合至一個執行個體 • 須大幅度修改應用程式

50. 案例二：整合採二階段完成