Transcript Microsoft SQL Server Consolidation

DBA必知:
如何成功進行資料庫系統整合
以Microsoft SQL Server 2008為例
台灣微軟 特約顧問
許致學
如何成功進行資料庫系統整合
以Microsoft SQL Server 2008為例
• 整合的好處
• 整合的策略
– 單一執行個體，多個資料庫
– 單一主機，多個執行個體
– 單一實體主機，多個虛擬主機
• 整合前的資訊收集
– 商業需求
– 效能瓶頸與需求
– 安全性與可用性
• 整合的規畫考量
• 整合的案例分享
整合的好處
• 商業環境面臨的挑戰
• 整合的驅動力量
• 整合的效益
• 整合的好時機
• 常見的整合阻力
• 整合後的挑戰
• 評估合適的整合方案
商業環境面臨的挑戰
• 為何會建置多部SQL Server主機
– 因為硬體效能、網路頻寬或應用程式需求
• 多部SQL Server主機衍生的困擾
–
–
–
–
–
增加管理複雜性
硬體設備與能源耗用費用
整體的硬體資源使用率偏低
軟體授權費用
缺乏可用性、安全性完整規劃
• 軟體生命週期
整合的驅動力量
硬體資源
使用率低
管理人員
負擔沉重
1990
2000
2010
整合的效益
• 減少資本支出
– 軟體／硬體升級
•
•
•
•
減少硬體數量
減少機房空間需求
減少電力需求
降低機房溫度
• 減少作業支出
– 提高硬體資源使用率
– 改善管理效率
– 降低複雜性
• 基礎架構的靈活度
– 負載平衡與動態調整
– 伺服器的標準化
降低管理
降低能源
複雜度
耗用
21%
18%
降低軟體
降低機房
空間需求
提高硬體
授權費用
18%
利用率與
18%
降低硬體
成本
25%
整合的好時機
• 降低成本
– 硬體設備、能源耗用、軟體授權、人事成本
• 硬體汰換升級
– 硬體使用年限不穩定停機風險
– 新主機硬體可應付更大的負荷
• 軟體版本已停止技術支援
– 新功能管理更容易，提升效能、安全性、可用性
• 因應企業新需求導入新系統
– 重新檢視既有架構
常見的整合阻力
• 應用程式
– 委外開發：廠商支援不佳、或無法升級
– 自行開發：文件不完整、開發人員世代交替
• 資料安全性
– 人事資料、財會資料、研發資料...
• 最佳的因應策略
– 主管的魄力
– 訂定為重要的績效目標
– 以新的應用系統取代舊有系統
整合後的挑戰--高可用性
• 單一伺服器的停機成本增加
• 計畫性停機與非計畫性停機影響程度更大
• 適當的高可用性規劃
– 熱備援：Windows 容錯叢集、資料庫鏡像
– 暖備援：交易記錄檔傳送、點對點交易式複寫
– 冷備援：備份壓縮、資料庫快照
整合後的挑戰--效能
• 單一伺服器的效能負荷增加
– 處理器：記憶體配置
• 每個處理器核心分配4GB~6GB記憶體
– 磁碟系統
• 磁碟容量愈來愈大以硬碟數量提升效能成本增加
– tempdb資料庫
• 一個執行個體只有一個tempdb資料庫
• 提升效能的新功能
–
–
–
–
–
資源管理員
資料分割
資料壓縮
篩選索引
...
整合後的挑戰--管理與安全性
• 單一伺服器的管理與安全性更重要
–
–
–
–
應用程式數量
資料庫數量
帳戶管理
...
• 提升管理與安全性的新功能
–
–
–
–
–
中央管理伺服器
原則管理
稽核
透明資料庫加密
...
評估合適的整合方案
– 應用系統之間的隔離性
• 安全性的隔離性
• 效能資源的隔離性
減少作業支出
• 高可用度系統異常的隔離性
– 應用系統的密集程度
基礎架構的
靈活度
減少資本支出
• 評估的主要因素
– 整合方案完成上線的時程
• 系統管理面的影響
• 效能高效益的資源使用率
• 高可用度規避單點異常的風險
• 整合方案完成需要歷時多久?
• 整合方案可彈性調整與擴充嗎？
整合的策略
單一執行個體
單一主機
單一實體主機
多個資料庫
多個執行個體
多個虛擬主機
資料密度愈高，成本愈低
資料隔離性愈高，成本愈高
單一執行個體，多個資料庫
• 多個資料庫整併於單一執行個體
 硬體設備與軟體授權成本低
 共同的管理與安全性層級
 既有的應用程式可能需要修改
 tempdb資料庫只有一個
 資源分配挑戰大
