Maintenance of MS SQL Database (vCentre)

Index Fragmentation

The vCenter database's indexes become fragmented over time, this is especially true of the performance statistic history tables, even if you have a Daily Maintenance Plan setup.

Show Index Fragmentation

Run the script below in order to show fragmented tables, note that you need to replace the name of your vCenter database.

USE VCDB                                                                          // Update with your database name
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 60 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Defragment Indexes

To defragment an individual table index use the following...

USE VCDB                                                                          // Update with your database name
ALTER IndexName ON TableName REORGANIZE

so for example

USE VCDB                                                                          // Update with your database name
ALTER INDEX PK_VPX_HIST_STAT4_106 ON VPX_HIST_STAT4_106 REORGANIZE

For tables without an index (shows NULL in IndexName in the results), rebuild the whole table, so for example

USE VCDB                                                                          // Update with your database name
ALTER TABLE VPX_STAT_COUNTER REBUILD

To generate a list of commands to run, use the following...

USE VCDB                                                                          // Update with your database name
SELECT 'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REORGANIZE' 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 60 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Pre SQL 2008

Summary of maintenance info from http://www.vmware.com/files/pdf/vc_microsoft_sql_server.pdf

Show Index Fragmentation

This maintenance procedure can be performed inline (ie without stopping the DB or VirtualCenter)

1. Log into the SQL database using Query Analyser
   1. Run up SQL Query Analyzer, and log into the SQL server
2. Perform query to check the level of fragmentation (may take 2 mins or so to run). Create and execute the following query;
   • USE DB (replace DB with whatever the database is called)
   • GO
   • DBCC SHOWCONTIG (VPX_HIST_STAT,VPXII_HIST_STAT)
   • GO
3. Check the query results for either of the following bad conditions
   • Scan Density < 90%
   • Logical Scan Fragmentation > 10%
4. If required, perform defragmentation by running the following query (will take some time depending on level of fragmentation)
   • DBCC INDEXDEFRAG ('DB', 'VPX_HIST_STAT', 'VPXII_HIST_STAT')
   • GO
5. Reheck the level of fragmentation using the following query (if Logical Scan Fragmentation is still > 30%, then may need to do reindex)
   • DBCC SHOWCONTIG (VPX_HIST_STAT,VPXII_HIST_STAT)
   • GO
6. Update the statistics to continue providing accurate metrics, use the following query;
   • UPDATE STATISTICS VPX_HIST_STAT WITH FULLSCAN
   • GO

You may occasionally encounter a deadlock issue that causes the INDEXDEFRAG session to terminate. If the session terminates in this way, there is no adverse impact on the database, and the database continues running normally. You can issue the INDEXDEFRAG command again and the operation will resume from where it left off.

Index Reindexing

This maintenance procedure can only be performed with the database offline, the VirtualCenter Server Service must be shutdown

1. Perform query to check the level of fragmentation (may take 2 mins or so to run). Create and execute the following query;
   • USE DB
   • GO
   • DBCC SHOWCONTIG (VPX_HIST_STAT,VPXII_HIST_STAT)
   • GO
2. Check the query results for either of the following bad conditions
   • Scan Density < 70%
   • Logical Scan Fragmentation > 30%
3. Stop the VirtualCenter Server Service
4. Reindex the table by running this query;
   • DBCC DBREINDEX ('VPX_HIST_STAT', , 70)
   • GO
5. Once complete, restart the VirtualCenter Server Service

Migrate SQL2000 to SQL2005

This should be fairly painless (so long as your DBA knows his stuff!).

Before you start, you must ensure the Virtual Centre is using a SQL Native Client driver to access the database (see VMware KB1003391)...

1. Open up ODBC Data Source Administrator, and go to the System DSN tab
2. Check that the driver you're using is SQL Native Client
3. If not, you'll need to upgrade...
   1. Download from Microsoft and install SQL (see the VMware KB link above)
   2. Stop your Virtual Centre Server service
   3. Rename your existing DSN (eg append "old" to it)
   4. Create a new DSN using the new driver but otherwise exactly the same as the previous (the name MUST be the same)
   5. Restart the Virtual Centre Server service
   6. Login using the VI Client and ensure everything looks good (clusters, ESX's, VM's, Alarms, Guest Customisations etc)

To complete the migration...

1. Stop the Virtual Centre Server service
2. Backup the database (and log files) from your old SQL 2000 server
3. Restore the database (and log files) to your new SQL 2005 server
4. Ensure that your Virtual Centre's database account is the db_owner of the Virtual Centre database on SQL 2005 (as it was on the SQL2000 database)
5. Change the ODBC DSN settings on the VC server so it points to the SQL 2005 server (just change the server name, if you need to specify a non-standard TCP port use <SERVER>, <PORT> rather than <SERVER>:<PORT>)
6. Restart the Virtual Centre Server service
7. Login using the VI Client and ensure everything looks good (clusters, ESX's, VM's, Alarms, Guest Customisations etc)

Other Tasks

Its also possible to delete old data from the database, this is achieved by running a script against the database, see http://kb.vmware.com/kb/1000125 for further info.