Cygna Security and Compliance Database Maintenance

Cygna Security and Compliance solutions are data intensive. Managing the data and maintaining the datastore is crucial for the operation and performance of the applications. Our solutions offer multiple ways for you to manage that data. We reviewed one way in the blog post “We put you in control of your data – Database Segmentation” where you can chose which data is gathered, retained and accessed from multiple databases. We also discussed how you can keep only the data you want for compliance mandates or internal policy in the blog post “We put you in control of your data – Purging”. Our archiving feature also gives you a way to retain more data for longer periods while keeping your more frequently accessed data performant.

SQL Database Maintenance Plan

The following steps through how to configure the recommended maintenance plan for Cygna databases. The SQL Server Maintenance Plan Wizard is in Microsoft SQL Server Management Studio.

1. Right click on Maintenance Plans and select Maintenance Plan Wizard.

   

2. Select next to start the wizard.

   

3. Create a name and then set a schedule.

   

4. We recommend configuring a recurring job that runs weekly that does not overlap with any scheduled purging, archiving or backups.

   

5. Once the plan has been named and scheduled, select next to configure the plan properties.

   

6. Configure the following maintenance tasks: • Shrink Database • Rebuild Index • Update Statistics • Back Up Database (Full) o This task may not be necessary if it is already covered in another maintenance plan or 3rd party backup solution. o Depending on the organization’s risk assessment on the data and function of the Cygna applications, you may want to do additional backups such as daily incremental on every other day.

   

7. Keep the default maintenance task order and select next.

   

8. Select the databases for shrink database task. These should be the ones used by the installed Cygna products. Make sure the correct databases are set for each maintenance task in the plan in the subsequent steps.

   

9. Set the shrink database options: • Verify that the amount of free space to remain after the shrink is 10% • Space is returned to the operating system by not selecting the “Retain freed space in the database files” radio button.

   

10. Set the rebuild index task options: • Verify that the “Optimize index only if:” o Fragmentation is greater than 20% o Page Count is greater than 500 pages

    

11. Set the update statistics task options: • Verify the Update option is set to: o All existing statistics • Verify the Scan type is set to: o Full scan

    

12. Set the back up database task options to suit your organization’s needs.

    

13. Set the report options. • Select if you want a report text file saved to folder. • Select if you want a report emailed.

    

14. After the last step is done a progress window appears to show the status of each action in the maintenance plan. If this completes without errors the plan is put in place.

    

The maintenance plan for Cygna solutions outlined in this post are suggested as minimum guidelines and are subject to change as the Cygna solutions and Microsoft SQL server features evolve. Organizations may also want to consider SQL Server’s high availability and fault tolerance feature sets. Data management through purging and archiving combined with database maintenance is required to keep the system performing well. If none of these are put in place the system and its performance will degrade over time, possibly to the point of not functioning properly.