資源管理員
SQL Server
Backup
OLTP
Activity
Admin
Tasks
• 可區分工作負荷
Executive
Reports
Ad-hoc
Reports
High
Admin Workload
OLTP Workload
Min Memory 10%
Max Memory 20%
Max CPU 20%
Admin Pool
Report Workload
Max CPU 90%
Application Pool
–
–
–
–
應用程式
登入帳號
資料庫
…
• 可限制
–
–
–
–
–
記憶體使用率%
CPU使用率%
CPU使用時間
授與逾時
最大要求數
單一主機，多個執行個體
• 多個執行個體執行於單一主機
 硬體設備低
 硬體資源可依系統負荷進行部分調配
 管理及安全性可部分隔離
 軟體授權成本並未節省
中央管理伺服器
• 中央管理伺服器
– Central Management Servers
• 伺服器群組
• 多伺服器查詢
• 原則管理
階層式伺服器群組
執行多伺服器查詢
中央管理伺服器 + 原則管理
原則評估後進行套用
單一實體主機，多個虛擬主機
• 伺服器硬體數量減少，虛擬主機數量與原本的
SQL Server執行個體數量相同
 提升硬體資源使用率
 資源負載可彈性調配
 可搭配內建的高可用性功能
 軟體可維持原本的版本
• 磁碟系統需要高效能規劃
整合前的資訊收集
• 商業需求
• 技術需求
• 效能瓶頸與需求
• 安全性與可用性
技術需求
•
•
•
•
•
•
•
•
•
訂定整合的基本原則
伺服器現況資訊
系統資料庫檢查清單
msdb資料庫檢查清單
定序和排序的檢查清單
安全性檢查清單
登入帳戶檢查清單
執行個體設定檢查清單
應用程式檢查清單
訂定整合的基本準則
• 通用準則
– 有多少伺服器需要整合？
– 每部伺服器有多少資料庫需要整合？
• Capacity管理
– 每部伺服器的效能現況
– 每部伺服器的資源使用現況
– 資料庫的資料量成長預估
評估伺服器的資源使用率
是否過高或過低？
• 連線方式
– 用戶端如何連接（存取）資料庫？
• 資料庫物件
– 是否有客製化的擴充預存程序（extended stored procedures）？
– 是否有相同名稱的物件？如登入帳戶、資料庫名稱...
• 安全性
– 每部伺服器的安全性原則的標準作業程序
伺服器現況資訊
• 系統基本資訊
– 硬體規格
– 軟體版本
– 應用程式...
• 處理器效能資訊
• 記憶體效能資訊
• 磁碟配置與效能資訊
– 磁碟數量、磁碟容量、RAID等級、效能...
• SQL Server伺服器選項資訊
– 可利用 sp_configure 收集
伺服器效能資料收集
利用logman進行自動化效能監控記錄
系統資料庫檢查清單
系統資料表是否有任何非預設安裝的其他物件？
系統資料表是否有任何非預設的設定？
是否有相同的物件名稱？
檢視每個物件指定的參照名稱，如路徑名稱、伺服器名
稱、作業名稱...
• 搜尋在tempdb資料庫之中是否有非預設的物件？
•
•
•
•
msdb資料庫檢查清單
•
•
•
•
警示
作業
操作員
DTS / SSIS 封裝
– 32-bit 資料來源/ 64-bit 資料來源
•
•
•
•
複寫
交易記錄檔傳送
SQL Server Mail / Agent Mail 的設定
使用者自訂物件
– 預存程序、使用者自訂函數...
定序和排序的檢查清單
• 執行個體與資料庫的定序和排序設定
• 是否有任何物件與執行個體的定序和排序設定不
同？
• SQL Server驗證的登入帳戶的密碼
– 安裝為不分大小寫的 SQL Server 2000 執行個體，會先
轉換為大寫後再儲存與使用
– 安裝為區分大小寫的 SQL Server 2000 執行個體，則不
會轉換為大寫
– SQL Server 2005 執行個體無論區分或不分大小寫，都
不會轉換為大寫
安全性檢查清單(I)
• 是否有相同的登入帳戶？
– sp_helplogins
•
•
•
•
•
•
是否有跨網域存取？網域之間的信任關係？
使用者帳戶 guest 是否啟用？
登入帳戶是否有管理者的權利？
資料庫角色 public 的權限？
是否有特定的登錄檔需要指定特殊的權限？
是否需要為擴充預存程序指定特殊的權限？
– xp_cmdshell...
安全性檢查清單(II)
• SQL Server 相關服務的登入帳戶是？
• 用戶端連線使用何種驗證方式？
– Windows驗證或SQL Server驗證
• 收集資料庫選項的設定
– sp_dboption
• 移轉加密後的密碼至新的伺服器
• sysadmin所有的登入帳戶
登入帳戶檢查清單
• 相同登入帳戶名稱在不同的執行個體可能有不同
的密碼
– 整合前必須先將密碼改為一致
• 為提升安全性，只給使用者必須的權限
• BUILTIN\ADMINISTRATORS登入帳戶
– 是否為sysadmin伺服器角色的成員
• 是否有停用或已失效的登入帳戶？
• 是否有應用程式以sa登入帳戶存取？
• 移轉登入帳戶和使用者帳戶
– 規劃、測試、實作
執行個體設定檢查清單
• 安裝執行個體之後是否有變更為非預設的選項
• 是否啟用 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 設定選項
應用程式檢查清單
• SQL Server的主機名稱與執行個體名稱
• 連線字串是否容易更改
– 包括 COM 物件與 ODBC DSN 之設定
•
•
•
•
•
是否有TCP/IP之外的連線方式
應用程式參數是否容易更改
應用程式開發廠商是否支援
應用程式產生的效能負荷
網域與相關資源的設定
– 信任關係、共享檔案、網路服務...
• 多伺服器查詢
– 連結伺服器、OPENROWSET、OPENQUERY
• 應用程式
• 檢視表、預存程序、使用者自訂函數、觸發程序
移轉登入帳戶與密碼
• SQL Server 2005/2008 執行個體之間移轉登入
帳戶與密碼
– KB 918992
• SQL Server 2000 執行個體 SQL Server
2005/2008 執行個體之間移轉登入帳戶與密碼
– KB 246133
整合的規畫考量
• 效能
– 硬體資源
• 處理器、記憶體、磁碟、網路
– SQL Server
• Tempdb、檔案群組和檔案、分割資料表、資料壓縮、資源管
理員、資料收集、計畫指南
•
•
•
•
可用性
資料庫移轉
安全性
應用程式
伺服器硬體資源使用率 (以處理器為例)
分組等級
% Processor Time (平均)
% Processor Time (最大)
低
≤5
≤20
中
≤20
≤50
高
>20
>50
Tempdb資料庫之規劃
• Tempdb資料庫之用途
– 暫存資料表、子查詢、HASH JOIN、ORDER BY、GROUP BY、
SELECT DISTINCT、快照式交易隔離等級、線上索引維護作業…
• 多個執行個體整合至單一執行個體
– 多個Tempdb資料庫一個Tempdb資料庫
• 大量使用 Tempdb資料庫時之設定
– 確保 Tempdb資料庫有足夠的資料檔大小
– 將 Tempdb資料庫的資料檔指定至不同的磁碟組
– 如果是 SQL Server 有多個 CPU 時，建議 Tempdb資料庫的
資料檔個數與 CPU 核心數相同
• MS KB328551
– 適當規範應用程式撰寫方式，Tempdb資料庫減少使用量
可用性綜合應用
• 資料庫鏡像
– 資料庫容錯 (本地／異地)
複寫
• 交易記錄檔傳送
– 資料庫容錯 (多個備援主機)
– 資料錯誤復原
• 複寫
查詢/ 報表
資料庫鏡像
叢集
熱備援
– 報表資料庫 (多主機)
正式
資料庫
– 查詢橫向擴充兼資料容錯
• 叢集
交易紀錄檔傳送
暖備援
– 本地伺服器容錯
• Always On 儲存設備
夥伴解決方案
– 儲存設備高可用度 (異地)
備份
交易紀錄檔傳送
(延遲還原)
資料
錯誤
復原
使用者資料庫移轉
天
BCP

