SQL Database Maintenance Recommendations for Protect

Version 4

    Purpose


    If you are at a company that is running Shavlik Protect on a full SQL environment and have a DBA on staff with SQL maintenance and backup policies already running against our databases, great!  If you are running SQL Express or full SQL but don’t have a maintenance and backup plan in place, please keep reading.

     

    A database that has no maintenance procedures being run against it is likely the single biggest cause of an upgrade issue that is encountered, the root cause of many GUI performance issues that can be mitigated, and in many cases, resolved by proactive maintenance on the database.  Below are our recommendations for good regular maintenance on your DB so you keep it running slim and clean for good performance and to reduce issues.

     

    Description


    Keep in mind this is a starting point.  If you have regulatory needs that require more data kept live you should adjust to keep more data live.  If that is the case you may want to analyze how frequently you are scanning.  1000 agents scanning 8 times a day will grow your DB at a much more rapid rate than once per day or once per week.  And in most cases, you don’t really need all of that data.

     

    Recommendations

     

    Recommendation for regular Database maintenance:

     

    Data Retention: Determine the amount of data that needs be kept on hand for operational purposes.  Typically 60-90 days is acceptable for operational purposes. The following document provides steps on how to perform deletion of old results in Protect:

     

    Shavlik Protect Database Maintenance - Purging or Cleaning Up a Large Database

     

    Reporting: Determine what report data is required for audit regulatory requirements.  Run monthly reports fulfilling these needs and keep on file as far back as policy requires.  Typically 13 months is acceptable.

     

    Database Backups: It is recommended to run weekly incremental and monthly full backups.  The backup should be run just before your scheduled purge.  Keep backups as far back as the reporting data. See the following document on how to create backups using Protect's database maintenance function:

     

    How to create a backup of the database with Protect

     

    This Microsoft Technet article covers how to create a database backup using Management Studio:

    (SQL 2012) http://technet.microsoft.com/en-us/library/ms187510.aspx

     

    Recommended Database Maintenance Schedule:

     

    Backups: full monthly, just after patch maintenance for that month.  Incremental weekly, end of each week (after weekend patch windows preferably).

     

    Purge Data: After Full Monthly backup is run

     

    Reindex: After Purge Data is run

     

    Integrity: After Reindex is run

     

    Full SQL Maintenance Guidance:

     

    If you are using full SQL it may be easiest to setup maintenance plans using the maintenance wizard. If you have a DBA, they have most likely set maintenance tasks up already and you should check with them first. See the following Microsoft Technet articles on how to use the SQL Wizard to setup and maintenance plan:

     

    (SQL 2012) http://technet.microsoft.com/en-us/library/ms191002.aspxhttp://www.networkworld.com/subnets/microsoft/110107-ch8-sql-server.html?page=2

    (SQL 2008R2) http://technet.microsoft.com/en-us/library/ms189036(v=SQL.105).aspx

     

     

    Additional Information


    Additional information can be found in Microsoft Technet articles, here: http://technet.microsoft.com/en-US/sqlserver/

     

    These Shavlik Community articles may also be relevant:

     

    Limitations when using SQL Express editions as backend for Protect

    How To: Shrink a Database

    Restore Shavlik database from backup using SQL Server Management Studio

     

     

    Affected Product(s)


    Shavlik Protect 9.x