This article explores the options Embotics vCommander administrators have available within Microsoft SQL 2when creating maintenance plans, used to ensure the database is functioning optimally. When looking at SQL performance, it’s important to understand each of these decisions, as a setting appropriate for one environment may not be ideal for another. It's possible that your organization already has some SQL maintenance in place. If that's the case, Embotics still recommends having your database admins review this article to make sure our best practices are followed.  If you’re uncertain of what makes the most sense for you, contact Embotics Technical Support to discuss. 


Important: All of the queries and examples in this article are prepared for a database named vcommander.

The sections below describe particular configuration points and what the impact is of each.

General Configuration


The size of your database is dependent on many factors, and it’s difficult to predict what the database footprint will be. In addition to the other means of managing the storage consumption described in this article, you can also schedule regular maintenance to purge unneeded data inside vCommander. Beyond this and other solutions offered here, the growth of the database should not be restricted. Otherwise, if the maximum allowed space is consumed, vCommander will not continue to function normally. For example, the power state and other changes to VMs will not be synchronized, as no writable space is available to record the observations taken from the managed system.

Autogrowth


To confirm the current autogrowth settings do not restrict the vCommander database:

  1. Connect to the database server using SQL Management Studio.
  2. Expand Databases. Right-click the vcommander database and choose Properties.

    blob1478098020455.png


  3. Select the Files page, and scroll right until you can see the entire Autogrowth / Maxsize column.
  4. Click on each line (one for database file and one for log file) to view the dialog where you can set the autogrowth options.
  5. Set the following for the database file (vcommander.mdf):
    • Enable Autogrowth is checked
    • File Growth > In Megabytes: 1MB
    • Maximum File Size is Unlimited
  6. Set the following for the log file (vcommander_log.ldf):
    • Enable Autogrowth is checked
    • File Growth > In Percent: 10%
    • Maximum File Size is Unlimited
  7. Click OK, and then OK again.

Alternatively,  run the following query to configure the settings as described above:

  

ALTER DATABASE [vcommander] MODIFY FILE ( NAME = 'vcommander', MAXSIZE = UNLIMITED, FILEGROWTH = 1MB );
ALTER DATABASE [vcommander] MODIFY FILE ( NAME = 'vcommander_log', MAXSIZE = UNLIMITED, FILEGROWTH = 10% );

   

Transaction Log Management


Currently Embotics recommends the simple recovery model for the vCommander database. This means that once a transaction is committed to the database, it is purged from the log file. The primary advantage to simple recovery is that far less disk space is consumed by the log file, but if you need to restore from a backup, you introduce the potential for some data loss (events from vCenter can be collected again from a restored vCommander, but event records of user actions in vCommander will be lost).

When the database is set to full recovery model, committed transactions are not purged from the log until a backup has occurred, so you can’t lose any history, but the disk space consumption is significantly higher. Embotics only recommends using full recovery if your operations policy requires a perfect audit trail be maintained, such as when a compliance standard requires this. To manage the log file size, you need to run the backup job very frequently. You may need to tweak how frequently you run the backups until you find what works well in your environment, but one to four backups daily is not unusual for very active environments, and the most active environments may require hourly backups if a small log file is to be maintained.


To confirm or set Recovery model:

 

  1. In the SQL Management Studio Object Explorer, expand databases.
  2. Right-click the vCommander database and choose Properties.
  3. Switch to the Options page. Set the Recovery Model and click OK.

    blob1478098158782.png


Creating the Defragmentation Task


Over time, database indices will become fragmented and lead to reduced performance on all read/write operations. This is especially true in very active environments where there is a lot of inventory churn. To combat this performance degradation over time, run the queries included in this zip file (files also attached to this article below) against the vCommander database. These create tables and a stored procedure used to schedule a defragment task in the maintenance plan, ensuring optimal database performance.


The queries must be run in the following sequence:

  1. CommandLog.sql
  2. CommandExecute.sql
  3. IndexOptimize.sql

 

