VCM post-installation tasks - database fine tuning

VCM relies heavily on its SQL databases for operation. You must update the default settings in order to optimize SQL Server performance. We will create a maintenance plan for VCM databases.

Getting ready

Log in to SQL Server with an account that has SQL admin privileges.

How to do it...

We will perform the fine-tuning at three different levels, as detailed in the following subsections.

SQL Server - database settings

To ensure that VCM runs at peak performance and requires little operator intervention during its lifecycle, set up a routine maintenance plan. Take a look at the VCM Administration Guide.

Open SQL Server Management Studio and connect to the VCM SQL Server instance. Then, follow these steps:

1. Right-click on the SQL instance that you installed and select Properties.
2. In the Select a page area, select Database Settings.
3. Configure the following settings:
   • Default index fill factor: Set the fill factor to 80% in order to keep 20% free space available in each index page

   Tip

   Note: This sets a percentage value for the amount of free space in each index page when the page is rebuilt. Set the fill factor to 80% to keep 20% free space available in each index page. This setting is part of the SQL maintenance plan wizard. If you configure the default fill factor using this setting, keep space free in an index when you run a maintenance plan.

   • Recovery interval (minutes): Set the value to 5

   Tip

   Note: This configures the approximate amount of time that SQL Server takes to run the recovery process. The default setting is 0, which causes SQL Server to adjust this value and base the values on the historical operation of the server. In large environments, the recovery interval can affect the overall performance of VCM. Because VCM constantly updates how it interacts with SQL Server to process activities whose intervals differ, such as an inspection request and a compliance run, the server expends a lot of time constantly adjusting this value. By setting the recovery interval to 5 minutes, SQL Server no longer needs to tune this value.

4. Click on OK to save the settings.

SQL Server - maintenance plan

To ensure that VCM runs at peak performance and requires little operator intervention during its lifecycle, you must set up a routine maintenance plan. VCM relies heavily on its SQL databases for operation.

The maintenance plan uses the automated maintenance functions on the SQL Server instances that host the VCM database.

On the VCM SQL Server instance, follow these steps:

1. Click on Start.
2. Select All Programs | Microsoft SQL Server {version} | SQL Server Management Studio.
3. Expand the Management folder, right-click on Maintenance Plans, and select Maintenance Plan Wizard.
4. On the Maintenance Plan Wizard page, click on Next.
5. On the Select Plan Properties page, enter a maintenance plan name, select Single schedule for the entire plan or no schedule, and click on Change.
6. On the Job Schedule Properties - Maintenance Plan page, set the scheduling properties to run the maintenance plan when the SQL Server is idle or has low usage.
7. Click on OK to return to the Select Plan Properties page, and click on Next.
8. On the Select Maintenance Tasks page, select the following maintenance tasks and click on Next:
   • Check Database Integrity
   • Rebuild Index
   • Update Statistics
   • Clean Up History
9. On the Select Maintenance Task Order page, order the maintenance tasks and click on Next.
10. On the Define Database Check Integrity Task page, define how the maintenance plan will check database integrity:
    1. Click on the Databases drop-down menu.
    2. Select the following databases and click on OK:
       • VCM
       • VCM_Coll
       • VCM_Raw
       • VCM_UNIX

       Tip

       Note: You must select the VCM_Raw database, because it contains transient data that the other databases consume.

    3. Select Include indexes and click on Next.
11. On the Define Rebuild Index Task page, define how the maintenance plan will rebuild the index:
    1. Click on the Databases drop-down menu.
    2. Select the following databases and click on OK:
       • VCM
       • VCM_Coll
       • VCM_UNIX

       Tip

       Note: Do not rebuild the index for the VCM_Raw database.

    3. In the Advanced Options area, select Sort results in tempdb and click on Next.
12. On the Define Update Statistics Task page, define how the maintenance plan will update database statistics:
    1. Click on the Databases drop-down menu.
    2. Select the following databases and click on OK:
       • VCM
       • VCM_Coll
       • VCM_UNIX

       Tip

       Note: Do not update statistics for the VCM_Raw database.

13. On the Define History Cleanup Task page, define how the maintenance plan will clean up historical data from the SQL Server machine, and click on Next:
    1. Select Backup and restore history.
    2. Select SQL Server Agent job history.
    3. Select Maintenance plan history.
    4. Set the cleanup task to remove historical data older than 4 months.
14. On the Select Report Options page, save a report of the maintenance plan actions:
    1. Select Write a report to a text file.
    2. Select a folder for the report and click Next.
15. On the Complete the Wizard page, verify your selections in the Maintenance Plan Wizard summary, expand the selections to view the settings, and click on Finish.
16. When the Maintenance Plan Wizard progress has finished, verify that each action was successful.

How it works...

In this recipe, we tried to make sure that our SQL Server for VCM is performing optimally and we don't need much operator intervention for VCM maintenance.

We scheduled a maintenance plan to keep our database clutter free and help it perform better.