This article explores the options Snow Commander 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, Snow Software® 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 Snow Technical Support to discuss. 

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

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

The images used in this article were taken from SQL Server 2014, but the general concepts and instructions continue to apply in SQL 2016. Later versions of SQL Server may have slightly different interfaces, but the concepts will continue to apply. 

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 Commander.

Beyond this and other solutions offered here, the growth of the database should not be restricted. Otherwise, if the maximum allowed space is consumed, Commander 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 Commander database:

1. Connect to the database server using SQL Management Studio.
2. Expand Databases. Right-click the commander 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 (commander.mdf):
   • Enable Autogrowth is checked
   • File Growth > In Megabytes: 1MB
   • Maximum File Size is Unlimited
6. Set the following for the log file (commander_log.ldf):
   • Enable Autogrowth is checked
   • File Growth > In Percent: 10%
   • Maximum File Size is Unlimited
7. Click OK, and then OK again.
8. Alternatively, run the following query to configure the settings as described above:
   

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

    

   Transaction Log Management

   Snow recommends the simple recovery model for the Commander 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 Commander, but event records of user actions in Commander 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. Snow 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 Commander 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 the zip file (files attached to this article) against the Commander 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 Commander DB and set the Owner. Typically, the owner will be the Commander 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 commander with the name of your Commander database, then click OK:
   • sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d commander -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'commander', @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 notification options as suitable. 
10. Click OK and then 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. Snow recommends that these tasks be scheduled at 03:00 in most environments, so as not to collide with the default Commander scheduled tasks. If you have scheduled tasks, such as 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:
4. Right-click in the Toolbox dialog and choose Show All.
   
   
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.
   
   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 commander SET AUTO_SHRINK OFF;
Null

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, Snow 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

Commander 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, Snow 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

• Commander Database Maintenance documentation for using the in-application maintenance schedule. This should be used in conjunction with scheduled maintenance in SQL Server.