Breadcrumbs

How-to - Configure SQL Maintenance Plans on SQL database(s) for SQL based Source Systems Plan

Aim

The CI Sync Agent is a Windows Service that uses a SQL Server engine to track Assets that need to sync via CI Sync. Various SQL based source systems also leverage a SQL Server Database (e.g. The Lansweeper and SCCM Source Connectors).

This article guides the user on how to setup a Rebuild Index Maintenance plan on SQL Server to maximise performance via minimising index fragmentation.

This KB applies to:

Applies to Versions

All

Applies to Source Connectors

Lansweeper On-Prem, SCCM

Applies to Destination Connectors

All

Process

Important Note

Some versions of SQL Server SSMS 21 did not support Maintenance Plans - see the blog post below on how to address this.

Announcing the release of SQL Server Management Studio (SSMS) 21.2.5 | Microsoft Community Hub

This guide assumes:

  • SQL Server Management Studio (SSMS) is installed and setup

  • SSMS is running on the same server as the RecVer and Lansweeper databases

Step 1 - Setup a Rebuild Index Maintenance Plan

The scope of the maintenance plan should include the following:

  1. The CI Sync RecVer DB and

  2. Any other SQL based source systems being used by CI Sync (e.g. Lansweeper or SCCM)


  1. Login to SQL Server and open SQL Server Management Studio and expand Databases

    image-20250115-043054.png

  

  1. Expand Management then click on Maintenance Plans

    image-20250115-043116.png


  2. Right click on Maintenance Plans and click New Maintenance Plan

    image-20250115-043200.png


  3. Name the plan Daily Rebuild Index or similar and click OK.

    image-20250115-043227.png


  4. On the left of SQL click on Toolbox and expand Maintenance Plan Tasks

    Note: If you cannot see Toolbox then on the SQL menu click View then Toolbox.

    image-20250115-043257.png


  5. Click on Rebuild Index Task and drag it to the new plan

    image-20250115-043317.png


  6. Double Click on the Rebuild Index Task then:

    1. Drop down Databases

    2. Select the RecVer database

      1. If using Lansweeper, Syncfish recommend you also select the lansweeperdb database

      2. If using SCCM, Syncfish recommend you also select the sccm database

    3. Click OK

    4. Then again click OK

      image-20250115-043437.png


  7. Click on the Schedule and set to Daily and chose a Time of Day to run the job.  Then click OK

Choose a time that does not clash with Backups and other tasks scheduled on SQL Server.

image-20250115-043511.png

  1. Click Save. The maintenance plan has been setup.

    image-20250115-043656.png

Step 2 - Setup a Refresh Statistics Maintenance Plan

The scope of the maintenance plan should include the following:

  1. The CI Sync RecVer DB and

  2. Any other SQL based source systems being used by CI Sync (e.g. Lansweeper or SCCM)

  1. Login to SQL Server and open SQL Server Management Studio and expand Databases

    image-20250115-043054.png

  

  1. Expand Management then click on Maintenance Plans

    image-20250115-043116.png


  2. Right click on Maintenance Plans and click New Maintenance Plan

    image-20250115-043200.png


  3. Name the plan Daily Update Statistics or similar and click OK.

    image-20250326-065407.png

  4. On the left of SQL click on Toolbox and expand Maintenance Plan Tasks

    Note: If you cannot see Toolbox then on the SQL menu click View then Toolbox.

    image-20250326-065431.png

  5. Click on Update Statistics Task and drag it to the new plan

    image-20250326-065508.png

  6. Double Click on the Update Statistics Task then:

    1. Drop down Databases

    2. Select the RecVer database

      1. If using Lansweeper, Syncfish recommend you also select the lansweeperdb database

      2. If using SCCM, Syncfish recommend you also select the sccm database

    3. Click OK

    4. Then again click OK

      image-20250326-065550.png

  7. Click on the Schedule and set to Daily and chose a Time of Day to run the job.  Then click OK

Choose a time that does not clash with Backups and other tasks scheduled on SQL Server.

image-20250326-065611.png

  1. Click Save. The maintenance plan has been setup.

image-20250326-065625.png

  1. Refresh the Maintenance Plans and expand to confirm the new job appears in the list (i.e. the new job has been created)

image-20250326-065702.png

Step 3 - Confirm the CI Sync RecVer Database is in Simple Recovery Mode

Simple recovery mode prevents the creation of transaction log files which could impact disk space over time.

  1. Login to SQL Server and open SQL Server Management Studio and expand Databases, then

    1. Right-click on the CI Sync RecVer database (named accordingly when it was created as part of setting up a Source System Connection during Step 5) and select Properties.

    2. Click the Options page

    3. Check the Recover Mode of the RecVer database and make sure it’s set to Simple as the Recovery Mode (if it isn’t then set to “Simple”)

    4. Click OK

image-20250326-070033.png

Other Suggestions for SQL Database Health

Your SQL DBA may want to consider the following additional jobs.

  1. MSDB history clean-up

  2. Database integrity checks

  3. Job report file clean-up (i.e. a purge of report files created by the job)


See also:

Create a maintenance plan

There are currently no related articles.

Control Information

Created

Reviewed

Data Classification

PUBLIC
Classified in accordance with the Syncfish Data Classification Framework