匯入／匯出精靈
時
資料庫檔案可重新
配置為多個檔案

停
機
時
間
分
資料庫檔案無法重
新配置為多個檔案
卸離／附加
完整備份+差異備份
交易記錄檔傳送

秒
SSIS 工具
移轉SQL Server物件
無需再移轉權限
與遺漏之物件
資料庫鏡像
高效能／高保護
搭配交易式複寫
縮短移轉時間
移轉權限與
遺漏之物件
附加至SQL 2008
後，就無法再附
加回SQL 2005
搭配備份壓縮
縮短移轉時間
執行個體須
相同版本
備份壓縮實測案例
縮短備份時間與減少儲存空間
備份方式
備份檔案大小
備份時間
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.
1. 備份壓縮比例會因資料庫內的資料型態等因
素而有所差異
2. 測試主機 : HP ProLiant DL360 G4p
• CPU: Xeon 3.0GHz Dual-Core * 2
• Memory: 4GB
• HDD: 73GB * 2 10,000 rpm (RAID 0)
整合的案例分享
• 多個主機，多個執行個體
• 升級與整合
– 單一主機，多個虛擬主機  單一執行個體
案例一：多個主機，多個執行個體
• 整合前：三部獨立的SQL Server主機
– 考慮提高系統可用性，建立容錯叢集
• SQL Server A，三年內資料庫 > 200GB
• SQL Server B，三年內資料庫 < 10GB
• SQL Server C，三年內資料庫 < 150GB
• 整合後：二組容錯叢集
– 第一組採用Active-Active
• 二部主機各自執行一個主要的SQL Server執行個體
• 互為另一部主機的SQL Server執行個體的備援主機
– 第二組採用Active-Passive
• 僅安裝一個SQL Server執行個體
案例一：多個主機，多個執行個體
三年內資料 整合前
庫成長為
%Processor
Time (max)
整合前
Available
MBytes
整合前
Avg. Disk
Queue Length
SQL Server A
> 200GB
> 70%
> 2GB
<2
SQL Server B
< 10GB
> 70%
> 1GB
<2
SQL Server C
< 150GB
< 50%
< 2GB
> 20
• 第一組(A+B) 採用Active-Active
– 整合前先進行效能調校，降低處理器負荷
– 設定每個執行個體最大記憶體之限制
• 第二組(C)採用Active-Passive
– 整合前先進行效能調校，降低磁碟負荷
案例二：升級與整合
• 整合前：共有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組容錯叢集
案例二：整合前硬體資源分析(External)
Process
(sqlservr)\%
Processor
Time (max)
Avg. Disk
Avg. Disk
Target
Buffer
tempdb
Read Queue Write Queue
Server
cache hit
Size
Length
Length
Memory ratio (min) (MB)
(max)
(max)
(GB )
EX_SQL1
190.93
1.67
29.52
1931
208.53
17163.84
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
4.21
84.63
993
180.53
1040.34
EX_SQL5
244.37
3.16
91.30
87
282.23
342.99
EX_SQL6
659.09
4.20
87.73
1650
428.91
1740.83
EX_SQL7
362.01
3.16
89.35
362
112.01
807.30
2490.01
23.76
Total
4845
案例二：整合前硬體資源分析(Internal)
Process
(sqlservr)\%
Processor
Time (max)
Avg. Disk
Avg. Disk
Target
Buffer
tempdb
Read Queue Write Queue
Server
cache hit
Size
Length
Length
Memory ratio (min) (MB)
(max)
(max)
(GB )
IN_SQL1
798.63
2.7
96.74
110
50.27
543.88
IN_SQL2
393.85
2.7
79.06
516
66.92
5320.09
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
6.3
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
1927.57
2974.02
28.30
Total
10676
案例二：整合後硬體架構
SQL Server Cluster
External
Group
•
•
•
SQL Server Cluster
Internal
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
案例二：整合前應用程式現況
• 當初因為廣域網路頻寬小，採用分散式架構
– 多個資料庫利用複寫分散至多部遠端主機
• 若全部整合至一個執行個體
– 須大幅度修改應用程式
案例二：整合採二階段完成
• 一部主機安裝多個虛擬主機
階段一
• 繼續維持伺服器之間的複寫架構
• 無須大幅度修改應用程式，僅須更改連線字串
• 一部主機安裝一個執行個體
階段二
• 撰寫新的應用程式系統，取代既有的應用程式系統
• 採用集中式資料庫架構，去除資料庫之間的複寫架構
案例二：整合階段效益
• 一部主機安裝多個虛擬主機
• 降低硬體設備費用
階段一
• 彈性調整硬體資源配置、提升硬體資源使用率
• 整合移轉時程縮短
• 一部主機安裝一個執行個體
• 降低軟體授權費用
階段二
• 資料庫集中管理，減少儲存空間需求
• 簡化資料庫管理
如何成功進行資料庫系統整合
以Microsoft SQL Server 2008為例
• 整合的好處
• 整合的策略
– 單一執行個體，多個資料庫
– 單一主機，多個執行個體
– 單一實體主機，多個虛擬主機
• 整合前的資訊收集
– 商業需求
– 效能瓶頸與需求
– 安全性與可用性
• 整合的規畫考量
• 整合的案例分享