This article explores the options Embotics® vCommander® administrators have available within Microsoft SQL when 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.

  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.

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.

  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

  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.

To create the maintenance plan:

  1. Under the Tools menu, select Choose Toolbox Items.

  2. Make sure all items are checked, and click OK.
  3. Under the View menu, choose Toolbox.
    Important! It's easy to lose focus on the Toolbox dialog. If you do, it may show up as another tab behind the Object Explorer:

    blob1478022800320.png

  4. Right-click in the Toolbox dialog and choose Show All.

    blob1478023555292.png

  5. Switch back to the Object Explorer pane, expand Management. Right-click Maintenance Plans and choose New Maintenance Plan.

  6. Provide a name for the plan and click OK.
  7. Drag items from the Toolbox over to the plan, where they are represented as tasks. Connect the tasks in order using the green arrow controls.

    blob1478023763900.png

    In the end, if you are including each of the tasks we recommend, the plan will look something like this:

  8. In the maintenance plan tab, double click each task you add to set its specific options. Refer to the sections below for the particulars of configuring each.

    Index Defragmentation and Statistics Updates
    Corruption Detection
    Backup
    History and Maintenance Cleanup

  9. Click  to open the scheduling controls. Set the schedule as per your needs. 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.

  10. Close the maintenance plan pane, choosing to Save when prompted.


Note that the recommendations 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;

  

Index Defragmentation and Statistics Updates


Add a Execute SQL Server Agent Job task to your maintenance plan, selecting this job created earlier.


Corruption Detection


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;

 

Backup


vCommander administrators are required to take regular backups of the database. This is typically done manually prior to making a significant change to the system, such as performing an upgrade or making substantial changes to potentially destructive policies, but best practices for disaster recovery also include regular scheduled backups be taken.


It’s also important to understand the actions of any third-party backups which may exist, so please check with your network administrator or other parties as appropriate.


When configuring the backup task, Embotics recommends nightly full database backups. Preservation of the backups is outside the scope of this maintenance plan, and can be handled at your own discretion.



History and Maintenance Cleanup


The final recommendation is to remove maintenance history older than  four weeks old. Use the History Cleanup Task to delete records older  than four weeks for:

  • Backup and restore history
  • SQL Server Agent job history
  • Maintenance plan history

The Maintenance Cleanup can be added with the default values as shown below. You will need to choose the folder where you store the backups, and specify the back file extension (typically .bak).




See Also