This knowledgebase article contains our suggested best practices for maintaining the SQL Server database included in our Endpoint products. We suggest that you also refer to Microsoft's general SQL server best practice articles, which can be found at https://msdn.microsoft.com/en-us/sqlserver/bb671430.aspx.
With larger installations (over 3000 clients), we find that having all the management components installed on a single server can slow down operations in Enterprise Console. Of the server-side components that make up the Sophos management server, the three most likely to consume resources are (from left-to-right: most consumed to least consumed):
Moving the SQL process to a dedicated machine will give the Sophos Message Router and the Sophos Management Service more resources, resulting in faster message processing and a faster response time from Enterprise Console.
Other ways you can free up resources on the management server include:
If your company has a dedicated SQL DBA team managing your SQL Servers, it may be more appropriate to delegate the management of the database component to your DBAs as they should have dedicated hardware optimally configured for running SQL server. Also, existing maintenance software such as monitoring and backup could then be used to manage your Sophos database along with any other standard maintenance plans.
To sum up, a dedicated SQL server should make for a higher performing system and it is therefore worth considering using a dedicated SQL server when managing a larger installation (more than 3000 clients). Also, consider the demands to be placed on the server before installing Enterprise Console. If you will be managing one or two thousand clients on a server that also runs several other management programs, you should consider installing the database on a separate server.
Finally, if your company is required to comply with PCI regulations, you may need to install the database on a server that is dedicated to this role.
Currently the version shipped with Sophos Enterprise Console is SQL Express 2012 R2. This version is suitable for most deployments; however due to certain limitations or personal preference, other versions of SQL Server may be used. Please see http://www.sophos.com/products/enterprise/endpoint/security-and-control/management/sysreqs.html for details of the versions of SQL Server supported by Endpoint Security and Control.
The following Microsoft article might help you decide and compare the features of the various editions of SQL server: https://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/overview.aspx
The only real consideration for a Sophos installation is database size; fortunately, even SQL 2005 Express Edition's limit of 4GB should be ample storage space for the Sophos database.
With disk I/O being crucial to the performance of SQL Server, we recommend that where possible:
These practices will ensure disk I/O is as fast as possible; placing the files on non-system disks (disks that do not have the operating system installed) will further improve performance.
SQL Server best practices are described in a Microsoft article: https://msdn.microsoft.com/library/cc966412.
To ensure query times are kept as low as possible, and generally improve system performance, we recommend that you review and purge historic data periodically.
By default, the database is checked every 24 hours for Alerts, Errors and Events that are older than 12 months. These records are automatically purged. However, if the alerts or errors are outstanding, they are never purged. Therefore, we recommend that you regularly check and clear your alerts to optimize system performance.
You can change the defaults in Enterprise Console from the Tools menu | Configure Reporting | Purge tab.
To supplement the built in purge task, Enterprise Solutions also comes with a command line tool called PurgeDB.exe. Aside from purging events and alerts, this tool also allows you to purge machines that have not reported in to the management server within X number of days.
To ensure the database does not grow too large set the recovery model to Simple. For details on how to do this see https://msdn.microsoft.com/en-us/library/ms189272.aspx.
For details on why this option is set to prevent the transaction log files from growing unexpectedly see Microsoft article: https://support.microsoft.com/kb/873235.
Due to the constant flow of data between the Sophos Management Service and the SQL database, if a distributed installation is deployed, the network latency between the two should be kept to a minimum, this will help to ensure that Enterprise Console and the processing of messages runs smoothly.
It is worth noting that the load placed on SQL by the Sophos Management service will vary throughout the day depending on many variables, such as:
It is therefore worth considering this in line with any other demands on the network at similar times.
In an ideal world each SQL database for a single application would be hosted in a dedicated SQL server instance on dedicated hardware (as long as it does not cross-reference another database in the same instance). It is not always possible to do this, but if you are considering adding the Sophos database to an existing SQL instance, it is worth ensuring that the additional load on resources is accounted for.
In terms of Sophos products, the PureMessage for Microsoft Exchange and Sophos for Sharepoint databases are totally independent from the Enterprise Console database, so there is no need for them to reside in the same SQL Server instance.
We also recommend using the name SOPHOS for the instance of SQL that you use for the Enterprise Console database. This is because we have documented many alternative ways of performing operations on the database and we assume that a SOPHOS instance is used in all of the articles. When you install Enterprise Console, the installation program creates the SOPHOS instance for you automatically.
When configuring your SQL server, you should also consider our recommended practices with regards to exclusions. For more information see article 35970.
Regular database backups are an essential part of any database maintenance plan.
Using SQL Management Studio
Regular backups can be configured using a wizard in Microsoft SQL Server Management Studio (under Management | Maintenance Plans). If you are using the version of SQL Express that was installed by the Endpoint Security and Control installation program, you may not have this installed on your network. However, it is a free download from Microsoft and can be found by searching for “Microsoft SQL Server Management Studio”. At the time of writing SQL Management Studio Express 2008, 2012 and 2014 are available.
The next section “Creating a maintenance plan” explains some of the options you may wish to consider when creating a maintenance plan.
Using scripts installed with Endpoint Security and Control
Sophos Endpoint Security and Control comes with a couple of batch files which make it simple to take a full backup of the SOPHOS database and perform a restore. You can find these tools in the following directory on the server where the database resides, by default this is %program files%\Sophos\Enterprise Console\DB\
The filenames are:
To create a full backup you can run: BackupDB.bat SOPHOS.bak DBServer\SOPHOS SOPHOS521
Where: SOPHOS.bak is the resulting backup file. DBServer\SOPHOS is a combination of the database server name and SQL instance name. SOPHOS521 is the database name to be backed up. This is the database for Enterprise Console 5.3.1.
To restore the previous backup run: RestoreDB.bat SOPHOS.bak DBServer\SOPHOS SOPHOS521
Where: SOPHOS.bak is the backup file. DBServer\SOPHOS is a combination of the database server name and SQL instance name. SOPHOS521 is the database name to be restored. This is the database for Enterprise Console 5.3.1. Note: See article 17323 for a full list of database names by version.
You should schedule a regular backup and take backups after major changes to policies or where there are other significant changes in your network or group structure.
Note: Depending on which account your SQL Server service is running as, the directories it can write to when creating the backup might be limited. If you have any errors when taking a backup please use a directory which the account SQL service is running as can write to, e.g. %windir%\temp\.
Note: Due to the Express editions of Microsoft SQL Server not supporting SQL Server Integration Services (SSIS) maintenance plans can only be created when using a full version of Microsoft SQL Server.
Once you have installed Microsoft SQL Server Management Studio, started the application and connected to the SQL instance hosting the Sophos database, you can begin to create your maintenance plan.
In the Object Explorer tree view on the left, you should see the databases attached to the instance. Depending on the version of Enterprise Console you have installed, this might be SOPHOS521 (Enterprise Console 5.3.1) or SOPHOS540 (Enterprise Console 5.4.0). You might see more than these if you have upgraded from version to version over time as the previous database is not deleted after upgrade. If you are confident you can delete previous versions, you are free to do so and they can be deleted from within Microsoft SQL Server Management Studio.
Once you have found the Sophos database for the version of Enterprise Console that you have installed, 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: https://msdn.microsoft.com/en-us/library/ms189036.aspx.
Give the maintenance plan a name and choose whether you would like to create a single schedule for all tasks or use the same schedule. This is up to you and how you feel it is best to organize the tasks running on the server.
More details on the maintenance options available can be found on the Microsoft SQL Best Practice site: https://msdn.microsoft.com/en-us/sqlserver/bb671430.aspx.
Every comment submitted here is read (by a human) but we do not reply to specific technical questions. If you need technical support please post a question to our community. Alternatively for licensed products open a support ticket.