SafeGuard Enterprise: SQL Database Maintenance Best Practice

  • Artikel-ID: 113001
  • Aktualisiert: 22 Apr 2014

Issue
SafeGuard Enterprise: SQL Database Maintenance Best Practice

Known to apply to the following Sophos product(s) and version(s)

SafeGuard Management Center
SafeGuard Enterprise Server

Operating systems
All supported Operating Systems

What To Do

A. Fully-fledged Microsoft SQL Server 2008

1. Create a maintenance plan

Once you have installed Microsoft SQL Server Management Studio, started the application, and connected to the SQL instance hosting the SafeGuard database, you can begin to create your maintenance plan.

In the Object Explorer tree view on the left (see picture below), you should see the databases attached to the instance. The default name of the SGN database is "SafeGuard" but it might have been changed during the initial setup.

Once you have found the SafeGuard database, expand the “Management” folder. You can then right-click on the subfolder called “Maintenance Plans” and choose either: “New Maintenance Plan” or “Maintenance Plan Wizard”. For the purposes of this document we will use the Wizard which will guide us through the tasks. The following article gives an overview of the wizard: http://msdn.microsoft.com/en-us/library/ms189036.aspx.

Give the maintenance plan a name and choose whether you would like to create a schedule for each task or use the same schedule for all tasks. This is up to you and how you feel it is best to organize the tasks running on the server.

We recommend setting up Shrink Database and Reorganize Index tasks along with a Backup Task (customized to your needs). You may choose to do this for other databases in addition to your SafeGuard database.

More details on the maintenance options can be found on the Microsoft SQL Best Practice site: http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx.

Example:


 

2. Scheduled cleanup of the Event Table

In order to prevent the event table growing too big, a scheduled cleanup is recommended.

For an automatic and efficient cleanup of the EVENT table, four SQL scripts are available in the \tools directory on your SafeGuard Enterprise product CD:

- spShrinkEventTable_install.sql
- ScheduledShrinkEventTable_install.sql
- spShrinkEventTable_uninstall.sql
- ScheduledShrinkEventTable_uninstall.sql


The two scripts spShrinkEventTable_install.sql and ScheduledShrinkEventTable_install.sql install a stored procedure at the database server as well as a scheduled job which runs the stored procedure at defined regular intervals.

The stored procedure moves events from the EVENT table to the backup log table EVENT_BACKUP leaving a defined number of latest events in the EVENT table.

The two scripts spShrinkEventTable_uninstall.sql and ScheduledShrinkEventTable_uninstall.sql uninstall the stored procedure as well as the scheduled job and delete the EVENT_BACKUP table.

Details on how to setup, run, and customize these scripts, are available in the "SafeGuard Enterprise Administrator Help" manual and on the product CD.

 

B. Microsoft SQL Server 2008 Express (or older versions of SQL)

1. Create backups of the DB on a regular base

Either manually or using a scheduled task (example: "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe" -E -Q "BACKUP DATABASE safeguard TO DISK='C:\DBBackup\safeguard.bak'").

The following article provides an overview of the possible parameters of osql.exe:

http://msdn.microsoft.com/en-us/library/ms162806%28v=SQL.100%29.aspx

2. Rebuild indexes

If an Express version of SQL is used, or a maintenance plan as described above is out of scope, the attached script should be run on a regular base (at least in environments with more than 1000 objects in the DB and frequent changes in the ActiveDirectory). This can improve the performance of the SafeGuard Enterprise Server and Management Center.

Click here to download theAlterIndexAll.zip

 

 

 
Wenn Sie weitere Informationen oder Unterstützung benötigen, wenden Sie sich bitte an den technischen Support.

Artikel bewerten

Ungenügend Hervorragend

Anmerkungen