Maintenance of MS SQL Database (vCentre)

SQL Database

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

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.