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

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

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

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

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

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

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

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

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

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

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

10 整合後的挑戰--效能 單一伺服器的效能負荷增加 提升效能的新功能 處理器：記憶體配置 磁碟系統 tempdb資料庫 資源管理員 資料分割
每個處理器核心分配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 OLTP Workload Report Workload Min Memory 10% Max Memory 20% Max CPU 20% Admin Pool Max CPU 90% Application Pool

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

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

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

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

20 原則評估後進行套用

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

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

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

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

25 伺服器現況資訊 系統基本資訊 處理器效能資訊 記憶體效能資訊 磁碟配置與效能資訊 SQL Server伺服器選項資訊 硬體規格 軟體版本
應用程式... 處理器效能資訊 記憶體效能資訊 磁碟配置與效能資訊 磁碟數量、磁碟容量、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) 是否有相同的登入帳戶？ 是否有跨網域存取？網域之間的信任關係？ 使用者帳戶 guest 是否啟用？
sp_helplogins 是否有跨網域存取？網域之間的信任關係？ 使用者帳戶 guest 是否啟用？ 登入帳戶是否有管理者的權利？ 資料庫角色 public 的權限？ 是否有特定的登錄檔需要指定特殊的權限？ 是否需要為擴充預存程序指定特殊的權限？ xp_cmdshell...

31 安全性檢查清單(II) SQL Server 相關服務的登入帳戶是？ 用戶端連線使用何種驗證方式？ 收集資料庫選項的設定
Windows驗證或SQL Server驗證 收集資料庫選項的設定 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..sysmessages where error >= 50000 SQL Server Mail / Agent Mail 設定選項

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

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

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

37 伺服器硬體資源使用率 (以處理器為例) 分組等級 % Processor Time (平均) % Processor Time (最大) 低
≤5 ≤20 中 ≤50 高 >20 >50

38 Tempdb資料庫之規劃 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 儲存設備夥伴解決方案 資料庫容錯 (本地／異地)
3/22/ :28 AM 可用性綜合應用 資料庫鏡像 資料庫容錯 (本地／異地) 交易記錄檔傳送 資料庫容錯 (多個備援主機) 資料錯誤復原 複寫 報表資料庫 (多主機) 查詢橫向擴充兼資料容錯 叢集 本地伺服器容錯 Always On 儲存設備夥伴解決方案 儲存設備高可用度 (異地) 複寫 查詢/ 報表 資料庫鏡像 叢集 熱備援 正式 資料庫 交易紀錄檔傳送 暖備援 資料 錯誤 復原 交易紀錄檔傳送 (延遲還原) 備份 © 2006 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

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

41 備份壓縮實測案例 縮短備份時間與減少儲存空間
備份方式 備份檔案大小 備份時間 A 未壓縮，1 個備份檔案 32.8 GB * 1 1524 秒 (00:25:24) B 未壓縮，4 個備份檔案 8.2 GB * 4 1020 秒 (00:17:00) C 壓縮，1 個備份檔案 5.1 GB * 1 417 秒 (00:06:57) D 壓縮，4 個備份檔案 1.3 GB * 4 392 秒 (00:06:32) 執行時間 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 案例一：多個主機，多個執行個體 整合前：三部獨立的SQL Server主機 整合後：二組容錯叢集 考慮提高系統可用性，建立容錯叢集
SQL Server A，三年內資料庫 > 200GB SQL Server B，三年內資料庫 < 10GB SQL Server C，三年內資料庫 < 150GB 整合後：二組容錯叢集 第一組採用Active-Active 二部主機各自執行一個主要的SQL Server執行個體 互為另一部主機的SQL Server執行個體的備援主機 第二組採用Active-Passive 僅安裝一個SQL Server執行個體

44 案例一：多個主機，多個執行個體 第一組(A+B) 採用Active-Active 第二組(C)採用Active-Passive
三年內資料 庫成長為 整合前 %Processor Time (max) 整合前 Available MBytes 整合前 Avg. Disk Queue Length SQL Server A > 200GB > 70% > 2GB < 2 SQL Server B < 10GB > 1GB SQL Server C < 150GB < 50% < 2GB > 20 第一組(A+B) 採用Active-Active 整合前先進行效能調校，降低處理器負荷 設定每個執行個體最大記憶體之限制 第二組(C)採用Active-Passive 整合前先進行效能調校，降低磁碟負荷

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

46 案例二：整合前硬體資源分析(External)
Process (sqlservr)\% Processor Time (max) Target Server Memory (GB ) Buffer cache hit ratio (min) tempdb Size (MB) Avg. Disk Read Queue Length (max) Avg. Disk Write Queue Length (max) EX_SQL1 190.93 1.67 29.52 1931 208.53 EX_SQL2 144.06 4.21 96.01 752 27.16 151.83 EX_SQL3 465.83 3.16 88.31 1001 177.38 397.89 EX_SQL4 423.72 84.63 993 180.53 EX_SQL5 244.37 91.30 87 282.23 342.99 EX_SQL6 659.09 4.20 87.73 1650 428.91 EX_SQL7 362.01 89.35 362 112.01 807.30 Total 23.76 4845 硬體主機 4P*8Cores 64GB Storage建議 執行個體對照表 整合理由 AP 效能 Internal/External 其他非SQL Server的資料庫: AS400 DB2 (Windows Server 2008 R2 supported?) COM+ MSDTC RPC random port

47 案例二：整合前硬體資源分析(Internal)
Process (sqlservr)\% Processor Time (max) Target Server Memory (GB ) Buffer cache hit ratio (min) tempdb Size (MB) Avg. Disk Read Queue Length (max) Avg. Disk Write Queue Length (max) IN_SQL1 798.63 2.7 96.74 110 50.27 543.88 IN_SQL2 393.85 79.06 516 66.92 IN_SQL3 271.04 6.3 87.32 8 82.54 37.60 IN_SQL4 515.42 4.2 85.03 424 153.25 55.56 IN_SQL5 311.35 3.2 96.00 1774 69.68 178.84 IN_SQL6 108.74 0.7 8.05 93 59.29 170.88 IN_SQL7 259.58 96.49 7384 39.15 53.81 IN_SQL8 217.71 1.7 90.12 9 64.07 115.01 IN_SQL9 97.71 0.6 45.18 469 48.38 Total 28.30 10676 硬體主機 4P*8Cores 64GB Storage建議 執行個體對照表 整合理由 AP 效能 Internal/External 其他非SQL Server的資料庫: AS400 DB2 (Windows Server 2008 R2 supported?) COM+ MSDTC RPC random port

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

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

50 案例二：整合採二階段完成 階段一 階段二 一部主機安裝一個執行個體 一部主機安裝多個虛擬主機 繼續維持伺服器之間的複寫架構
無須大幅度修改應用程式，僅須更改連線字串 階段二 一部主機安裝一個執行個體 撰寫新的應用程式系統，取代既有的應用程式系統 採用集中式資料庫架構，去除資料庫之間的複寫架構

51 案例二：整合階段效益 階段一 階段二 一部主機安裝多個虛擬主機 一部主機安裝一個執行個體 降低硬體設備費用
彈性調整硬體資源配置、提升硬體資源使用率 整合移轉時程縮短 階段二 一部主機安裝一個執行個體 降低軟體授權費用 資料庫集中管理，減少儲存空間需求 簡化資料庫管理

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