SafeGuard Enterprise: SQL Database Maintenance Best Practice
Known to apply to the following Sophos product(s) and version(s)
SafeGuard Management Center / Local Policy Editor
SafeGuard Enterprise Server
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.
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:
The two scripts
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
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 basis
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:
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 the AlterIndexAll.zip