Next, create a SQL Server Agent Job:


  1. In the Object Explorer, expand SQL Server Agent.
  2. Right-click Jobs and choose New Job...
  3. On the General page, provide the Name Defrag vCommander DB and set the Owner. Typically, the owner will be the vCommander connection user, or the sa account.

    blob1478111229925.png


  4. Switch to the Steps page and click New...
  5. Enter the Step Name Execute Command.
  6. Set Type to Operating system (CmdExec).
  7. Set Run as to SQL Server Agent Service Account.
  8. Enter the following as the Command, replacing vcommander with the name of your vCommander database, then click OK.

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d vcommander -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'vcommander', @LogToTable = 'Y', @TimeLimit = '3600', @UpdateStatistics = 'ALL'" –b

    blob1478111396441.png



  9. Optionally, switch to the Notifications page. Check Write to the Windows Application event log: When the job fails if you are able to monitor the log for failures and wish to do so. Otherwise, configure any other notifcation options as suitable. 
  10. Click OK.

Creating the Maintenance Plan


The rest of the configurations discussed in this article are likely to be scheduled activities as part of a maintenance plan. Embotics recommends that these tasks be scheduled at 03:00 in most environments, so as not to collide with the default vCommander scheduled tasks. If you have scheduled tasks, such as Guest OS scans or reports delivery to be run at this time, choose another low-activity time.

Note: In some environments, you may need to refresh to see objects you just created.


The SQL Server Agent service must be running and set to automatic in order for the maintenance plan to run.

blob1478098376420.png


To create the maintenance plan:

  1. In the Object Explorer pane, expand Management and right-click Maintenance Plans, and select Maintenance Plan Wizard.

    blob1478098559964.png


  2. Optionally check Do not show this starting page again, then click Next.
  3. On the Select Plan Properties page, provide a Name for the plan (such as vCommander Maintenance). Choose the Single schedule for the entire plan or no schedule radio button.

    blob1478099617171.png


  4. Click Change and configure the schedule on the dialog which appears. We recommend a weekly run on Saturday or Sunday night, but you should be looking to run it during your off-peak hours whenever they should occur. 

    blob1478099974663.png

  5. Click OK, then click Next.

  6. On the Select Maintenance Tasks page, check the following and click Next:

    Check Database Integrity
    Execute SQL Server Agent Job
    Backup Database (Full)
    Maintenance Cleanup Task

    blob1478108829745.png

  7. On the Select Maintenance Task Order page, use the Move Up / Move Down controls to set the order as show below, then click Next.

    Execute SQL Server Agent Job
    Check Database Integrity
    Backup Database (Full)
    Maintenance Cleanup Task

    blob1478109161690.png


  8. On the Define Execute SQL Server Agent Job Taskpage, check Defrag vCommander DB and click Next.

    blob1478111674569.png


  9. On the Define Database Check Integrity Task page, click the Databases combo box and select These databases checking the vCommander db from the list. Click OK, then click Next.

    blob1478112484186.png


  10. On the Define Back Up Database (Full) Task page click in the Database(s) combo box, choose the vCommander database and click OK.

    blob1478269259812.png


  11. Under Backup component, choose Database.

  12. If desired, check Backup set will expire and set the number of days.

  13. Choose to Back up to either Disk or Tape, and click Add... to select the file system path or tape to use. Click Next.

  14. On the Define Maintenance Cleanup Task page, under Delete files of the following type choose Backup Files.

    blob1478270006820.png

  15. Under File location choose Search folder and delete files based on an extension and click  blob1478270206698.png to browse to and select the location where your backups are stored.  Enter .bak as the File Extension.

  16. Under File age check Delete files based on the age of the file at task run time and set the age at which files will be deleted. Click Next.

  17. On the Select Report Options page accept the defaults by clicking Next.

  18. Click Finish.

    blob1478270821013.png


Additional Information


Note that the recommendations above do not include shrinking the database, as this simply removes white space from the database which will be recreated for new data. 

To set auto-shrink off, execute the query: 

 

ALTER DATABASE vcommander SET AUTO_SHRINK OFF;


Adding a database integrity check protects you from continuing to operate with a database that’s been corrupted, but because the operation is resource-intensive, Embotics recommends that this only occur during maintenance windows or periods of very low activity.


To run this task manually, execute this query:

 

DBCC CHECKDB ('vcommander') WITH NO_INFOMSGS, ALL_ERRORMSGS;

 

See Also