Maintenance of MS SQL Database (vCentre) - Revision history

Sstrutt: Typo fix

2016-11-01T11:13:41Z

Typo fix

← Older revision		Revision as of 11:13, 1 November 2016
Line 1:		Line 1:
	== Index Fragmentation ==		== 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 [[[MS_SQL#Daily_Maintenance_Plan\|Daily Maintenance Plan]] setup.		The vCenter database's indexes become fragmented over time, this is especially true of the performance statistic history tables, even if you have a [[MS_SQL#Daily_Maintenance_Plan\|Daily Maintenance Plan]] setup.

	=== Show Index Fragmentation ===		=== Show Index Fragmentation ===

Sstrutt: /* Defragment Indexes */ Added for HEAP

2016-11-01T10:57:18Z

Defragment Indexes: Added for HEAP

← Older revision		Revision as of 10:57, 1 November 2016
Line 28:		Line 28:
	USE VCDB // Update with your database name		USE VCDB // Update with your database name
	ALTER INDEX PK_VPX_HIST_STAT4_106 ON VPX_HIST_STAT4_106 REORGANIZE		ALTER INDEX PK_VPX_HIST_STAT4_106 ON VPX_HIST_STAT4_106 REORGANIZE
			</source>

			For tables without an index (shows <code>NULL</code> in IndexName in the results), rebuild the whole table, so for example
			<source lang="sql">
			USE VCDB // Update with your database name
			ALTER TABLE VPX_STAT_COUNTER REBUILD
	</source>		</source>

Line 41:		Line 47:
	ORDER BY indexstats.avg_fragmentation_in_percent DESC		ORDER BY indexstats.avg_fragmentation_in_percent DESC
	</source>		</source>


	=== Pre SQL 2008 ===		=== Pre SQL 2008 ===

Sstrutt: Reworked for SQL2008+

2016-11-01T10:45:26Z

Reworked for SQL2008+

@@ Line 1: / Line 1: @@
-== SQL Database ==
+== Index Fragmentation ==
 ''Summary of maintenance info from http://www.vmware.com/files/pdf/vc_microsoft_sql_server.pdf''
-=== Index Fragmentation ===
+==== Show Index Fragmentation ====
 '''This maintenance procedure can be performed inline (ie without stopping the DB or VirtualCenter)'''
 # Log into the SQL database using Query Analyser
@@ Line 26: / Line 70: @@
 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 ===
+==== Index Reindexing ====
 '''This maintenance procedure can only be performed with the database offline, the VirtualCenter Server Service must be shutdown'''
 # Perform query to check the level of fragmentation (may take 2 mins or so to run). Create and execute the following query;
@@ Line 42: / Line 86: @@
 # Once complete, restart the VirtualCenter Server Service
-=== Migrate SQL2000 to SQL2005 ===
+== Migrate SQL2000 to SQL2005 ==
 This should be fairly painless (so long as your DBA knows his stuff!).
@@ Line 65: / Line 109: @@
 # Login using the VI Client and ensure everything looks good (clusters, ESX's, VM's, Alarms, Guest Customisations etc)
-===Other Tasks===
+==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.

Sstrutt: Sstrutt moved page Maintenance (vCentre) to Maintenance of MS SQL Database (vCentre)

2016-11-01T09:59:49Z

Sstrutt moved page Maintenance (vCentre) to Maintenance of MS SQL Database (vCentre)

← Older revision	Revision as of 09:59, 1 November 2016
(No difference)

Sstrutt: Sstrutt moved page Maintenance (Virtual Centre) to Maintenance (vCentre) without leaving a redirect: Incorrect titke

2013-01-15T13:42:35Z

Sstrutt moved page Maintenance (Virtual Centre) to Maintenance (vCentre) without leaving a redirect: Incorrect titke

← Older revision	Revision as of 13:42, 15 January 2013
(No difference)

Sstrutt: Initial creation - content from vCentre page

2013-01-15T12:05:59Z

Initial creation - content from vCentre page

New page

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

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'''
# Perform query to check the level of fragmentation (may take 2 mins or so to run). Create and execute the following query;
#* <code> USE DB </code>
#* <code> GO </code>
#* <code> DBCC SHOWCONTIG (VPX_HIST_STAT,VPXII_HIST_STAT) </code>
#* <code> GO </code>
# Check the query results for either of the following bad conditions
#* Scan Density < 70%
#* Logical Scan Fragmentation > 30%
# Stop the VirtualCenter Server Service
# Reindex the table by running this query;
#* <code> DBCC DBREINDEX ('VPX_HIST_STAT', '', 70) </code>
#* <code> GO </code>
# 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 [http://kb.vmware.com/kb/1003391 VMware KB1003391])...
# Open up '''ODBC Data Source Administrator''', and go to the '''System DSN''' tab
# Check that the driver you're using is '''SQL Native Client'''
# If not, you'll need to upgrade...
## Download from Microsoft and install SQL (see the VMware KB link above)
## Stop your Virtual Centre Server service
## Rename your existing DSN (eg append "old" to it)
## Create a new DSN using the new driver but otherwise exactly the same as the previous (the name MUST be the same)
## Restart the Virtual Centre Server service
## Login using the VI Client and ensure everything looks good (clusters, ESX's, VM's, Alarms, Guest Customisations etc)

'''To complete the migration...'''
# Stop the Virtual Centre Server service
# Backup the database (and log files) from your old SQL 2000 server
# Restore the database (and log files) to your new SQL 2005 server
# 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)
# 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 <code><SERVER>, <PORT></code> rather than <code><SERVER>:<PORT></code>)
# Restart the Virtual Centre Server service
# 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.

[[Category:VMware]]
[[Category:vCentre]]
[[Category: MS SQL]]