iT邦幫忙

7

[Day13 - VMware] Purging old data from the vCenter database

透過VMware vCenter可集中管理ESX/ESXi Host及Virtual Machine,並且將登入帳號、執行動作、效能數據等資訊記錄於vCenter Database,長期下來,將造成vCenter Database儲存許多舊資料,並影響vCenter運作。
本文將實作分享如何將vCenter Database的舊資料清除
[Lab Information]
此次實作環境已先安裝vCenter及Database於同一台主機,該主機資訊如下:
OS: <span style="color: red;">Windows Server 2008 R2 Data Center 64 bit</span>
OS account: <span style="color: red;">Administrator</span>
User <span style="color: blue;">Administrator</span> password: <span style="color: red;">12345678</span>
Database: <span style="color: red;">Microsoft SQL Server 2008 R2 64 bit</span>
vCenter version: <span style="color: red;">5.0</span>
vCenter database instance name: <span style="color: red;">VCDB</span>

[Download SQL Statement]
請至VMware Knowledge Base下載SQL Server所使用的<span style="color: red;">VCDB_Purge_MSSQL.zip</span>,若Database為Oracle則請下載<span style="color: red;">VCDB_Purge_ORACLE.zip</span>
下載完畢請執行解壓縮動作

[Backup VC Database]
刪除舊資料前請先對vCenter Database執行完整備份,並確認備份檔案狀態為正常
備份與法可參照下列步驟:

'# DATABASE: VCDB請自行更換 #'
'# DISK: 請自行更換備份路徑及備份檔案名稱 #'
BACKUP DATABASE VCDB TO DISK = 'C:\VCDB20130929.BAK'
GO

[Shutdown vCenter Service]
停止「VMware VirtualCenter Server」服務,步驟如下:

  1. 使用帳號:<span style="color: red;">Administrator</span>登入正在運作中的vCenter Server
  2. 點選『開始』,選擇『執行』
  3. 輸入『services.msc』並確認
  4. 於服務名稱「VMware VirtualCenter Server」點選滑鼠右鍵,選擇『停止』即將vCenter服務停止

[Execute SQL Statement]
使用Microsoft SQL Server Management Studio連線至vCenter Database,並選擇『VCDB』作為執行資料庫
可先執行下列SQL statement觀察每個Table空間使用狀態,執行結果如下圖示:

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    UsedSpaceKB desc


後續開始執行解壓縮完畢的<span style="color: red;">VCDB_table_cleanup_MSSQL.sql</span>,此SQL statement可調整參數如下:

'# 0 = 只統計筆數; 1 = 刪除資料 #'
SET @DELETE_DATA = 0
'# 選擇統計或刪除幾天以前的資料,可自行調整天數 #'
SET @CUTOFF_DATE = GETUTCDATE()-180
'# 可直接指定統計或刪除該日期以前的資料 #'
SET @CUTOFF_DATE = '2007/01/01'
'# 每筆交易可刪除的資料列數 #'
SET @BATCH_SIZE = 10000

<span style="color: red;">執行此SQL statement將產生大量的Transaction Log,將造成磁碟空間迅速成長,故執行前請謹慎評估磁碟空間狀態</span>
可透過下列SQL statement查詢磁碟空間使用狀態:

exec sp_spaceused
dbcc sqlperf (logspace)

[Shrink vCenter Database]
刪除資料完畢後,可執行下列SQL statement壓縮vCenter Database並釋放無使用的磁碟空間

'# VCDB請自行更換 #'
'# 5為壓縮比例,請自行調整 #'
DBCC SHRINKDATABASE ( VCDB , 5);
GO

[Startup vCenter Service]
啓動「VMware VirtualCenter Server」服務,步驟如下:

  1. 使用帳號:<span style="color: red;">Administrator</span>登入正在運作中的vCenter Server
  2. 點選『開始』,選擇『執行』
  3. 輸入『services.msc』並確認
  4. 於服務名稱「VMware VirtualCenter Server」點選滑鼠右鍵,選擇『啓動』即將vCenter服務啓動
  5. 透過vSphere Client確認vCenter可正常連線,且可正常操作

[Reference]
Stopping, starting, or restarting vCenter services
Purging old data from the database used by VMware vCenter Server 4.x and 5.x
Reducing the size of the vCenter Server database when the rollup scripts take a long time to run
Shrinking the size of the vCenter Server SQL database
DBCC SHRINKDATABASE (Transact-SQL)


尚未有邦友留言

立即登入留言